Hello!
I have a table tracking agent info and testing statuses, and I need to create a Current Status and a Current Date & Time field.
Imagine a table where (among other things) you have 6 fields, 3 single-selects and 3 date & time fields. Each pair of single-selects & date & time fields represents a testing attempt. So we have 1st Attempt Status, 1st Attempt Date & Time, 2nd Attempt Status, 2nd Attempt Date & Time, 3rd Attempt Status, & 3rd Attempt Date & Time.
I need a formula/automation that always shows the current status & date & time of the current attempt. Preferably into 2 fields, 1 for the current status and 1 for the current date & time. I need it to keep up if it changes too, i.e. Scheduled changes to Pass/Fail
I have an automation currently to watch each Status field and if they’re updated, it will return the most recently edited information BUT, if it’s updated out of order, (2nd attempt to Scheduled, THEN 1st attempt to Fail) you don’t get the most current attempt.
I also have this formula:
"1st Attempt: “&{1st Attempt Status}&” “&{1st Attempt Score}*100&” “&IF({1st Attempt Date & Time}, DATESTR({1st Attempt Date & Time}))&”; 2nd Attempt: “&{2nd Attempt Status}&” “&{2nd Attempt Score}*100&” “&IF({2nd Attempt Date & Time}, DATESTR({2nd Attempt Date & Time}))&”; 3rd Attempt: “&{3rd Attempt Status}&” “&{3rd Attempt Score}*100&” "&IF({3rd Attempt Date & Time}, DATESTR({3rd Attempt Date & Time}))
Which returns something like this:
1st Attempt: Miss 0 2021-06-09; 2nd Attempt: Fail 0 2021-06-17; 3rd Attempt: Pass 0 2021-06-18
Which is nice, but you can’t filter by it.
Using the example above, I need a field called Current Test Status that says: Pass, and a Current Attempt Date & Time field that says: 2021-6-18.
That way I can filter by the Current Attempt status being Scheduled, Pass, Fail, Miss, Cancelled, etc. and it will return all agents with that status currently, regardless of what attempt it is in.
*Also a note, it would definitely make more sense to just have 2 fields that are just manually updated when the current changes, BUT we need to keep track of the history so each attempt has to have it’s own fields.
*And no third party-apps, Airtable Pro ideas only please.
