Expression Assistance, Part 2: Lookup with nested MaxRow

[mykrobinson]

Earlier today I posted this thread and got some great help:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Expression-Assistance-MAXROW/m-p/402862

The expression is working well but in tested I have come across a problem and need to talk this out to figure out how to adjust.

The expression:

lookup(MAXROW("admissions","Admission Date",([_patientIdentifier]=[_THISROW].[_patientIdentifier])),"admissions","_encounterID","Room")

successfully pulls the most recent room a patient was in, based on the admission date. I tested and added another record for the same dummy patient and the formula did not update the room number. It looks like this is because the expression is running a query strictly based on the date, but there is no timestamp attached to the record.

At first, i thought, no problem, I'll make sure to capture a timestamp to the table when records are added and base the return value on when the record was created. But that made me think, what if someone goes in an retroactively adds records? For example, a patient is in the hospital now so they add a record to record which hospital and which room number. As they are looking through further information, they discover they were at another hospital, let's say yesterday... So they add an admission record for yesterday. We now have a NEWER record added, but the actual admission date is in the past...

I need to figure out how to return a result that somehow figures out the right thing to display for the current room, but also factoring in timestamp of record creation 🤔.... 

[graham_howe]

If you want to identify the most recent room the patient was in, then ideally you want a date to filter on. Is it possible that someone entering records retrospectively is asked to populate the date through the form, the default value could still be today()? If it is really not possible to ensure that a date is entered, then you can use maxrow() with "_rownumber". This will return the last matching entry in the spreadsheet. However, if someone is retroactively entering information and not providing the date, then how do you even know they will enter the rooms in the correct sequence?

Edit to say: the more I think about it, the only way to ensure this works correctly is make the "Admission Date" field a required field and set the initial value to today(). This will provide the speed and efficiency of a timestamp for most entries, but will also allow retrospective entries of historical data.