I am hoping that someone can help me out with an automation that I am trying to build. I am new to Airtable and am figuring things out as I go. Well, trying to at least.
We are a small behavior services company that works with adults with intellectual disabilities. Since our inception, I have been tracking how many hours we provided to each of our clients in Excel but it was too clunky so I have started using Airtable instead. So far I am impressed but I am having some challenges getting all the things I have pictured into my head to become reality in Airtable.
So far I have built a base that has a couple of important tables. One that has the necessary information that I need to track for our clients including what services they qualify for, a weekly tracking sheet that captures each clients hours delivered for that week and a rate sheet which then can tell me what those hours translate to in terms of billing payments. I also figured out how to build a form that allows me to easily select the client, choose the date of service, allows me to select which of the services we provided and the amount of hours we provided. This information is how I input what goes into the weekly schedule.
What I need to do now is create a way for these hours I input each week to deduct from the amount of hours the client has in their plan.
Allow me to explain, each client of ours is issued a service plan by the state. This service plan includes what services the client qualifies for and the amount of “units” they have for the life of that service plan which is typically one year.
Units are 15 minute increments. So if we provide 2 hours of a certain service to a client that would equal 8 units.
I need to figure out a way where when I enter in the number of hours provide to my client from the form it would be able to look up that client’s record on the client table, match it to the column where I have inputted each clients number of units, translate hours to units and then deduct it from that column. For example, if I enter in that we did 4.5 hours of “service X” it would find that column and deduct 18 units.
For bonus points I would love it where I could either create an automation that notifies me when a clients units fall under a certain threshold or if I am looking up the clients record there will be some kind of warning that they are low or out of units.
Thanks to anyone who made it this far. :)
Page 1 / 1
Hey @TMander!
This is pretty long, would you rather schedule a call and go through it together?
After out call we could go back to this post and share the different answers for any future reader :D
You just need 3 tables: A table of clients, a table of hours purchased, and a table of time used.
In your table of hours purchased, each record would have: (1) a link to the client, (2) the date that they purchased the hours, and (3) how many hours they purchased on that date. (You can also create a formula field that converts hours into units.)
In your table of time used, each record would have: (1) a link to the client, (2) the date that they used their hours/units, and (3) how many hours/units they used on that date.
Then, back in your table of clients, you would just create a rollup field to bring in the total number of hours/units purchased, and then you would create another rollup field to bring in the total number of hours/units used. Then, you would create a formula field that subtracts the 2nd rollup field from the 1st rollup field, which will show you how many hours/units are leftover.
Your automation would trigger off that formula field, as soon as that formula field drops below a certain level.
Hope this helps!
Since you’re new to Airtable, you might benefit from taking my free Airtable training course, which you can take for free by signing up for a free 30-day trial with LinkedIn Learning. The course is very outdated because it was created many years ago, but the core concepts of linked record fields are still relevant today. I don’t cover rollup fields in my course, but I do cover lookup fields — which are very similar to rollup fields.
Here's how I'd set up the tables and fields to make this work
Clients Name | Plan (linked to Plans)
Plans Plan Name | Service (linked to Services) | Units Allowed
Services Service Name | Rate
Weekly Log Client (linked to Clients) | Date | Service Provided (Linked to services)| Units Used
Each Plan defines what Service the client is eligible for and how many Units they are allowed.
Each Client links to a Plan, so they inherit the service and unit allowance.
The Weekly Log records service usage by client, date, and hours provided.
On the Client table, you can add a Rollup field called "Used Units" that rolls up from the linked Weekly Log records, targeting the "Units Used" field in the Weekly Log table.
Then, add a Lookup field to bring in "Units Allowed" from the linked Plan, and a Formula field to calculate Units Remaining by subtracting Used Units from Units Allowed.
To show a warning when a client is running low or out of units, you can create a formula field in the Clients table that checks the Units Remaining value. For example, the formula could be something like IF({Units Remaining} <= 20, "Low", ""), which will display a warning message when the client has 20 or fewer units left.
You can then set up an Airtable automation that watches this field. Whenever the “Low” text appears, the automation can send you an email.
Does this look right? I’ve set it up here for you to check out
Some notes:
You mentioned that that there are service plans have a life and a set number of units, to that end I created a ‘Client Plants’ table where each record represents a single plan for each client, along with the start and end date. There’s a ‘Status’ field that will output key information about that plan
Low = <5 units
Expired = We're past the End Date
Active = Still in use
Fully used = 0 units
This Status field would then power the automation you mentioned wanting to get an alert when they’re low / out of units:
The ‘Unit’s calculation currently goes to one decimal place, if you’d like for it to round up you could add a ROUNDUP to the formula too
I set up the linked fields to use conditional logic to make data entry easier. For example, when selecting plans, only active plans will be selectable:
And when selecting services, only services tied to the Client Plan are available
re: I need to figure out a way where when I enter in the number of hours provide to my client from the form it would be able to look up that client’s record on the client table, match it to the column where I have inputted each clients number of units, translate hours to units and then deduct it from that column.
Hm, does this mean that each Service has its own number of units? If so, this complicates matters a bit
Does this look right? I’ve set it up here for you to check out
Some notes:
You mentioned that that there are service plans have a life and a set number of units, to that end I created a ‘Client Plants’ table where each record represents a single plan for each client, along with the start and end date. There’s a ‘Status’ field that will output key information about that plan
Low = <5 units
Expired = We're past the End Date
Active = Still in use
Fully used = 0 units
This Status field would then power the automation you mentioned wanting to get an alert when they’re low / out of units:
The ‘Unit’s calculation currently goes to one decimal place, if you’d like for it to round up you could add a ROUNDUP to the formula too
I set up the linked fields to use conditional logic to make data entry easier. For example, when selecting plans, only active plans will be selectable:
And when selecting services, only services tied to the Client Plan are available
re: I need to figure out a way where when I enter in the number of hours provide to my client from the form it would be able to look up that client’s record on the client table, match it to the column where I have inputted each clients number of units, translate hours to units and then deduct it from that column.
Hm, does this mean that each Service has its own number of units? If so, this complicates matters a bit
Great start here - thank you for this. To answer your question, yes - each service plan has it’s own number of units and it varies per client. For example, we have a service called SCC. Not all of my clients qualify for SCC and the one’s that do have various amounts of units.
There are some services that do have a usual consistent number of units forever since each client has a different term period they rarely have the same amount of units remaining.
Ahh yeah, then you’re going to need a table where each record represents a single Plan <> Service, which will let you set the individual service units per plan. I set it up in a new base as I had to delete a bunch of fields / update the automations, and wanted to leave the original for you as a reference just in case
And you’d have an automation that would help you link them together like so:
