Creating Rows in a table based on another table

May 31, 200124 yr

Newbies

Dear FM Experts, Sadly someone has given me this job to do in FM5 and I need a little help.

What I need to do is (in SQL terms)

Insert into INVOICE_LINES(X,Y,Z)

as SELECT X,Y,Z from CALCULATIONS where BILL_ID = :BILL_ID

In other words, for each row in CALCULATIONS that has a BILL_ID equal to a known value, I want to create a matching row in INVOICE_LINES with the

same field values.

Can anyone point me in the right direction ?

Many thanks,

Peter

June 1, 200124 yr

If I understand you correctly, there are a couple of ways to do it. I would probably use an import script step.

First you would run a script in the CALCULATIONS file that finds the records you want to bring into the INVOICE_LINES file. If the value that you are searching for is a constant value, then you can perform the find manually, create a script that has a Perform Find [Restore] and when you run the script, those records will be the found set in CALCULATIONS.

If the BILL_ID that you search for isn't constant, but is based on other values, then you can script the find like this in CALCULATIONS:

Enter Find Mode

Set Field [ BILL_ID, your calculation here ]

Perform Find []

Once you have the records you want in the found set, go into the INVOICE_LINES file and perform an import manually, matching up the fields you want to bring the information from. Then create a script in INVOICE_LINES that has the Import Records [ Restore, Perform without dialog ] and use the Specify File button to specify your CALCULATIONS file.

Now you need to run both scripts. If you're starting this process from CALCULATIONS, then at the end of the script (after the Perform Find line) have a Perform Script [ Sub-scripts, External: INVOICE_LINES (Import Records) ].

If you're starting the script from INVOICE_LINES, then at the beginning of the script (before the Import Records line) have a Perform Script [ Sub-scripts, External: CALCULATIONS (Find Bill_ID) ].

And, if you're starting the script from a third file, then use the Perform Script to perform both externals, first the CALCULATIONS and then the INVOICE_LINES script.

Chuck