Custom Fields

QuickBooks Desktop lets users define custom fields (also called Data Extensions or “DataExt” in the SDK) on lists (customers, vendors, items, employees, other-names) and on transactions (invoices, sales orders, purchase orders, and every other transaction type). Syntra discovers these definitions automatically and surfaces each custom field as an ordinary column on the matching table, prefixed custom_. Read, write, filter, join, sort, aggregate — custom field columns work just like native columns.

How discovery works

At startup and at each full sync, Syntra issues a DataExtDefQueryRq request to QuickBooks. QB returns the list of user-defined custom fields together with their AssignToObject scope. Syntra builds an in-process custom field registry from that response and adds the discovered columns to every matching entity’s schema.

AssignToObject values that cover multiple tables are expanded automatically:

• Item expands to every item subtype (item_inventories, item_inventory_assemblies, item_services, item_non_inventories, item_other_charges, item_fixed_assets, item_discounts, item_groups, item_payments, item_sales_taxes, item_sales_tax_groups, item_subtotals).
• Scopes like Customer, Vendor, Employee, Invoice, SalesOrder, PurchaseOrder, Bill, Check, and the rest map 1:1 to their matching table (the *Ret response-tag name, minus the Ret).

So if your QuickBooks file has a custom field called UPC Code with AssignToObject="Item", it shows up as custom_upc_code on every item_* table automatically.

Naming convention

QB custom-field names become column names via this transform:

1. Lowercase every alphanumeric character.
2. Replace each run of non-alphanumeric characters with a single _.
3. Trim any trailing _.
4. Prepend custom_.

QB custom field	Column name
Account Manager	custom_account_manager
PO Required	custom_po_required
Region/Territory	custom_region_territory
Tax Exempt #	custom_tax_exempt
Credit Limit ($)	custom_credit_limit
O'Brien's Note	custom_o_brien_s_note

The transform is deterministic — you can run it in your head or check the registry via information_schema.columns.

Reading

Treat custom_* columns like any other column. They appear in SELECT, WHERE, GROUP BY, ORDER BY, JOIN, and subqueries.

-- Filter by a custom field
SELECT full_name, custom_account_manager, balance
FROM customers
WHERE custom_account_manager = 'Jane Smith';

-- Group by a custom field
SELECT custom_region, COUNT(*) AS customer_count, SUM(balance) AS total_balance
FROM customers
GROUP BY custom_region;

-- Join on a custom field
SELECT c.full_name, i.txn_date, i.ref_number
FROM customers c
INNER JOIN invoices i ON i.customer_ref_list_id = c.list_id
WHERE c.custom_region = 'West';

QuickBooks stores all custom field values as strings internally, so Syntra exposes them as VARCHAR. Cast in your queries when you need numeric or date semantics:

SELECT full_name, CAST(custom_credit_limit AS DECIMAL(12,2)) AS credit_limit
FROM customers
WHERE CAST(custom_credit_limit AS DECIMAL(12,2)) > 10000;

Writing — INSERT

Custom field columns are accepted on INSERT exactly like native columns. Syntra embeds the value as an inline <DataExt> element inside the parent *Add request, so the custom field posts atomically with the new record.

-- Create a new inventory item with custom fields in one statement
INSERT INTO item_inventories
    (name, sales_price,
     income_account_ref_full_name, cogs_account_ref_full_name, asset_account_ref_full_name,
     custom_upc_code, custom_weight)
VALUES
    ('Widget-A', '50.00',
     'Sales', 'Cost of Goods Sold', 'Inventory Asset',
     '012345678905', '1.25');

-- Create a customer and tag it with a region
INSERT INTO customers (name, custom_region, custom_account_manager)
VALUES ('Acme Corp', 'West', 'Jane Smith');

Multi-row VALUES works too, and custom fields follow the same header-grouping rule as other columns:

• Multi-row INSERT into a list table (e.g. customers) creates one customer per row, each with its own custom field values.
• Multi-row INSERT into a line-bearing transaction (e.g. invoices) with identical headers AND identical custom field values across rows groups the rows into one parent with N line items (see INSERT / UPDATE / DELETE). If the custom field values differ across rows, the grouping is disabled and each row becomes its own parent.

Writing — UPDATE

UPDATE ... SET custom_x = ? posts the change as an inline <DataExt> inside the parent *Mod request, alongside any native-field updates in the same statement. QB’s edit-sequence check still applies; Syntra refreshes the edit sequence and retries once on a 3200 mismatch.

-- Update a customer's custom fields
UPDATE customers
SET custom_account_manager = 'John Doe',
    custom_region          = 'West'
WHERE list_id = '80000001-1234567890';

-- Native + custom fields together
UPDATE item_inventories
SET sales_price     = '65.00',
    custom_upc_code = '012345678929'
WHERE list_id = '80000005-1234567890';

Discovering what’s available

Check information_schema or run a SHOW COLUMNS style query:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name  = 'item_inventories'
  AND column_name LIKE 'custom\_%' ESCAPE '\'
ORDER BY column_name;

Or inspect all columns on the table:

DESCRIBE TABLE item_inventories;
-- or just fetch a sample row and inspect the column headings:
SELECT * FROM item_inventories LIMIT 1;

Supported entities

Custom fields land on any entity QB accepts a DataExtDefAdd for:

• Name lists — customers, vendors, employees, other_names
• Item lists — all item_* tables (see the AssignToObject=“Item” expansion above)
• Transactions — invoices, sales_orders, purchase_orders, sales_receipts, credit_memos, estimates, bills, item_receipts, checks, credit_card_charges, credit_card_credits, vendor_credits, deposits, journal_entries, inventory_adjustments, build_assemblies, transfers, time_trackings — any transaction type that accepts DataExt in qbXML

Scope is driven entirely by what QB reports in its DataExtDefQueryRq response — Syntra doesn’t hardcode anything beyond the Item expansion. Adding a new custom field in QB is picked up on the next full sync (or immediately via CALL qb_rebuild_all()).

Line-level custom fields

QuickBooks allows custom fields on transaction lines (e.g. per-invoice-line notes) in addition to the transaction header. These are not yet exposed via the line_custom_* SQL pattern. If you need to write line-level custom fields today, use the MCP insert_record tool’s _lines[].data_ext structure.

Refreshing after QB changes

• Adding a new custom field in QB — Syntra picks it up at the next full sync. To force an immediate refresh: CALL qb_rebuild_all().
• Renaming a custom field in QB — the old column name disappears and a new one appears with the new QB name. Existing data stays in QB under the new name.
• Deleting a custom field in QB — the column disappears from the schema. Values that existed on records before the delete remain in QB but are no longer queryable.

Notes and limits

• QB applies per-entity custom field limits: typically 7 shared across customers/vendors/employees/other-names, 5 on items, and 15 on transactions. Syntra reports whatever QB reports — it does not enforce limits itself.
• Custom field values are always strings in QB. Syntra does not attempt to infer numeric/date types. Use CAST(...) in queries where typed semantics matter.
• Syntra writes custom fields using an inline <DataExt> block inside the parent *Add / *Mod request, so the parent record and the custom field post in a single atomic qbXML operation. A custom-field write never leaves a record partially updated.
• The custom-field registry is rebuilt on every connection to make sure newly-added QB fields are visible to long-lived sessions.