Split and create array


This may have been asked several times but i can not find it (excuse me)

I have 2 MySQL tables

1> invoice details
2> invoice line items

I need to send an email for each new invoice with invoice details and line items relating to the invoice.

There are many new invoices at one time.

As per image, it’s creating one email per line item… (there are 2 invoices with 2 line items)… with proper invoice details.

I need to process each invoice(details), get the (line items) and send… one by one…

but how can i do it??

thank you in advance…

Hi @xewonder, welcome to the community :tada:

You probably want to use the Split In Batches node after your Get new invoices node to split the execution in batches of 1 and then build a loop for each invoice.

Then in the loop for each invoice, fetch the line items and build the email. This is a bit tricky as n8n would run the Gmail node once for each item it receives. So before passing on all your line items to the Gmail node, you would need to aggregate them into a single item. This does unfortunately require a bit of code. I’ve done something similar a while back and you should be able to adjust the example as needed:

Hope this helps! Let us know if you get stuck somewhere!

@MutedJam his solution is the best solution in most cases.

However if you cannot use split in batches for some reason. It is also possible to group your lines by Invoice creating the body of the mail like @MutedJam suggested for each invoice and Merge them together with the Invoice headers.
The result would be an item per invoice with a field containing the body of the email.

1 Like

Oh, I like that approach, thanks for sharing!

1 Like

Thank you so much!
Let me get to work and i revert asap!

1 Like

I’m so sorry…

I am not so advanced… I looked at the code and it’s just over my head…

is there not a function to process one “record” at the time??

it would be kind of be “logical”…

“take one record, move it on… do what you have to do… next record” - loop ?

So n8n processes one record at a time which is kind of the problem here. Because of that, you’d by default get one email per invoice item here.

So what the Function code here would do is build the HTML body of an email:
This would be the key part:

for (item of items) {
  email_html += `<div>
    <p><a href="${item.json.link}">Link</a></p>

Here we take the title, content:encodedSnippet and link fields from each item (which would be our invoice items) and add them to a single new item. Your fields will most likely have different names so you would need to adjust this part a little bit. If you could share the current output of your Get line-items node we might be able to help you with this.

Hi @MutedJam

The reality is that i need to create an xml json and send it on to the tax authorities (quite important stuff)

1 invoice header with all relevant line-items at the time.

“Email” was for testing purposes in order to see how to split the items.

I really can not get my head around this… I’m not a great programmer!

Hi @xewonder, I am afraid I am not entirely sure what you mean by XML JSON. Are you just trying to wrap XML data inside the existing n8n JSON structure? That’s what the XML node would do, so make sure to check that out.

You’d still need a little bit of code to convert multiple n8n items into a single n8n item. Here’s an example workflow doing the below:

  1. Get some dummy data using the HTTP Request + Item Lists nodes
  2. Convert all incoming items into the line_items property of a single new item respecting n8n’s data structure using the Function node
  3. Convert our data into an XML structure using the XML node
  4. Create a binary file using the Move Binary Data node

You can then download the XML file generated by this workflow or automatically process it like any other binary file in n8n.