Client Tracking Automation Question

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

 Mike, Consultant @ Automatic Nation

Hi ​@TMander,

Welcome to the community!

Yes, this is extremely easy to setup.

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.

If you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

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.

Taha, Airtable Advisor

Does this look right?  I’ve set it up here for you to check out

Some notes:

1. 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
   1. Low = <5 units
   2. Expired = We're past the End Date
   3. Active = Still in use
   4. Fully used = 0 units

2. This Status field would then power the automation you mentioned wanting to get an alert when they’re low / out of units:

   1. 

3. 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

4. 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:

   1. 

   2. 

       

5. And when selecting services, only services tied to the Client Plan are available

   1. 

   2. 

       

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:

1. 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
   1. Low = <5 units
   2. Expired = We're past the End Date
   3. Active = Still in use
   4. Fully used = 0 units

2. This Status field would then power the automation you mentioned wanting to get an alert when they’re low / out of units:

   1. 

3. 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

4. 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:

   1. 

   2. 

       

5. And when selecting services, only services tied to the Client Plan are available

   1. 

   2. 

       

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: