Ideas for tracking account assignment history?

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. :slight_smile:

You’ll need to create a many-to-many relationship:

I hear what you’re saying. That would allow me to link multiple researchers to one alien, but only want one lead researcher at a time, and then I want a field that tracks the history of researchers and the dates they worked on that alien. I’m trying to figure out how to do this with an automation and I’m halfway there… I’ll keep plugging away. :slight_smile:

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.