Training Center - Updating remaining free hours


#1

Hi everyone !

After reading formula guide, I have few questions regarding what’s doable with Airtable (i’m not really familiar with Excel formula, so any advice is welcome).

I’m working in a training center for Maternal assistant and life assistant for dependant person. We have various training that last from 14 hours to 35 hours. Each year, these trainee have 58 ! hours of free training. We use Airtable to follow the subscription process and to know which training they choose. It is important for us to know how many free hours they still have.

For now, we use 3 tables for that :

  • First one is a database with Trainee ID, the training they’ve choosed this year, their remaining free hours
  • Second one is our subscription table : fields are grouped by training dates AND training - we use it to check if we have all the pieces for the subscription
  • Third one is a “training database” : We have all the training names, teachers and hours for each… I created it to link the content to the first and second tables

We follow their remaining hours on the first table.

My questions are :

  • Can I create a formula that associate a number of hours for each training ? ( What I mean is that on our third table, we have each training and their duration - Can I link them or must I redo it in the formula ?)
  • Can I create a formula which automatically upload their remaining hours according the training they choose? (If X training is putted, which last 14 hours, then the next column goes from 58 hours to 44 hours)

Sorry for my frenchie english :smile: I hope this was clear enough.

Thanks all !


#2

You can use a Look Up field in the Subscription table to get the number of hours of training associated with each subscription. (Use a Roll Up field if there are multiple training events associated with one subscription).

You can then use a Roll Up field in your Trainee table to sum the number of hours from the trainee’s linked subscriptions. You could then use a formula field to subtract that number from 58 to get their remaining hours.


#3

Hi @David_Skinner

Thank you so much for your help, it works as intended !
Took me some time because testing your solution made me realize few mistakes in the tables organization. Now it works perfectly :slight_smile: