Calculate Age At Time of Release
This has probably been answered before, but I'm not sure how formulas work or how to maniuplate them to fit my use case.
My base has multiple tables, one of which is a Members table. This includes the person's name as the primary field and other info, including their birthdate, in other fields.
On another table, I have a list named Releases. Each Release record links to the Members table, and includes every Member who participated in a Release. Also, each Release has a release date field.
As an example (Note that I'm only interested in linking to the H!P Members field, not the Non-H!P Members field):
Let's use the record named Morning Coffee to hopefully explain things well enough.
Morning Coffee was released 1998-01-28, and five H!P Members participated in the release: Nakazawa Yuuko, Ishiguro Aya, Iida Kaori, Abe Natsumi, Fukuda Asuka. I would like to have a field running a formula that displays each individual member's age in years on that date, preferably in a list. As I still plan on having the field linking to the H!P Members table, the direct link in the formula output isn't necessary, but I would like that option if possible.
So whatever the formula would be would throw up a result like this:
- {Nakazawa Yuuko}: {Age in Years on 1998-01-28}
- {Ishiguro Aya}: {Age in Years on 1998-01-28}
- {Iida Kaori}: {Age in Years on 1998-01-28}
- {Abe Natsumi}: {Age in Years on 1998-01-28}
- {Fukuda Asuka}: {Age in Years on 1998-01-28}
Or, for another release:
- {Heike Michiyo}: {Age in Years on 1998-03-25}
The number of Members in a Release can be anywhere from one to almost 70 (I have a total of 283 records in the H!P Members table for context), so I need it to pull from the Birth Date field in the H!P Members table directly, then calcuate that data against the Release Date field in the Releases table.
I really can't seem to wrap my head around formulas no matter how hard I try! I feel like this is a very easy one, but I have no idea what I'm doing.
If this doesn't make any sense I do apologise!
Accepted Solutions
Unfortunately I don't think you're missing anything and the only way I can think of handling this is via a junction table. We'd create one record per album <> member variant, and then use that to calculate their age at the time of the album's release date:
Link to base
With one record per album<>member variation created in the "For Age Calculation" table, we can create lookup fields to show the member's birthday and the album's release date, and thus can use a formula field to calculate the age. We then use a rollup field to pull that data back into the "Albums" table
And the records are created via an automation that's triggered when we tick the checkbox, and you can view how the automation's set up in the table as well
Unfortunately I don't think you're missing anything and the only way I can think of handling this is via a junction table. We'd create one record per album <> member variant, and then use that to calculate their age at the time of the album's release date:
Link to base
With one record per album<>member variation created in the "For Age Calculation" table, we can create lookup fields to show the member's birthday and the album's release date, and thus can use a formula field to calculate the age. We then use a rollup field to pull that data back into the "Albums" table
And the records are created via an automation that's triggered when we tick the checkbox, and you can view how the automation's set up in the table as well
