Order import

[Anuviel]

I need to import a certain number of orders every day. The order consists of order number, date, item sku, quantity, price and total. One order can have multiple items in it.

What would be the best way to handle imports?

Do I create an order table and items table then relate the two and import my data into each table and then display the orders in order table and add a portal to it show all the items or is there a better way?

Import is always csv, looking like

1235478,1/1/08,item1,1,5,5

or like this for multiple items

1235478,1/1/08,item1,5,1,item2,10,2,25

Thank you.

[Anuviel]

Let me rephrase that.

If I had a file like the one described in the first post how would I structure my file?

Would I have one table or two and would I need anything else?

[Cabinetman]

I've been mulling over this same thing for a while myself.

Haven't reached a conclusion yet. I know this isn't anything new... but.

Advice given to me before was basically the 2 tables. However that application doesn't seem the best for me. But what do I know??

I'm guessing you also need to process these orders - grouped together - and then save them.

[comment]

If I had a file like the one described in the first post how would I structure my file?

You don't structure your file by the format of the imported data - you structure it to fit the things you want to track. You didn't say what's the purpose of the file, but going by the book you would have (at least) three tables: Orders, OrderItems and Products.

Regardless of your structure, it will be complicated to import the data the way it's structured, because each order can have any number of items - but the importing table must have a fixed number of fields.

[Anuviel]

Correct. Basically all of the orders are processed in a different application and I have no need to edit or modify them once they are imported into FM. The reason for import is reporting. The original application has little or no available reports in it. I would create my reports in FM.

Basically I would do reports by date, item, total and quantity.

On any given day I would run a report on particular sku in a set date range and would see how many of that particular sku sold in given time.

[comment]

This specific report can be done with a single table of OrderItems (find the records in range, sort and sub-summarize by SKU). However, you will still need to split the lines into individual records, one for each item sold.

[Anuviel]

Ok, if I am following correctly that would mean 1 import table where each item would be its own record and they would share some of the information such as order number and date. That way I would get the wanted report in the way you described but I would also be able to do a self join where order number would = order number and that would make it possible to do a single order display layout where I would see

Order: 12354 Date 1/1/08

Item1 5 $1.00

Item2 1 $5.00

Total 6 $6.00

[comment]

Yes, except that if you foresee the need for such display, you might be better off having an Orders table just as well. The thing is, you will need to run a script to parse your imported data into individual items anyway, and the added effort to create the orders at the same time is minimal.

[Anuviel]

Thanks, yes, I believe that display will be necessary. Ok, so only one more thing left and that is how would I do the script? Would I do the whole thing via an import script where I would use the script to import the records and automatically separate them or would I manually import the file and then run the script and how would the script look like?

A small example would be sufficient i believe I could work it out further.

Thank you.,

[comment]

Is there a limit on how many items a single order can have?

[Anuviel]

Yes, the limit is 50.

[comment]

This is really bad. Basically you have two options here:

1. Create a temp table with 153 fields (3 fields for the order data, and 50 x 3 fields for the items). Import into this table, then split out the items into the "real" table/s;

2. Import the entire file into a field (using Import Folder…), then loop through each line to parse out the data.

Either way, it's a lot of scripting. Are you sure you cannot get the data in a more friendly format?

[Anuviel]

I might be able to. What format would be more friendly?

Basically I wrote an xml template in the program from which I export the data. I might be able to write a new export template and put the data into different format.

[comment]

Almost anything that puts each item on a separate line, e.g.:

"Order Number", "Date", "Item Sku", "Quantity", "Price", "Total"¶

with the "Order Number", "Date", and "Total" data being duplicated for each item.

If the originating application can export XML, you may be able to import that directly (using a stylesheet).

[Anuviel]

Ok, I will rewrite the export template as you suggested. Will have to look into the direct import from xml via style sheet, have never done that. I also tried connecting FM directly to the application but it gets a bit to advanced for me with all of the tables, live data and odbc connections.

[Anuviel]

Ok, done with the export template. Now it does this, as described above.

Order, Total, Item, Item Cost

12345 $10.00 Item1 $5.00

12345 $10.00 Item2 $4.00

12345 $10.00 Item3 $1.00

[comment]

I think Quantity is required, and Date would be nice to have (esp. if you want to report a date range). Anyway, once you have that, you simply import the same file twice:

The first import is into the OrderItems table, importing OrderID, Item, Cost and Quantity.

The second import is into the Orders table, importing OrderID and Total (and Date, if you have it).

In the Orders table, make sure the OrderID field is defined as 'Unique, Validate Always'.

The order of the imports is not important. Relate the two tables on OrderID and you're done.

[Anuviel]

Thanks, done, awesome. Works nicely. The only thing left is to format layouts and make reports. I attached csv and FM file if you want to look at it.

Thanks a lot.

Sales.zip

[Anuviel]

Heh, it figures, I got stuck again. I made a report showing SKU and quantity only. It is grouped by SKU and has a subtotal by quantity. Report shows fine in preview mode. I however forgot how to get the same in list view.

So my report in preview mode is showing

Item1 20 pieces

Item2 10 pieces

I now need to work out how to get that in list view and then this project is done.

[Anuviel]

Heh, figured it out. All done now.

Thanks.,