Using XML to Import new Debtors with new Sales Orders

[pricerc]

Hi.

So I've succeeded in getting the Import Queue processor to import Parts, Debtors and Sales Orders, by manually running the queries that insert into the IM_ImportQueue table, and making sure that the processor has imported the previous 'batch' before embarking on the next.

So I now have two questions:
1) Is there a reliable way for me to tell the importer 'import the Debtors before you import the Sales Orders', when the 'queues' for both with be getting loaded programmatically (probably a SQL scheduled script) at the same time?

2) Is there any way, other than querying the status and transformed XML columns for me to match up the imported data with the source data? (so I can track down failures and/or upload a flag in the source data) - in particular, I was wondering what the Batch No and Item columns are for, and whether I can hi-jack them to store some data so I don't have to do XML queries?

/Ryan

[Mike.Sheen]

Hi.
1) Is there a reliable way for me to tell the importer 'import the Debtors before you import the Sales Orders', when the 'queues' for both with be getting loaded programmatically (probably a SQL scheduled script) at the same time?

2) Is there any way, other than querying the status and transformed XML columns for me to match up the imported data with the source data? (so I can track down failures and/or upload a flag in the source data) - in particular, I was wondering what the Batch No and Item columns are for, and whether I can hi-jack them to store some data so I don't have to do XML queries?

/Ryan

Before I dive in and answer - why are you using the import queue processing technique ? Are you aware you don't have to be limited to that technique, and you can just invoke the JiwaXMLImport console application ad-hoc to do it for you ?

Just asking - the XML Queue Import is usually more complex for importing than most people need - but it does have it's place.

[Mike.Sheen]

And now to answer your questions :

1) Is there a reliable way for me to tell the importer 'import the Debtors before you import the Sales Orders', when the 'queues' for both with be getting loaded programmatically (probably a SQL scheduled script) at the same time?

Just make sure the "AddedToQueueDateTime" is ordered the way you want. Personally, I'd keep it simple by say making all Debtors the year 2008, Inventory 2009, Sales Orders 2010 when inserting into the table, thus ensuring the order is Debtors, Inventory and then Sales.

2) Is there any way, other than querying the status and transformed XML columns for me to match up the imported data with the source data? (so I can track down failures and/or upload a flag in the source data) - in particular, I was wondering what the Batch No and Item columns are for, and whether I can hi-jack them to store some data so I don't have to do XML queries?
/Ryan

That batch ID is really for grouping a bunch of documents together. If you were to use the JiwaImportQManager object directly, you can direct it to process only documents with a specific batchID.

For your purposes, you could hijack the BatchID field - if 36 characters isn't enough, then you need to create your own table with an ID (char(36)) and a description, and when you insert into the IM_ImportQueue table, provide the ID of a record you create. That way the IM_ImportQueue table will provide the data you need.

Hope this is clear enough ?

[pricerc]

Before I dive in and answer - why are you using the import queue processing technique ? Are you aware you don't have to be limited to that technique, and you can just invoke the JiwaXMLImport console application ad-hoc to do it for you ?

Just asking - the XML Queue Import is usually more complex for importing than most people need - but it does have it's place.

Because we're bulk importing data from another database. Specifically, I'm running a SQL query against the source data in a database on a remote server that is directly generating the XML straight into the IM_ImportQueue table. With a single query (that runs in less than a second on a local table on my laptop), I can insert a couple of hundred orders into the queue.

As far as I know, all the alternatives require that I create an XML file somewhere!

[pricerc]

Just make sure the "AddedToQueueDateTime" is ordered the way you want.

Ok, that's easy. That's being set to GETDATE() by the insert queries anyway. So as long as I wait at least a second between importing debtors and importing orders, it should be sweet! (we won't be importing inventory in the live system, although I did it for testing purposes)

For your purposes, you could hijack the BatchID field - if 36 characters isn't enough,

36 characters will be ample. I just need somewhere to put in some 'key' information from the source data (which all uses integer-based primary keys), to make it easier to verify that 'order 12345' has a status '1' in the import queue. That '12345' is going into a custom field in the Sales Order, and so I *could* query it straight out of the TransformedXML column, but then I'd need to use an embedded XPath query, which I want to avoid; even if it *is* fashionable, it would still be more work than comparing a char(36) with an int.