Two Concat expressions?

[ChadC]

Is it possible to write multiple concat expressions to essentially field calc. one field? What I am trying to do here is populate a 'FullAddress' field. I have set this up so the end user can type in another city/zip (in a separate field) by choosing the 'other' option in the pre-populated drop-down menus. The intent here is to capture data that is adjacent to the State boundary.

if(selected(${CommName},'Other') and ${ZipCode}, '99999', concat(${Location}, ' ', ${Suite}, ' ', ${Street}, ' ', ${OtherComm}, ' ', ${StateName}, ' ', ${OtherZip})),

if(selected(${CommName}, '!= Other') and ${ZipCode}, '!= 99999', concat(${Location}, ' ', ${Suite}, ' ', ${Street}, ' ', ${CommName}, ' ', ${StateName}, ' ', ${ZipCode}))

Running each of these expressions works by themselves, but when I combine them (as above), it fails to convert the XLS Form in Survey123 Connect. Any help would be greatly appreciated! Maybe I am attempting something that's not even possible to begin with?

[abureaux · Accepted solution]

You can try this:

if(selected(${CommName},'Other') and selected(${ZipCode}, '99999'), concat(${Location}, ' ', ${Suite}, ' ', ${Street}, ' ', ${OtherComm}, ' ', ${StateName}, ' ', ${OtherZip}),'')

if(not(selected(${CommName}, 'Other')) and not(selected(${ZipCode}, '99999')), concat(${Location}, ' ', ${Suite}, ' ', ${Street}, ' ', ${CommName}, ' ', ${StateName}, ' ', ${ZipCode}),'')

EDIT: Probably worth mentioning, but you may also wish to try this...

For your Zip Code, it seems like you have two Zip Code fields (OtherZip and ZipCode). I assume only one is conditionally shown at a time. In that case, you could set up a Calculate with a formula of coalesce(${OtherZip},${ZipCode}). Now you just need one concat(), and you reference this calculate in your concat() rather than OtherZip or ZipCode. Assuming I am interpreting your above logic correctly.

[ChadC]

Thanks a million! I was able to get it to work via the following expression:

concat(${Location}, ' ', ${Suite}, ' ', ${Street}, ' ', coalesce(${OtherName},${CommName}), ' ', ${StateName}, ' ', coalesce(${OtherZip},${ZipCode}))

I coded <null> values for the ZipCode and CommName fields for the 'Other' option. The key is the ordering (as you demonstrated); using the coalesce function returns the first non-NULL value. The text the user types in for the Other Zip field must precede the coded <null> value associated with the 'Other' label for the ZipCode and CommName field. It works now. Thanks again!