Populate automatically a "Link to another record" field with a calculated data
Hi,
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?
Hi, thanks for the reply!
i guess my explanation and question were unclear as i got 49 view but only 1 feedback :slightly_smiling_face:
Anyway, if i understand what you suggest, it would format my “Début” field (see below an exemple with the DATEFORMAT “YYYY-ww” displaying the year followed by the week number):
What i aim for, regardless of the ways to achieve it (in my first post i described one of my attempt and the dead end i encountered), is to calculate subtotals of the “duration in seconds” field for a day/week/month/year.
I can indeed extract the day/week/month/year of the start date of the moment (the “Début” field) with the DATEFORMAT function and even filter a view by day/week/month/year to get the subtotal of “duration in seconds” field for that filtered view (see capture below for one day). But that not practical in daily operations: i would have to do a new filtered view for each day for exemple, and that “filtered” subtotal is not exported nor referenced in formulas.
I guess that without proper solutions, i guess i will try to export my “Moments” table regularly and import that in my old Filemaker Pro 12 to get the filtered subtotals i want.
