How to handle moving data between 2 FM files...???

[angelleye]

I've got a file called Invoices and another file called InvoiceItems. Of course, InvoiceItems holds the items in each Invoice and they are related by InvoiceNumber.

I've got a script setup to create a credit memo for the current invoice. I was able to use FM script variables to store invoice data and then insert it into the new credit memo. However, I cannot do that with the InvoiceItem data because it's in a different file.

I need to loop through all InvoiceItem records that relate to the original invoice and add them to the credit memo.

Any information on how I can accomplish this would be greatly appreciated. Thanks!

[Brett W]

One cool trick I was taught is instead of having to use a loop to set information from one table to another, which can get confusing, simply import records from one Table Occurence to another. Bear with me for a minute. So you find the Invoice Lines that you want to create in the Credit Memo Line table, best way is to use a go to related script step from the found set of Invoices. After you have that found set, open a new window in the credit memo line items. From the CM Line Items, set up an import script step from the Invoice Line Items mapped to the CM Line items table, then simply import the records.

If all goes well and the mapping was correct, you will have your related lines, much faster than a complicated looping script. Best to make a key field that relates to the Invoices from the Credit Memo and set that in the import, then build a relationship based on the Credit Memo Invoice foreign key. The Credit Memo lines will relate back to the credit memo through the Invoice #, instead of a Credit Memo number.

Might seem a little complicated at first, but trust me, once you get the hang of this idea this is the way to go.