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.

Receiving Against a Purchase Order

The same linking pattern receives a bill or item receipt against specific purchase-order lines (since v0.2.25). Add line_link_to_txn_txn_id and line_link_to_txn_txn_line_id to the item-line columns; each line points at the PO line it receives from, and QuickBooks updates the PO’s received quantity and billed status, exactly like receiving against the PO inside QuickBooks:

INSERT INTO item_receipts
    (vendor_ref_full_name, ref_number,
     line_item_ref_full_name, line_quantity,
     line_link_to_txn_txn_id, line_link_to_txn_txn_line_id)
VALUES
    ('Office Supplies Co', 'IR-002',
     'Widget-A', '40',
     '19A-1234567890', '19B-1234567890');

The item, description, and cost are inherited from the linked PO line; the line_item_ref_* column here only routes the row to item_lines (see the shared-line routing rule above) and is not sent to QuickBooks when a link is present. Supply line_quantity to receive a partial quantity. The identical columns work on bills for entering a bill directly against a PO.

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. When creating a new transaction, lines are declared alongside the parent in a single SQL INSERT using the line_* column pattern shown above, or through the MCP server’s _lines nested array.

Eight line tables also support modifying an existing transaction’s lines after it has been created: invoice_lines, credit_memo_lines, estimate_lines, sales_order_lines, purchase_order_lines, and sales_receipt_lines (since v0.2.24), plus expense_lines and item_lines (since v0.2.25). The shared tables expense_lines and item_lines work on every parent transaction type they appear under: bills, item receipts, vendor credits, checks, credit card charges, and credit card credits. Syntra resolves the actual parent type automatically from the txn_id, so the same UPDATE item_lines ... statement works whether the parent is a bill or an item receipt.

• UPDATE individual line fields, identified by txn_id + txn_line_id. All other lines on the transaction are preserved automatically. See Updating individual lines.
• INSERT appends a new line to the end of the existing transaction, identified by txn_id. A row that also supplies a non-empty txn_line_id modifies that existing line in place instead (since v0.2.25), so one INSERT can mix edits and appends. See Appending lines to an existing transaction.
• DELETE of individual lines is not supported yet.
• Transactions containing group items (line groups) are refused for line-level UPDATE/INSERT; modify the parent transaction in QuickBooks instead.

All other line tables (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.

Updating individual lines

On the mod-capable line tables (see How line tables work), UPDATE a specific line by its txn_id + txn_line_id:

UPDATE invoice_lines
SET quantity = 9
WHERE txn_id = '12345-1234567890'
  AND txn_line_id = '12347-1234567890';

QuickBooks Mod requests replace a transaction’s entire line set, so Syntra first asks QuickBooks for the transaction’s current lines and sends one request that changes the targeted lines while explicitly retaining every other line unchanged. You never need to (and should not) restate the other lines yourself.

Several lines of the same transaction can be changed in one statement; they are applied together in a single QuickBooks request:

UPDATE invoice_lines
SET rate = 12.00
WHERE txn_id = '12345-1234567890'
  AND txn_line_id IN ('12346-1234567890', '12348-1234567890');

Notes:

• amount is recomputed by QuickBooks from quantity x rate unless you set it explicitly.
• The targeted line must exist on the transaction in QuickBooks; targeting a stale or non-line row fails without modifying anything.
• Transactions containing group items are refused.

Appending lines to an existing transaction

INSERT into a mod-capable line table with the parent’s txn_id to append a new line at the end of the document (since v0.2.24):

INSERT INTO invoice_lines (txn_id, item_ref_full_name, quantity, rate)
VALUES ('12345-1234567890', 'Widget A', 4, 2.50);

Existing lines are preserved; the new line is appended after them. Multiple VALUES rows append multiple lines in order.

Since v0.2.25, a row that also supplies a non-empty txn_line_id modifies that existing line in place instead of appending. One multi-row INSERT can mix in-place edits and appends against the same transaction; they are applied together as a single change in QuickBooks, and every untouched line is preserved:

INSERT INTO item_lines (txn_id, txn_line_id, item_ref_full_name, quantity)
VALUES
    ('45A-1234567890', '45B-1234567890', 'Widget-A', 12),  -- edit existing line
    ('45A-1234567890', '',               'Widget-B', 5);   -- append new line

Each existing line can be targeted by at most one row per statement, and the targeted txn_line_id must exist on the transaction in QuickBooks; otherwise the whole statement is refused before anything is sent.

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.