topic Re: Only calculate relevant fields in ArcGIS Survey123 Questions

Only calculate relevant fields

Philine — Mon, 27 Mar 2023 12:55:46 GMT

Hi everyone,

I have a group of questions containing a few yes/no questions, which are dependent on the previous question. Once a question group has been answered I should get a list of suggestions ranging from A - F. Once all group questions have been answered I would like to calculate which answers have been suggested the most and be able to pass on a result based on that.

I am able to show the answers to a group of questions by using if(selected(${question, 'Yes'), 'Yes', 'No') and the relevant field, but I don't seem to be able to calculate this. When using calculations it seems to add all the questions instead of just the selected questions of that group.

See example below, where bold defines the answer given.

I hope this makes sense.

Thanks so much in advance.

Question 1				A	B	C	D	E
Yes:	Question 1.1
	Yes:	Questions 1.1.1
		Yes:		1	1	1	1	1
		No:		1	1	1	0	0
	No:			1	0	0	0	0
No:	Questions 1.2
	Yes:			1	1	0	0	1
	No:			1	0	0	0	0

Question 2
Yes:	Question 2.1
	Yes:	Questions 2.1.1
		Yes:		1	1	1	1	1
		No:		1	1	1	0	1
	No:			1	1	0	0	0
No:	Questions 2.2
	Yes:			1	1	1	0	1
	No:			1	0	0	0	0


			Total	2	1	1	1	1

Re: Only calculate relevant fields

DougBrowning — Mon, 27 Mar 2023 14:58:34 GMT

Not sure I follow but my guess is that when a field is not relevant it changes to empty string "". So a non relevant field will never have a value other than "". If in the form if you try to add this up it will break since "" is not a number. If you are counting it is better to use a type hidden and get it out of the group that has the relevant in it. That way it was always calcualte.

Hope that is it

Re: Only calculate relevant fields

Philine — Mon, 27 Mar 2023 16:23:06 GMT

Hi Doug,

thanks for the quick reply!

I probably haven't explained it properly. Attached an example question group.

I am currently showing results through the relevant field. But I cannot add this up across the different question groups as mentioned above. When I start calculating the field (row49-50) I am obviously doing something wrong since it should match what is shown in the answers above and not adding the numbers. Aware that I might be going about this the total wrong way...

Re: Only calculate relevant fields

DougBrowning — Mon, 27 Mar 2023 17:20:13 GMT

Looks like my guess was correct. You are trying to add together "". I would remove the relevant from lines 37-47. It should calc to 0 then your final total will use that 0 instead of "".

Also why are you using '1' and '0' all over. Change all of these to real numbers. Adding strings is not going to work out well even when using number() or int() later. Also this makes you attribute table all wrong. Change to just 1 and then set the bind type column to int. Or set to integer then set appearance to hidden. That should do it. I would also bind the final scores to int or better change the type to int then make then read only.

In the end you are trying to add strings is the issue.

Hope that helps

Re: Only calculate relevant fields

Philine — Mon, 27 Mar 2023 22:00:14 GMT

Thank you so much Doug! That has done the trick. I clearly had no idea what I was doing.

Unfortunately once I hit publish and access the form via a browser I see the dreaded NaN. I have seen other posts about similar issues here on the forum too, but I haven't been able to fix this. Would you be able to advise once more?

Thanks again!

Re: Only calculate relevant fields

DougBrowning — Mon, 27 Mar 2023 22:02:55 GMT

My guess is you missed changing one of the strings.

Re: Only calculate relevant fields

Philine — Tue, 28 Mar 2023 12:10:07 GMT

I just don't seem to be able to fix it. Thankful for any advice on what I am missing.

Re: Only calculate relevant fields

DougBrowning — Tue, 28 Mar 2023 14:41:00 GMT

It looks like this is in a really old schema. Have you not updated 123 in some time? Next it looks like you did some copy paste vs paste values and messed up all the validation in excel. Always use paste values in excel. The bind should be int not integer but your drops downs are all broke. May be one column off too. But really on lines 32 to 49 you do not need either bind since you have the field type as integer. Next why change the Yes and no to 1 and 0? That will look ugly in the data and years from now no one will know what 1 and 0 are. I always keep the name and label the same or its just confusing.

There is no need for all those temp fields so I removed all of those. Your calcs make no sense to me but i assume they are right. I did all but 3 of them so fix that up. I also moved you to the new schema. It all works now.

Hope this helps these are all valuable lessons.