Keeping track (history log) of previous selections in: A) Single Choice field and B) Linked field

Airtable Questions
1

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 A
Option B
Option C
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.

2

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.

In this case, create an Airtable automation for that table, triggered by a record when it is modified.

If the single select is modified, run a Find on your History log table to see if that combination has existed before (X record, Y single select).

If it has, do nothing. If it hasn’t, create a record that links back to the trigger record, and states the select value it just changed to.

–

Same premise for your linked record. Trigger am Airtable automation when it changes, create a new record in your history log with those details, linked back to the original trigger field.

3

I see, that may work through the helper history table. I guess to show that result back in my main table I could use a roll up field with conditions to show only history entries rolled up for that specific record. That that could work.

Unfortunately I just realized I may find hard to to do it via automation because I would need about 40 automations for that.

The one point I omitted (actually forgot to mention but if I did. it would make my requirement too complicated) is that, I only want the history log entry if the record shows in a specific view. I have about 40 different views. While I can have automation to watch the same set of conditions I have set for view filtering, I would need essentially 40 different automations because each of the views have different filter, so each of the automation would need follow a separate set of conditions.

Meh, so i think I am asking too much. There sin’t probably a solution for this. Maybe some sort of script would work. But Airtable fields do not support scripts and I don’t have even scripts enabled in my Airtable plan because scripts are only on the Pro and Enterprise plans.

So I guess that may not work much… hm. Good approach though.

4

Well, I professionally disagree :smile:

The answer is never going to be that you need 40 automations, you need a view that says if A or B or C or D is true, and each of those can be the combination of filters that you use in each view.

That’s one automation.

If you’d like some more support to make this happen, shoot me a DM.

5

Okay I see. Thank you for the offer, let’s see if we can squeeze out of my brain first.

Most of the views have about 12 or more conditions in the filter and not all all views have the same number of condition filters.

You are right I missed the fact I can do conditional actions. That would require me to replicate the filters I have setup for each of the view within each of the conditional actions. Now that will definitively take more than one automation because only a few filters can go to the trigger, all the rest would have to be in the conditional action:

image
image998×333 22.8 KB

Another way would be perhaps setup a formula field to evaluate if the combination of 12+ conditions is met and then perhaps display a value 1,2,3 or a,b,c and that would become trigger for the automation with conditional actions now much slimmer.

Okay, did you have anything else in mind?

I am just surprised I found in automation I can’t set a condition in the conditional action saying run the action in this group if the record shows up in the specific view. I think this is not available. Or did I miss it somewhere? That would solve my problem pretty easily without the hassle I am talking about earlier.