Jump to content

Importing, Moving Multiple Records Between Tables Script


This topic is 2844 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello All,

 
I have a file with shipment information that I need to import into separate tables, a shipment name/id table and a shipment line items table. The problem is it is formatted in a way that the shipment name/id is located in column 2, row 1. The shipment line item information starts at row 9 and but the number of subsequent lines vary with each shipment.
 
I’m not sure how to go about writing a script to create a new shipment in the shipment table using the value from column 2/row 1, then create child records in the shipment line items table with the values starting at row 9.
 
I have written a couple executeSQL calculations that gather the shipment name and line item information I need but I can’t figure out how to use the data from the executeSQL calc to create the shipment line items records.
 
Is there a better way to do this?
 
Any help and/or suggestion would be very much appreciated!
 
Link to comment
Share on other sites

The problem is it is formatted in a way that the shipment name/id is located in column 2, row 1. The shipment line item information starts at row 9 and but the number of subsequent lines vary with each shipment.

 

An example would be useful here, IMHO. What format is the source file in?

Link to comment
Share on other sites

I would suggest you do it this way:

 

1. Import the text file into a temp table;

2. Load the Shipment data (in records 1 - 7)  into variables;

3. Go to the first record and omit 9 records;

4. Import the temp table into the Shipment Items table;

5. Replace the field contents of the Shipment Items::ShipmentID field with the value of the $shipmentID variable (from step#2);

5. Create a new record in the Shipments table and populate it with the variables set in step #2.

 

You need to add some kind of protection against importing the same shipment twice.

Link to comment
Share on other sites

how do I go about loading the additional variables from records 2-7 into the script?

 

You just need to step into each record in turn, say:

Go to Record/Request/Page[ First ]
Set Variable [ $shipmentID; Value:Temp::f2 ]

Go to Record/Request/Page [ Next ]
Set Variable [ $name; Value:Temp::f2 ]

Go to Record/Request/Page [ Next ]
Set Variable [ $planID; Value:Temp::f2 ]

Go to Record/Request/Page [ Next ]
Set Variable [ $shipTo; Value:Temp::f2 ]
...

Don't forget to return to the first record before the next step.

Link to comment
Share on other sites

Great, thanks! If you don't mind I have another question regarding importing. 

 

I have another file with multiple sales transactions/order id's, I need to create records in the sales table based on order id and then the line items in a separate table.

 

What is the best way to avoid adding duplicate order ids to the order table?

Link to comment
Share on other sites

This topic is 2844 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.