Staff Allocation Tool in Airtable

Kenny Abarca CEO | Apr 23, 2020

Why should you build a staff allocation tool in Airtable? For the last 2 years, we have relied on Resource Guru to manage the Tetris of resourcing allocation for our staff of 58 employees. What we liked about Guru is that unlike other SaaS platforms out there, the simplicity to be focused specifically on the allocation itself makes it perfect for what we needed. Many other tools provide things like project budget, management and tasks assignment which are things we already manage somewhere else.

The downside of Resource Guru is the pricing model in which you have to pay for every employee listed, even though they won’t be accessing the platform which is a drawback for us. Our latest invoice was for $170, a little too much considering that a single person manages the bookings and three others access the platform but in view mode only.

Now, our team is very familiar with Airtable since we are already using —moreless abusing— it as a backend for our website built in GatsbyJS on Netlify. And recently, someone from the team popped up the question: what if we handled the staff allocation or team workload with Airtable? And that’s how it started.

How to build a staff allocation tool using Airtable

Here’s a link to a template of the base table, go ahead and copy it into your account using the “copy base” button on the top right corner. You will need to have a paid Airtable account in order to use the blocks functionality such as Gantt and charts.

airtable slack

As you can see, the fields are pretty self explanatory and one important aspect is that we wanted to keep it as simple as possible, just like Resources Guru but you can go ahead and extend it further with things such as a Department or SoW attachment fields.

Notice there are two views you can access using the dropdown arrow as described below. There’s an All Bookings view containing all records with no filters added. The view called Current Bookings has a filter applied to display only the bookings currently active, using the field Days to end> 0.

Screen Shot 2020-04-09 at 14.43.11

Regarding the part where we automate the process of having Airtable send notifications to a channel in Slack when a booking is 8 days from ending, Airtable itself cannot perform this task so we did it through Integromat but it can also be done in Zapier.

First, you need to create a free account in Integromat and then select Airtable and Slack from the list of applications.

On the next screen, click on the (?) and select Airtable. Then select Search Records from the list. On the next window click on “Add” in order to connect with Airtable and retrieve your base. You will need to get the API token from Airtable (find your API token) in order to connect.

Screen Shot 2020-04-10 at 11.54.02

Then Integromat will take you through the rest of the selection process by selecting the base and the table. The Formula field contains ( {Days to End} = "8") where Days to End is the name of the field column in Airtable and 8 is the specific count when we would like to have the trigger. Make sure to select the right view in the following dropdown as shown in the image below and add a _Max records number.

formula integromat

Once you have them all filled out, go ahead and click OK. Now click the Run once button located in the lower left corner. If everything goes well, you should see no errors and Airtable records coming in. Congratulations!

Now that we have the records, we need to trigger the Slack message. Either double click or right click anywhere in the canvas to Add a new module, then select Slack. Next, click Add to connect the Integromat app to Slack.

You will be presented with the list of channels so you can select where the notifications will be posted. Then, using the long text field, choose the records that will be included as part of the message. You can even add icons for display as part of the message! Click Ok to save and close the window.

slack-build-message

If you like, go ahead and once again click on Run once in order to make sure the whole process runs smoothly. If it goes well, you should get a Slack message posted in your channel.

slack-message

Now, let’s schedule the cron to run at a particular time by clicking the clock. In this case, we have it setup to run at 9:00am every day. Play around with the different options to fit your needs.

schedule-settings

That’s it! Enjoy your new Staff Allocation platform and customize it further to fit your needs. And don’t hesitate to reach out if you have any questions or comments.

Related Posts

KeepTrack Tool

By WeKnow | June 2, 2019

After a long process of trial and error within our team, we ended up creating our own HR tool to manage our resources. We had tested several…

- READ MORE
weKnow’s remote working guide to success

By admin | October 2, 2018

People’s work needs and styles have changed to facilitate never before comforts; as it offers many benefits such as avoiding the dreadful…

- READ MORE
weKnow’s remote working guide to success - Part 2

By admin | November 2, 2018

As a fully distributed company, weKnow supports remote working; a form of management and daily routine that may not be for everyone but, we…

- READ MORE
Want to learn how to take advantage of these modules?

We can show you how these modules can improve your Drupal and Gatsby integration.

Contact us for a 30-minute demo today!