Processing related records

[Lawrence Fox]

Some background:

Employer_Benefits is a sort of "parent" table with several related "child" tables (AgeFactors, Benefit_Volumes, Drug_Cards, etc). For each record in Employer_Benefits there may be none, one or many in the children, depending on the type of the parent record (for example, Age Factors has information if the Benefit is Basic Life, but nothing if the benefit is Health. On the other hand, Drug Cards are irrelevant to Life Benefits).

I need to go create a procedure that will do the following and I'm not quite sure of the syntax:

For Employer_Benefits with some criteria (e.g., EmployerKey = 42001)

Select related records in AgeFactors
Do something to them or with them

Select related records in Benefit_Volumes
do something to them or with them

and so on....until all Employer_Benefits with a key of "42001" have been taken care of.

The linking fields between the two tables may not be the same (e.g., AgeFactors doesn't use the same key fields to link to Employer_Benefits as Benefit_Volumes).

I don't mind creating several procedures do this instead of one large one, but any pointers on the XBasic syntax or overall approach would be helpful.

I've been playing around with this for a while and I think I've been going about the wrong and inefficient way. It shouldn't be this difficult to grok and code :)

I need to do this several times throughout the application, btw.

As usual, thanks in advance to all suggestions.

[Tom Cone Jr]

Lawrence, I can think of two good ways to do this. They each involve separate processes for each child table.

1) run query against the child table, filtering it for records that contain the matching link field value. Then process them in a While / End While loop, fetching a new record at the bottom of the loop. Processing would end when <tbl>.fetch_eof() is true.

2) open the child table; set an index primary (based on the link field); fetch_find the first record with a matching key; then use a While / End While loop to step through the records, processing each as you go, until the key in the next fetched record doesn't match. The loop would continue while link field value in each fetched child table record equals parent table link field value.

-- tom

[Lawrence Fox]

Tom:

Could you post some sample pseudo-code for each approach? It would be greatly appreciated...

[Lawrence Fox]

Another (related) question:

Would building several sets each one containing the "parent" and the different "child" table and then running the select query against the parent accomplish the same sort of thing? How would I refer to the fields in the "child" part of the set?

(I get so confused by this stuff sometimes....).

[Tom Cone Jr]

Example1, using set based form

Lawrence, here's an example that illustrates how one might process the linked child table records using a set based form. Because the form has already identified those records which are linked to the current parent table record, there's no need to run a new query. You can process the related children by using a pointer to the instance of the child table which is supporting the form.

Unzip the attachment to an empty folder. Open the only form you'll find. Select a Vendor, then push the button. Then check the Trace Window. Then select a different vendor, push the button, and check the trace window again.

More to follow, when time permits.

-- tom

[Tom Cone Jr]

Example2

Lawrence,

Unzip the attachment to an empty folder.

I've added two more examples for you, both found in scripts on the Code page of the Control Panel.

One of them illustrates how to loop through and process the records in a query list your script creates, without using a set based form.

The other illustrates how to loop through and process records that match a specific search criteria after indexing them in a specific sequence, without running a query and without using a set based form.

-- tom

[Lawrence Fox]

Tom:

Thank-you very much. This goes way above and beyond the kind of support one gets elsewhere, I'll tell ya.

I will be downloading them later this evening for perusal and study. (Just got back from a client and have other stuffs to do first....)

[Tom Cone Jr]

Lawrence, these are necessary tools to have in our kit. I hope you'll holler if my code puzzles you.

BTW, I've put a copy of everything in the Code Archive forum, also, since it seems to me that this would be useful to lots of folks, and I couldn't find anything there that seemed to do the same sorts of things.

-- tom