Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Time Range Formula

Solved
Jump to Solution
397 0
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Weigle
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi everyone,

I'm looking to create a formula where, given a specific day of the week, and a time range, the formula will generate an output, and then write a script where it will take the output and enter it into a Linked Field box. 

Screenshot 2024-08-28 at 8.21.36 AM.png

For example, on Wednesdays between 7:20am to 8:00am, I want to generate a result that says "Period 1." 8:06am - 8:50am would be "Period 2," and so on, so forth. We have different bell schedules for different day of the weeks. 

And then, once it generates the result, I want to have a script run to add the "Class Period" into the empty Class Period box. 

Is this possible? 

Thanks in advance for any help that could be provided!

 

1 Solution

Accepted Solutions

Hello David,

I have a solution that seems to work with formulas and an automation. It is a bit complex and requires to spend some time on setup. Maybe someone will come out with an easiest solution.

Here is the result that I get after running the automation:

Pascal_Gallais_0-1724859218912.png

- Step 1 - Table Periods

This table contains one line for each period to consider (reason why there is a lot of setup to do):

Pascal_Gallais_1-1724859453733.png

For each period, we set the day, the starting point separated in two number type fields: one for the hour, another one for the minutes and same thing for the ending point.

Constraints to respect:

- hours must be expressed from 0 to 23

- Two periods for the same days must never overlap

-Step 2- Formulas in table period

We had two formulas to compute starting and ending points in minutes:

Pascal_Gallais_2-1724859838341.png

Pascal_Gallais_3-1724859872750.pngPascal_Gallais_4-1724859896443.png

-Step 3- Formulas in table "Shifts"

Table Shifts in this example is the one where we want to bring down the period information.

We had 3 formulas: Hour, Minute and Minutes

Pascal_Gallais_5-1724860093136.png

Pascal_Gallais_7-1724860137675.png

Pascal_Gallais_8-1724860167776.png

Pascal_Gallais_10-1724860199954.png

- Step 4- Link table "Shifts" to table period

Pascal_Gallais_14-1724860828605.png

 

- Step 5- Automation

You will need to decide what the trigger is. For my example I added a checkbox field "Test" in table "Shifts", this field being the trigger.

Pascal_Gallais_11-1724860303051.png

First action is to find the corresponding record in table Period (we do not want to find more than one record, reason why you must make sure that Periods for a given day do not overlap).

Pascal_Gallais_12-1724860450377.png

We then add a conditional action to update only if exactly 1 record has been found:

Pascal_Gallais_13-1724860568176.png

And we update the record in table shift:

Pascal_Gallais_15-1724863743992.png

Where "List of Period_id" is set a follows:

Pascal_Gallais_16-1724863798010.png

This will bring down the period primary key into table Shifts, meaning that you can then add some lookup field such as the period name.

Regards,

Pascal

 

 

 

 

 

 

 

 

See Solution in Thread

3 Replies 3

Hello David,

I have a solution that seems to work with formulas and an automation. It is a bit complex and requires to spend some time on setup. Maybe someone will come out with an easiest solution.

Here is the result that I get after running the automation:

Pascal_Gallais_0-1724859218912.png

- Step 1 - Table Periods

This table contains one line for each period to consider (reason why there is a lot of setup to do):

Pascal_Gallais_1-1724859453733.png

For each period, we set the day, the starting point separated in two number type fields: one for the hour, another one for the minutes and same thing for the ending point.

Constraints to respect:

- hours must be expressed from 0 to 23

- Two periods for the same days must never overlap

-Step 2- Formulas in table period

We had two formulas to compute starting and ending points in minutes:

Pascal_Gallais_2-1724859838341.png

Pascal_Gallais_3-1724859872750.pngPascal_Gallais_4-1724859896443.png

-Step 3- Formulas in table "Shifts"

Table Shifts in this example is the one where we want to bring down the period information.

We had 3 formulas: Hour, Minute and Minutes

Pascal_Gallais_5-1724860093136.png

Pascal_Gallais_7-1724860137675.png

Pascal_Gallais_8-1724860167776.png

Pascal_Gallais_10-1724860199954.png

- Step 4- Link table "Shifts" to table period

Pascal_Gallais_14-1724860828605.png

 

- Step 5- Automation

You will need to decide what the trigger is. For my example I added a checkbox field "Test" in table "Shifts", this field being the trigger.

Pascal_Gallais_11-1724860303051.png

First action is to find the corresponding record in table Period (we do not want to find more than one record, reason why you must make sure that Periods for a given day do not overlap).

Pascal_Gallais_12-1724860450377.png

We then add a conditional action to update only if exactly 1 record has been found:

Pascal_Gallais_13-1724860568176.png

And we update the record in table shift:

Pascal_Gallais_15-1724863743992.png

Where "List of Period_id" is set a follows:

Pascal_Gallais_16-1724863798010.png

This will bring down the period primary key into table Shifts, meaning that you can then add some lookup field such as the period name.

Regards,

Pascal

 

 

 

 

 

 

 

 

Thank you! I will start looking at putting this together!

I ended up using a good chunk of your solution--and it worked! Ultimately, we're using this to try and track Student Tardies by Class Period. I ended up creating a formula on our "Tardy" table that calculated the Minute of Day for the tardy, then using ranges for each date. It actually wasn't that difficult to set up once I got it going. 

Thanks for your help!!! I greatly appreciate it!