topic Re: create a list of invoices per customer in Community Chat

create a list of invoices per customer

Nesh — Mon, 22 Apr 2024 17:22:30 GMT

Hello,

I have a recipe in which I am pulling a list of customers with outstanding invoices. I am able to pull a list of all customers using Lists by workato. I want to create a table/list of invoices for customers who have more than one outstanding invoice and send one email reminder listing all of their invoices in one email instead of sending multiple emails with each invoice details. How do I accomplish this? Any guidance is appreciated. Thanks!

Re: create a list of invoices per customer

Prajwal — Mon, 22 Apr 2024 17:57:35 GMT

Hi @Nesh,

Are you trying to send multiple invoices in a single email? Are those invoices are the attachments? If you provide these details I can suggest some ideas accordingly.

Re: create a list of invoices per customer

Nesh — Mon, 22 Apr 2024 21:39:24 GMT

Hi @Prajwal

I have some customers with multiple outstanding invoices so I want to send then a list of all their invoices in one single email. I am trying to avoid file attachments. Perhaps a table with three columns (Invoice # , Invoice date and invoice amount) listing all invoices? I would also like to add, I do have some customers who will only have one invoice. Thank you for your assistance!

Re: create a list of invoices per customer

Prajwal — Tue, 23 Apr 2024 03:41:10 GMT

Gotcha! We do have a way to insert these details into an email in the form of a table.
This is the common way to do this-
1. Prepare a message template that accepts the list of inputs from your recipe.
2. Use the message template to add all the invoice details to your table(use the list mapping to your message template) and then map the document output to your email body.

Results:

Use this message template schema;
Message Template Schema -

<body> <table border="1"> <tr> <th>{{invoice_header}}</th> <th>{{invoice_date_header}}</th> <th>{{invoice_amount_header}}</th> </tr> {{#list}} <tr> <td>{{invoice}}</td> <td>{{invoice_date}}</td> <td>{{invoice_amount}}</td>  </tr> {{/list}} </table> </body>

Message template input schema-

[ { "name": "invoice_header", "type": "string", "optional": false, "control_type": "text", "label": "Header 1" }, { "name": "invoice_date_header", "type": "string", "optional": false, "control_type": "text", "label": "Header 2" }, { "name": "invoice_amount_header", "type": "string", "optional": false, "control_type": "text", "label": "Invoice amount header" }, { "name": "list", "type": "array", "optional": false, "properties": [ { "name": "invoice", "type": "string", "optional": false, "control_type": "text", "label": "Invoice" }, { "name": "invoice_date", "type": "string", "optional": false, "control_type": "text", "label": "Invoice date" }, { "name": "invoice_amount", "type": "string", "optional": false, "control_type": "text", "label": "Invoice amount" } ], "of": "object", "label": "List" } ]

Email configuration-

These configurations should solve your puzzle

Re: create a list of invoices per customer

Nesh — Tue, 23 Apr 2024 21:48:29 GMT

It is a good idea to create a message template. Thank you

Re: create a list of invoices per customer

Nesh — Tue, 23 Apr 2024 21:54:20 GMT

I guess my other problem is not being able to modify the recipe to retrieve a list of invoices for each customer. I am able to accumulate a list of ALL customers who have outstanding invoices, but I can not figure out how to accumulate a list for each customer on that overall list. Do you have any suggestions on how to achieve this?

Re: create a list of invoices per customer

Prajwal — Wed, 24 Apr 2024 04:14:09 GMT

You mean, you are not able to point out the invoices to the respective customers?. Is my understanding is correct? Provide me with more details that what you are trying to accomplish here. If you can able to mention what is the source you are using to fetch the customer or if you provide the sample output schema of the source application that you have the invoice and customer details I can figure out a way to achieve this.

Re: create a list of invoices per customer

girish-naralla — Wed, 24 Apr 2024 11:21:20 GMT

HI @Nesh ,

You can use the SQL Collection here to consolidate the all the outstanding invoices for each customer.

Step 1 : Create a collection for the whole list.
Step 2: Get Distinct customer details (Using SQL Query)
Step 3 : Keep for each for all the customers.
Step 4 : Get all the Out Standing bills for each customer (Using SQL Query)
Step 5 : Create Message Template and send mail to each customer.

Sample Recipe Link : https://app.workato.com/recipes/46858994/edit