Help with Creating an Invoice WIth Quickbooks Online Workflow

[domiflichi]

Hello,

I’m brand new to n8n, so please keep that in mind. I’ve been learning about it over the past couple weeks and have it installed and working on a Windows 11 virtual machine to play around with it, and having fun with it.

I am having a problem with my 1st project. What I’m trying to do is pull some data from a SQL Server database and create an invoice in Quickbooks Online with some of that data. I’ve got a working SQL Server and Quickbooks Online credential/connection set up and working.

Here is a screenshot of my Workflow so far:

Here’s a quick overview of what’s going on in this workflow.

1. The 1st node (I’m not counting the 'When clicking ‘Test Workflow’ node), 'SQL - Find Order (WO#18786) to Invoice is currently simplified so that it just pulls a single, specific database record from a table called ‘Orders’. This record contains most of the information for the invoice (basically everything except the line items). One of the fields that it’s pulling though is called ‘WorkOrderNum’. This will be used in the next node.
   Just for reference, here’s a screenshot of the 1st node:
   
   
2. The 2nd node is another SQL query. This pulls 1 or more records from a table called ‘ProposalDetail’ that are associated with the ‘WorkOrderNum’ from the previous node. Any and all records returned here are to be corresponding line items in the invoice that we’re about to create.
   Here’s a screenshot of the 2nd node:
   
   
3. Next is my ‘Code’ node that ChatGPT actually wrote for me (I’m not real good with Javascript) to format the results/records from the 2nd node (the one that will be line items in Quickbooks) into JSON as I understand that is what Quickbooks is expecting. Note that I commented out a few lines to simplify things.
   Screenshot:
   
   
4. Lastly, the final node, my QuickBooks Online node that is to create the invoice. At first I thought I was supposed to do it like this:
   
   

But this results in only the first record getting turned into a line item, leaving me with only 1 line item when there should be 2 in this case.

So further digging made me think that I need to do it like this:

However, this results in an error as you can see in the screenshot.

I feel like I have it formatted right, but obviously I’ve got something wrong somewhere. Here’s a reference directly from Intuit’s site for what Quickbooks is expecting:

Intuit Developer

Sorry, we were unable to generate a preview for this web page, because the following oEmbed / OpenGraph tags could not be found: description, image

I feel like I’m so close, but am just doing something wrong or missing something. I’ve searched here on this forum, done general Googling, watched countless n8n YouTube videos, asked ChatGPT, but have kind of hit a wall here. I’ve been able to overcome everything else that I ran into, but just can’t get past this one, so am reaching out to see if anyone could guide me.

Please keep in mind though that I’m very new to n8n so the more detail the better.

Thank you,
Jamie

[domiflichi]

Being new to n8n and JSON, I’m not real clear on how this all works. However, I think I know what’s happening here.

What I believe is happening is that I’m passing the entire ‘Line’ object (I don’t know if that’s the correct terminology, so someone can correct me if that’s incorrect) to every line item field.

For example, I’m passing ‘DetailType, Amount, SalesItemLineDetail, etc’, to each one of those fields individually, whereas I should only be passing ‘DetailType’ to the ‘DetailType’ field.

I’m not sure how to do that, and I didn’t know that that was the problem so I started looking into the HTTP Requests node and now am getting further along so I’m going to use that.

The other advantage to using the HTTP Requests node instead is that it has access to fields in Quickbooks Invoices that the Quickbooks node doesn’t have access to…at least that’s how I understand it.

Anyway, hopefully this helps someone. n8n is awesome and I still have a lot to learn but dang this is amazing software!

Jamie