Updated: Aug 1, 2019
I've seen several requests on Knack's and other forums asking how to send PDFs from their databases. With the recent introduction of Integromat's PDF app, which converts HTML into a PDF, I thought I would explore the possibilities.
I have a database containing ticket bookings for events. I want to send each person who's booked an email with a PDF showing each of their bookings and associated cost:
For the purposes of this blog, the database is in Knack but it could just as easily be in another such as Airtable etc.
There are a couple of steps we need to complete in order to accomplish this task:
Create the HTML for the document - this needs to include a Table to contain the multiple rows representing the different eventsBuild the Integromat Scenario to collect the data and insert it into the HTML Creating the HTML
You may be fully familiar with HTML in which case this won't be a problem for you - however, it may be easier to use an online editor to do this - which is what I did:
This particular editor is available here.
You'll notice that I have entered some 'placeholders' in the text for [Customer], [Event], [Quantity], [Price Each] and [Total]. These will be used by Integromat to insert these values from the database.
The Integromat Scenario
Processes that you build in Integromat are called Scenarios and here we are going to build one which broadly does the following:
Searches for Customer recordsBuilds the top part of the HTML (from the top down to the table headings)Retrieves all the Booking records for the customer and loops through them, creating a table row for each and aggregating themConverts the resulting HTML into a PDFFinally sends the PDF as an attachment This is what the scansion looks like overall:
Finding the Customer
In this example I'm just going to look for a particular customer in the database - but you can build whatever search criteria may be required:
The next two Integromat steps use the Set Variable app to first set the basic HTML for the text and the next replaces the [Customer] placeholder with the Customer Name, Job Title and Company:
These two steps could easily be merged into one - but for the sake of this post I thought it would be simpler to separate them. Note that [Customer] is replaced with the Name fields, then a line break, the Job Title and another break and then the Company. This puts the values on separate lines - you may choose to put them on a single line of course.
Also note that the Basic HTML variable includes the HTML from our sample down to the end of the first table row - we'll add the rest later.
Setting up the Table Row Mask
Next we need to create a variable for the basic HTML for each row in the table of bookings:
Note again that this has the placeholders from the table we created in the editor.
Now we effectively have the header for the document including the headings for the table. The next step retrieves the records for the bookings associated with the particular customer, substitute the values into the table row mask and then 'aggregate' these rows together to form the table.
Looping Through Related Bookings
This is handled by a Search For a Record Knack app, a Set Variable and then a Text Aggregator. The latter acts as the end of a Loop and creates a single text output joining together the table rows created in the Set Variable step:
You'll see that the creation of the table rows requires 4 'nested' replace statements which can get a little mind bending but it should be fairly self explanatory.
The really clever part here is the Text Aggregator - notice that it asks you for the Source Module (where the data it's going to loop though is coming from) and well as which value(s) to aggregate!
Creating the PDF
For the purposes of making the example simpler, I've once again split this into two steps, firstly adding together the two sections of the HTML we've created and then converting it to a PDF:
Both these steps should be quite self explanatory - except for perhaps the - these just correctly end the table (they come after the table rows).
Sending the Email
The finally step it to use Gmail or other email connector to send the email - this is what this looks like:
And here's the result in my email:
Hopefully this post will give you confidence to try sending PDFs from your database app using Integromat!
You can find out more about Integromat here
and about Knack here