1 Reply Latest reply on Nov 16, 2013 7:39 AM by philmodjunk

    Best way to move data from one table to another?



      Best way to move data from one table to another?


           I have a invoice and a purchase order table with layouts that have portals for line items in each one. So To avoid double entry I would like to take the invoice line items and create a new purchase order and input the same info in the purchase order line item.  

           They both have same field names.   


        • 1. Re: Best way to move data from one table to another?

               Often, not always, the best way to move data from one table to another is to not have two tables, but use one table for both purposes.

               Is the list of line items in the invoice always going to be exactly the same as the list of line items in your purchase order? if so, you can use the same table for both purposes and link an invoice and also a purchase order record to the same set of related records.

               If that doesn't work for your business model, you can use either Import Records or a looping script to move the data from a found set of records in one table into new records in the other.

               Either method starts with a scripted find or Go to related records to produce the desired found set in a layout based on your line items table. Import records then copies the records over all in one step. A replace field contents step can then update the imported records to assign a value to their foreign key fields to link them to the new parent record.

               A looping script does it one field of one record at a time by setting data from fields to variables, changing layouts, creating a new record and then setting the fields of the new record to values from the variables. It then returns to the original layout goes to the next record and continues the loop...