Importing into portal

[Steven1337]

Here is my scenario:

My business gets it its orders from our online shopping cart website. The orders get stored in the control panel of our shopping cart solution. These orders have to then be taken out and put into filemaker where we process the orders.

When there are a lot of orders this becomes a very tedious process, but I noticed that the shopping cart solution allows you to export all the orders.

The only issue is that in my filemaker invoice solution all the items for the order are stored in a portal field. Whenever I export the records as a csv file from the website, and a order has more than 1 item the second item is made into a completely new record with the rest of the fields repeated.

Is there any possible way to export the orders and import them into filemaker while adding these items to the portal field so that this whole process is automatic and I don't have a bunch of repeated records?

Edited October 11, 2010 by Guest

[comment]

You need to import twice: once into the OrderItems table, and once into the Orders table. The second import can import from the original again, or directly from the OrderItems table.

To prevent duplicate orders being created, validate the field that identifies the order (hopefully, the source has such field) as Unique, Validate always.

[Steven1337]

That's exactly what I am trying to do right now. The only problem I am having right now is relating the second import of the items back to the invoices. When I export the items from the website they have an invoice number but it is different from the field InvoiceID which we use in our filemaker. The InvoiceID is an auto created serial which keeps each invoice unique.

The portal field is set related to the InvoiceID field not the one that comes off the cart. So for the second import I would need all the InvoiceID's that were just created from the first import to relate the items and have them show up on the portal fields.

But anyways I am trying a couple of different solutions thanks for your help. I'll probably be back if I get stuck.

[comment]

Before you import, the only thing that links items to orders is the invoice number issued by the shopping cart. So the only way to link up items to their parent orders after importing is to import the invoice number into both tables and use it to relate the two.

After that, you can populate the InvoiceID field of the items with the parent invoice's ID. If the shopping cart system is reliable, you could skip that and leave the relationship based on the original invoice number.