Importing orders from X-Cart

[kllrwlf]

I'm new to the board and have been using FileMaker Pro for a little bit.

I'm looking for any suggestions on creating a database so that you can import orders from X-Cart.

For those who do not know what X-Cart is, it's a internet shopping cart that exports the orders similiar to this:

OrderNo | CustomerName | Product Sku | Product Price | Shipping

1 John Smith 2345 32.32 5.5

1 John Smith 5465 46.23 5.5

1 John Smith 1342 12.32 5.5

1 John Smith 7842 87.54 5.5

2 Jane Doe 2534 17.54 8.5

3 Chris Can 3534 57.54 3.5

3 Chris Can 9534 17.54 3.5

So in this example, there are 3 orders, John Smith ordered 4 items, Jan Doe ordered 1 and Chris Can ordered 2 items.

Have 2 FM files, one that imports the raw orders (7 records), and the other would use a portal to get the information to create 1 record per customer. The problem that I have is, how would I have the orders imported once per customer?

I hope this makes sense.

Any suggestion, or a different way of doing this would be appreciated!

[BobWeaver]

Have you provided all the information here? What about the customer information like mailing address etc.?

I would import into the raw orders file the order number and the item information fields. Then run a script that creates related records in the Customer Info file. You can do this by setting up a relationship to the customer info file using the order number as the key field for your relationship and checking the allow "creation of related records" box. Then your script need only loop through the records and perform a step like:

Set Field(CustomerInfo::SomeDummyField, "1")

That will create a single Customer Info record per order no matter how many items there are for that order.

Finally, do an import from the original data file into the Customer Info file using the Update Existing Records only option, importing only the customer info fields.