Importing single csv into two tables - will this solution be okay

[Tyler]

We have a Magento website, and we have an order export setup to export orders from Magento using an Xtento plugin. We basically have one csv, and that needs to get imported into two tables. If you do not mind, would you tell me if this looks okay or if there is a better way to do this:

 

Okay, we have a FileMaker 11 client/server based Invoice solution utilizing essentially five tables:

 

Products

Customers

Invoices

Invoice line items

Web Order Import table<- I created this one just for the Magento Web Export

 

Setup an automated Import to import the raw Magento csv export into a Web Order Import Table.

 

Create a key_invoice_id field in the WebOrder Import Table.

Create a line_item_id fields for the Web Order Import table.

Create a Global field for this incremental order ID in Invoices (gincrement_id)

Create a Global field for line item ID (gline_item_id) in the invoices DB.

Create a matching key_increment_id field in the Invoices DB (web_key_increment_id).

Create a matching key_line_item_id field in the Invoices DB  (web_key_line_item_id) (I may not need to do this)

 

First: Do Invoices Import

Each line item in Web Order Import table has a Magento incremental order ID (increment_id) that basically functions as the order number in Magento. There will be more than one record in this table with this same ID if there is more than one product ordered on the order.

 

Clear Global fields.

Set gincrement_id in invoices to increment_id of the first record in the in the Web Order Import table

Set gline_item_id in invoices to line_item_if of the first record in the Web Order Import table

Then create a new record in invoices DB, a new invoice # is assigned (key_invoice_id) then set web_key_increment_id and web_key_line_item_id fields(I may not need to do this) in Invoices to the respective values from the gincrement_id and gline_item_id (I may not need to do this).

 

Based on a relationship between the respective key_increment_id’s and key_line_item ID’s do lookups and bring in key invoice related information from the Web Order Import table (name, addresses, email, shipping methods, shipping costs, etc., ).

After lookups are done, set the matching invoice # in the key_invoice_id field the in web order import table.

Set a flag field for each line in the Web Order Import Table that the invoices import was completed for that record (invoice_imported_chkbox)

 

Then, go to the next record. If the gkey_increment_id in invoices matches the increment_id in the Web Order Import table, have the script go to the next record (so that it will cycle through each record until it comes to a non-matching increment_id then the process is started all over again for the next unique id until you have cycled through to the end.

 

Once you are at the end, you would import each product ordered from the Web Order Import table into the Invoice Line Items table.

 

Products Import

Create a calculation field in Web Order Import Table to calculate the correct matching SKU (c_matching_SKU) from the SKU in the web order Import table so that the SKU’s will match with our databases.

 

Create a loop then go record by record in the Product Import database, creating a record in the Invoice Line Items DB for each item in the import database and doing lookups based on SKU with our products Database. Then set the key_invoice_id for each record in in the Invoice Line Items DB so that it will then match up with each related invoice.

 

For good measure, I can also setup an increment_id field in the Invoice Line Items DB, but this is redundant, since this can be extrapolated from the Invoices DB once it is redundant.

 

And after each product is imported into the Invoice Line Items table from the Web Import Table, it can set a flag for each record there that shows the product import was completed.

Then once all of the import is done, the contents of the web order import table can be archived into an archive table or just deleted.

 

Does this all make sense or is there a better way to do this? Naturally, there are other minor details that will get processed along the way, but this is the bulk of it.

 

Thanks!!!

[comment]

Each line item in Web Order Import table has a Magento incremental order ID (increment_id) that basically functions as the order number in Magento.

 

Couldn't you use this ID as the InvoiceID in your Invoices table (assuming that it will never collide with your "native " auto-generated invoice ids)? Because then you could import the records directly into their respective tables (with the import to Invoices set to NOT perform auto-enter options, and the Invoice ID set to always validate as unique) and be done with this.

 

 

Even if that's not an option, you could:

 

1. Import into the Invoices table, with the increment_id field routed to a Invoices::MagentoID field (validates as always unique) and auto-enter options turned on;

2. Import into the LineItems table, with the increment_id field routed to a LineItems::MagentoID field;

3. Replace the contents of the LineItems::invoiceID with the related Invoices::invoiceID value, using a relationship matching on the common MagentoID field.

[Tyler]

I think folks would prefer to keep the existing invoice numbering scheme, so the second option would work better. I forgot the validate as unique option during import, so that leaves out a whole slew of steps to manage duplicate increment_id's.

 

Thank you!