Updating all child records when parent field value changes.

[preston2]

I tried to tackle this a few years back and got no where but there has to be away to do this.

Basically lets say you have a Parent table with numeric field for a multiplier.

The child records are shown on the form in a Browse Object.

The child table has a calculated field that needs to use the multiplier from the parent.

If the value in the Parent record changes I need it to also change all the child record calculation to reflect the change.

Attached is a simple example of what I am trying to achieve. You will see two fields named "Each" and Each2". One tries to pull the multiplier from the parent table and the other tries to pull it from a field that is supposed to be filled with the value of the parent field.

Confused yet?

[Ray in Capetown]

Re: Updating all child records when parent field value changes.

Remove the abt_items->MULT field rule, that's not going to work for later changes.
You can use the OnChange event on the form to recalc all children at the time of event change. Or CanSave for when adding new children - depending on your app.
Need suggested sample code for that?

[preston2]

Re: Updating all child records when parent field value changes.

Need suggested sample code for that?

If you are asking if I need a sample code, then yes. I have looked all over wiki and cannot find anything that works yet.

[Stan Mathews]

Re: Updating all child records when parent field value changes.

Preston,

Are you under the impression that the calculated field rules for each and each2 are somehow different? There are no set field rules, only table field rules. If you examine both expressions in the Expression Builder you will note that the fields from the header table are not available. As you are building a table level field rule for the abt_items table

ABT_ITEMS->COST*MULT
ABT_ITEMS->COST*ABT_ITEMS->MULT
COST*ABT_ITEMS->MULT
COST*MULT

are all effectively the same.

[preston2]

Re: Updating all child records when parent field value changes.

I think I have figured it out. Scrap all the field rule calculations.

Put a calculated field on the form to do the calculation of abt_items->cost*abt_header->Mult

Place variable in the Browse object on the form and tie it to the calculated field.

On the report, instead of using a Var in the Details just use a calculated field.

[Ray in Capetown]

Re: Updating all child records when parent field value changes.

If you want it to calculated ONLY on a report then that is all there is to it, as simple as it is.
This is what you asked

If the value in the Parent record changes I need it to also change all the child record calculation to reflect the change.

If you wish to change the records on the form when the parent value changes, without going into too much details, one reliable quite quick method

tbl = table.current(2)	' use the child table
nr=tbl.query_create("","abt_id="+quote(tbl.ibt_id))	' set the query
cend=nr.Records_Get()	'get the number of records (could use fetch_eof() if you dont require the number)

then as usual, loop either while .not. fetch_eof() or For count = 1 to cend make the change in the table then resynch the form.
As always, interested in any other view, this is the way I found works best.

[Stan Mathews]

Re: Updating all child records when parent field value changes.

abt_id is numeric so you don't need the quote(). Getting a pointer to table.current(2) limits the records to those linked to the current parent so the query should not be necessary.

That said, I wasn't able to make such a script operate correctly from a button on his form. Essentially the same script works in AlphaSports on the invoice form to change the value in the quantity field.

parentform.Commit()
tbl = table.current(3)
tbl.fetch_first()
while .not. tbl.fetch_eof()
	tbl.change_begin()
	tbl.quantity = 3
	tbl.change_end(.T.)
	tbl.fetch_next()
end while
parentform.resynch()

Didn't spend much time trying to find what's different in his set/form.

[Ray in Capetown]

Re: Updating all child records when parent field value changes.

neat and tidy.

[preston2]

Re: Updating all child records when parent field value changes.

At any rate I got things working the way I needed them too using the method I used in the Browse Test 2 sample.

That method does not really update the child table records and that is ok as a lot of fields I had in my app to hold data for calculations are no longer needed.

What it does do it display all the correct calculated values on the form and within the Browse Object that has the child table in it. And the calculations stick until you change some value that would affect the calculation values.

This is not a simple "price in a catalog" type app like the Alpha Sports Example is.

It takes a item cost and then modifies it 10 ways to Sunday that rely on total items in all the child records associated with the parent record. So as more Child Records (line Items) get added it changes the total item count which then changes every other calculation needed to get the sell price.

Anyway, it is working and pretty slick I might add.

Thanks for all the suggestions, they helped me out.

[MoGrace]

Re: Updating all child records when parent field value changes.

Stan,
Shouldn't your script also work on a set based form with

tbl = table.get("tblname") ?

I am trying to use this script in the TitleClick event for the browse and can't seem to get it to work...

Edit:
nevermind - I tried it again and it worked. Apparently my script needed the parentform.resynch() instead I was trying to refresh the browse itself, which is always problematic for some reason.

[Stan Mathews]

Re: Updating all child records when parent field value changes.

You are correct. Table.get() should/does work equivalently. I just use the methods I learned first. Table.get() is probably better as it will not fail if the set is restructured for some reason.