Return data for latest instance of unique records
I have a few tables which are populated by form submissions where users are presented with list of vehicles, key rings, etc... These unique assets repeat in the same table over and over each time a new row is inserted in the same table.
For example, vehicle number 104 his submitted each month. There are a total of 96 vehicles.
Everyone month I'm asked which vehicles haven't submitted their vehicle inspection. Today I'm exporting to excel and manually comparing to the full list. There has to be a way to accomplish this in Smartsheets, right? How would I do that?
Additionally, we have 168 numbered key rings that users can select in a form with the actions Out and Return.
The KeyNumber field allows multi select. I need to figure out a way to return a list of all keys that are "Out", but only where the latest Created date is after the latest "Return" Created date.
I guess I'm imaging these two solutions will involve creating two separate tables named "Key Summary" and "Vehicle Summary", with a list of each unique record and a formula that returns the latest date from Created, and respectively the latest action (Out or Return) and odometer value for the latest row.
Would someone give me some guidance and examples of the formulas to accomplish this?
Thank you in advance for any guidance.
Help Article Resources
Check out the Formula Handbook template!