pass table name to a function

[davidv43]

Is there a way to pass a table name to a function, which then opens a table based on that name? Here's what I'm trying to use, but I keep getting an error when I execute the script on the line where i have underlined:

FUNCTION frequency AS F (itbl AS C, ifld AS C )

dim tbl as P
dim outlist as U
dim keys as U
dim k as C
dim num as N
dim qry as P


tbl = table.open(itbl,FILE_RO_SHARED)
num = tbl.records_get()

'query.filter = unique_key_value()
query.order = "VAR->ifld"
query.options = "U"
debug(1)

qry = tbl.query_create()

num = qry.records_get()
qry.close()
tbl.close()
dim temparr[num] as P

temparr = initialize_from_table(itbl)
j = temparr.first_empty()

for i = 1 to j
k = temparr[i].ifld
num = tablecount(s_quote(itbl),s_quote(ifld) +" = " + s_quote(k))
next i

query.filter = ""
query.order = ""
query.options = ""



END FUNCTION

[CALocklin]

Re: pass table name to a function

It would help if we knew the actual error message but I believe your problem is here:

query.order = "VAR->ifld"

This is telling the query to sort the results by a field called specifically Var->ifld

If you want it to sort by a field name that is stored in the "ifld" variable, I think you can just remove the quotes.

query.order = VAR->ifld

or, depending on your preference, simply:

query.order = ifld

[davidv43]

Re: pass table name to a function

Cal,

it fails at the line I highlighted above with a message "No such field"

I used the debugger to examine the num variable which correctly shows 147 records. I tried commenting out the query parameters and that did not solve the problem, either.

Thank you!

[Peter.Greulich]

Re: pass table name to a function

it fails at the line I highlighted above with a message "No such field"

David,

It fails at the qry = tbl.query_create() because query.order = "VAR->ifld" is just a harmless variable assignment (query.order is the variable). But when you try to pass the nonsensical value "VAR->ifld" to the query command [tbl.query_create()], she breaks at that point. As Cal said, Alpha thinks you want to sort a field called VAR->ifld, which, of course, doesn't exist.

[CALocklin]

Re: pass table name to a function

Did you try just removing the quotes as in my initial suggestion?

Also, it may be that query.filter has been set somewhere else. Try setting it to
query.filter = ""
rather than just commenting it out. If you comment it out, it will use whatever value it was last set to.

This works fine on my system: (I used fetch_last() in my test because there are blank last names in the table. My initial tests did return <blank> but I prefer seeing an actual value.)

ifld = "Last_Namef"
 
tp = table.open("customer")
query.filter = ""
query.order = VAR->ifld
query.options = "U"
ndx = tp.query_create()
tp.fetch_last()
ui_msg_box( "RESULT", tp.Last_namef )
tp.close()

This doesn't give me an error (which seems rather odd) but it doesn't set the order properly either. It simply uses the record number index:

ifld = "Last_Namef"
 
tp = table.open("customer")
query.filter = ""
query.order = [B][COLOR=red]"[/COLOR][/B]VAR->ifld[B][COLOR=red]"[/COLOR][/B]
query.options = "U"
ndx = tp.query_create()
tp.fetch_last()
ui_msg_box( "RESULT", tp.Last_namef )
tp.close()

FWIW: Setting the options to "U" or adding unique_key_value() to the filter should both yield the same results.

[davidv43]

Re: pass table name to a function

thank you guys... when I added the query.description variable, it now works, or should I say, doesn't fail.

My goal with this function is to call a function:
FUNCTION frequency AS F (itbl AS C, ifld AS C )

This function should take the table (itbl), look up each unique value in ifld (a user-defined field) and generate a CR-LF list of each occurence of the value of ifld and the number of times it occurs in the database.

For example:

Smith
Able
Wilson
Smith
Thomas
Jones
Smith
Thomas


should generate:
Able 1
Jones 1
Smith 3
Thomas 2
Wilson 1



Is there a simpler way to do this? It seems like this would be a common requirement.

Thanks!

Dave

[davidv43]

Re: pass table name to a function

as I work more of this function, i hit another snag...

can arrays be accessed in a for ... next loop?

for 1 = 1 to 10

[davidv43]

Re: pass table name to a function

as I work more of this function, i hit another snag...

can arrays be accessed in a for ... next loop?

for i = 1 to 10
k = myarray[i]
next i


dave

[Peter.Greulich]

Re: pass table name to a function

can arrays be accessed in a for ... next loop?

Yes.

Maybe you want something like this?

tbl = table.open(itbl,FILE_RO_SHARED)
num = tbl.records_get()

'other code here...
    
Dim temparr[num] as P
for i = 1 to j
    k = temparr[i].ifld
    'do something with k (otherwise k keeps changing w. each iteration)
next i

[davidv43]

Re: pass table name to a function

that would seem to be the way to go, but I get an error "property not found" temparr[] not found.

Since the actual property name isn't known (it comes from the variable 'ifld'), I tried the syntax

k = temparr[i] as well as k = eval(temparr[i].ifld)

I don't find any reference in the help manual that arrays can be referenced by a variable (i), nor that properties can be referenced by a var.

[davidv43]

Re: pass table name to a function

Peter,

do you have any other method to accomplish this?

My goal with this function is to call a function:
FUNCTION frequency AS F (itbl AS C, ifld AS C )

This function should take the table (itbl), look up each unique value in ifld (a user-defined field) and generate a CR-LF list of each occurence of the value of ifld and the number of times it occurs in the database.

For example:

Smith
Able
Wilson
Smith
Thomas
Jones
Smith
Thomas


should generate:
Able 1
Jones 1
Smith 3
Thomas 2
Wilson 1



Is there a simpler way to do this? It seems like this would be a common requirement.

Thanks!

Dave

[Peter.Greulich]

Re: pass table name to a function

Make sure that you have dimmed the array as per my example.

k = temparr[i] doesn't do anything.

k = temparr[i].ifld does - assuming that you have prev. assigned a value to that variable.

[davidv43]

Re: pass table name to a function

here's the current working version... i think i did it your way

tbl = table.open(itbl,FILE_RO_SHARED)
num = tbl.records_get()
query.description = "temp"
query.filter = ".t."
query.order = ifld
query.options = "U"
qry = tbl.query_create()
num = qry.records_get()
dim temparr[num] as P
temparr.initialize_from_table(tbl,".t.",ifld)
j = temparr.first_empty()

for i = 1 to j-1
k = temparr[i].ifld
num = tablecount((itbl),ifld +" = " + s_quote(k))
next i

query.filter = ""
query.order = ""
query.options = ""
qry.close()
tbl.close()

[Peter.Greulich]

Re: pass table name to a function

I forget the syntax, but i think you are initializing an array w. ALL the table records, but you are dimming it with a query value. You're also reusing "num" in seemingly diff. contexts - i would use diff. variable names. Again, both k & num will change value upon each iteration. Maybe you want to put in a crlf() list as you said above? - then:

k = k + crlf() + temparr[i].ifld
num = num + crlf() + tablecount((itbl),ifld +" = " + s_quote(k))

Make sure you dim k & num first...

[Tim Kiebert]

Re: pass table name to a function

Peter,

do you have any other method to accomplish this?

My goal with this function is to call a function:
FUNCTION frequency AS F (itbl AS C, ifld AS C )

This function should take the table (itbl), look up each unique value in ifld (a user-defined field) and generate a CR-LF list of each occurence of the value of ifld and the number of times it occurs in the database.

For example:

Smith
Able
Wilson
Smith
Thomas
Jones
Smith
Thomas


should generate:
Able 1
Jones 1
Smith 3
Thomas 2
Wilson 1



Is there a simpler way to do this? It seems like this would be a common requirement.

Thanks!

Dave

Try this

Function GetCountsOfValues as C(iTbl as C,iFld as C)
    dim valuelist as C
    dim countlist as C
    valuelist = table.external_record_content_get(iTbl,iFld,iFld,"unique_key_value()")
    countlist = *for_each(valu,valu +" - " + tablecount(iTbl,iFld + "=" +quote(valu))  ,valuelist)
    GetCountsOfValues = countlist
End Function