Systematic Community

Nesh · 4 weeks ago

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!

Prajwal · 4 weeks ago

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

Prajwal Prasanna

View solution in original post

girish-naralla · 3 weeks ago

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

View solution in original post

Nesh · 4 weeks ago

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?

Prajwal · 4 weeks ago

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.

Prajwal Prasanna

girish-naralla · 3 weeks ago

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

Systematic Community

create a list of invoices per customer