Hey all:

Is it possible to insert new records into table_A from inside a loop of a found set of table_B, using data from the found set of table_B to populate fields in table_A?

Example: I want to loop through a found set (table_: of clients to whom invoices should be sent. For each distinct client I want to create records in an Invoices table (table_A) that contains the current invoice number and the IDs of all line items for that distinct client.

So my Invoices table would look like:

Inv_ID | InvNum | LI_ID

1 1001 719

2 1001 721

3 1001 725

4 1001 726

5 1002 122

6 1002 124

... etc.

But if I am looping the clients table in the script, and I need to shift to the Invoices table inside the loop, will I lose my position in the Clients table when I transfer back to that table for the next pass through the outer loop?

I hope that makes sense?

Thanks

Kurt

Strictly answering your question: No, you wouldn't lose your place in the Clients table. Where you are in one table doesn't affect another table, unless you navigate back in a way that moves the current record.

But what is it you really want to do? If you have line items, which you are then going to move, what do the line items belong to start with? Line items implies they belong to something already.

If you are moving many line items at once from one table to another, the fastest way is to Import, not to Loop. You have to be careful to have the correct found set of items, and be on the correct layout to import into. (And have version 7.0v3)

But how then do you get a field which is new, yet common to all of them, such as a ClientID? (You may ask :-)

My method is to set that data into a field with global storage in the line items table first, then import it into a regular field, while importing the line items. Since the global is the same for all records, it imports the same for all. The operation has to be scripted so that you're sure that global is set to the correct ClientID first.

Hello again and thanks, Fenton.

Yeah, I forgot to include the clientID in my exampl, but do carry it over in reality.

To be direct, I know this is a stupid way to do this. But I am modifying a solution that already exists and this was the best way I could see to create invoices from already existing job data that had never been invoiced.

I don't know if it matters, but at any given time there should only be up to 12 records per client, so I don't think a loop should be too dangerous, but I am looking at your import idea.

Thanks

Kurt