Custom formula to display first "last modified" status

Topic Labels: Formulas
427 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I need a formula which can display which checkbox which was modified FIRST.
my idea was to create multiple checkboxes for multiple staff, and we need a way to see which staff ticked their checkbox FIRST and display that as a field.

So multiple checkboxes [E.G Adam, Su, Ivy]
then a last modified field for each of these checkboxes.
Then a custom formula which can see which one of these last modified fields is the OLDEST down to the second.

One problem i forsee if they happened within the same second, does airtable deal with nanoseconds?

Any help would be appreciated

1 Reply 1

With Airtable’s formulas, the only way to compare is by nesting IF() functions. If you’ve got two checkboxes, the test is pretty easy: if A is less than B then output “A, B”; otherwise output “B, A”. With each comparison to make after that, though, the number of necessary tests grows dramatically. A three-option test has six possible configurations:

IF(AND(A < B, B < C), "A, B, C",
IF(AND(A < C, C < B), "A, C, B",
IF(AND(B < A, A < C), "B, A, C",
IF(AND(B < C, C < A), "B, C, A",
IF(AND(C < A, A < B), "C, A, B",
IF(AND(C < B, B < A), "C, B, A"))))))

Want to test four options? There are 24 configurations. Five options? 120 configurations. And don’t forget that if someone hasn’t checked a box yet, you have to include logic to account for that. Long story short: it gets very messy very quickly.

TL;DR - I don’t recommend attempting this using a formula field. Look into a scripting solution—either in the Scripting app, or in a “Run script” action in an automation—where you can tap into the built-in sorting features of the JavaScript language.