Complex if/else statements in Survey123

[Anonymous User]

Hi, I am trying to calculate a complex if/else statement with several ands and ors. I keep getting an error with inbalanced brackets but cannot figure it out. Any help appreciated!

Statement: 

if(selected(${OutletGradeId},'At Stream Grade'),'Full AOP', if(selected(${InletGradeId},'Free Fall') and ${OutletDropToWaterSurface} > 1 and ${OUTLETGRADEDIVIDED} > 0.5 ),'No AOP',if(selected(${OutletGradeId},'Free Fall onto Cascade' ) and ${OutletDropToWaterSurface} > 1 and ${OUTLETGRADEDIVIDED} > 0.5 ),'No AOP',if(selected(${OutletGradeId},'Cascade') and ${OutletDropToWaterSurface} > 1 and ${OUTLETGRADEDIVIDED} > 0.5 ),'No AOP',),'Reduced AOP','NULL')))))

Thank you!

[KassandraRodriguez · Accepted solution]

if(selected(${OutletGradeId}, 'At Stream Grade'), 'Full AOP',

if(selected(${InletGradeId}, 'Free Fall') and ${OutletDropToWaterSurface} > 1 and ${OUTLETGRADEDIVIDED} > 0.5, 'No AOP', 

if(selected(${OutletGradeId}, 'Free Fall onto Cascade') and ${OutletDropToWaterSurface} > 1 and ${OUTLETGRADEDIVIDED} > 0.5, 'No AOP', 

if(selected(${OutletGradeId}, 'Cascade') and ${OutletDropToWaterSurface} > 1 and ${OUTLETGRADEDIVIDED} > 0.5, 'No AOP','Reduced AOP', 'NULL'))))
‍‍‍‍‍‍‍

I broke it down so that it's easier to see, So I think your error is at the last if statement it can either be 'No AOP' or 'Reduced AOP' cant be both so you'll have to write another if statement if you want to include the other one. Also I removed unnecessary brackets.

[Anonymous User]

Thank you! That worked! 

[Anonymous User]

I went through and corrected all of my other calculations based on your advce and have just one left that is stumping me:  if(${INLETGRADESCORE},'Full AOP' and ${OUTLETGRADESCORE}, 'Full AOP' and ${DEPTHSCORE},'Full AOP' and ${SUBSTRATEMATCHSCORE},'Full AOP' and ${SUBSTRATECOVERSCORE},'Full AOP' and ${PHYSBARRSCORE},'Full AOP','No Barrier', 'Reduced AOP', 'NULL')

This one says "something broke the parser, if requires 3 arguments, only 10 provided"

I've tried all number of combinations I could think of.

Any ideas?

Thank you!

[KassandraRodriguez]

if(${INLETGRADESCORE},'Full AOP' and ${OUTLETGRADESCORE}, 'Full AOP' and ${DEPTHSCORE},'Full AOP' and ${SUBSTRATEMATCHSCORE},'Full AOP' and ${SUBSTRATECOVERSCORE},'Full AOP' and ${PHYSBARRSCORE},'Full AOP','No Barrier', 'Reduced AOP', 'NULL')‍

Hmmm I'm not sure what the argument is here but I think you meant to use "if(selected(" ? If not take a look at my example below:

if(${COUNTY} = 'PLA' and ${ROUTE}='65' and ${EndPM} <= 12.5, 'Marcy Pillett', 'N/A')‍‍

Mine has one answer if the argument is met which is 'Marcy Pillet' if it's not met then it's 'N/A'. Yours has too many answers. What I think you meant to do is 

if(${INLETGRADESCORE} = 'Full AOP' and ${OUTLETGRADESCORE} = 'Full AOP' and ${DEPTHSCORE} = 'Full AOP' and ${SUBSTRATEMATCHSCORE} ='Full AOP' and ${SUBSTRATECOVERSCORE} = 'Full AOP' and ${PHYSBARRSCORE} = 'Full AOP', 'No Barrier', 'NULL')‍

So the answer if the argument is met would be 'No Barrier' otherwise if it's not met it's 'NULL'. Also notice the syntax is different from "if selected" I used equals sign.

[Anonymous User]

Thank you! It is not a select field unfortunately. I think the issue is

that ALL of those have to be true to get No Barrier otherwise it gets

reduced AOP or null if any of those fields has a null. Does your second

option work here or would that give “No barrier” if any of them are true?

Thank you again!

On Thu, Jan 16, 2020 at 12:43 PM Kassandra Rodriguez <geonet@esri.com>

[KassandraRodriguez]

if(${INLETGRADESCORE} = 'Full AOP' and ${OUTLETGRADESCORE} = 'Full AOP' and ${DEPTHSCORE} = 'Full AOP' and ${SUBSTRATEMATCHSCORE} ='Full AOP' and ${SUBSTRATECOVERSCORE} = 'Full AOP' and ${PHYSBARRSCORE} = 'Full AOP', 'No Barrier', 

if(${INLETGRADESCORE} = 'NULL' or ${OUTLETGRADESCORE} = 'NULL' or ${DEPTHSCORE} = 'NULL' or ${SUBSTRATEMATCHSCORE} ='NULL' or ${SUBSTRATECOVERSCORE} = 'NULL' or ${PHYSBARRSCORE} = 'NULL', 'NULL', 'Reduced AOP')‍‍‍‍‍‍‍‍‍‍‍‍

I think you might have luck with this not sure, I broke it down again to see it easier. Since the answer is NULL if ANY of them are null then I used "or". If you share your xlsx with me it would be easier to help.