In this post, I aim to address a common use-case which appears often in the Knack Community. That is how to send an email with a table of records from your Knack database.
Examples of this would be, for example, invoices and orders with multiple product lines, details of a customer with a list of all their contacts - or, as I am going to do here, a simple reminder of outstanding tasks for each user from a task list object (called Activities in the Simple Contact Manager app I have used for other posts).
To achieve this, I am going to use Integromat to get my users, find their outstanding tasks and then aggregate them into a table and send the user an email - simple!!
To prepare for this I have created a count field in the database's Accounts Object which calculates how many outstanding tasks they have:
The point of doing this is simply to simplify the search criteria in the Integromat search module so that I only select users who have actually got some outstanding tasks. It could be a useful field in the system for other purposes of course.
Building the Integromat Scenario
I am going to assume for the purposes of this post that you know how to use the basics of Integromat and so won't spend a lot of time explaining each step. But to start, we need to select the Accounts (Users) we want to email:
We start with a Search for Records Knack module:
Note we are using a filter of Outstanding Tasks > 0. This will provide the list of people we need to email.
Now, we need to find all of the outstanding tasks for each of these people:
Here we are Searching for records Assigned to the Account from the previous module (note here that Integromat automatically loops through records when it finds more than one).
Now for the clever part. We are going to use a feature of Integromat called a "Text Aggregator" to group all of the tasks we find for each user and create HTML table rows
from them:
Using the advanced settings we simply use the standard <td> and <tr> tags to build up each table row one by one! The aggregator will then provide a single value which we can insert into our email!
Building and Sending the Email
Finally we will need a module to send the email - I am going to use an Microsoft 365 Email module but you may have a different mail service in which case there are other mail connectors in Integromat:
So, here we have built up the email body including a salutation, some text and then the HTML table which starts and ends with the <table> and </table> tags as you can see. Then we have the definition of the header row and below that the aggregated table rows from the previous step.
If you are unfamiliar with HTML Tables I would recommend visiting this link to find out more.
Running this Scenario will produce and email which looks like this:
Looks pretty dull, I think you will agree - but it works!
Formatting The Email
I don't intend to go into this in much detail but I thought I would show an example of some simple formatting that you could then apply to this table to make it more attractive:
So between <style> and </style> we have some CSS which styles the table itself and the header and data rows in the table as well. This results in a much nicer looking email:
So this is what the complete Scenario should look like:
Some Thoughts
I have not mentioned anything about when this Scenario would run - but in this case I would probably set the scheduling option on the fist module to run it either daily or weekly as appropriate.
Integromat is an Incredible tool - especially in combination with Knack. I have used it to do some amazing things for my clients and it takes Knack's capabilities to a new level!
If you want to find out more about Knack click here.
or Integromat click here.
I hope you have found this post useful - and as always please feel free to comment!