Skip to main content
Best answer

Nested line items - returning comma separated string

Hi,

I have the following data that I am sending to Xero:

  • Job 1 - JOB-00001 
    • Invoice Line Item 1
      • Description - ‘Apple’
      • Cost - 50
    • Invoice Line Item 2
      • Description - ‘Orange’
      • Cost - 60
  • Job 2 - JOB-00002
    • Invoice Line Item 3
      • Description - ‘Banana’
      • Cost 30
    • Invoice Line Item 4
      • Description - ‘Pear’
      • Cost 70

I have managed to pull out all the descriptions and costs as a comma separated string using the line-itemiser, which looks like this:

Output: Description: Apple,Orange,Banana,Pear
Output: Cost: 50,60,30,70

Which is great, but I need to tell Xero which items belong to which job. What I actually need for the description is:

Output: Description: JOB-00001 - Apple,JOB-00001 - Orange,JOB-00002 - Banana,JOB-00002 - Pear

Because the job number sits one level up on the message format, I can’t use the itemiser or append to match the invoice line items.

I think I need to use a code snippet to loop through each invoice item, find the relevant job number, and append it to the description and return a comma separated string, but I don’t know how to approach this with the non-array text inputs into the code snippet.

Any help would be appreciated. Thanks.

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

22 replies

awilson
Userlevel 1

@Troy Tessalone

I have read through all 16 articles there and unfortunately nothing resolves my issues. 

There are great tips there that helped me with a single array, and I have used them to spilt() text strings into arrays, count items in an array etc. however nothing refers to a nested structure where you need to refer to the level above.

Is there a way to pass in the raw JSON into the code block?

Or to count the number of invoice items per job, and then multiply the job number out into a comma separated string that many times?

 

Hi I came across this thread when trying to create Bills in Quickbooks or Xero with multiple line items.  It wasn’t totally clear how to pass the line item fields (description, quantity etc) into the Zap action.  I’m not totally sure if that was the exact issue you were facing.  It seems that you did want to do that but after appending an additional string to the individual description fields.

I’ve achieved what I needed to do with a code block - I had all my line item data coming from a Google Sheet as a single string.  I needed to parse out the string, format each field for the individual items and create arrays for each field.  I note that you have reverted to a work around but if you did want to solve the issue with a custom code block I may be able to help (I usually write in Python in Zapier but I’m fluent in JS too).

What I didn’t quite understand from the thread was where you needed to get the job number from - with a code block you can pass in data from any area of the upstream Zap.

No problem if you’re happy with your current implementation.

compound.uk.com - automations & digital business tools - built quickly and cost-effectively.
J
Userlevel 2

Hi All,

Thanks for the help, however I took the easy way out with this one. I am lucky enough to know the API developers for the trigger action and am getting them to add the job number on the invoice item level so that I have it in the same level of the message.

Sorry for everyone reading this hoping for an answer who doesn’t have the luxury of this approach.

 

GetUWired
Userlevel 7
Badge +12

@j-red 

Would you mind sharing a screenshot of the data? Preferably, not directly from the test of the trigger step but in one of the actions after Zapier has collapsed everything? 

 

Thanks!

Tim @ GetUWired

R
Userlevel 2

I think I had a similar issue to you in sending stuff to Quick books and created what may get seen as a pretty inelegant hack by just using an array - but it does mean that my entire invoicing provcess and also freelance billing / payment process is automated from Salesforce through to QB and back (I use Airtable as a staging table for ease of visualisation) 

 

This is the zap - and the hack is effectively deciding on a maximum number of line items that will be possible in an invoice and then using a form for these.  

https://zapier.com/shared/d26f8e78a9737d6f7352acbf54223d405b904133

If you need to create the line description using some form of concatenate and look up - i can’t see this being too difficult either

 

I have no experience of coding - so please be nice in the comments if this is either way off base or the ugliest solution you have ever seen

 

Russell

R
Userlevel 3
Badge +1

Sean, J-Red, et al.,

Despite the Catch Raw Hook facility - which can be very helpful, and which I appreciate - generally I’ve found it more satisfactory to use an API (when available) to pull in orders and other objects as JSON text, parse them, and dereference them as needed to form the objects I need for subsequent steps.

For example, in one of my Zaps I take a WooCommerce order notification email, parse it for the order number, then use the WooCommerce API to bring in the order. I loop through the line items to get product SKU’s use the API to pull in product information, including image URLs. I can then make an array of objects containing just the information I require for each line item, and all of this in one code step.

Of course your situation is different and one size does NOT fit all. But working with the raw object is almost always easier - at least for me. Hooks have associated background code that can interfere with message parsing.

J.

S
Userlevel 1
Badge

@Troy Tessalone alright we are getting somewhere mate!

I have the following which I have now done that very much helps. Just stringify the payload!
 

let string = JSON.stringify(grouped);

output = {payload: [string]};

The only problem I have now is PDFMonkey won’t accept it because of: “invalid token at JSON position ‘x’”

S
Userlevel 1
Badge

Not useful thanks anyway .

Troy Tessalone
Userlevel 7
Badge +14

@SeanWilson 

Here are some code articles with JavaScript examples that may be useful:

https://zapier.com/help/create/code-webhooks/javascript-code-examples-in-zaps
⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
S
Userlevel 1
Badge

To follow up: I also tried doing something like this but not dice:

 

var xhr = new XMLHttpRequest();
xhr.open("POST", "webhookURL", true);
xhr.setRequestHeader('Content-Type', 'application/json');
xhr.send(JSON.stringify({
--array of data I want to send--
}));

output = xhr;

S
Userlevel 1
Badge

@Troy Tessalone unfortunately the issue resides in the output for the code step.

When I have another step after the code, the only output options I have is the prettified ‘smart’ code by zapier output.

For example you can see in these images:

{
  Doors and Windows (Internal): [{
  specCategory: "Doors and Windows (Internal)",
  specDes: "they look uggo",
  specName: "door leafs"
}, {
  specCategory: "Doors and Windows (Internal)",
  specDes: "twist n shout",
  specName: "door handles"
}],
  Electrical: [{
  specCategory: "Electrical",
  specDes: "zip zap",
  specName: "storm spirit"
}],
  Kitchen Appliances: [{
  specCategory: "Kitchen Appliances",
  specDes: "yeh cook it honey",
  specName: "oven"
}, {
  specCategory: "Kitchen Appliances",
  specDes: "put yo hood up",
  specName: "range hood"
}]
}

Actual Output selection to looks like this. If I could put in the entire payload to a POST request and catch it with another raw webhook then I probably wouldn’t need to even do this entire process in the first place as code by zapier would output a raw JSON object which would be perfect.

 

 

Troy Tessalone
Userlevel 7
Badge +14

@SeanWilson Yes, that’s the idea, then Zapier’s webhook trigger won't parse the provided payload.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
S
Userlevel 1
Badge

Hi @Troy Tessalone I am going to give this a while now.

For clarity’s sake, I have the initial catch as a raw webhook. What you are saying is to send the data output from the javascript app to a webhook by zapier and to catch the raw webhook right?

Just to clarify:

Step1: catch raw hook (this catches a raw hook from Airtable with a payload containing the record ID)
Step2: search Airtable base (searches the base using the record ID)
Step 3: code by zapier - javascript (inputs are three fields from Airtable, specCategory, specDescription, specName)
Step3a: split each of the inputs by the delimiter, loop over them and create a new object with specCategory[i], specName[I], specDescription[i]
Push that new object to the created array payload = []
Output = {payload};

When the payload is parsed by zapier it seems to do something where it goes through each object in the payload array and concatenates each of the values according to the similar key.

So the idea would be to then output the code by zapier output to a POST request and send the POST to a URL. Then create a new catch webhook (the URL to which we are POSTing) and run the rest of the steps from there?

Troy Tessalone
Userlevel 7
Badge +14

Hi @SeanWilson and @j-red 

If you can, send the data to a Catch Raw Hook for the trigger via the Webhooks app: https://zapier.com/apps/webhook/integrations#triggers-and-actions

 

Also of note…the Webhooks app as an action via the Custom method has this option:

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
S
Userlevel 1
Badge

Yep, this is a huge issue.

I have the same thing going on. I am trying to send a payload of data to PDFmonkey.io. I have posted on stack overflow as well: https://stackoverflow.com/questions/67148831/zapier-breaks-down-arrays-of-objects-into-distinct-arrays-containing-the-keys-of

The problem is when you are writing the code, the output is basically perfect when you look at the test results.

When you go to access the data in subsequent steps, you expect a large payload but then zapier tries to be ‘smart’ and output the data by combining each of the values for the various keys into one big concatenated string separated by “,”.

Its honestly infuriating and at this point its better to build your own webapp with a web dev than it is to use zapier lol.

J
Userlevel 2

@Troy Tessalone 

I have read through all 16 articles there and unfortunately nothing resolves my issues. 

There are great tips there that helped me with a single array, and I have used them to spilt() text strings into arrays, count items in an array etc. however nothing refers to a nested structure where you need to refer to the level above.

Is there a way to pass in the raw JSON into the code block?

Or to count the number of invoice items per job, and then multiply the job number out into a comma separated string that many times?

 

Troy Tessalone
Userlevel 7
Badge +14

@j-red 

Check out some of these articles that may help: https://community.zapier.com/search?q=array&content_type%5B0%5D=article

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
J
Userlevel 2

With no index to loop through, how would I go about linking the following?
 

Job Number field: JOB-00001, JOB-00002
Description field: Apple,Orange,Banana,Pear

Based on your brief responses there must be something obvious I’m missing? Is there a way to add a faux-index?

Troy Tessalone
Userlevel 7
Badge +14

@j-red 

The JavaScript code in a Code step would be used to reconcile them to each other.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
J
Userlevel 2

Of course Javascript supports arrays, however Zapier’s code block won’t let me pass in the nested structure as it automatically converts them to comma-separated values with no way to reconcile them to eachother.

 

Troy Tessalone
Userlevel 7
Badge +14

Hi @j-red 

JavaScript supports arrays and there are methods to handle working with them in a Code block: https://www.w3schools.com/jsref/jsref_obj_array.asp

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
J
Userlevel 2

Hi Troy, 

I have had a look at all three of those and I’m not sure they can solve my problem, at least without my zap getting close to 50 tasks per job, so a single invoice to Xero could get into the hundreds of tasks.

I can loop through the jobs and store the job number in each loop to append to the description, however I can’t then loop through the invoice items for those jobs (as you can’t do nested loops), meaning that I still get every line item throughout each loop index.
This is also causing the tasks to blow out massively.

I’m still open to using the loop if someone could suggest how to loop through each job, and then append the job number to the description only for the line items within that job?

I would be happy to use a code block, but I am unsure how to get the input in a usable format.

For inputs I have:

Job Number field: JOB-00001, JOB-00002
Description field: Apple,Orange,Banana,Pear

which can’t be reconciled.

I don’t think you can pass in arrays to the code block so I’m not sure how to even get the required data input into the code block?

Troy Tessalone
Userlevel 7
Badge +14

Hi @j-red 

Maybe give these more advanced native Zap apps a try if you don’t want to use a Code step:

Looping: https://zapier.com/apps/looping/integrations

Sub-Zap: https://zapier.com/apps/sub-zap-by-zapier/integrations

Storage: https://zapier.com/apps/storage/help

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Terms & Conditions