INSERT / UPDATE / DELETE

Syntra ODBC supports write operations that go directly to QuickBooks Desktop via the COM SDK. Unlike read queries that hit the local cache, all writes are executed against QuickBooks in real time. INSERT, UPDATE, and DELETE are available on every paid plan (Standard and Pro). Voiding a transaction via CALL qb_void(...) is a Pro-only feature — see the pricing page for the full plan matrix.

Primary keys

Every writable entity has either a list_id (for list-backed records like customers, vendors, items, accounts) or a txn_id (for transactions like invoices, bills, journal entries). UPDATE and DELETE operations identify records by these primary keys.

INSERT: Creating Records

Customers

INSERT INTO customers (name, phone, email, bill_address_addr1, bill_address_city, bill_address_state, bill_address_postal_code)
VALUES ('Acme Corporation', '555-0100', '[email protected]', '123 Main St', 'Springfield', 'IL', '62701');

Vendors

INSERT INTO vendors (name, company_name, phone, email)
VALUES ('Office Supplies Co', 'Office Supplies Co LLC', '555-0200', '[email protected]');

Accounts

INSERT INTO accounts (name, account_type)
VALUES ('New Expense Account', 'Expense');

Classes

INSERT INTO classes (name)
VALUES ('Consulting');

Transactions with Line Items

Line-bearing transactions (invoices, bills, sales receipts, credit memos, estimates, sales orders, purchase orders, journal entries, inventory adjustments) must be created with their header and line items in a single qbXML request. Syntra exposes this through the line_ column prefix in SQL, or through the _lines nested array via the MCP server.

Invoice with one line

INSERT INTO invoices
    (customer_ref_full_name, txn_date, ref_number, memo,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-06-15', 'INV-1001', 'Consulting services',
     'Consulting:Hourly', '10', '150.00');

The line_* columns declare the fields for one line item. Header columns declare the parent fields. Syntra assembles them into a single parent + line insert against QuickBooks.

Invoice with multiple lines

To create one transaction with multiple lines over plain SQL, supply one VALUES row per line and repeat the identical header values on every row. Syntra detects that all header columns match across the rows and groups them into a single parent Add with N <*LineAdd> children — exactly one invoice with N lines.

INSERT INTO invoices
    (customer_ref_full_name, txn_date, ref_number, memo,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-06-15', 'INV-1001', 'June services',
     'Consulting:Hourly', '10', '150.00'),
    ('Acme Corporation', '2026-06-15', 'INV-1001', 'June services',
     'Consulting:Advisory', '4', '225.00'),
    ('Acme Corporation', '2026-06-15', 'INV-1001', 'June services',
     'Travel:Mileage', '120', '0.67');

Grouping rules:

• The INSERT must include at least one line_* column.
• Every non-line_* column value must be identical across every row. A mismatch in any header cell disables grouping.
• If both conditions hold, Syntra sends one *Add request carrying one <*LineAdd> block per row. The result reports affected_rows = 1 (one parent created).
• Otherwise — rows have differing headers, or there are no line columns — Syntra falls back to the per-row behavior and each VALUES row becomes its own parent record. This preserves existing semantics for multi-row list INSERTs like INSERT INTO customers VALUES (...), (...), (...).

If you need multiple invoices with different headers in one call, keep each row’s header distinct and Syntra will create one invoice per row. If you need multiple invoices with identical headers (unusual), run separate INSERT statements.

Bill

A bill with one expense line:

INSERT INTO bills
    (vendor_ref_full_name, txn_date, ref_number, memo,
     line_account_ref_full_name, line_amount, line_memo)
VALUES
    ('Office Supplies Co', '2026-06-01', 'BILL-501', 'Monthly supplies',
     'Office Supplies', '99.99', 'Printer paper');

Bills accept expense-line columns (line_account_ref_full_name, line_amount) or item-line columns (line_item_ref_full_name, line_quantity, line_cost). Syntra picks the child table by inspecting the line columns you supply: a column starting with line_account_ref_ routes to expense_lines, line_item_ref_ routes to item_lines, and line_item_group_ref_ routes to item_group_lines. This auto-routing applies to all six shared-line parents — bills, item_receipts, vendor_credits, checks, credit_card_charges, credit_card_credits — so the same pattern works across all of them.

A bill with multiple item lines (grouped via identical headers):

INSERT INTO bills
    (vendor_ref_full_name, ref_number,
     line_item_ref_full_name, line_quantity, line_cost)
VALUES
    ('Office Supplies Co', 'BILL-502',
     'Widget-A', '50', '10.00'),
    ('Office Supplies Co', 'BILL-502',
     'Widget-B', '20', '25.00');

Item Receipt

INSERT INTO item_receipts
    (vendor_ref_full_name, ref_number, memo,
     line_item_ref_full_name, line_quantity, line_cost)
VALUES
    ('Office Supplies Co', 'IR-001', 'Monday delivery',
     'Widget-A', '100', '10.00'),
    ('Office Supplies Co', 'IR-001', 'Monday delivery',
     'Widget-B', '50', '25.00');

Inventory Adjustment

Inventory adjustments use quantity_difference on the line (positive to receive, negative to reduce) and optionally value_difference for value-only adjustments. One parent + many lines via the same grouping rule:

INSERT INTO inventory_adjustments
    (account_ref_full_name, ref_number, memo,
     line_item_ref_full_name, line_quantity_difference)
VALUES
    ('Inventory Adjustment', 'ADJ-001', 'Cycle count',
     'Widget-A', '3'),
    ('Inventory Adjustment', 'ADJ-001', 'Cycle count',
     'Widget-B', '-2');

Sales Receipt

INSERT INTO sales_receipts
    (customer_ref_full_name, txn_date, deposit_to_account_ref_full_name,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-06-15', 'Undeposited Funds',
     'Widget-500', '3', '50.00');

Credit Memo

INSERT INTO credit_memos
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-06-20',
     'Widget-500', '1', '50.00');

Estimate

INSERT INTO estimates
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-06-01',
     'Widget-500', '5', '25.00');

Sales Order

INSERT INTO sales_orders
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-07-01',
     'Widget-500', '20', '45.00');

Purchase Order

INSERT INTO purchase_orders
    (vendor_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Office Supplies Co', '2026-06-01',
     'Widget-500', '100', '10.00');

Journal Entry

Journal entries require balanced debit and credit lines. The MCP server’s preview_journal_entry tool validates the entry before creation.

INSERT INTO journal_entries
    (txn_date, ref_number,
     line_journal_line_type, line_account_ref_full_name, line_amount, line_memo)
VALUES
    ('2026-06-30', 'JE-001',
     'Debit', 'Office Supplies', '100.00', 'April supplies'),
    ('2026-06-30', 'JE-001',
     'Credit', 'Checking', '100.00', 'April supplies');

Note: journal_entries.memo is read-only at the header level. Put descriptive text in the line-level line_memo column.

Linking Invoice Lines to a Sales Order

To bill specific sales-order lines, add line_link_to_txn_txn_id and line_link_to_txn_txn_line_id to the INSERT. Each invoice line points at the SO line it draws from. QuickBooks marks the SO line invoiced and pulls the item/quantity/price from the SO automatically.

INSERT INTO invoices
    (customer_ref_list_id, ref_number,
     line_link_to_txn_txn_id, line_link_to_txn_txn_line_id, line_quantity)
VALUES
    ('80000001-1234567890', 'INV-2001',
     '12A-1234567890', '12B-1234567890', '5'),
    ('80000001-1234567890', 'INV-2001',
     '12A-1234567890', '12C-1234567890', '3');

The same pattern works on credit_memos for refunding specific invoice lines.

Custom Fields (Data Extensions)

QuickBooks supports user-defined custom fields on both list entities (customers, vendors, items) and transactions (invoices, sales orders, etc.). Syntra surfaces them as columns prefixed custom_<name>, derived from the QB field name via a lowercase + non-alphanumeric-to-underscore transform (e.g. QB field UPC Code → column custom_upc_code).

Custom field columns are available on INSERT and UPDATE alongside the native entity columns — Syntra embeds the values as inline <DataExt> blocks inside the parent Add/Mod request so the custom field posts atomically with the record.

-- Insert 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');

-- Update an existing item's custom field
UPDATE item_inventories
SET custom_upc_code = '012345678929'
WHERE list_id = '80000005-1234567890';

Discover what custom fields exist on a table:

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

The custom_* columns are discovered at startup by querying QB’s DataExt definitions (via DataExtDefQueryRq) — so any field you add to QuickBooks at runtime appears the next time Syntra reconciles the registry.

Available line_* Columns

The set of valid line_* columns depends on the parent transaction type. Common columns across most transactions:

Column	Description
line_item_ref_full_name	Item name (for item lines)
line_item_ref_list_id	Item by ListID
line_quantity	Quantity
line_rate	Unit price
line_amount	Total amount (overrides qty x rate)
line_desc	Line description
line_class_ref_full_name	Class assignment
line_sales_tax_code_ref_full_name	Tax code
line_account_ref_full_name	Account (for expense lines on bills, credit card charges)
line_memo	Line memo
line_journal_line_type	Debit or Credit (journal entries only)

Use SHOW COLUMNS FROM <line_table> to see the full set for a specific parent. For example, SHOW COLUMNS FROM invoice_lines lists every column you can pass as line_<column> on an invoice INSERT.

How line tables work

Line entities (invoice_lines, expense_lines, item_lines, journal_entry_lines, credit_memo_lines, etc.) are child tables attached to their parent transaction. You cannot INSERT or DELETE directly into a line table. Lines are created alongside the parent in a single SQL INSERT using the line_* column pattern shown above, or through the MCP server’s _lines nested array.

A few line tables (invoice_lines, credit_memo_lines, estimate_lines, purchase_order_lines, sales_order_lines, sales_receipt_lines, and their _line_groups variants) do support UPDATE of individual line fields after the parent has been created. Most other line tables (expense_lines, item_lines, journal_entry_lines, inventory_adjustment_lines, deposit_lines, discount_lines, shipping_lines, sales_tax_lines, payroll line tables) are read-only once the parent is posted.

UPDATE: Modifying Records

UPDATE requires identifying the record by its list_id (for list entities) or txn_id (for transactions).

Update a Customer

UPDATE customers
SET phone = '555-0200', email = '[email protected]'
WHERE list_id = '80000001-1234567890';

Update an Invoice

UPDATE invoices
SET memo = 'Updated memo', is_pending = false
WHERE txn_id = '12345-1234567890';

QuickBooks requires the current edit_sequence to update a record. Syntra automatically reads the current edit_sequence before issuing the update, so you do not need to pass it explicitly.

DELETE: Removing Records

DELETE also requires list_id or txn_id:

DELETE FROM customers
WHERE list_id = '80000001-1234567890';

For transactions, QuickBooks distinguishes between deleting and voiding. A DELETE removes the transaction entirely. To void a transaction instead (preserving the record with a zero amount), use the special void command:

CALL qb_void('Invoice', '12345-1234567890');

See Special Commands for more details on voiding.

Supported Entities for Write Operations

Headers that support INSERT and UPDATE:

Entity category	Examples
Customer-side lists	customers, customer_msgs, customer_types, sales_reps, sales_tax_codes, price_levels, standard_terms, date_driven_terms
Vendor-side lists	vendors, vendor_types, ship_methods, payment_methods, billing_rates
Accounts and classes	accounts, classes, currencies, other_names, job_types, sales_tax_return_lines
Items	item_services, item_inventories, item_inventory_assemblies, item_non_inventories, item_other_charges, item_fixed_assets, item_discounts, item_payments, item_sales_taxes, item_sales_tax_groups, item_subtotals, item_groups
Employees	employees (DELETE not supported)
Transactions	invoices, bills, sales_receipts, credit_memos, estimates, sales_orders, purchase_orders, journal_entries, inventory_adjustments, build_assemblies, checks, credit_card_charges, credit_card_credits, deposits, transfers, time_trackings, vendor_credits, item_receipts, receive_payments, bill_payment_checks, bill_payment_credit_cards, sales_tax_payment_checks, charges

Entities that are read-only (no INSERT/UPDATE/DELETE):

account_tax_line_infos, alerts, bill_to_pays, companies, company_activities, data_ext_defs, employee_defaults, form1099_category_account_mappings, hosts, item_assemblies_can_builds, item_sites, list_deleteds, paychecks, payroll_item_non_wages, payroll_last_periods, payroll_liability_checks, preferences, receive_payment_to_deposits, sales_tax_payables, sales_tax_returns, templates, transactions, txn_deleteds, and every line table (invoice_lines, expense_lines, etc.).

Some reference lists are create-only (INSERT + DELETE but no UPDATE): ar_refund_credit_cards, billing_rates, customer_msgs, customer_types, date_driven_terms, job_types, payment_methods, sales_tax_return_lines, ship_methods, standard_terms, unit_of_measure_sets, vendor_types, vehicle_mileages.

To check any specific table, use DESCRIBE TABLE <name> or the describe_table MCP tool — the response includes supports_insert, supports_update, and supports_delete flags that reflect the live capabilities for that entity.

Important Notes

• QuickBooks must be running: Write operations require an active QuickBooks Desktop instance with the company file open.
• Plan gating: INSERT, UPDATE, and DELETE are included on Standard and Pro. Only CALL qb_void(...) requires Pro — Standard connections return a licensing error on void. See Pricing for the complete plan comparison.
• Multi-row VALUES: For list entities (customers, vendors, items, etc.) and for line-bearing transactions whose rows carry differing header values, each VALUES row becomes its own record. For line-bearing transactions whose rows share identical header values, Syntra groups them into a single parent with one line per row — see Invoice with multiple lines above. The MCP insert_record tool also accepts a _lines nested array (equivalent to the grouped-row INSERT shown above) plus an outer array for batch creation of multiple parents in one call.
• Transaction boundaries: Each statement is auto-committed. There is no multi-statement transaction support.
• Required fields: QuickBooks enforces its own required fields. If a required field is missing, the operation fails with a descriptive error.
• Cache refresh: After a write operation, the local cache is automatically updated with the new data.
• Edit sequence: Updates require the current edit_sequence value. Syntra reads it for you automatically.