Skip to main content

Formula for totaling check in and check out hours for Study Hall

  • September 18, 2022
  • 2 replies
  • 48 views
B

Hi Everyone, I know very little about formulas and only a little about designing a base. I am trying to create a table to track the hours of those attending study hall. Here is the screen shot

I have a form set up so the student can check in then check out. I could not figure out how to make the form reappear so they could check in and later check out on the same form. So I created one form and they have to check in and when they leave check out.

This formula is wrong but maybe you can get an idea of what I want to do: IF(IDs = IDs, {Check In Date-Time} = {Check Out Date-Time}DATETIME_DIFF({Check Out Date-Time} - {Check In Date-Time})

2 replies

Rupert_Hoffsch1
Forum|alt.badge.img+21

Hi there! At the moment your check in and check out times are in different records, so you can’t run a formula against them. You could create an automation that matches the ID and copies the checkout time into the first record, though this is quite tedious (potentially even requires a script).

Unfortunately you can’t reference a record in forms in Airtable and a form submit will always create a new record. Maybe you have the time to look into tools like Stackr, Softr, etc. which are made to manipulate existing Airtable records from the outside (aka without creating an Airtable account for every student).

Let me know if you have more questions re. this!

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • September 19, 2022

@Brenda_Snider You will need to get all of your data on the same line in order to do the math you want.

Based on your existing setup, probably the easiest & quickest way to solve this would be to figure out a way to link the check out record to the check in record using a linked record field. Once you do that, all you need to do is create some lookup fields to bring in the data that you need onto the same line.

Since the ID’s are both the same for both records, and the check out record will presumably always be created after the check in record, I would simply create an automation that is triggered based on the “check out” checkmark.

Once the check out checkmark is checked, the automation should search for that same ID, but search for the check in record that matches that ID.

When it finds the matching record of the check in record, take the record ID of the check in record and put it into the linked record field of the check out record.

Then you can create lookup fields to bring in the check in fields onto the check out record.

So all of your math will be done on the check out record’s line.

p.s. Note that all of this will only work if you have one check in record and one check out record per ID. If you’re going to have multiple check ins and multiple check outs based on the same ID, then a significantly more complex setup would need to be figured out.

Terms & ConditionsAccessibility statement