This is the first thing I can help with, because my firm did the same thing! We had a V1 of it that was simpler, but this ended up being more robust, and I'm not sure how to create it.
First, you need to create a rank based off of some criterion. We used this formula numeric field (names changed to be more like yours):
var text query = "{8.EX.'" & [Patient Name] & "'}AND{10.LTE.'" & [As of Date] & "}";
Size(GetRecords($query))
This gets you the count of records by that patient with the as of date being less than or equal to the as of date of this records. So, if this is the 10th visit, the rank will be 10. If you go to the last time they were year, the Less than or Equal Date to won't pick up the most recent visit and then be 9.
You then can create a "prior rank" field which is just equal to [rank] - 1. You could also re-define this in this next field, but if you're going to use it to pull multiple fields in, this makes more sense.
You then can create a query like this:
ToText(GetFieldValues(GetRecords("{8.EX.'"&[Patient Name]&"'}AND{15.EX.'"&[Prior Rank]&"'}"), 20)
(in this example, 8 is the patient name field ID, 15 is the rank field ID, and 20 is the "why are you here today" field)
You pretty much query your own table for the same patient, previous rank.
I'm not sure how formula or query familiar you are, so I hope that helped! but let me know if you have questions
------------------------------
Thomas Otten
------------------------------
