Sunday, September 19, 2010

Quickbooks XML List ID relationships to Exported IIF refnum

I have a client that has been using exported customer lists in IIF format for a while in order to interface with an external system that creates invoices in IIF format.

As we are getting ready to move them into MS CRM with a QB link solution from Inogic, we discovered that the Inogic system uses the QuickBooks XML interface which exposes the internal ListID of the customer, and not the QB "refnum" used by their other system (via IIF file).

So the problem was that the old external system had a mass of data that used the QB Refnum field (from customer IIF file), but there was no way to correlate that with the XML version of the QB interface so that we could convert their data to the Inogic data. after a bit of playing with numbers, we discovered that the XML ListID's of the customers had a pattern and found that the first half of the ListID (before the hyphen) was really an encoded version of the Refnum.

To decode it, we used the following procedure:

1. Take the ListID and break it into 2 parts from either side of the hyphen and call them Part1 and Part2.
2. If Part1 is less than 7 characters, pad the left with zeros until it is 7 characters.
3. If the right 4 characters of Part1 ends with 0000, 0001,0002, or 0003 then get the first three characters from Part1 and convert them from Hex to decimal - that is the Refnum
4. If the first character in Part1 is "8", then use the last 3 characters from Part1 and convert them from Hex to Decimal and that is the Refnum.

Now that we have a Refnum for each customer that matches the ListID on the XML interface, we can convert all the data in the external system to use the correct customer linkage in CRM.