Custom Fields

QuickBooks Desktop allows users to define custom fields on Customers, Vendors, Items, and Employees. Syntra ODBC automatically discovers these custom fields and exposes them as queryable columns on the corresponding tables.

How Custom Fields Work

When Syntra syncs data from QuickBooks, it scans for custom field definitions and adds them to the table schema automatically. Custom field columns are prefixed with custom_ to distinguish them from built-in QuickBooks fields.

For example, if you have a custom field called “Account Manager” on the Customer entity, it appears as:

SELECT name, custom_account_manager
FROM customers;

Naming Convention

Custom field names are converted to column names using these rules:

1. Prefixed with custom_
2. Converted to lowercase
3. Spaces replaced with underscores
4. Special characters removed

QuickBooks Custom Field	Column Name
Account Manager	custom_account_manager
PO Required	custom_po_required
Region/Territory	custom_regionterritory
Tax Exempt #	custom_tax_exempt

Querying Custom Fields

Custom fields can be used anywhere a regular column can, including in SELECT, WHERE, ORDER BY, GROUP BY, and JOIN conditions:

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

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

-- Order by custom field
SELECT name, custom_priority_level
FROM customers
ORDER BY custom_priority_level DESC;

Discovering Custom Fields

To see all columns (including custom fields) available on a table, use standard PostgreSQL introspection:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'customers'
  AND column_name LIKE 'custom_%'
ORDER BY column_name;

Or simply run a SELECT * query with LIMIT 1 to see all columns:

SELECT * FROM customers LIMIT 1;

Writing Custom Field Values

You can set custom field values using INSERT and UPDATE statements:

UPDATE customers
SET custom_account_manager = 'John Doe', custom_region = 'West'
WHERE list_id = '80000001-1234567890';

INSERT INTO customers (name, custom_account_manager, custom_region)
VALUES ('New Client LLC', 'Jane Smith', 'East');

Supported Entities

Custom fields are supported on:

• Customers: customers table
• Vendors: vendors table
• Items (Inventory, Non-Inventory, Service): items table
• Employees: employees table

Data Types

QuickBooks custom fields are always stored as text in QuickBooks Desktop. Syntra exposes them as TEXT columns. If you need to use a custom field as a number or date, cast it in your query:

SELECT name, CAST(custom_credit_limit AS DECIMAL) AS CreditLimit
FROM customers
WHERE CAST(custom_credit_limit AS DECIMAL) > 10000;

Notes

• Custom fields are discovered during cache sync. If you add a new custom field in QuickBooks, run CALL qb_rebuild_all() or wait for the next full sync to pick it up.
• QuickBooks Desktop limits custom fields to 15 per entity type.
• Custom fields with no values set on any record may not appear until at least one record has a value.