โ04-22-2024 10:22 AM
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!
Solved! Go to Solution.
โ04-22-2024 08:41 PM
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>
<!-- Add more columns as needed -->
</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
โ04-24-2024 04:21 AM
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
โ04-22-2024 10:57 AM
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.
โ04-22-2024 02:39 PM
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!
โ04-22-2024 08:41 PM
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>
<!-- Add more columns as needed -->
</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
โ04-23-2024 02:48 PM
It is a good idea to create a message template. Thank you