Survey 123 - Need help concatenating multiple calculations on one line

Question

I am trying to create one "note/hidden" field called "Report" that will combine or concat multiple inputs/calculations if selected or true.

I'm not sure if/where I need "if", "if selected", "selected", "and", "or" statements. Sometimes the form error returns missing brackets, sometimes I need another argument. I've tried various combinations and tested individual lines of calculations and it's fine, it's the combining of them I still can't get it right. I also want to try and simplify/condense this mess of arguments if I can.

if true:

${route_id}, if(selected(${select_format}, 'Excel'), ' xls', '')

or if true:

${station_id}, concat(${station_id},' stat '), if(selected(${select_format}, 'Excel'), ' xls', '')

or if true:

${congestion}, concat(${congestion},' con '), if(selected(${select_format}, 'Excel'), ' xls', ''), if(${request_year})

Note: Depending on whether Route id, Station id, Congestion is true (only one can be selected), the option to select Excel format will be available for all. Station id and Congestion will have a concat (respectively either stat, con). And only Congestion will have an option to input Year.

Email Me.xlsx

clt_cabq_0-1715698108684.png

SArcher · Answer

Thanks! Actually, your first formula worked great. It is on one line and I tweaked it a little to the following because I needed the spaces in a certain spot and wanted to add the format and year:

if(${select_report} = "Route_ID", ${route_id}, if(${select_report} = "Station_ID", concat(${station_id}, ' stat'),
if(${select_report} = "Congestion", concat(${congestion}, ' con'), '0'))) + if(${select_format}, ' xls ', ' ') + ${request_year}

However, while it works as expected in 123 Connect preview, Online is a different issue. I am getting a NaN (not a number) error in the line. I looked it up and I think it has something to do with the "+" and that values cannot be empty. Hence, why you probably did the "string-length(${question})". But it is still all confusing.

Do you know much about the error I am getting/how to fix it?

dougbrowning · Answer

I would change the excel question to a select_one.

I kept thinking there was an easier way but if you want the spaces in there I think you have to write it out.

if(string-length(${select_format}) > 0 and string-length(${request_year}) > 0, ${finalid} + " " + "xls" + " " + string(${request_year}), if(string-length(${select_format}) > 0, ${finalid} + " " + "xls", if(string-length(${request_year}) > 0, ${finalid} + " " + string(${request_year}), ${finalid})))

Form 7.xlsx

SArcher · Answer

That works Great! Thanks!

How can I combine the if(${select_format} = "Excel", concat(${select_report}, ' xls') and if(${request_year}) to the same line? I tried adding those using your formulas but got errors. Ultimately, the Report line should be something like this:

ex.

If "Congestion" selected: XYZ con

or if "Congestion" and "Format" selected: XYZ con xls

or if "Congestion" and "Format" and "Year" selected: XYZ con xls 2021