Invoice API, Pull detailed Products, Itemized

cthompson

I apologize if this has already been brought up. I’m currently working with N8N to sync invoices between ITFlow and QuickBooks Online.

One issue I’ve run into is that when pulling invoice data through the ITFlow API, I’m not seeing any product names or IDs included in the invoice payload. This makes it difficult to properly match or map individual line items between QBO and ITFlow.

As a result, when I import an invoice into QuickBooks, it comes through as a single lump sum instead of being itemized by product or service.

Would it be possible to add product name/ID fields to the invoice API response in a future update? That would make integrations like this much more seamless.

Thank you!
—Chris

cthompson

My N8N Workflow

digibandit

cthompson Holy workflow batman!

I was just trying to get line items https://forum.itflow.org/d/2332-api-is-there-any-way-to-get-invoice-line-items but came up dry.

Products (`/api/v1/products/read.php`)

Query: SELECT * FROM products

Returns:

product_id, product_name, product_description
product_price (no cost field in API)
product_type (service/product), product_code, product_location
product_currency_code
product_tax_id, product_category_id
product_created_at, product_updated_at, product_archived_at

Good for:

Product catalog
Pricing lists
Product lookup by ID

NOT included:

Sales history
Quantity sold
Which invoices/quotes use this product

Quotes (`/api/v1/quotes/read.php`)

Query: SELECT * FROM quotes

Returns:

quote_id, quote_number, quote_prefix
quote_scope (description)
quote_status (Draft, Sent, Accepted, etc.)
quote_date, quote_expire
quote_amount, quote_discount_amount
quote_currency_code, quote_note, quote_url_key
quote_client_id, quote_category_id
Timestamps (created_at, updated_at, archived_at)

Good for:

Quote tracking
Conversion analysis (accepted vs sent)
Pipeline value

NOT included:

Quote line items
Individual products quoted

Invoices (`/api/v1/invoices/read.php`)

Query: SELECT * FROM invoices

Returns:

invoice_id, invoice_number, invoice_prefix
invoice_scope (description)
invoice_status (Draft, Sent, Viewed, Paid, etc.)
invoice_date, invoice_due
invoice_amount, invoice_discount_amount, invoice_credit_amount
invoice_currency_code, invoice_note, invoice_url_key
invoice_client_id, invoice_category_id, invoice_recurring_invoice_id
Timestamps (created_at, updated_at, archived_at)

Good for:

Revenue reporting
AR aging
Payment tracking

NOT included:

Invoice line items
Products sold
Quantities

I was just about to learn how to query the database directly with n8n, as the database has:

SELECT
ii.item_id,
ii.item_name,
ii.item_description,
ii.item_quantity,
ii.item_price,
ii.item_subtotal,
ii.item_tax,
ii.item_total,
ii.item_product_id,
ii.item_created_at,
i.invoice_id,
i.invoice_number,
i.invoice_prefix,
i.invoice_date,
i.invoice_status,
i.invoice_client_id
FROM invoice_items ii
JOIN invoices i ON ii.item_invoice_id = i.invoice_id
WHERE YEAR(i.invoice_date) = YEAR(CURDATE())
AND i.invoice_archived_at IS NULL
ORDER BY i.invoice_date DESC, ii.item_id;