Whether you're working with a team on a large-scale project, or tracking changes across hundreds of records every day, you may want a quick way to see what's most recently been updated.
With Airtable's last modified time field and an integration built with third-party tool Zapier, you can create a custom email digest to put those updates directly in your inbox.
Build the foundation of your daily digest
Airtable’s last modified time field will automatically mark the date (and time) of the most recent change to a record. You can even tell it to only update when certain fields have changed. This can come in handy in Airtable, all by itself, to help you sort and group records based on most recent activity.
You can also build on top of it to create other useful tools, like the daily digest we’re learning to make right now. Because the last modified time field updates automatically, we can use some simple logic to create a view that tracks daily updates. Later, we’ll provide Zapier with that view in order to create a daily digest that will round up every update and send them to you in one, concise summary that you design.
First things first, create a new field and choose Last modified time as its type. We’ll name it Last update. (You can choose another name, but you might want to stick with it for now because we’ll use it in a formula later. If you pick something else, just make sure to update the formula accordingly once we get to it.)
When you do, you’ll notice you have an option with All editable fields chosen as the default. If you want this field to update whenever someone changes any other field in a given record you want the default choice. If you want to ignore changes to specific fields, however, you can choose the Specific fields… option instead.
This way you can effectively ignore updates to less-important fields with a simple toggle. Once you’ve made your selection, click the Formatting tab if you want to adjust the way the date (and time) stamp displays and click Save.
Now the field will operate according to your choices. If you change your mind, you can always double-click its header and change the way it updates and displays its information.
Create a “Today” view
Once you have your last modified time field in place, you need a special view that only shows records that received an update during the current day. We’ll give this view to Zapier later so it will only see records that received updates on the current day.
From the view selector, create a new grid view and name it Today.
Next, create a filter where your last modified time field must include today’s date.
This will display all records with a last modified date that’s the same as the current date—everything we want to send in our daily digest.
Now, we need to do a couple of extra things in order to make this setup work with Zapier. Because Zapier won’t trigger on the same record twice by default, we’ll use a few tricks to work around that limitation. In order to employ those tricks, however, we’ll need to create a date field that Zapier can fill in to keep track of the records it processed (and when) and a formula field to determine the status easily.
To get started, first create a new date field (with a time field included) called Zapped.
We’re going to let Zapier use this field to tell Airtable that it acquired the contents of this record already so you don’t need to do anything beyond creating it right now.
Next, create a formula field called Hide.
Now, add this formula to it:
IF({Zapped},IF({Zapped}={Last update}, 'yes', 'no'),'no')
If you changed the names of any of the fields we’ve created you’ll need to alter this formula accordingly. If not, it should work without any changes. The formula does nothing other than check if the Zapped field has anything in it and, if it does, see if the Zapped and Last update field contain the same date and time. If it does, the formula will return a yes. If it does not, or if the Zapped field is empty, the formula will return a no.
This comparison tells us if Zapier processed the latest update to a given record. With the formula in place, we can add this field to our filter to ignore any records that have already been processed for today.
Don’t worry if this doesn’t make sense just yet—once we get started making the digest in Zapier, it will all come together. For now, we just need to save this field and make a small update to the filter in the Today view.
Add a new filter underneath the one you created earlier, make sure to set it to AND (rather than OR) and set it to filter on the Hide formula field we just created. Choose is not and set the blank field beside it to yes so any records that have a yes won’t show up in the view.
This will ensure that the Today view only shows records updated today and that Zapier has yet to process. With your filters in place, you’ve finished setting up your base. Now it’s time to move onto Zapier.
Create a daily digest with Zapier
If you haven’t used Zapier with Airtable before, you’ll first need to follow the steps to connect your Airtable account. Also, you’ll need a paid Zapier account in order to set this up because it requires more than one action step. We’ll create two zaps—one to create the digest and another to send it—so make sure you have two zaps available in your account’s quota as well.
Zapier won’t trigger on any record more than once, so instead of using a record trigger we need to create one that will trigger frequently to check for new records. We’ll use the Code trigger to do that (but don’t worry, you don’t have to write any code). After that, we’ll search for updated records to add them to your digest. Zapier can only find one at a time, and so we need it to search for updates so often using the Code trigger trick.
To begin, log into your Zapier account, create a new zap, choose Code as the trigger, and click Continue.
This might seem like a weird choice, but because Zapier will only trigger on records it hasn’t processed before we need to use a few tricks to make that happen. The Code trigger will let us do that, and we don’t even need to write any code. All you have to do is choose the JavaScript option and click Continue. The code just needs to have an output line that creates an object, and Zapier provides that by default.
After you’ve tested this step, add a new step, search for Airtable, select it, and click Continue. Next, choose Find Record as the action for this step and click Continue again.
Next, we need to tell Zapier how to find the records we want. Thanks to the Code trigger trick, this zap will run every five to 15 minutes to look for new records so it will keep searching in case more than one updated record exists. If it fails to find one using this Find Record action, Zapier will halt it automatically so you don’t need to do anything special.
In order for this to work, we need Zapier to find only the records we want it to process and we’ll use the Hide formula field we created earlier to accomplish that. When editing the Find Record template, set the base and table to the one with your last modified time field and tell Zapier to search for records that have their Hide field set to no.
You’ll also need to limit Zapier’s search to the Today view we created to only display records with a last modified time that’s consistent with the current day.
When finished, click Continue, test this step to make sure it works, and then add a new step and add Airtable again. This time, however, choose the Update Record action.
This step will ensure Zapier doesn’t keep adding the same record to your digest every time your zap triggers. Because Zapier only pulls one record at a time, we need it to mark the record it finds so we can hide it from the Today view. This is why we created the Zapped date and time field—now we can finally put it to use.
In this Update Record step, we’re going to tell Zapier to fill the Zapped field to trigger the formula in the Hide field so that it will display a yes instead of a no. This will prevent Zapier from triggering the same record until it gets updated again.
Continue your way to the Edit Template portion of this step and make the same base and table selections you made earlier. In the Record field, choose Use a Custom Value. This will add a new field called Custom Value for Record ID and you can use that to specify the record Zapier found. Just click the button on the right end of the field and find the record ID acquired in your second step (Find Record).
With that in place, we just need to update the Zapped field to contain a date and time stamp that matches the last updated time from this current record. We got that in the second step, too, so add the last modified time as the input for the Zapped field and you’re good to go.
When you’re done, click Continue and test the step to make sure it works. If it does, you’re almost done! To complete this zap, we need to add this record to a digest. To make that happen, add a new action step and choose Digest.
For the action, choose Append Entry and Schedule Digest.
In the Edit Template section of this step, choose a Title for your digest that’s under 32 characters (the max that Zapier will accept). You’ll only use this name in the setup process, so it just needs to be short, unique, and memorable.
In the Entry field you can format the way each entry will appear. You should add spaces or some kind of visible separator (like a series of hyphens) at the top and/or bottom to separate each entry from the others. To add the fields from your Airtable record, just click the button on the right corner of the Entry field and choose what you want to include from your second step (Find Record).
Finally, set the Frequency field to Manual.
Why a manual trigger? If we ask Zapier to trigger it automatically, it needs to happen in this zap after it searches for new records to append to the digest. If Zapier can’t find any when the digest should release itself, it will never even reach this step and cannot perform that task. Instead, we’ll make a second zap to release it after completing this one.
On that note, test this step. If it works, click Finish, name it, and turn it on. The hard part’s over!
Fortunately, the second zap doesn’t require much work to set up. Go ahead and create a new zap and choose Schedule as the trigger. Next, choose Every Day as the action and click Continue.
Next, Zapier will ask you to decide when you want this zap to trigger. Choose according to your preferences and click Continue.
You’ll now need to test this step, and you may notice during the test that this trigger supplies a lot of date and time information. You may want to use this when formatting your digest in the subsequent steps.
Once you’ve finished testing, go ahead and add a new action step. Choose Digest and click show less common options so you can choose the Release Existing Digest option.
Click Continue and fill in the name of the digest you created earlier. If you don’t remember the exact name, Zapier will automatically provide you a list of active digests to choose from so you can find it.
Click Continue and test the step to see the contents of your digest. Since you’ve just begun, it probably has only a single option. After a successful test, you have one more action to add. For this example, will choose Email but you can choose any app that Zapier supports and can serve as a destination for your digest. You can send it back to an Airtable base, save it as a note in Evernote, push it to Slack as a message, or anything else that can handle a decent amount of text in the way you prefer.
Regardless of what app you choose, you’ll find the process doesn’t change all that much. For email, we’ll go with the only option of Send Outbound Email for this action. For other apps, you’ll want to chose a comparable action. Make your selection and click Save + Continue.
In the Edit Template section of this step, fill in each field like you’re composing an email. Set the To field to any email addresses (separated by commas) that should receive the digest. Next, enter a Subject of your choice. You may want to grab the current date from the first step you created in this zap. Finally, choose Final Digest (from the second step) as the contents of the Body field. You can also add any other text you want.
Feel free to fill out any of the other optional fields if you wish. If you’ve chosen another app as a destination for your digest, you’ll probably fill out different fields. Nevertheless, you just have to make sure the Final Digest ends up in your app’s equivalent of the Body field.
Finally, click Continue and test the zap. If everything works, you should receive an email (or whatever you chose) with the contents of your digest.
If everything looks good, name your zap and turn it on.
Now you have everything in place to send out a daily digest of Airtable record updates automatically.