Skip to main content

Populate automatically a "Link to another record" field with a calculated data

  • June 2, 2016
  • 6 replies
  • 75 views
V

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

  1. 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.
  2. Create a “Link to another record” field in the “Moments” table, linked to the “Days” table.
  3. 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?

    C

    6 replies

    K
    Forum|alt.badge.img+4

    Have you tried datetime_format on dateadd?

    V
    • Vincent_Pelleti
    • Author
    • Participating Frequently
    • June 19, 2016

    Have you tried datetime_format on dateadd?


    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.

    K
    Forum|alt.badge.img+4

    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.


    Hi, sorry I didn’t notice your reply. Have you got a solution yet?

    I confess I don’t really understand what you’re trying to achieve. It seems you want a field where a difference between dates is displayed as seconds, or days, or weeks? For that you can use datetime_diff. Or is it something else you want?

    V
    • Vincent_Pelleti
    • Author
    • Participating Frequently
    • July 8, 2016

    Hi, sorry I didn’t notice your reply. Have you got a solution yet?

    I confess I don’t really understand what you’re trying to achieve. It seems you want a field where a difference between dates is displayed as seconds, or days, or weeks? For that you can use datetime_diff. Or is it something else you want?


    Hi,

    no worry for the late reply, it’s just a little experiment that occupy me when i have time for it, and that’s not really the case right now :slightly_smiling_face: So there is no urgency whatsoever. And thanks for the reply!

    What i want to do is a project management system with time tracking/journaling at the heart of it.

    I have a Moments table where i record chunks of time with start and end dates. Those moments are related to tasks/projects/clients via links to their own tables.

    In the Moments table, i also calculate the duration of the moment in seconds and from that extract it in hours and minutes (“10h13”).

    Thanks to link, rollup and formula fields, i can automatically obtain the duration summed up by tasks/projects/clients in their own tables.

    But i can’t find a way to automatically obtain those duration subtotals by year/month/week/day.

    The problem i encounter is this:
    When i create a moment, i choose (or create if needed) a task with its project/client related data. Then the link/rollup/formula fields do their thing in the related tables.
    For the year/month/week/day data, i can do the same as the tasks/projects/clients data:

    • create a table for each time data (a Years table, a Month table, etc.),
    • populate them in advance,
    • create the links between them and the moments table.
      The problem lies in the filling of those linked fields in the Moments table: if i fill them manually for each moment, it would work but it would be totally unproductive.
      I can extract these data from the start date in their own formula fields (year/month/week/day). But as they are formula fields, they can’t also be link fields so i can’t use my magic thing to obtain automatically the duration sum in the linked tables.

    The solution i came with for now is to fill those link fields (year/month/week/day) in the Moments table in batch once in a while, with copy/paste: copy a value in a cell of a linked field, paste it in a selection of empty cells. But it’s more of a hack than a proper solution.

    What i learned from that little experiment is that for now it’s complicated to make automated reports from tables in Airtable.

    May be i looked at it the wrong way and there is a much elegant way to solve this, but i couldn’t figure it :confounded:
    Anyway thanks to everyone that have been reading my long and complicated posts!

    M
    • Mics_Sky
    • Known Participant
    • July 8, 2016

    Hi,

    no worry for the late reply, it’s just a little experiment that occupy me when i have time for it, and that’s not really the case right now :slightly_smiling_face: So there is no urgency whatsoever. And thanks for the reply!

    What i want to do is a project management system with time tracking/journaling at the heart of it.

    I have a Moments table where i record chunks of time with start and end dates. Those moments are related to tasks/projects/clients via links to their own tables.

    In the Moments table, i also calculate the duration of the moment in seconds and from that extract it in hours and minutes (“10h13”).

    Thanks to link, rollup and formula fields, i can automatically obtain the duration summed up by tasks/projects/clients in their own tables.

    But i can’t find a way to automatically obtain those duration subtotals by year/month/week/day.

    The problem i encounter is this:
    When i create a moment, i choose (or create if needed) a task with its project/client related data. Then the link/rollup/formula fields do their thing in the related tables.
    For the year/month/week/day data, i can do the same as the tasks/projects/clients data:

    • create a table for each time data (a Years table, a Month table, etc.),
    • populate them in advance,
    • create the links between them and the moments table.
      The problem lies in the filling of those linked fields in the Moments table: if i fill them manually for each moment, it would work but it would be totally unproductive.
      I can extract these data from the start date in their own formula fields (year/month/week/day). But as they are formula fields, they can’t also be link fields so i can’t use my magic thing to obtain automatically the duration sum in the linked tables.

    The solution i came with for now is to fill those link fields (year/month/week/day) in the Moments table in batch once in a while, with copy/paste: copy a value in a cell of a linked field, paste it in a selection of empty cells. But it’s more of a hack than a proper solution.

    What i learned from that little experiment is that for now it’s complicated to make automated reports from tables in Airtable.

    May be i looked at it the wrong way and there is a much elegant way to solve this, but i couldn’t figure it :confounded:
    Anyway thanks to everyone that have been reading my long and complicated posts!


    The same feeling here!

    M
    Forum|alt.badge.img+15
    • Moe
    • Inspiring
    • August 25, 2020

    Hi,

    no worry for the late reply, it’s just a little experiment that occupy me when i have time for it, and that’s not really the case right now :slightly_smiling_face: So there is no urgency whatsoever. And thanks for the reply!

    What i want to do is a project management system with time tracking/journaling at the heart of it.

    I have a Moments table where i record chunks of time with start and end dates. Those moments are related to tasks/projects/clients via links to their own tables.

    In the Moments table, i also calculate the duration of the moment in seconds and from that extract it in hours and minutes (“10h13”).

    Thanks to link, rollup and formula fields, i can automatically obtain the duration summed up by tasks/projects/clients in their own tables.

    But i can’t find a way to automatically obtain those duration subtotals by year/month/week/day.

    The problem i encounter is this:
    When i create a moment, i choose (or create if needed) a task with its project/client related data. Then the link/rollup/formula fields do their thing in the related tables.
    For the year/month/week/day data, i can do the same as the tasks/projects/clients data:

    • create a table for each time data (a Years table, a Month table, etc.),
    • populate them in advance,
    • create the links between them and the moments table.
      The problem lies in the filling of those linked fields in the Moments table: if i fill them manually for each moment, it would work but it would be totally unproductive.
      I can extract these data from the start date in their own formula fields (year/month/week/day). But as they are formula fields, they can’t also be link fields so i can’t use my magic thing to obtain automatically the duration sum in the linked tables.

    The solution i came with for now is to fill those link fields (year/month/week/day) in the Moments table in batch once in a while, with copy/paste: copy a value in a cell of a linked field, paste it in a selection of empty cells. But it’s more of a hack than a proper solution.

    What i learned from that little experiment is that for now it’s complicated to make automated reports from tables in Airtable.

    May be i looked at it the wrong way and there is a much elegant way to solve this, but i couldn’t figure it :confounded:
    Anyway thanks to everyone that have been reading my long and complicated posts!


    If you’re looking for a way to automate this copy/pasting process, we’ve built an extension that handles this on your behalf.

    Terms & ConditionsAccessibility statement