cancel
Showing results for 
Search instead for 
Did you mean: 

create a list of invoices per customer

Nesh
Deputy Chef II
Deputy Chef II

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!

2 ACCEPTED SOLUTIONS

Prajwal
Deputy Chef III
Deputy Chef III

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:

Screenshot 2024-04-23 090222.png

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-
Screenshot 2024-04-23 090740.png

 These configurations should solve your puzzle

Prajwal Prasanna

View solution in original post

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

7 REPLIES 7

Prajwal
Deputy Chef III
Deputy Chef III

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
Deputy Chef II
Deputy Chef II

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
Deputy Chef III
Deputy Chef III

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:

Screenshot 2024-04-23 090222.png

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-
Screenshot 2024-04-23 090740.png

 These configurations should solve your puzzle

Prajwal Prasanna

Nesh
Deputy Chef II
Deputy Chef II

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