Tablesum filter

[RobPolley]

I'm trying to calcuate the total charges for each family. The total will go into a calculated field in the "family" table called TotCharges. The values I need to add together are in the "lessons" table in a field called "balance" (It's not really a balance field, it's the charge for that one row).

When I hard code the filter, such as this: TABLESUM("lessons","family_num='006411' ","balance"), the balance is calculated correctly for that family. When I try to use a variable (taking the family number value from a field in the family table called "familynumber2", I get only the "Invalid or incomplete expression" error message. Here's my attempt to use a variable instead of hard-coding the filter:

TABLESUM("lessons","family_num= " ' + familynumber2 + ' " ","balance")

The "family_num" and "familynumber2" fields are both character fields.

I'd appreciate another set of eyes on this.

Thank you,

Rob

[RobPolley]

Re: Tablesum filter

Update: I had a mistake in the variable syntax (good thing Steve will have a session on syntax later today).

...but the tablesum total is 0 for every record, even when I use the "recalculate field rules" utility.

Thanks,

R

[Mike Wilson]

Re: Tablesum filter

Hi Rob,
I used to use the syntax you are using years ago, and was experiencing many difficulties consistently getting it correct and often making/missing mistakes.

TABLESUM("lessons","family_num= " ' + familynumber2 + ' " ","balance")

It was then suggested to me from a wise member on this board to change my syntax for filters to syntax that doesn't have so many ' and " that by the natural human visual condition are hard to pick out and separate. So I switched to the syntax suggested and since then my life has been so much better!

TABLESUM("lessons","family_num="+quote(familynumber2),"balance")

As it was moving from this:

dim t as p
t=table.open("families")
query.filter ="family_num="+quote(familynumber2)
query.order = "balance"
qry = tbl.query_create()
msgbox(t.balance)
qry.drop()
t.close()

To:

dim t as p
t=table.open("families")
xt=t.order("balance","family_num="+quote(familynumber2))
msgbox(t.balance)
t.close()

[MoGrace]

Re: Tablesum filter

Where are you doing this - in a form or in a report?

[Mike Wilson]

Re: Tablesum filter

Robin,
I believe he is trying to make a calculated field rule in his "Family" table that sums the values in the "balance" field in his table "lessons" for each family.

[RobPolley]

Re: Tablesum filter

In a dbf table using a calculated field.

[Ted Giles]

Re: Tablesum filter

I've been doing summat similar in a council app I'm building.
I took a different approach as I need a formal account of changes to expenditure. You cannot delete so must reverse an entry.
I'm using the Post Operation to add amounts +ive and -ive to a specific item in a budget vs expenditure table.

[RobPolley]

Re: Tablesum filter

Thanks, Ted. I've narrowed the problem. I can't get the id value from the current row into the filter. When I hard code it, it works, correctly. When I use the current() by itself, it shows the correct value, but when I use it as part of a filter, it shows 0. Here's what I think should work:

tablesum("lessons","family_num='current(familynumber2)'","balance")

The result is 0.

If I hard code the id (which is "familynumber2" in one table and "family_num" in the other (both character fields) I get the correct result.

If I try only "current(familynumber2) it returns the correct value for familynumber2.

I've also tried treating current(familynumber2) as a variable, using " ' +Current(familynumber2)+ ' ". No dice.

Thank you,

Rob

[Ted Giles]

Re: Tablesum filter

testtot = tablesum("amt","Fam = Var->vFam","Fees")

This works.
Expl

I set up a Calculated Field to test the logic.
amt = the cumulative value in the Fam (Family) table
Fam is the identifier
Var->vFam is set "on Fetch in the Form.
Fees is the cumulative fees for the Fam.

It would be easier for you to create a Form which captures the PK you want to use when a record is selected,
Create a Calculated Field on a Form which uses the PK in both Tables
Structure the filter as shown above.
"Fam = Var->vFam"

[RobPolley]

Re: Tablesum filter

Ted,

I appreciate your thoughts on this, but your answer confuses me. I thought the tablesum method required (the table, the filter, and the field to be totaled). I'm guessing that your descirption should have said that "amt" is the table?

In addition, I thought it would be possible for me to create an expression in the calculated field using field rules. Is that not correct?

Thank you,

Rob

[Ted Giles]

Re: Tablesum filter

Correct, amt is the table.
The issue you will have is that a calculated field in the table will need to be recalculated on a change in the other fees table.
It won't happen automatically.

Try creating a Calculated field on a Form which updates when a new record is selected.
Once you have this working you can use the logic in your Table field.
You really don't need a database field to hold the summation unless you really want to.

[Ted Giles]

Re: Tablesum filter

Here is an example.
Pull it apart and see what it does.

Recalculating the Family Total Fees in the Family Table won't happen unless the record is edited. Put a [.] after the name to see what I mean.
The Calculated Field on the Form should always be up to date though.
Family Name in the Fees is picked from the Family table for consistency.