Survey 123 - Need help concatenating multiple calculations on one line

2151
5
06-23-2021 09:44 AM
SArcher
New Contributor III

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.

 

0 Kudos
5 Replies
DougBrowning
MVP Esteemed Contributor

I would make one field and use an if to pick the correct id.  That way the id is always in the same field.  Not sure if that works for you flow.  You could also then just have one user entered ID field really.

if(${select_report} = "Route_ID", ${route_id}, if(${select_report} = "Station_ID", concat(${station_id}, ' stat'), if(${select_report} = "Congestion", concat(${congestion}, ' con'), "Nothing Selected")))

Hope that helps

0 Kudos
SArcher
New Contributor III

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 

0 Kudos
DougBrowning
MVP Esteemed Contributor

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})))

0 Kudos
SArcher
New Contributor III

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?

0 Kudos
codergrl
New Contributor III

I like to separate each if statement into its own calculation. Makes it much easier to keep track of parentheses. Calculations don't show up in the form and if you set the esriFieldType to null they don't show up in the feature class either. 

0 Kudos