I recently read a post in a database forum where the questioner asserted that it wasn't possible to use an online forms app like Cognito Forms to capture multiple records for a database in a single form - using Zapier to take the form content and populating the database. I thought this was unlikely and in this post I am going to share how this is done.
What is Cognito Forms
Cognito Forms is a tool to build online forms for your website or standalone use (via a link) and can be quite simple - or highly sophisticated - and are pretty simple to create. Forms can include repeating sections, conditional logic (only showing required elements based on choices for example), online payment processing with a number of providers, digital signatures, etc. There is a fairly comprehensive Free plan - but more powerful forms can be built with the paid plans:
📷
The Scenario
On the forum, the question related to capturing building inspections - inspecting apartment units - and then updating a database with the results of these inspections. In order to show this, I have created databases in both Airtable and Knack, each with a Buildings table and an Inspections table. I am going to show how to take the values from a Cognito form with a repeating section for each unit and post these individually to each database (as you will see the process to post the new records is actually the same for each database). The hard work will happen in Zapier but let's start by having a look at the form.
Building the Form
In this article, the features we will be using in Cognito are all available in the Free plan - however, I am using a Pro plan as it enables me to create forms with a digital signature, save and resume, etc. I'm using this to provide consultancy terms for clients which they can sign and slowly replacing other forms used to run my business.
This particular form is pretty straightforward in that it simply has a few one time fields - Building, Inspector and Date and then a repeating section with six elements - a Unit number, overall Status and Notes followed by three rating levels for Decoration, Bathrooms and Kitchens. I have made all of the fields Required because otherwise the method of processing the values in Zapier would be much more difficult (or perhaps impossible) - and limited the repeating section to taking a maximum of 24 entries. This latter option is again due to Zapier - and I'll explain later on.
📷
Actually creating this form is simply a matter of dragging and dropping elements from the Add Field box:
📷
Notice that you can have text content sections, page breaks, tables etc. - plus the repeating section used here.
This is an example of a completed form due for submission:
📷
Once the form is complete, the next stage is to build the Zap.
Building the Zap to Process Multiple Records
At first sight, it doesn't really seem possible to use Zapier to process multiple entries and then loop through them to create multiple records in a database (or send multiple emails etc). However, this is possible - it just requires the use of a little Javascript to convert the input parameters (in this case the values from the repeating section) into what's called an 'Array of Objects'. This sounds complicated but actually it's quite simple - and I posted a pretty generic version of some code to do this in a previous article:
//this code is required to turn the string containing comma separated data into
//an array of objects. Because the output is an array of objects the following
//steps will run for each record found.
if (inputData.csString == null) {
var listArray = [];
} else {
var listArray = inputData.csString.split(",");
}
var output = [];
var arrayNos = listArray.length;
var i=0;
do {
var thisItem = new String(listArray[i]);
var thisItemObj = {};
thisItemObj.record = thisItem;
output.push({thisItemObj});
i++;
}
while (i < arrayNos);
As it says in the comments, this code 'returns' an array of objects - in this case with just one value per object called record. Subsequent Zapier actions will then run for each 'object' in the array. The only thing to be aware of with this is that Zapier has a limit of 25 'objects' that it will cycle through in this way, which is why I limited the repeating section to only access 24 entries in the form definition above.
This code also assumes that the triggering app (Cognito Forms in this case) will supply it's multi valued entries in a comma separated string - fortunately this is the case with Cognito with a comma separated string for each multi valued field. In preparing for this article, I discovered that Cognito will not include values for any field left empty - which is why I made all the fields in the form 'required'.
Now lets look at the Zap itself starting with the Cognito forms Trigger:
📷
All that's necessary here is to simply select the Form we are looking to 'pick up' (having set up a link to Cognito in Zapier)!
The next step is to implement the 'Code' Zapier step to split up all the comma separated strings from the form (1 for each repeating field) and combine them into 'Objects'. Each object will have a property relating to each repeating element (which is why we need a value for each field in the repeating section and they are required entries). The code is a little bit longer than the above - but works in the same way:
📷
Rather than sowing the code in it's Code box just below the Input Data Variables as a screenshot, I thought it would be more useful to show it as text:
//this code is required to turn the strings containing record values into
//an array of objects. Because the output is an array of objects the following
//steps will run for each record found.
if (inputData.stringOne == null)
{
var listOneArray = [];
var listTwoArray = [];
var listThreeArray = [];
var listBathroomArray = [];
var listKitchenArray = [];
var listDecorationArray = [];
}
else
{
var listOneArray = inputData.stringOne.split(",");
var listTwoArray = inputData.stringTwo.split(",");
var listThreeArray = inputData.stringThree.split(",");
var listBathroomArray = inputData.stringBathroom.split(",");
var listKitchenArray = inputData.stringKitchen.split(",");
var listDecorationArray = inputData.stringDecoration.split(",");
}
var output = [];
var arrayNos = listOneArray.length;
var i=0;
do {
var thisItemOne = new String(listOneArray[i]);
var thisItemTwo = new String(listTwoArray[i]);
var thisItemThree = new String(listThreeArray[i]);
var thisItemBathroom = new String(listBathroomArray[i]);
var thisItemKitchen = new String(listKitchenArray[i]);
var thisItemDecoration = new String(listDecorationArray[i]);
var thisItemObj = {};
thisItemObj.itemOne = thisItemOne;
thisItemObj.itemTwo = thisItemTwo;
thisItemObj.itemThree = thisItemThree;
thisItemObj.itemBathroom = thisItemBathroom;
thisItemObj.itemKitchen = thisItemKitchen;
thisItemObj.itemDecoration = thisItemDecoration;
output.push({thisItemObj});
i++;
}
while (i < arrayNos);
The resulting output is an array of objects each containing 6 fields (itemOne, itemTwo, itemThree, itemBathroom, itemKitchen and itemDecoration). The first three field names are from a generic version of this code which I use regularly hence the generic names.
Now all we need to do is to post these values to the relevant database - and in this example I am going to post them to both Knack and Airtable.
Posting the Results to Knack
Knack is a no code database / app builder (see earlier review) with which you can build secure forms based apps quickly and easily. Like all databases, it requires some understanding of Relational Databases to get the most from it but it does this in an approachable way. First let's look at the Zap action required:
📷
📷
When this Zap runs, the records are inserted into the Inspections table (Object in Knack parlance):
📷
Posting the Results to Airtable
Airtable is a very different type of database, offering more of a collaborative approach with visual cues from the Spreadsheet. It can feel a lot more approachable - but each of these options are suitable for a range of projects. Airtable is visually very appealing - but Knack offers more granular security and allows the design of the database to be more protected.
The actual Zap step is essentially the same as for Knack - except the link between Airtable and Zapier covers all databases and so when setting you Zap up you also select the Base(Airtable name for a database):
📷
📷
and this is what the record look like in Airtable:
📷
Summary
So we now have a complete process which takes multiple entries in a single Cognito form, processed it in Zapier and then posts it to two databases, Airtable and Knack. In the case of Knack, being more forms based than Airtable, it may be possible to achieve this inside an app - but specialist forms apps offer a lot of flexibility in design and flexibility that may be lacking in published database forms and so this technique may have many possible use-cases.
If you'd like to find out more about Cognito Forms, click here.
Or Airtable, click here.
Or Knack, click here.
I hope you've found this useful - please feel free to comment!
Thanks for sharing