It could just be that I'm dumber than a bag of hammers but, for certain mappings of customer address lines, I cannot get my front-end field values to map correctly in QuickBooks Pro 2004.

Specifically, when a customer's address details call for billing or shipping company name on the second address line [name, company, addr, city, state, zip], the street address that's mapped to Addr3 is lost.

By stepping thru the code and checking QB's Address Details window after each field, we've observed that Addr3 data is being written to the City field instead of to the third address line, then it's overwritten by the City value.

This only occurs when a third address line is involved. Individual customers [name, addr, city, state, zip] map correctly.

The SQL syntax runs and maps as expected when tested with literals in the VB Demo, but not when sent by our application. Here's a sample of the VFP output:

INSERT INTO Customer ( Name, AccountNumber ) VALUES ('Jones, Jennie','1-2900')
SELECT Name, ListID FROM Customer WHERE accountnumber = '1-2900'
UPDATE Customer SET "CompanyName" = 'JENNICO' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "FirstName" = 'JENNIE' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "LastName" = 'JONES' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "BillAddressAddr1" = 'JENNIE JONES' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "BillAddressAddr2" = 'JENNICO' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "BillAddressAddr3" = '2222 40TH AVE' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "BillAddressCity" = 'GULFSHORE' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "BillAddressState" = 'FL' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "BillAddressPostalCode" = '33333' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "BillAddressCountry" = 'US' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "ShipAddressAddr1" = 'JENNIE JONES' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "ShipAddressAddr2" = 'JENNICO' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "ShipAddressAddr3" = '2222 40TH AVE' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "ShipAddressCity" = 'GULFSHORE' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "ShipAddressState" = 'FL' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "ShipAddressPostalCode" = '33333' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "ShipAddressCountry" = 'US' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "Phone" = '800-555-1212' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "Email" = 'jones@gmail.com' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "CustomerTypeRefListId" = '70000-1100522107' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "SalesRepRefListID" = '20000-1098652216' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "CustomFieldTGACustNo" = '829' WHERE "ListID" = '6BD0002-1147979533'
UPDATE Customer SET "CustomFieldTGAPrefCustomer" = 'Y' WHERE "ListID" = '6BD0002-1147979533'

QuickBooks then shows [in the Billing Address, for example]:

JENNIE JONES
JENNICO
GULFSHORE, FL 33333
US

We've tried all the variations we can think of, and the result is always the same: CompanyName on Addr2 = No StreetAddr on Addr3

Any advice would be gratefully appreciated.

Thanks,
Bob

Normally a single insert statement is used but let's go with what you have. QODBC only sends XML requests to QuickBooks, and QuickBooks does all the verifcations and the crazy things is does sometimes. You have used Country, and on top of that "US" instead of "USA" as required, so the QuickBooks country verifcation did some moving around.

The fix is to move Addr3 updates to the end (after the country updates) like this:

INSERT INTO Customer ( Name, AccountNumber ) VALUES ('Jones, Jennie','1-2900')
sp_lastinsertid Customer
UPDATE Customer SET "CompanyName" = 'JENNICO' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "FirstName" = 'JENNIE' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "LastName" = 'JONES' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "BillAddressAddr1" = 'JENNIE JONES' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "BillAddressAddr2" = 'JENNICO' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "BillAddressCity" = 'GULFSHORE' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "BillAddressState" = 'FL' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "BillAddressPostalCode" = '33333' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "BillAddressCountry" = 'US' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "ShipAddressAddr1" = 'JENNIE JONES' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "ShipAddressAddr2" = 'JENNICO' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "ShipAddressCity" = 'GULFSHORE' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "ShipAddressState" = 'FL' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "ShipAddressPostalCode" = '33333' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "ShipAddressCountry" = 'US' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "Phone" = '800-555-1212' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "Email" = 'jones@gmail.com' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "BillAddressAddr3" = '2222 40TH AVE' WHERE "ListID" = 'AD0000-1197762837'
UPDATE Customer SET "ShipAddressAddr3" = '2222 40TH AVE' WHERE "ListID" = 'AD0000-1197762837'

Thanks, Tom. That solution works great, and we would ~never~ have found it on our own.

BTW, the 'one-liners' were a byproduct of debugging, not a design preference. =)

I couldn't help noticing that QB still changed our country value to 'USA' -- indicating, I guess, its non-acceptance of ISO standard country abbreviations.

Which raises the broader question: Clearly you have a deep understanding of QuickBooks' esoterica. How would you advise another developer to acquire knowledge of QB's, shall we say, special needs?

For example, you recognized that our use of the country field and a two-character country abbreviation was buying trouble. We, on the other hand, knew only what we learned from the qbXML reference and schema:
"Country" = 'The country name in an address...(31-char string, 0-1 occurrences, possible values are US, CA, UK, and AU.)'

With all the types of QB requests, we're bound to encounter other such Quirks and obscure requirements. To achieve compliance most efficiently, are there additional steps we should take or places we should look before coming to you for a solution?

Thanks again,
Bob

It's all the quirks and obscure things in both QODBC and QuickBooks that got me to setup this forum in the first place and as I approach 700 posts I'm still learning too!

The best bit of advice I can give is to always investigate what you want to do using the QuickBooks User Interface because QuickBooks has the final say in everything we do with QODBC.

As far as countries is concerned, QuickBooks wants the full country name for US Mail etc., see Address Mixup in Australian, Canadian & USA versions QuickBooks? for another example of what to watch out for!