If statements with concatenations

[EMMA_UM]

I've read many posts on here but am still having a tough time getting this statement working. Any help is GREATLY APPRECIATED! I broke it down to make it a little easier to read.

Thank you!

if(selected(${WB}, 'RV'), concat(${RV_ST_LP},${FD_RV},${FD_ST},${RV_ST_WF},${RV_ST_Nat_Mod},${RV_ST_Hum_Mod}),

if(selected${WB}, 'ST'), concat(${RV_ST_LP},${FD_RV},${FD_ST},${RV_ST_WF},${RV_ST_Nat_Mod},${RV_ST_Hum_Mod}),

if(selected(${WB}, 'LK'), concat(${LK_PD_LP},${WB},${LK_PD_WF}),

if(selected(${WB},'PD') and ${LK_PD_LP} != 'TE', concat(${LK_PD_LP},${WB},${LK_PD_WF}),

if(selected(${WB}, 'PD') and ${LK_PD_LP} = 'TE', concat(${LK_PD_TE},${WB},${LK_PD_WF}, 'NULL')))))

[Katie_Clark]

At a quick glance, I noticed in your expression that you're missing a parentheses here:

Does adding that fix the statement?

[EMMA_UM]

Good catch! Unfortunately, that did not solve the issue.

[Katie_Clark · Accepted solution]

Sorry, I didn't notice this the first time....I think you need a parentheses here too? Because the 'NULL' is the final ELSE statement. (I highly recommend writing these statements in an editor like this with syntax highlighting! It always helps me with the grouping.)

If this still doesn't work I'll try to help more, but...let's try to get the simple stuff out of the way first 🙂

[EMMA_UM]

It's working! Thanks for your help. Do you have a go-to editor to check syntax (outside of this platform)?

[Katie_Clark]

So glad you got it working! 🙂

For writing quick statements like this, I usually use Notepad++. You don't have to save the document as a certain file type for the parentheses highlighting to display (like you can see in the screenshot, where the matching ones appear in red.)

For more involved coding, I've used PyCharm, Atom, or Sublime depending on the language I'm writing in. 

[EMMA_UM]

Hey Katherine,

Thanks for your help earlier. I'm pretty new to more complex if statements. Connect keeps throwing a "There is no survey element with this name" message when I add in the this formula into the calculation column. There is indeed a a question named, "Water_Mod". I originally wrote this line (row 62) using the string-length function but also tested count-selected. No dice. Any thoughts? XLS is attached. TIA!

if((count-selected(${Water_Mod}) != 0 and count-selected(${Landscape_Mod}) = 0), ${Water_Mod}), if((count-selected(${Water_Mod}) = 0 and count-selected(${Landscape_Mod}) != 0), ${Landscape_Mod}), if((${count-selected(${Water_Mod}) != 0 and scount-selected(${Landscape_Mod}) != 0), concat(${Water_Mod}, ',' ,${Landscape_Mod}), ''))))

[JenniferAcunto]

There are a couple of errors in your calculation that are causing you issues:

1. if((${count-selected(${Water_Mod}) != 0 

2. Your parenthesis are very unbalanced. I recommend doing what Katherine suggested and building your statements in Notepad ++. I used Notepadd++ to count each parenthesis and you have 13 opening ones, but 16 closing ones. 

3. Your calculation is a list of values to display if any of your expressions are true, but if statements require a default value to provide when your expressions are false. You do not need the last if statement at all, as that is your default false value. 

if(count-selected(${Water_Mod}) !=0 and count-selected(${Landscape_Mod}) =0, ${Water_Mod},
if(count-selected(${Water_Mod}) =0 and count-selected(${Landscape_Mod}) !=0, ${Landscape_Mod}, concat(${Water_Mod},', ', ${Landscape_Mod})))

Some general tips to writing and troubleshooting long calculations:

Write it out elsewhere and break it up so it's easier to read. Use the count feature to ensure your brackets and parentheses are balanced. If you keep getting an error and can't figure out what it is, add your calculation line by line and save each time to narrow down which section of your calculation is having issues. For example, I would first add. 

if(count-selected(${Water_Mod}) !=0 and count-selected(${Landscape_Mod}) =0, ${Water_Mod}, '') 

If that saved without any errors I would then add in the second piece.

if(count-selected(${Water_Mod}) !=0 and count-selected(${Landscape_Mod}) =0, ${Water_Mod},
if(count-selected(${Water_Mod}) =0 and count-selected(${Landscape_Mod}) !=0, ${Landscape_Mod}, ''))

If that saved without any errors, then I know the issues is probably with the final section and would do a deep dive into it. 

[EMMA_UM]

Thanks for the advice Jennifer! As you can tell, I'm a bit green on this topic. I was able to get the expression working!

Using " '' " will leave the field NULL, correct? The idea is to leave it NULL if no selections are made in Water_Mod or Landscape_Mod fields.

if(count-selected(${Water_Mod}) !=0 and count-selected(${Landscape_Mod}) =0, ${Water_Mod}, if(count-selected(${Water_Mod}) =0 and count-selected(${Landscape_Mod}) !=0, ${Landscape_Mod},
if(count-selected(${Water_Mod}) !=0 and count-selected(${Landscape_Mod}) !=0, concat(${Water_Mod},', ', ${Landscape_Mod}), if(count-selected(${Water_Mod}) =0 and count-selected(${Landscape_Mod}) =0, '', ''))))

[JenniferAcunto]

Yep. A pair of empty quotes will leave the field null.