I'm trying to write a formula for a calculated field in a repeating instrument to calculate (pull data from) a field in a different repeating instrument; I need the formula to reference ANY of the instances, not just a specific one and the available smart variable options (e.g. current-instance, next-instance) don't seem to address this need. The formula I'm trying to write is along the lines of following (variable 1 is a date in a repeating instance; variable 2 is a date in a different repeating instance; the references I'm trying to/unable to code are in all caps)
if([variable1][current-instance]=[variable2][ANY-INSTANCE], [variable3][FROM SAME INSTANCE AS VARIABLE2],"na")`
In other words, there a way to tell REDCap to 'look for match in these variables in any existing instance and return a 3rd variable if found'?
I tested to make sure I have date formats set up correctly by referencing specific instances e.g.
if([variable1][1]=[variable2][1], [variable3][1], "na")
this formula returns the correct value but I don't want to have to specify the instance number (instead I want to formula to 'look for' ANY instance to find a match).
the same formula w/ no references to a specific instance returns 'no value' (presumably because REDCap is looking at only the first instance of each repeating instrument).
