I'd like to have REDCap auto-generate a state abbreviation in a calculated text field when a user selects an institution from a drop-down. So far, I've only been able to get it to work with nested if statements in the Action Tags section for a simple example:

@CALCTEXT(if([institution]=2 or [institution]=113,'MO',
if([institution]=4,'MA','')))

The reality is, of course, more complex. I have about 117 institutions (and growing), representing about 20 states (and growing), and that nesting will turn into a monster. I can't arrange things so that institutions within the same state have consecutive values (which would allow me to use < > ranges) because we're always adding institutions (which does require me to modify the [institution] field every time, but that's manageable). I've played around with the following which would help to simplify things, but didn't work:

@CALCTEXT(if([institution] %in% c(2,113),'MO',''))

@CALCTEXT(if([institution]='2,113','MO',''))

@CALCTEXT(if([institution]=113,'MO',''))
@CALCTEXT(if([institution]=4,'MA',''))

That's about the limits of my creativity and knowledge here, but it's possible I've missed a simpler solution. If, however, nested ifs are really the only way to go, I'll give up on including state in the database and run it on the analysis end instead, which is easy enough.

I'd like to have REDCap auto-generate a state abbreviation in a calculated text field when a user selects an institution from a drop-down. So far, I've only been able to get it to work with nested if statements in the Action Tags section for a simple example:

@CALCTEXT(if([institution]=2 or [institution]=113,'MO',
if([institution]=4,'MA','')))

The reality is, of course, more complex. I have about 117 institutions (and growing), representing about 20 states (and growing), and that nesting will turn into a monster. I can't arrange things so that institutions within the same state have consecutive values (which would allow me to use < > ranges) because we're always adding institutions (which does require me to modify the [institution] field every time, but that's manageable). I've played around with the following which would help to simplify things, but didn't work:

@CALCTEXT(if([institution] %in% c(2,113),'MO',''))

@CALCTEXT(if([institution]='2,113','MO',''))

@CALCTEXT(if([institution]=113,'MO',''))
@CALCTEXT(if([institution]=4,'MA',''))

That's about the limits of my creativity and knowledge here, but it's possible I've missed a simpler solution. If, however, nested ifs are really the only way to go, I'll give up on including state in the database and run it on the analysis end instead, which is easy enough.

• redcap

Share Follow asked Mar 7 at 18:20 bcarothersbcarothers 9241010 silver badges2323 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

I believe the only way to get want you want is with nested if statements. But you can avoid typing everything out using Excel with a function like this:

=CONCAT("if([institution]=",A2,", '",B2,"'),")

where column A has the variable numbers and column B has the associated state abbreviation. Then just copy/paste the output into a @CALCTEXT function to end up with something like this:

@CALCTEXT( 
    if([institution]=1, 'AZ'),
    if([institution]=2, 'MS'),
    if([institution]=3, 'MI'),
    if([institution]=4, 'IN'), ""
)))))

How about including the state codes within the option values for each institution option?

Say you encode your "institution" field along these lines:

I001AZ, Institution 1, Phoenix, AZ
I002MS, Institution 2, Minneapolis, MS
I003MI, Institution 3, Detroit, MI
...

You can then just grab the state from the right two characters of the field value:

@CALCTEXT(right[institution,2))

Just be aware that non-integer values can't be labelled if you want to export to Stata. Otherwise they don't tend to cause any drama.