Importing, Moving Multiple Records Between Tables Script

[kat012002]

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!

 

[comment]

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?

[kat012002]

It is a txt file, I have attached a sample file below.

FBA2FJ4NX8.txt

[comment]

Can there be multiple shipments in the same file?

[kat012002]

No, each shipment is download in a separate .txt file. 

[comment]

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.

[kat012002]

Sounds perfect, thank you so much!!!

[kat012002]

 

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

 

 

I understand how to load the variable from the first record, but how do I go about loading the additional variables from records 2-7 into the script?

[comment]

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.

[kat012002]

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?

[comment]

I suggest starting a new thread for your new question, as it it's probably of no interest to anyone reading this in the hope of solving a problem similar to your first one.