Systematic Community

Nesh · ‎04-22-2024

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 · ‎04-22-2024

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 · ‎04-24-2024

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

Prajwal · ‎04-22-2024

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.

Prajwal Prasanna

Nesh · ‎04-22-2024

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!

Prajwal · ‎04-22-2024

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

Nesh · ‎04-23-2024

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

Systematic Community

create a list of invoices per customer