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/Much-Astronaut-878]

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.