Knack is loaded with features right out of the box that make it simple to build powerful online database apps. What can be easy to forget is how much more power you can add by integrating other services with your Knack database.
Our API makes it easy for developers to add integrations, and Zapier is great for creating simple connections and triggers between these services.
In our first guest blog post, Alex Mohseni explains how he used Knack and Zapier to build a TripIt like clone to help manage education credits.
Alex is a practicing Emergency Medicine physician around Washington, DC. He integrated Knack with services like DropBox and Mandrill to combine their functionality into a single powerful service that gave him the exact features he needed.
Part of what makes Knack so powerful is that it integrates easily with Zapier — but how to do that can be a bit tricky, so Alex decided to include some of his “lessons learned” with a fantastic tutorial.
Take it away, Alex!
I am not a programmer; but, I am a physician who is easily frustrated by inefficient processes, and I like to solve problems myself. A few months ago, I decided to solve the following problem: all physicians have to track their continuing medical education (“CME”) credits, in order to maintain their licensure. Invariably, keeping track of one’s CME credits ends up much like keeping receipts for your income taxes: you stuff receipts into a box and pray that you have captured everything.
Rejecting this workflow, I decided to build a better system. What I planned to do was simple: just like TripIt, in which one can forward travel itineraries to email@example.com and have one’s travel data aggregated automatically, I wanted to forward my CME certificates to an email address and have my CME captured and organized automatically for me. Additionally, I wanted this service to work for any physician, even if he/she had not previously signed up with my service.
In learning to use Knack and Zapier, I sometimes wished that I had more actual examples to follow. Now that I’ve built this service, I am sharing my multistep zap as a detailed example for others. In this write-up, I’d like to share how I used Knack, Zapier, Dropbox, Google Drive, and Mandrill to build this automated system, which I built at cmelog.org. Here is a general overview of the system:
Email with CME attachment is sent to firstname.lastname@example.org > Attachment is saved to Dropbox > Backup and log is created in Google Drive > Data and link to attachment is all saved in Knack (cloud database) > confirmation email is sent via Mandrill
This workflow is only possible because of Zapier’s multistep zaps. Below, I will show each step of my multistep zap that performs the above workflow. This demonstration assumes a basic working understanding of Zapier.
1. Trigger the workflow: Gmail – New Email
The first step is to trigger the multistep zap when a new email is received. My email provider for CMElog.org is Gmail, so I use the Gmail trigger.
I set the option for new emails in the Inbox.
So now, I can access the first name and last name as separate data points.
3. Add sender to my mailing list: Mailchimp
I want to make sure that I add every active user to my mailing list, so that I can send them important updates about the service. Now that I have the sender’s email address from Step 1, and their first and last name from Step 2, I have everything I need to add them to my mailing list. Because the sender may already be on my mailing list, I use the “Find a Subscriber” option in Mailchimp:
In the Edit Options section, I built it as follows:
As you can see, if the sender does not already exist in my list, then he/she is added.
4. Filter out non-CME emails: Zapier Filter
What if somebody sends me an email that is not a CME certificate (e.g., an email just asking for help with the service)? I need to make sure that the rest of the zap only applies to emails that have CME certificates as attachments. I used the Zapier Filter action for this. And, to understand my filter a bit better, I asked all my users to include the number of credits in the subject line of their email. Therefore, if an email does not have a number in the subject line, then it is not a properly submitted CME certificate.
As you can see, the filter checks to make sure that there is 1) at least one attachment, 2) there is a subject in the email, 3) the subject is a number greater than zero, 4) and the subject is not two of my other special terms (“balance”, and “log”).
If all of the above filter parameters are met, then the multistep zap continues.
5. Create an account in Knack: Knack – Create Record
I use Knack. I love Knack. Knack is an absolutely amazing cloud based database service that is a blessing to non-programmers, such as myself. It’s easy to use, and it has really great tools for creating a beautiful front end for your database. When cmelog’s users access their CMElog.org account to review their prior CME submissions, they are interfacing with Knack. Here’s a screenshot of what I see as a CMElog user (this is built using Knack):
As you can see, I can see a table of my prior CME submissions at the bottom, my personal details at the top, and buttons to submit new CME and export my CME log in between.
Here are some key things I learned about using Knack with Zapier:
- When uploading a document (such as a CME certificate) to Knack via Zapier, if the record is to have a connection, such as to a user, then that user needs to exist first. Meaning, you can’t create both a new user and connect the certificate at the same time. This is obvious in retrospect.
- When uploading a certificate into Knack via Zapier, I could not perform a search first, in order to see if the sender was already in my Knack database (“create record” is the only option currently). The ideal workflow would have been to check Knack to see if the user already exists, and if he doesn’t, to create an account. The solution was to attempt to create a new user account for each email. Either of two things happens: the user already has an account, in which the account creation zap step fails, but the zap continues, or the user doesn’t exist, and a new account is created for that user. This is not an elegant solution, because it means I get a lot of “zap error” messages, when the zap is correctly identifying that the user already has an account in Knack, but I just ignore these until I build a workaround.
- You can’t send the actual file attachment through Zapier to Knack, but you can send a link to the file.
Connecting to Knack via Zapier is easy: just look up your Application ID and API Key from within Knack (here I’ve hidden them in red):
Creating a new user is then just a matter of assigning your Zapier inputs to the right fields in Knack:
6. Upload attachment to Dropbox: Dropbox – Upload File
Before I can upload the attachment to Knack, I have to store the file in the cloud and get a link to it. I use Dropbox for this because I learned that when you upload a file to Dropbox using Zapier, you automatically generate a link for sharing the file (not the case with Google Drive, as far as I could tell). In other words, when uploading to Google Drive, the default status for the file was not shared. In uploading a file to Dropbox, you can also decide on how to name the file. I decided on a convention you can see below:
7. Keep a log of all users: Google Sheet
I wanted to make sure that I have an spreadsheet with all of my users. In order to do this, I used the Google Sheet “Find or Create Row” action, which is a legacy action now. Nonetheless, it’s pretty simple: first lookup if the user is already in the list on the sheet, and if not, add the user.
8. Log the certificate: Google Sheet – Create Spreadsheet Row
Even though everything was going into a database, I wanted to make sure I had a separate log of each certificate as a backup. The log entry, as you will see, includes a link to the file in Dropbox. Note how by uploading to Dropbox, I am able to access a Dropbox field called “Share Link.”
9. Upload same certificate to Google Drive as a backup: Google Drive
This is very similar to Dropbox.
10. Create CME record in Knack: Knack – Create Record
Now that I have my file as a Dropbox link, I’ve created my backup of the certificate, and the sender has an account in Knack, I am ready to create the CME record in Knack. This is just a matter of matching up Zapier fields with Knack fields. I learned a couple tricks though:
In order to connect this CME record to the right user in Knack, I have to have a connection field, and that field needs to be something I can input via Zapier. For this reason, I use the user’s email address as the connection field.
Also, I had to create two separate “file” fields in Knack for each CME record. One field is actually a link field, to store the link to the file in Dropbox. The other field is a true Knack file field, for those users who upload their certificates directly in cmelog.org.
11. Confirm that record was uploaded into Knack: Filter
Next, I used the Filter action to make sure that the record was successfully uploaded into Knack. I do this by checking to see if an “ID” exists for the record that I just attempted to create in Knack.
Here’s a screenshot of how you access the Knack record’s ID:
12. Extract data from Knack: Webhooks by Zapier
I have a table of sponsors for cmelog, and their information is stored in Knack. I show different sponsors’ information in my transactional emails on different days. Somehow, I needed to access this information from within Knack via Zapier. This, for me, was one of the trickiest things to do, as Knack’s Zapier actions currently are limited to “Create Record”, not to read records.
I decided to use the Webhooks function, using a GET command.
Here’s how you define the right URL for the Knack API as well as how you set up filters so that you can query Knack to find the right record:
13. Send confirmation email to user: Mandrill – Send Email
Finally—we have extracted all of the relevant user data, uploaded the attachment to Dropbox, created the user account in Knack, created logs and backups, uploaded the CME data to Knack, and verified the upload—we are ready to send a confirmation email back to the user.
I use Mandrill for my transactional email. Here’s what the actual email that users receive looks like:
Here are the settings and HTML I wrote to create the above email:
I hope the above explanations and screenshots help other non-programmers build awesome tools that help all of us be happier and more efficient. I could not have created my service without Knack, Zapier, and Dropbox, and am so grateful for their support in building cmelog.org.
Alex Mohseni, MD, is a practicing Emergency Medicine physician in the Washington, DC, metro region, the Chief Innovation Officer for Emergency Medicine Associates, founder of CMElog.org, and the technology writer for Emergency Medicine News. He has been using Knack to build solutions for multiple healthcare and business related applications for more than one year, including solutions for managing employee expenses and company-wide performance dashboards.