in the screenshot below, i have a start date field and a formula field that give the number of seconds of the moment (an end date field exists but is hidden).
I would like to get the duration by day (and/or year/month/week… you get the idea) but can’t find a way to do it automatically.
What i tried so far (in the day case):
- Create a “Days” table with the primary field in the format “YYYY-MM-DD”. It can be populated quickly with auto number field and a formula primary field.
- Create a “Link to another record” field in the “Moments” table, linked to the “Days” table.
- Create a rollup field in the “Days” table based on the duration field of the “Moments” table with the sum aggregation function.
In that configuration, when a new moment is created and a new start date is entered, i will have to enter manually the day of the start date in the linked field. That way, in the “Days” table the rollup field give me the sum by day i want.
But, if i also need week/month/year sums, i will have to enter theme manually for each data whereas those informations (day, week, month and year) can be extracted from the start date field via formulas.
So my issue could be summarized in: how to populate automatically a “Link to another record” field with a calculated data?
Or is there another way to achieve this?