A while ago, my boss asked if it was possible to set up an automated tip of the day. His idea was to have an Airtable base with a list of tips and use Make to grab one each day and post it into Slack.
I’d already come up with my own auto-numbering system in Airtable, and I knew this would be key to getting what we wanted. I set about it with the following goals in mind.
- Post only one tip each day.
- Keep track of the tips posted.
- Keep track of the tips ready to post.
- Make it simple enough for anyone to add a new tip.
- Make it possible to re-order the tips.
- Make it possible to put a tip back in the queue for reuse.
We’ll need two tables for our base, and a linked field between them.
Table 1 - List, part 1
We’ll call our first table ‘List’, and our second table ‘Queue’.
For the ‘List’ table, we need the following fields:
- Short Name (Single Line Text)
- Techtip Text (Long Text)
- Next (Rollup with a calculation)
- Posted (Checkbox)
- Timestamp (Formula)
- Queue (A link to a single record only)
- TimeStamp Lookup (Lookup a Rollup field from the Queue table)
- Date for Make (Last Modified)
For the ‘Queue’ table, we need these fields:
- Name
- List (Link to multiple records, generated by the Queue field in ‘List’)
- TimeStamp (Rollup - ArrayJoin(values,’;’)
- Form URL (Formula, you only need this long enough to generate the url)
We’ll start by creating a new base and naming our first table ‘List’. We can rename and change the types of our default fields.
- Name → Short Name
- Notes → Techtip Text (Long Text)
- Attachments → Posted (Checkbox)
- Status → Timestamp
We’ve got our first formula, and it’s simple. This field is how we’re going to automatically number our records, and choose our tip of the day. We need a value that will be unique for each record. For my purposes, I know that I’ll have records created sequentially so a date/time should be enough to get a unique value. If you’re creating records en masse (import, or multiple people adding at the same time) you may be better off using the Airtable Record ID.
For me, I’ve used the formula DATETIME_FORMAT( CREATED_TIME(), ’X’ ) to get a unix timestamp.
Our next field is going to be the link. Create a new field and name it ‘Queue’ choose the field type ‘Link to another record’. Uncheck the option to allow linking to multiple records, and have it create a new table.
Next, we’ll add a date field and call it ‘Date for Make’. This should be a ‘Last modified time’ field and should look at ‘All editable fields. You can also do this as a formula. You won’t need the time in the Formatting.
And we need a second checkbox field called ‘Review’.
We have a couple more fields to create. Since they require fields from the Queue table, we’ll finish that first.
Table 2 - Queue
Only a few fields needed here. Our first default field is fine as is. Let’s rename the Notes field to ‘TimeStamp Rollup’. Change the field type to ‘Rollup’ and select the ‘TimeStamp’ field from List. Use the Aggregation formula ‘ARRAYJOIN(values, ‘;’)
Then, rename the Attachments field to ‘Form URL’. You’ll need the share link for the base, so grab that. Then switch this field to a formula.
CONCATENATE(‘paste your share url here’, ‘?prefill_Review=1&prefill_Queue=’, RECORD_ID(), ‘&hide_Queue=true&hide_Review=true’)
We need two records named: ‘Up Next’ and ‘Posted’. Delete the third record.
Table 1 - List part 2
Now we make the magic™ happen.
Group your current view by the ‘Queue’ field, and Filter it by the ‘Review’ field is ‘unchecked’, and ‘Posted’ is ‘unchecked’…
Make a new field called ‘Next’. Set it to be a Rollup of the ‘TimeStamp Rollup’ field from ‘Queue’ and use the Aggregation formula:
FIND({TimeStamp} & ‘;’,values&’;’) - FIND({TimeStamp},SUBSTITUTE(values&’’,’;’,’’)) +1
List Views
Our first view can be called ‘Create’. Link one record to the ‘Up Next’ record in Queue, and link a second to ‘Posted’. Posted disappears because it’s filtered out. Delete the third record. Note that creating a new record in this view automatically links it to ‘Up Next’. This is your queue.
We’ll create 3 additional views along with one form.
Up Next List For this view you can hide all fields other than ‘Techtip Text’. Filter it by ‘Queue’ contains ‘Up Next’. Sort it by ‘Next’ from 1>9. You can also color the fields if you’d like a visual que for the next item to post.
Today’s TechTip This view is what we’ll use in Make for our automation. You can hid all fields except ‘Techtip Text’ and ‘Date for Make’. Filter it by ‘Next’ = ‘1’, and ‘Queue’ contains ‘Up Next’. You’ll only ever have one record here.
Tip Suggestion Box Your form. Show the ‘Short Name’, ‘Techtip Text’, ‘Review’ and ‘Queue’. Use the link that we generate in the ‘Queue’ table from the ‘Up Next’ record.
For Review Tips entered via the form will show up here first. I’d hide all fields except ‘Techtip Text’, ‘Queue’, and ‘Review’. When you’re reviewing suggestions, verify the ‘Queue’ field is ‘Up Next’ and uncheck the ‘Review’ when you think things look good.
Make
The last thing to do is create a workflow in Make (you could also build your own in Airtable, or use Zapier or any of the other no-code platforms).
We need 3 actions in Make. Two ‘Airtable’ actions with a ‘Slack’ action in the middle.
Your first action is to watch the Airtable base. Limit it to a single record in your ‘Today’s TechTip’ view (extra precaution) and trigger it on the ‘Date for Make’ field.
Action two is to post to the Slack channel of your choice.
Action three will update our record in Airtable to bump the queue. In the same base, have it update and use the Record ID (ID) from the first action. Set the ‘Posted’ field to ‘Yes’ and empty the ‘Review’ field (or set to ‘No’). Lastly, add the Record ID of your ‘Posted’ record to the the ‘Queue’ field.
The last thing to do is set the Schedule for the Scenario. You can choose the days to post, and a time. Or you can have it run all the time.
Make your first tip, an invitation to add to the suggestion box and provide the link to the form.
