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:

- Step 1 - Table Periods

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

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:

-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

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

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

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

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

And we update the record in table shift:

Where "List of Period_id" is set a follows:

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

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:

- Step 1 - Table Periods

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

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:

-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

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

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

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

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

And we update the record in table shift:

Where "List of Period_id" is set a follows:

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