Hi there,
I am working on an alien database project in Airtable that needs to track how each alien’s Lead Researcher has changed over time.
I have a table of “Aliens” listing all their relevant traits (home planet, native gravity, atmosphere, diet, etc.) and I have a table of “Lead Researchers” listing all their relevant traits (name, major phobias, favorite color, etc.)
I have a field that tells me who the current “Lead Researcher” is for each alien. Each Lead Researcher is the lead for several (between 20-40) different aliens. When the Lead Researchers change, we simply update this field. Easy peasy. However, I also need a way to see “Researcher History” for each alien, including the dates that the lead researcher started and ended with that subject. I know that Airtable tracks changes, but I am looking to have this in my actual data as part of the base.
I can imagine building out fields to make the table wider with something like this:
- Previous Researcher 1
- Start Date 1
- End Date 1
- Previous Researcher 2
- Start Date 2
- End Date 2
- Etc…
But this isn’t very elegant. I would rather have a linked field that linked to multiple records showing all of the researcher history in that one field. I would want the linked field to be something like a formula concatenating {Researcher Name}&{Start Date}&{End Date}
Right now it is very easy to update Lead Researcher assignments, and I don’t want to lose that. Can anyone think of a way to use the update process to create the records that would get linked to the Researcher History field?
The ultimate goal (which I can manage if I can figure out the above) is so that say, if Will Smith and/or Tommy Lee Jones need to come audit our data, they can see easily on a custom interface what the researcher history was.
Does that make sense?
Grateful for any ideas. 