Re-use multiple JSON items from another node inside an http node request body that also has data (Quickbooks node -> http node)

joeldeteves · October 20, 2022, 11:23pm

Describe the issue/error/question

I need to capture an array of items and use them inside the JSON request body of the HTTP module:

[
  {
    "Id": "21"
  },
  {
    "Id": "22"
  }
]

The items need to be added to the http request body in JSON format (exactly as they are shown now, but without the array brackets), without clobbering the JSON that I already have in the request body.

Currently if I try to reference inside the request body I can only get one value at a time.

The reason for this is I am trying to add new line items to an existing QuickBooks invoice, and the current QuickBooks node does not support adding line items.

Unfortunately, the QuickBooks API also requires you to preserve the existing transaction IDs in the request body otherwise they get deleted and replaced with the new one - so I am also not able to loop my http request.

What’s the best way to accomplish this? Also, if anyone knows of a better way to accomplish adding new line items to a QBO invoice using N8N please share.

What is the error message (if any)?

Please share the workflow

Share the output returned by the last node

Information on your n8n setup

n8n version: 0.198.2
Database you’re using (default: SQLite): Postgres
Running n8n with the execution process [own(default), main]: own
Running n8n via [Docker, npm, n8n.cloud, desktop app]: k8s

Jon · October 21, 2022, 9:01am

Hey @joeldeteves,

It looks like you have 2 items so you could use a Code node to merge them into one object, Do you need to send a string like what you have in the set node (without the []) or a json object?

A link to the API doc you are following may be handy if you have it.

joeldeteves · October 21, 2022, 2:20pm

Thanks @Jon - this is just mock data but the real data is being pulled from the existing invoice using the QuickBooks node.

Basically what I need to do is preserve the ID of the existing line items and add them into the request body along with the rest of the data I’m sending.

The API docs are located here: Intuit Developer

And here’s a response from a QBO developer: Intuit Developer Support

QBO presents lines as an array. There is no method to only send an element of the line array.

Jon · October 21, 2022, 2:27pm

Hey @joeldeteves,

So I take it then you are doing the full update, Is it this part you are trying to preserve? or is it just an ID? knowing which field you are using will be a big help.

"Line": [
    {
      "LineNum": 1, 
      "Amount": 150.0, 
      "SalesItemLineDetail": {
        "TaxCodeRef": {
          "value": "NON"
        }, 
        "ItemRef": {
          "name": "Services", 
          "value": "1"
        }
      }, 
      "Id": "1", 
      "DetailType": "SalesItemLineDetail"
    }, 
    {
      "DetailType": "SubTotalLineDetail", 
      "Amount": 150.0, 
      "SubTotalLineDetail": {}
    }
  ],

joeldeteves · October 21, 2022, 2:51pm

Hey Jon, I think I just figured out a solution that’s much more elegant than what I was trying to do initially.

Pass the ‘base’ (top-level) JSON parameters I want into a Set node.
From here, I can pull the existing ID’s from the invoice (the ones I want to preserve) using an expression into the Line field

This seems to output the data in exactly the correct format.

So now I just need to add more items into Line using another node

I think I can do that easily using the Code node!

So we have a partial solution, and we’re well on our way to a full solution, yay!

Jon · October 21, 2022, 3:15pm

Hey @joeldeteves,

This is a nice solution and is normally the first thing I try as I feel like using a Code node is cheating

joeldeteves · October 21, 2022, 8:55pm

I’m still a bit stuck on this.

With the Set node, I’ve got exactly the output and data preserved that I want now.

However I’m still having trouble merging the new data in. I am assuming a code node is best for this but JavaScript is not my strong suit.

What I need to do now is 1) Loop through a list of input values 2) Construct them using the JSON expected by the QBO API and 3) Append them to the output of the Build QBO Request node I shared.

Here’s the format QBO expects:

    {
      "Id": "-1",
      "Amount": {{$json["UnitPrice"]}}, 
      "SalesItemLineDetail": {
        "TaxCodeRef": {
          "value": "{{$json["SalesTaxCodeRef"]["value"]}}"
        },
        "ItemRef": {
          "value": "{{$json["Id"]}}"
        }
      },
      "DetailType": "SalesItemLineDetail"
    }

Each one needs to get appended to the Line field (in this example it would show up as item 2, item 3, etc depending how many items are inputted):

Appreciate any assistance!

joeldeteves · October 21, 2022, 10:33pm

I got a bit further with using more Set nodes to construct the new items.

Now I just have to figure out how to merge the output of the last Set node with the output of the List node:

joeldeteves · October 22, 2022, 6:21pm

I’ve just about got this working @Jon except I seem to be stuck now on gathering all my combined data and replace the Line field with it.

I’m following the suggestion from Jan in Function Node: Access all items from previous node output - #2 by jan however it seems to be adding an extra ‘json’ field to the output of each item:

Any idea how I can stop this from happening? I just need the raw output of the entire previous node to populate this field without adding the extra json key.

joeldeteves · October 22, 2022, 7:09pm

Sometimes, I overcomplicate things!

Turns out all I needed to achieve the final output I wanted was a List node set to aggregate.

Problem solved, woo-hoo!

maxT · October 24, 2022, 7:07am

@joeldeteves happy to hear you found a solution. Prior to discovering the Item Lists node, can you remember what you thought you were looking for?
Basically what search term/ how were you thinking about the problem prior to finding Item Lists node? We can add alias search terms to any node, so would be helpful to know. This way we can make it more discoverable for others in future

joeldeteves · October 24, 2022, 3:47pm

@maxT yes - my workflow is as follows:

New invoice from QuickBooks triggers a webhook
Check customer subscriptions in our CSP partner portal
Add subsriptions to the invoice (matched by SKU)
Email invoice to the client

The main challenge was rebuilding the QBO Item List - QuickBooks API uses an array to store this information and the only way to “add” an line item is to capture the existing line items and include them in your query, otherwise all the existing line items are overwritten / removed.

Therefore I needed to capture that data, combine it with my new data and re-construct it into JSON for the final HTTP request.

I guess I was originally trying to do this using a SET node, when in fact I needed several SET nodes and an Item Lists node using the aggregate feature to combine them.

I think the best solution would be to update the QuickBooks node to have an “Add Line Item to Invoice” feature - that would eliminate a ton of the logic being used here.

But overall, I guess you would want to add the search term “combine” and “aggregate” to the Item Lists node - those are definitely things that are useful when pulling data from multiple sources and re-constructing into a single field.