In our previous posts in this series, we started our base with a data model of Organizations, Contacts, and Interactions, then followed that up by adding a Web Leads table and a web form for capturing those leads on our website, then we created an Opportunities table to track our pipeline, wins & losses, and we most recently created a process for converting the web lead into the main CRM date model. In this post, we are going to create a simple way to track our Tasks.
If other CRMs I have worked in, namely Salesforce, the Tasks object held both true Tasks (things you need to do) as well as Interactions (copies of sent emails, call notes, etc). This always confused users that I worked with. In this CRM system, we separate Interactions from Tasks. Interactions are the documenting of conversations that took place via Messages, LinkedIn, Email, Phone, etc. We set up that table in the first post in this series. Tasks are really about things you need to remember to do and you mark them as Done when you are finished.
Fields
We are creating a new Tasks table with a number of fields.
| Field Name | Data Type | Description |
|---|---|---|
| Name | Single Line Text | This is the standard Name field and is set to be a single line of text. Think of this as the Task title or subject. |
| Done | Checkbox | A simple Yes/No checkbox as to whether a task is finished or not. |
| Due | Date | The Date that the Task is Due to be complete. |
| Completed | Date | A date field that documents when the Task is actually completed. This field is being managed by an Automation. |
| Assigned To | Collaborator | Documents to whom this task is assigned, a user of our Airtable system. |
| Status | Formula | A formula field that looks at the Done and Due fields to display a status for the record. |
| Note | Long Text | A place to put a lot more Notes about the Task |
| Files | Attachment | A place to associate files with the Task |
| Contacts | Link to Contacts | Ability to associate a Task to specific Contacts. Turn ON “Allow linking to multiple records” |
| Opportunities | Link to Opportunities | Ability to associate a Task to specific Opportunities. Turn ON “Allow linking to multiple records” if you want to. |
| Web Leads | Link to Web Leads | Ability to associate a Task to specific Web Leads. Turn ON “Allow linking to multiple records” if you want to. |
| Created | Created Time | When the record was Created. |
| Last Modified | Last Modified Time | When the record was last Modified. |
Status Formula
The Status field is a formula field that looks at the Due and Done fields to figure out a status to display. The formula is below.
IF(Due = BLANK(), "", IF(Done, "Completed", IF(DATETIME_DIFF(Due, TODAY(), 'days') =-1, "
Due Yesterday",IF(DATETIME_DIFF(Due, TODAY(), 'days') =0, "
Due Today", IF(DATETIME_DIFF(Due, TODAY(), 'days') =1, "Due Tomorrow", IF(DATETIME_DIFF(Due, TODAY(), 'days') <-1, "
Overdue (" & DATETIME_DIFF(TODAY(), Due, 'days') & " days)", IF(DATETIME_DIFF(Due, TODAY(), 'days') >1, "Due in " & (DATETIME_DIFF(Due, TODAY(), 'days') & " days"))))))))The formula is doing the following:
- Is Empty if the Due field is blank
- “Completed” if the Task is Done
- Shows “ Due Yesterday” if the task was due yesterday
- Shows “ Due Today” if the task is due today
- Shows “Due Tomorrow” if the task is due tomorrow
- Shows “ Overdue (x days)” if the task was due prior to yesterday. The x is replaced with how many days overdue it is
- Shows “Due in x days” if the task is due prior after tomorrow. The x is replaced with how many days away the due date is
It is a nice touch to add some color to Airtable. It helps usability for users. This can be done in lots of places, anywhere text is used. To accomplish this for this fields, I am using the Emoji keyboard on my Mac, which I open with Control + Command + Spacebar. In there, I can find , , and . There are many more things to choose from.
Views
I created 3 simple views for this table.
Open Tasks
Displays all Tasks that are open, aka not completed yet. This has a filter for Done = False. I also am hiding the Completed field from this view.
Completed Tasks
Displays all Tasks that are completed. This has a filter for Done = False. I am showing the Completed field in this view because the field is populated when it’s completed.
Calendar
It’s always nice to see things in a Calendar view. The Calendar is based on the Due field and I am coloring the Tasks based upon whether they are Done or not.
Automations
I have 1 automation setup that populates the Completed field based upon whether or not the Task is Done. When it is, the system will timestamp when it was completed. If it’s not yet complete, the system makes sure that field is not populated.
As I have learned Automations in Airtable, I have discovered that there are important, missing features such as the ability to use a formula like NOW() or NULL to set a field value. Rather than have this, Airtable wants you to create other formula fields with the values you need and then use those field to set other fields. This seems very avoidable by Airtable allowing for some formulas within the automation. But, alas, they do not. So I turned to Scripting, which I find much easier.
The trigger for this Automation is the update of a Task record. To limit how often this automation fires, I am limiting it to watch only 2 fields: Done and Completed.
The action is a script. I am using 2 input variables that will allow me to have the record Id and the Done value from the record so I can use them in the script without needing to requery the record.
The code is very simple.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
let table = base.getTable( "Tasks" );let inputs = input.config();if (inputs.done){ await table.updateRecordAsync(inputs.recordId, { "Completed" : new Date() });} else { await table.updateRecordAsync(inputs.recordId, { "Completed" : "" });} |
Review
Up to this point, we have done the following in our CRM:
- Created a base set of objects for Organizations, Contacts, and Interactions (post)
- Created a new table to house leads that we capture from our website (post)
- Created an Airtable Form to add to our website that will take in the lead information (post)
- Created an Automation to clean up the lead record to set the Status to New (post)
- Created an Opportunities table to track our sales and marketing success (post)
- Created a button script on the Web Lead table to “convert” that lead to a corresponding Organization, Contact, Opportunity and Interaction record (post)
- Created a Task Management system that integrates with the rest of our CRM (this post)
The CRM template is near completion.
Comments