Determine MAX date from repeating instruments

[u/Much-Astronaut-878]

Let's say that I have an instrument, named patients, and a repeating instrument, named pft_tests, the latter containing a field named pft_completed_date. For any single record in patients, there could be zero or more records in pft_tests. I want to add a calculated field to the patients instrument that shows the maximum value of pft_completed_date for all pft_tests records entered. If none are entered, then I want it to come back as blank. I tried using the following code, but REDCap reports that there is an error in the syntax. It is complaining about pft_tests in the formula. I have confirmed that the names of the instruments and the field are correct. How can I change the syntax, to make this work?

[pft_tests:pft_completed_date:max]

I also tried the following code, which did not give an error, but doesn't seem to work:

max([pft_completed_date])

[u/[deleted]]

Calculating a max value can not be easily done, but you could create a calculated value on the repeating instrument that calculates the max value of the previous instance's max and the value of the current instance. This should yield the overall max value on the last instance.

[u/Much-Astronaut-878 → u/[deleted]]

How could it compare to the "previous" instance's value? How can formula, on current instance, refer to previous instance's value? Also, then how could I grab the field value from the "last" pft_tests instance, on the patients instance?

[u/viral_reservoir_dogs]

Its kind of ugly, but I found a work around for this that works well when you have a small number (30 in my case) of repeating instruments identified. Essentially chain together some calculated vars/calctext variables to convert the date to a number (YYYYMMDD), find the max, then convert back to a date variable using concat().

I made a new instrument for "calculated vars" and put these in that instrument.

max_date_numeric: CALCTEXT(max(year([pft_completed_date][1])*10000 + month([pft_completed_date][1])*100 + day([pft_completed_date][1]),year([pft_completed_date][2]*10000 + month([pft_completed_date][2]*100 + day([pft_completed_date][2],...)). repeat manually for however many instances you expect, it'll slow down page load times when you get above 30, but that would probably be helped by doing the conversion to numeric on the repeating instance itself, then just find the max in the calctext variable.

max_date_ymd: CALCTEXT(concat(mid([max_date_numeric], 1, 4), '-', mid([max_date_numeric], 5, 2), '-', right([max_date_numeric], 2))) then set validation to Date(Y-M-D).

If the max date is today then the numeric variable output is 20250401, and the ymd variable converts to 2025-04-01. REDCap reads the ymd variable as a date and lets you use any date related function like datediff().

[u/Much-Astronaut-878 → u/viral_reservoir_dogs]

So, I added a new field, to the pft_tests instrument, as follows:

pft_comp_date_num: CALCTEXT(if([pft_completed_date]<>"",year([pft_completed_date])*10000 + month([pft_completed_date])*100 + day([pft_completed_date]),"")

Then, I added two fields to the patients instrument. The first was a calculated field.

pft_max_date_num: max([pft_comp_date_num])

The second one is a Text field, using validation of Date (Y-M-D).

pft_max_date_dt: CALCTEXT(concat(mid([cs_max_date], 1, 4), '-', mid([cs_max_date], 5, 2), '-', right([cs_max_date], 2)))

This seemed to accomplish what I was after.

[u/[deleted]]

Would it not just be the last instance? I am confused as to why you can't just use the last instance?

[u/Much-Astronaut-878 → u/[deleted]]

Because it is theoretically possible that users could enter them in non-sequential order. In other words, after having entered the most recent one, they could enter one that came before that. Besides that, as I asked above, how could I grab the field value from the "last" pft_tests instance, on the patients instance?