Sum total in a text field on a form from linked child table records 2

[Steve-vfp9user]

Hello

I have a form with two tables, the child being ORDERS that is linked by ChildOrder=urnorder and RelationalExpr=urnorder. Linked order item have a unique number (example 550) in the urnorder N(10) field. This works as expected.

I did find this link:

http://www.tek-tips.com/viewthread.cfm?qid=1729040

but I've been able to adapt it.

I am aware of COUNT TO and also tried SCAN...ENDSCAN adding up the totals but as there 10's of thousands of linked orders this is taking some considerable time. I'm also aware that with reports you just change the field on the report to sum.

thisform.text1.value doesn't give any information about how to store totals from table in this scenario.

My question is, how can I sum total in a text field on a form from linked child table records from a field called TOTAL N(8,3)?

Any pointers would be appreciated.

Thank you

Steve Williams

 

[TamarGranor]

You'll have to hook the code in whatever changes to make you need to recalculate. I'll guess that the parent table is Customers or something like that, so you need to do the calculation when the record pointer moves in Customers. Since you don't say how you're showing that table, it's hard to know where that is, but regardless, I'd create a custom method to do the calculation and call it form anything that moves that record pointer.

As for how to do the calculation, SUM TO or SELECT SUM(xxx) should both be fast as long as you have the right index tag on Orders. Based on what you've said, you need a tag on URNOrder.

As for where to put the total, you can either simply push it into the relevant textbox:

ThisForm.Text1.Value = m.nMyTotal

or you can store it in a form property (say, nCustTotal) and set that property as the ControlSource for the textbox. Then, you just need to call the Textbox's Refresh method to update the display.

In fact, it occurs to me that you could call the code to do the calculation from the textbox's Refresh method. Then, when you do whatever changes things, you can just call the textbox refresh:

ThisForm.Text1.Refresh()

Tamar

 

[Steve-vfp9user]

Hey Tamar

I appreciate the post and suggestions which I'll look into and post back.

Thank you

Steve Williams

 

[JRB-Bldr]

how can I sum total in a text field on a form from linked child table records from a field called TOTAL N(8,3)?

Maybe I am over simplifying things, but unless the TOTAL values in the Child table are going to change while the user is in the Form, why not just run a SQL Query of the child table and accumulate the expression-specific totals on your Form.Load or Form.Init method?

Then as you change whatever you need to change on the form, the totals will be ready and waiting (result from a SEEK into your SUMData) for you to display.

Yes there would be 1 wait time while those totals were initially accumulated, but no wait each time they were needed.

Good Luck,
JRB-Bldr

 

[Steve-vfp9user]

The sum command worked. I hadn't heard of that before and found it in the help file.

Appreciate the quick reponse.

Thank you

Steve Williams