It took some digging to obtain the documentation below, so hopefully someone else will find the following useful.
Background Info
My company uses Endicia Professional for fulfillment. Barcode scanners read customers’ packing slips to pull shipping info and customs information via ODBC connection to our database.
Endicia’s GUI for mapping database fields to its shipping interface works well in most cases. The default Customs Map performs a basic query of the shopping cart table, reading all records that match the field you specify:
Assuming your packing slip items are contained in a table named cart_info, the above GUI will generate a basic query:
SELECT * FROM cart_info WHERE txnid = $ReferenceID;
The Customs Data Map is stored in an XML file in your Windows app directory:
%appdata%/Endicia/Professional/Customs Data Maps/Data Map 001
Unlike the Source Data Map and Post-back Data Maps, there’s no obvious way to modify the SQL statement through the GUI, or even through the XML map.
The above GUI produces a working XML file like this (database parameters obviously omitted):
<DbType>MySQL ODBC 5.1 Driver</DbType> <Type>ODBC</Type> <Path /> <GalaxyReferenceIdSource>Order Number</GalaxyReferenceIdSource> <ReferenceID>txnid</ReferenceID> <FormType /> <ContentsType /> <Quantity>quantity</Quantity> <Description>postaldescrip</Description> <Weight>weight</Weight> <Value>itemprice</Value> <Country /> <bPounds>false</bPounds> <SqlStatement />
Endicia actually supports custom SQL statements. They simply haven’t provided reference documentation…
After some trial-and-error, I called Endicia support. A guy named Ron replied within 24 hours by email; we exchanged code and he produced the following:
Here is what I found.
My tests were with Access database. When I used this in my Customs data map:
… WHERE [ORDERID]=$ReferenceID AND [GROUP] = 0
It did filter the customs data results.
Instead of $OrderNumber we should be using $ReferenceID
Because the sample uses single quotes, I assume txnid is alphanumeric – if txnid is a numeric only, then we should remove the single quotes.
You are correct that the pointy brackets are an issue due to XML. Regarding the ASCII, can you tell me which ones you tried?
About != , not sure if these would be faster, but for the alphanumeric values maybe try LIKE or NOT LIKE
As Ron points out, Endicia stores the order lookup variable in $ReferenceID. In GUI terms, $ReferenceID = “Match the value in the Endicia Professional field:”
Now we can setup a query. Replace the line <SqlStatement /> with:
<SqlStatement>SELECT * FROM cart_info WHERE txnid = $ReferenceID;</SqlStatement>
Since the query is stored in an XML file, be sure to replace any less-than or greater-than signs with their ASCII equivalent. I needed to restrict our query results to ignore items with a non-NULL field named autoGroup, unless the itemnumber matched 117 or 118.
Here’s my final working code for the Customs Data Map XML file. This code is nearly useless to anyone outside of our office, but I always find code examples helpful:
<SqlStatement>SELECT * FROM cart_info WHERE txnid='$ReferenceID' AND (autoGroup < 1 OR autoGroup IS NULL or itemnumber='117' OR itemnumber='118')</SqlStatement>
Save the XML file then close and re-open Endicia, and you should be in business:
Thanks to Ron and Endicia for the development support!