Updating parent rec. based on child values.

[[email protected]]

Hi
I am trying to update a parent record status field to 'Completed' when all its child records have the status of 'Completed' set. The ideal scenario would be for this to happen immediatley when the final outstanding child record status is changed on screen by the user.
Also can anyone recomend any good books at this level?

I have had a look through the various forums but to no avail. If anyone can help it would be a real life-saver!
Thanking you in advance,

Darrel

[Stan Mathews]

RE: Updating parent rec. based on child values.

I think you'll have to script this for the ondepart event for the child record status field, possibly on the data entry maintenance form. The ondepart event would fetch through the related child records and if none were incomplete, it would write completed to the parent record.

You don't specify what would happen if a new child record was added or if a child record is changed back to incomplete. Have you condidered those possibilities?

[[email protected]]

RE: Updating parent rec. based on child values.

Thanks for the reply Stan. I am assuming (without any A5 knowlege!) that some sort of iteration of the child records would take place to check each records status and as such, if a status had been changed from completed or a new chilld record were to be added, they would be caught-up again in the itteration process.
I was trying to stay away from field rule pocessing as the table to which the child records belong is used by different screens.
I guess i'm looking for an example of referencing child records while maintaining the relationship of the parent. I have no experience of this sort of programming although I have worked with Db languages on the big 'ol mainframes.
Any pointers?

Thanks,
Darrel

[Stan Mathews]

RE: Updating parent rec. based on child values.

You really have four possibilities and only one of them involves checking other child records.

1. Child record is set to true (completed) and parent status is already true. - take no action
2. Child record is set to false and parent is true - set parent to false
3. Child record is set to false and parent is false - take no action
4. Child record is set to true and parent is false - need to check if any other child record is still false

this could be handled by setting the parent record to true and fetching through the related child records changing parent back to false if a false child record is encountered.

"I was trying to stay away from field rule pocessing as the table to which the child records belong is used by different screens." - I didn't mention field rule processing. You are allowed to code the "events" for a form such as departing/exiting the field as represented on the form.

[G Gabriel]

RE: Updating parent rec. based on child values.

Darrel:
Few ideas come to mind, the easiest would be to use "post":
a-If you enter a new record or modify an existing record's staus to incomplete (or blank or anything other than complete) it will post "Incomplete to the parent
b-If you modify the status to "Complete", it will so post to the parent.
Gabe

[Stan Mathews]

RE: Updating parent rec. based on child values.

"a-If you enter a new record or modify an existing record's staus to incomplete (or blank or anything other than complete) it will post "Incomplete to the parent" - he's already stated he didn't want to use field rules

"b-If you modify the status to "Complete", it will so post to the parent. " - if you modify the parent record based on one child record, what about child records that are not complete?

[G Gabriel]

RE: Updating parent rec. based on child values.

Stan:
This will work more like a switch that you turn on and off:
1-Let's start out with the parent record and no child records:
The status field in the parent record will be empty (or has something other than complete).
2-Now add a child record. The status field in the child record is either empty or incomplete. When you save that first child record, it will post "incomplete" to the parent's field.
3-Sometime later, you modified that first child record to complete, when you save the changes, it will post to the parent field "complete".
4-Now add a second child record, the status in this one is either empty or incomplete (anything other than complete), when you save it, it will post to the parent's field "incomplete".

You see how it's switching back and forth based on what you do with the child records?
Gabe

[G Gabriel]

RE: Updating parent rec. based on child values.

Oupssssss!
I just realized a glitch in this protcol !! it won't work. Sorry.
So now, off to plan B:
create a calc field in the child table: if(status="complete",1,0)
In the parent table, the status field will be a calc field:
if ( dbmin()=0,"Incomplete","Complete")
Gabe

[G Gabriel]

RE: Updating parent rec. based on child values.

...Or...
Plan C, this is a better one, still using "post" :
1-Convert the status field in the parent table to numeric
2-Whenever you add a new child record or change the child's status field to anything other than "complete", it will post -1 to the parent
3-whenever you change the status to "complete", it will post 1
4-Create a calc field in the parent: if(Status"0,"Incomplete","Complete")

This scheme assumes that, when you enter a cild record for the first time, the status field is either empty or incomplete but is not entered as complete right off. If that is the case, it won't work (unless you do some other modifications).
Gabe

[Stan Mathews]

RE: Updating parent rec. based on child values.

Here's an example of a form ondepart event triggering a change in the "parent" status field when all the "child" linked records have their status set to true. I use quote marks because the set used here is inverted. What would be a one parent record to many child records is set up so that many child records are linked one-to-one to the parent records.

Of course this is worth exactly what you are paying for it so when (not if) you find a problem with it......