Moving field values from a found set of records to another table

[jf4026]

Greetings, I have posted this problem on other forums, but haven't gotten any help. I have a scripting problem in a bookkeeping Database that can be explained in simpler terms by thinking of a typical invoice structured DB. Let's say InvoiceDB, Line_Items, and ProductDB Tables. Typical Line_Items portal data entry from an InvoiceDB layout. ProductDB has a field that classifies related products into classes, but not all products have to be classified. I am trying to write a sctipt that sets a class of products into the Line_Items portal (table) as separate items, were 1 or more products could be deleted (instead of entering items one at a time). I am closing in on a solution with "set field", but I am only getting 1 item to migrate over to the Line_Items table (I need a found set of records moved to the Line_Items Table all at once). Any help would be appreciated.

[comment]

I am having trouble understanding this part:

I am trying to write a sctipt that sets a class of products into the Line_Items portal (table) as separate items, were 1 or more products could be deleted (instead of entering items one at a time). I am closing in on a solution with "set field", but I am only getting 1 item to migrate over to the Line_Items table (I need a found set of records moved to the Line_Items Table all at once).

Do you mean you want to select a class of products - say "Class A", containing 6 individual products - and have the script create 6 new line items for those 6 products?

ProductDB has a field that classifies related products into classes

Is there also a Classes table?

Edited October 15, 2011 by comment

[jf4026]

Yes, I need to move 2 field values (ProductDB::ProductID, and ProductDB:Price) from a found set of records (a class of products) into new records created in the Line Items database, instead of creating them 1 at a time through the portal in the InvoiceDB layout form. No, there is no classes DB, only a field in the products DB.

[comment]

from a found set of records (a class of products)

I presume this found set is in the Products table? How exactly does it get created - and when? IOW, is there already an invoice record to which the line items need to be added?

[jf4026]

I'm sorry if I haven't been clear. Yes, there is an invoice where products, from the Products table, are entered one at a time, into the Line_Items Table, from a Line_Items portal, on the invoice. I would like to have multiple products entered into the portal all at once, instead of one at a time.

[comment]

You still didn't say how you'll select/find those products. Perhaps something like the attached can work for you.

AddAllInCategory.zip

[jf4026]

Thanks! The Loop statement is what I was missing. I had already written the scripts for finding, then choosing from the found set, the desired records that needed to be moved. You have set me in the right direction. Please, what is your name?

John

[bcooney]

btw, you're not moving any data. You are populating a line items table with foreign keys.

[jf4026]

btw, you're not moving any data. You are populating a line items table with foreign keys.

Yes. all I need are the foreign keys moved, in order for the portal to populate with the related data from the products table, for end users to see.