I will start here with the simpler option - tracking the history of the Single Select field selections.
I am wondering what would be the most efficient and elegant approach here. A single select field is driving some calculations and formatting in my table and based on that I create an output CSV. The single select field has options e.g.:
Option D etc…
There is finite number of choices in the Single Select field which are pre-set and they do not change.
Every time I select a different option in the single select field, my table produces a different CSV export (the Single select field is the selector to trigger the changes the configuration needed to produce the type of CSV I need).
All works well. However, I want to be able to tell which of the options in that Single Select field were selected over period of time. What I need is another field that would collect the choices that were made over time. Let’s call that field a “history log” field.
The only reason I need this “history log” field is to visually check to see what the selections were in the Single Select field that were previously selected (chosen). That way I will know where did I use the individual exported CSV files. If the history logs shows Option A and Option C, then I know, I used the CSV for requirements A and requirement B. That’s all I need to know.
So it’s just a plain log. If the Option A was selected, then there will be an entry for that. If the Option D was selected later on, then the log will have the next entry Option D.
I do not need to keep track of every time the Option A was selected. I only need to know if it was EVER selected. If it was selected once or many times, it doesn’t matter, there will be 1 log entry in the that history log field.
So and now the more complicated part B)
I am looking for same to track history log for the linked records that are selected in a linked field in the table. So if a record ABC123 is selected in that linked field, then my “history log” field will show there an entry ABC123. Then later another linked record DEF456 is selected in the linked field, there will be another line in my history log field with the entry DEF456 and so on.
Same as in case of Single Select field scenario above A) , here also I do not care how many times the linked record in the linked field was selected, whether it was selected only once or many times, it doesn’t play s role, in both chases my history field will show just one log entry for that linked record.
Approaches I looked at in the past (but I failed) were Airtable automations and Make. For Airtable I tried to use in combination with multiple-choice field. As far as I understood at that time, the issue was, Airtable would just overwrite the option in the multi-choice field rather than append it. And there is so far no way to upsert record either using Automation.
When I looked at Make, I am sure it is capable of doing what I need but the shier overhead that this would cause, this is quite heavy workflow and the frightening prospect of having to dip deep into arrays which is still something I didn’t manage to understand well, I gave up on that.
Ideally I was looking to see the history log in the same table I am working on. I know I could do some finishing expedition by looking through the Airtable log for that field, but that is not super handy when going through many records.
I know this is a long post and I apologize but just wanted to be specific as possible as I don’t waste people’s time who may want to have experience with this and may be interested in suggesting what could work.