Create child records from found set

[pnelson]

I'm looking for a streamlined way to script the creation of child records for a found set of records. Example: Perform a manual find on Customer table which yields a found set of Customers. Using that found set, automatically create one child record for each parent record (i.e., for each Customer, create a new related transaction/line item record. The script would end showing a list of all the child records created and their parent IDs.

Apologies if there is a thread to this effect already - I couldn't find it.

[comment]

Well, you could simply import the found customers into the Transactions table - though I would ask why would you want to do this, instead of entering 'real' transactions only when they occur.

[pnelson]

I'm not looking to export/import if I can help it. As a further example, I find 100 customers and plan to contact each one of them with a piece of mail (that Filemaker will generate and each piece will ultimately be customized to the individual customer record). So I want to automatically create a hundred transaction items all of which will be automatically created in with basic record content from the parent record, coded as a mailer transaction type, and then each individual transaction item will be further added to and customized one at a time as/if needed before they are printed and mailed/faxed/emailed or whatever.

[Fenton]

Comment said "import," not "export/import". Unless you meant, "I don't want to export or import." In which case you'd need a Loop. There are a couple ways to create child records in a Loop.

1. [x] Allow creation of records via a relationship, setting some field other than the key field.

Or

2. Set a global (or Variable) to the parent ID, go the child layout, create a record, set the parent ID foreign key, return to parent layout

Both: Go to next record (exit after last), commit records.

Edited April 10, 2008 by Guest

[comment]

Ahm, wouldn't the first method create a FIRST child only - then keep modifying it?

I still think that if "each individual transaction item will be further added to and customized one at a time as/if needed", it might be preferable to create it at that time, and avoid the risk of having transactions created wholesale but never actually used.

[Fenton]

Oops. Yeah, if you wanted to use #1 you'd need to do it in a portal, go to field, go to portal row [last], set related field. That's why you should use #2.

[Søren Dyhr]

Ahm, wouldn't the first method create a FIRST child only - then keep modifying it?

No you can pollute a creator key with a type mismatch that forces a typecast thru, that makes the disconnection.

--sd

test.zip

[pnelson]

Thanks for the help, but the way I have solved this is as follows. First off, I used some of the techniques offered up by Dwayne Wright on his blog post entitled

Found Set in a Portal 2

What I did was create a portal for displaying all the records in a given found set. I added some global fields to count the number of records in the found set, and then created a loop script that basically just increments the counter as it moves through each record in the found set. During each pass it creates the child record for the parent ID that it is currently on. This seems to work OK and has the desired result. Everything is visible on one screen so the user is aware of all that is happening.

[Søren Dyhr]

Tampering with the clipboard as Dwayne does, is not fully embraced, except when sufficient measures exist to save what the user might have in his/her clipboard. Unfortunately are there exceptions to what you can unload into a container field for later restoration.

I would suggest you study the various methods found here:

http://www.filemakerpros.com/GetNthRecordTAIL.zip

....as well as the description found inside the template!

--sd

[dwins]

A similar problem solved by the Fenton #2. 100 businesses that sponsor an ad are all billed at once. Scrip, here shortened, creates one ORDER for each ACCOUNT (child and parent).

Go to Layout ORDERS

Show All Records

Show Omitted Only //this hides all records so in the end you view only the new ones

Go to Layout ACCOUNTS

Perform Find //to get the 100 parent records you need.

Go to Record First

Loop

Set Field gHolder;Pkey //you'd use variable here

Go to Layout ORDERS

New Record

Set Field Fkey;gHolder

Set Fields with the data you want on the record

Go to Layout ACCOUNTS

Go to Record Next Exit after last

End Loop

Go to Layout ORDERS

Data for the records is held in proxie globals. Accounts to be Billed are checked "X," then changed to "B" in the Loop. For safety the script will exit if it hits a B. B's are kept for further script action then cleared with a button.

[pnelson]

This is basically what I ended up doing. Plus, I am aware of the dangers of using the Copy All to clipboard command, but that is implemented as a separate way to simply view the found set in a portal and since the found sets are likely to be small, I'm not expecting it to be a problem.