Calculate field calculating even when question is not relevant

1362
7
Jump to solution
02-28-2019 10:30 AM
LeilaJackson1
Occasional Contributor III

I have a section of my Survey123 form that is only relevant if the inspector is completing the "Full" vs. the "Simple"; however, the calculate field (q_sum) within the group is calculating even when the simple form is completed, as is the question dependent on the calculate field (behi). I do not want these fields to calculate or autofill if the "Simple" form is being completed. When completing the "Simple" none of the questions in the group appear, but in the data table a value of "0" is present for q_sum and a value of "Low" is present for behi. I have tried leaving the relevant column blank, as well as several other scenarios, and always the q_sum and behi fields are completed. Anyone have any suggestions of what I am doing wrong?

begin groupsecond_groupBEHI Pre-Screening:If "Yes" to 2 or more of the following questions, perform BEHI.  If not, BEHI = Low.       ${Insp_type}="Full"                 
select_one yes_noprescrn_q1Does the bank exhibit less than or equal to 50% protection at the toe?yesAll 6 BEHI Pre-screening questions must be answered.
select_one yes_noprescrn_q2Does 50% or more of the bank exhibit an undercut of 6 inches or more?yesAll 6 BEHI Pre-screening questions must be answered.
select_one yes_noprescrn_q3Does 50% or more of the bank exhibit stratification where at least one layer is erodible material?yesAll 6 BEHI Pre-screening questions must be answered.
select_one yes_noprescrn_q4Does 50% or more of the bank have a bank height of 10 feet or more with 50% or more soil exposure?yesAll 6 BEHI Pre-screening questions must be answered.
select_one yes_noprescrn_q5Does 50% or more of the bank exhibit roots lacking bank soil material?yesAll 6 BEHI Pre-screening questions must be answered.
select_one yes_noprescrn_q6Is 50% or more of the bank void of rooted vegetation?yesAll 6 BEHI Pre-screening questions must be answered.
calculateq_sumSum of Questions:${prescrn_q1} != '' and ${prescrn_q2} != '' and ${prescrn_q3} != '' and ${prescrn_q4} != '' and ${prescrn_q5} != '' and ${prescrn_q6} != ''number(${prescrn_q1}) + number(${prescrn_q2}) + number(${prescrn_q3}) + number(${prescrn_q4}) + number(${prescrn_q5}) + number(${prescrn_q6})
textbehiBEHI:yes${prescrn_q1} != '' and ${prescrn_q2} != '' and ${prescrn_q3} != '' and ${prescrn_q4} != '' and ${prescrn_q5} != '' and ${prescrn_q6} != ''if(${q_sum} < 2, 'Low', 'Perform BEHI')
end group                            
0 Kudos
1 Solution

Accepted Solutions
LeilaJackson1
Occasional Contributor III

Hi Phil - This first option worked (didn't bother trying the 2nd option) with a couple tweaks and I changed the response to be null instead of "N/A". Here is the final code: 

if(${q_sum}='', '', if(${q_sum}=0,'Low', if(${q_sum}=1,'Low', if(${q_sum}=2,'Perform BEHI', if(${q_sum}=3,'Perform BEHI', if(${q_sum}=4,'Perform BEHI', if(${q_sum}=5,'Perform BEHI',if(${q_sum}=6,'Perform BEHI','N/A') ) ) ) ) ) ) )

Thanks for your help. Just for my edification - why did it make a difference moving the if(${q_sum}='', '', to the beginning versus having it at the end?

Best,

Leila

View solution in original post

0 Kudos
7 Replies
BrettStokes
Esri Contributor

Hi Leila,

Are you able to share your .xlsx so that I can investigate this for you?

Brett

0 Kudos
LeilaJackson1
Occasional Contributor III

Hi Brett - I have attached it above. Many thanks!

0 Kudos
BLove
by
New Contributor II

I've experienced similar results, and it is a pain, especially if you don't catch it until you've stored thousands of calculated fields that should have never been calculated in the first place. To get around it, I build the relevant into the calculation, with an IF statement. It is a pain, but it does keep you from displaying incomplete calculations.

Example:

if(${prescrn_q1} != '' and ${prescrn_q2} != '' and ${prescrn_q3} != '' and ${prescrn_q4} != '' and ${prescrn_q5} != '' and ${prescrn_q6} != '',number(${prescrn_q1}) + number(${prescrn_q2}) + number(${prescrn_q3}) + number(${prescrn_q4}) + number(${prescrn_q5}) + number(${prescrn_q6}),'')

LeilaJackson1
Occasional Contributor III

Hi Brian - That did the trick for that field - Many thanks! Unfortunately, I am still encountering the same issue for the next field "Behi". I have tried a variety of different options, the last of which is: 

if(${q_sum}=0,'Low', if(${q_sum}=1,'Low', if(${q_sum}=2,'Perform BEHI', if(${q_sum}=3,'Perform BEHI', if(${q_sum}=4,'Perform BEHI', if(${q_sum}=5,'Perform BEHI', if(${q_sum}=6,'Perform BEHI', if(string(${q_sum}=''),'N/A', 'N/A'))))))))

However, it still returns a response of "Low" when q_sum is null. I am not great with conditional statements, so perhaps there is a better way?

Thanks!

Leila

0 Kudos
by Anonymous User
Not applicable

Hi Leila,

Have you tried wrapping another if statement around your entire current if statement, which will return null if q_sum is null, versus at the end, may would work better, or change the order of that statement to be at the start for the null check. I haven't tested either of the below in the app, just created them in notepad++ to see the formatting, but I am hoping one of these should work as you are expecting. You can copy and paste it all onto one line to put into Excel, if that helps, just i like to see it formatted out properly to make sure I have the right number of statements, commas and brackets:

if(${q_sum}=''
	,'N/A'
		,(if(${q_sum}=0,'Low'
			,if(${q_sum}=1,'Low'
				,if(${q_sum}=2,'Perform BEHI'
					,if(${q_sum}=3,'Perform BEHI'
						,if(${q_sum}=4,'Perform BEHI'
							,if(${q_sum}=5,'Perform BEHI'
								,if(${q_sum}=6,'Perform BEHI'
									,'N/A'
									)
								)
							)
						)
					)
				)
			)
		)
	)

If that doesn't work try this:

if(${q_sum}=''
	,if(${q_sum}=0,'Low'
		,if(${q_sum}=1,'Low'
			,if(${q_sum}=2,'Perform BEHI'
				,if(${q_sum}=3,'Perform BEHI'
					,if(${q_sum}=4,'Perform BEHI'
						,if(${q_sum}=5,'Perform BEHI'
							,if(${q_sum}=6,'Perform BEHI'
								,'N/A'
								)
							)
						)
					)
				)
			)
		)
	)

Hope this helps.

Phil.

0 Kudos
LeilaJackson1
Occasional Contributor III

Hi Phil - This first option worked (didn't bother trying the 2nd option) with a couple tweaks and I changed the response to be null instead of "N/A". Here is the final code: 

if(${q_sum}='', '', if(${q_sum}=0,'Low', if(${q_sum}=1,'Low', if(${q_sum}=2,'Perform BEHI', if(${q_sum}=3,'Perform BEHI', if(${q_sum}=4,'Perform BEHI', if(${q_sum}=5,'Perform BEHI',if(${q_sum}=6,'Perform BEHI','N/A') ) ) ) ) ) ) )

Thanks for your help. Just for my edification - why did it make a difference moving the if(${q_sum}='', '', to the beginning versus having it at the end?

Best,

Leila

0 Kudos
by Anonymous User
Not applicable

Hi Leila,

Great it worked. The reason I think it worked after moving the order around is that I also compiled the if statement slightly differently (arguments, commas and brackets are in slightly different place to your original statement). The first part deals with what happens when null the second part deals with everything else, ie if "nulll" then "null" otherwise "run the next set of nested if statements if a value exists". The way you had it before it was trying to do the value and null expression all a part of the same nested if statement.

Phil.

0 Kudos