Tablesum question

[John Gamble]

Trying to define a Parent field rule to sum the value of a field in its child records.

tablesum("HSDETAIL",".T.","NO_KEYS" yields sum of all "NO_KEYS" in all child records.

Assume I have to somehow substitute ".T." with the value of the current parent record. But how ?

Suggestions appreciated.

John

[Ohlen Cartmell]

RE: Tablesum question

Yes, you need to substitute an expression that evaluates to true for the records you want totaled. If the linking field in the parent and child is called link it would be something like:

tablesum("HSDETAIL","link=parent->link","NO_KEYS")

Another way to do this is to index the child by the linking field and use the dbsum expression.

[John Gamble]

RE: Tablesum question

Ohlen

Thanks for your response, tried the following but got
"filter expression not logical"

tablesum("HSDETAIL","S_D_K=HSMASTER->S_D_K","NOKEYS")

S_D_K is the linking field. If I remove "HSMASTER->" I get the sum of all NOKEYS for all child records.

Have I misinterpreted your example ?
Thanks, John

[Ohlen Cartmell]

RE: Tablesum question

I think you interpreted it exactly. Try:

tablesum("HSDETAIL","S_D_K=+HSMASTER->S_D_K+","NOKEYS"

to try to force the expression to evaluate the value of the field in the parent table.

[Tom Cone Jr]

RE: Tablesum question

John, are you sure you want to do this in a table level field rule for records in the parent table?

Are you expecting the value to be updated for you automatically as new child records are added? If so, I think you'll find that it won't work that way, since the parent table won't have any way of knowing about data entry into the child table. Be sure and let me know if you get it going. I'll be surprised, but it won't be for the first time!

There are two other ideas which you might want to consider:

1) Define a table level field rule for the child table. In that rule, 'post' transaction data from each new child record to a field in the parent table. By 'adding' the transaction data to the parent table field you can keep a running total pretty easily.

- or-

2) Use a form level calculated display field to track the running total of all related child records. At key points, as for example, whenever a new child table record is saved, arrange for a short script in the form to update the desired field in the parent table.

-- tom

[John Gamble]

RE: Tablesum question

Ohlen,

Tried TABLESUM("HSDETAIL", "S_D_K=+ HSMASTER->S_D_K+","Nokeys")

Error "Expected Value"

Tried TABLESUM("HSDETAIL", "S_D_K=+ HSMASTER->S_D_K","Nokeys") (Removed 2 nd + "

Error "Argument is incorrect data type".

Can you think of anything else ?

Thanks, John

[John Gamble]

RE: Tablesum question

Tom

Thanks for the response.

Like the sound of option 1. However I am unable to get TABLESUM or DBSUM to work for an individual group of child records. Will work fine to total all records. The problem appears to be defining the "FILTER" in Tablesum to yield a true result. Any suggestions ?

PS. Seperate issue, when doing an append I sometimes get error "read past end of file" on one machine, yet on another works tickity boo. (Same data both mahines" ??

Thanks, John

[Tom Cone Jr]

RE: Tablesum question

Option 1 would not use dbsum() or tablesum(). It would be a simple post from new child records to the parent table.

-- tom

[John Gamble]

RE: Tablesum question

Thanks
Will try
John

[Ohlen Cartmell]

RE: Tablesum question

John,

Here's how I got this to work in my app. I defined a calculated field at the set level (not a field rule calculated field) and used this expression.


dbsum("charges.dbf","charges_by_type",tranno,"fee")

where charges.dbf is the child table, charges_by_type is the index, tranno is the linking field, and fee is the field to be summed. I didn't try this on a field rule calculated field. As I recall, I tried the tablesum expression in a calculated field and in a calculated field rule field and, despite my previous post, could not get a filter defined either.

[John Gamble]

RE: Tablesum question

Tom
Works like a charm when adding detail records. Thank you

However I'm still left with the problem of updating the master records with the count of all the existing child records. I assume I will have to use Tablesum or Dbsum but I can't seem to get either to work.

Any words of wisdom

Thanks, John

[John Gamble]

RE: Tablesum question

Ohlen
Tried your suggestion but unfortunately the result is 0. May haps I've missed something. Will keep trying.
Thanks for your efforts.
John

[Tom Cone Jr]

RE: Tablesum question

Why not define an update operation and use an expression that totals the child table values? Run it once. Then, from that point on, using the posting field rule in the child table to update the parent table record as changes occur in the child table.

My point, in the earlier message, was that I had misgivings about trying to use tablesum() in a parent table field rule, as you were attempting.

By the way, when working on new operations it is prudent to 'develop' the new operation while working on a throw away copy of your real data. Don't run an operation against your real data until you are sure it will work as you expect. Some of us are missing a few toes, because we shot ourselves in the foot, doing new operations on our actual data. This usually happens when we think we know what the heck we're doing, and have decided that testing on dummy data would be a waste of time. Not!

-- tom

[John Gamble]

RE: Tablesum question

Tom,

Did it & it worked. Thanks so much. One less problem to worry about. Based on similar past experiences, I always backup my application prior to shooting myself.

John

[Tom Cone Jr]

RE: Tablesum question

Congratulations!

-- tom