[QODBC-Desktop] Updating Customer Contact Fields (Mobile, Fax, etc.) via QODBC

Problem Description:

Users expect to be able to update contact fields such as Mobile, Fax, or other phone numbers on the Customer table via a simple UPDATE - for example, directly updating a Mobile field. However, QuickBooks Desktop stores these values in an AdditionalContactRef structure (a repeating field), not as individual columns on the Customer table. A direct update to a non-existent contact column fails.

Affected products: QODBC Desktop, QuickBooks Desktop
Expected outcome: Contact phone fields on Customer records can be updated by setting both AdditionalContactRef1ContactName (e.g., "Mobile") and AdditionalContactRef1ContactValue in the same UPDATE statement.

Cause:

QuickBooks Desktop does not store Contact's phone numbers as individual fields on the Customer table. Instead, they are stored in a repeating AdditionalContactRef structure where each contact entry has two parts: a ContactName (label - such as "Mobile", "Fax", "Other") and a corresponding ContactValue (the actual phone number). This is a QuickBooks SDK design constraint, not a QODBC limitation.

Resolution:

To update a customer's mobile or other contact phone number via QODBC, use both the ContactName and ContactValue parts of the AdditionalContactRef repeating field in the same UPDATE:

1. Set the ContactName to identify which field. Use AdditionalContactRef1ContactName and pass the label name (e.g., "Mobile", "Fax", "Other", or any value from the QuickBooks dropdown).
2. Set the ContactValue to the actual phone number. Use AdditionalContactRef1ContactValue and pass the phone number string.
3. Include both fields in a single UPDATE statement. The QuickBooks SDK requires both parts to be set simultaneously - they cannot be updated separately.

SQL Examples:

Update a customer's mobile phone number:

UPDATE Customer
SET AdditionalContactRef1ContactName = 'Mobile',
    AdditionalContactRef1ContactValue = '123-456-7890'
WHERE Name = 'Customer Name Here'

Update a customer's fax number:

UPDATE Customer
SET AdditionalContactRef1ContactName = 'Fax',
    AdditionalContactRef1ContactValue = '123-456-7891'
WHERE Name = 'Customer Name Here'

Update both mobile and fax in one statement:

UPDATE Customer
SET AdditionalContactRef1ContactName = 'Mobile',
    AdditionalContactRef1ContactValue = '123-456-7890',
    AdditionalContactRef2ContactName = 'Fax',
    AdditionalContactRef2ContactValue = '123-456-7891'
WHERE Name = 'Customer Name Here'

Example Scenarios:

Scenario 1 - Updating mobile phone via Excel VBA:

A user has an Excel workbook that syncs customer data from a CRM to QuickBooks. The CRM stores a "Mobile" field, but the QODBC UPDATE to a Mobile column on Customer always failed. William explained: contact fields in QuickBooks use the AdditionalContactRef repeating structure. The customer's VBA code was updated to set both AdditionalContactRef1ContactName and AdditionalContactRef1ContactValue simultaneously, which resolved the issue.

Additional Notes:

• ContactName values: Use one of the existing contact type names from your QuickBooks Customer record (e.g., "Mobile", "Fax", "Other"). The name must match a contact type that already exists or is in the QuickBooks dropdown for that field.
• Multiple contacts: Use AdditionalContactRef1, AdditionalContactRef2, etc. for different contact entries on the same Customer record.
• SDK requirement: Both ContactName and ContactValue must be provided in the same query - setting one without the other will not work, as this is enforced by the QuickBooks SDK at the data structure level.

Related Articles:

Understanding QODBC Data Access via the QuickBooks SDK QODBC Customer Table Field Reference

Keywords: