tablesum filter problem

[eeetee]

hi again:

i'm having difficulty filtering my tablesum to a variable. if i substitute 4110 for var->account_pointer it does give me the total for credits and debits. using the variable it returns a zero balance for both credit and debit balances. what is it that i'm not understanding?



account_pointer="4110"

credit_total=TABLESUM("g_l_temp.dbf","account_num = 'var->account_pointer'", "credit")
debit_total=TABLESUM("g_l_temp.dbf", "account_num = 'var->account_pointer'", "debit")


thanks again!!

regards,

ed

[Bill Warner]

RE: tablesum filter problem

Ed, it should be:

"account_num = var->account_pointer"

and not

"account_num = 'var->account_pointer'"

The expression reads anything within ' ' as a text string.

By the way, I have found that TABLESUM can be quite slow in certain cases (i.e., if you want the sum for a lot of records). I usually use DBSUM instead, because of that.

[eeetee]

RE: tablesum filter problem

hi bill:

thanks for your reply. i had tried that (just verified again) and i get the following error:

filter expression not logical

the table g_l_temp is filtered then packed and only had a small amount of records in it. the g_l_temp is the result of a join so any index i create gets clobbered when the join runs. i suppose that i could use tbl.index_add but i thought that the tablesum should work.

thanks again, bill!

regards,

ed

[Tom Cone Jr]

RE: tablesum filter problem

Ed,

I've posted a working example that shows how one might use either dbsum or tablesum and filter expressions which are based on a variable, instead of filter expressions based on a literal character string.

Check the 'Filtered Sums Example' in the Code Archive.

In the process of developing this, I discovered (rediscovered, actually) that the scope of the variable used in the filter expression is important. If it's dimensioned locally, and then used in the filter expression it appears to get reinitialized when the dbsum and tablesum functions do their magic. Got much better results if it's dimensioned 'shared' to begin with.

Hope this helps.

-- tom

[Jay Talbott]

RE: tablesum filter problem

Ed,

What might work even better than table sum would be:

initialize two variables to 0
filter the table using a query,
fetch the first record,
make the variables =previous value+debit (or credit) amount
fetch the next record
repeat until end of file.

This will be much quicker as the table grows.

Regards,

Jay Talbott

[eeetee]

RE: tablesum filter problem

hi tom:

thanks for your reply and your example. it appears that the plus signs surrounding the variable is what i was missing. where are they documented?

regards and thank you again,

ed

[eeetee]

RE: tablesum filter problem

hi jay:

this is what i did in the original code. i thought that the dbsum may be faster. i got it to work using dbsum and adding an index tag but if the record by record into an accumulator is faster in large tables i would switch back.



tbl2=table.open("view earnings") 'fetch the abbreviated chart of accounts
query.order="account_num"
query.filter=""
qry2=tbl2.query_create() 'sort by account number
tbl2.fetch_first()
while .not. tbl2.fetch_eof()
account_pointer=tbl2.account_num 'fetch the account number
credit_total=DBSUM("g_l_temp.dbf","account",var->account_pointer,"credit") 'account_num = var->account_pointer", "credit")
debit_total=DBSUM("g_l_temp.dbf", "account",var->account_pointer,"debit") 'account_num = var->account_pointer", "debit")
tbl2.change_begin()
if account_pointer

[Jay Talbott]

RE: tablesum filter problem

It is very well documented here:

http://www.learn alpha.com/QuerySyntax/QuerySyntaxGlossary.htm

written by our person friend (who, I still insist, is too nice of a gentleman to be in the profession he is in).

Print the article out, read it and remember it.

Jay

[eeetee]

RE: tablesum filter problem

thanks jay!

i'll have a gander.

regards,

ed

p.s. i wonder what the good doctor does in his spare time (as if he has any).

[whanigsberg]

RE: tablesum filter problem

It *is* an old profession.

[Jay Talbott]

RE: tablesum filter problem

It is written by Tom Cone, who is not a doctor.

Neither is he an Indian Chief.

Jay

[Tom Cone Jr]

RE: tablesum filter problem

I'm glad you didn't say 'oldest'!

-- tom