Count only specified select_one answers

1359
4
Jump to solution
10-17-2019 12:44 PM
IvanKozoletov1
New Contributor III

I'm new to this application and I've built playground inspection form (see attached) which is basically trying to mimic layout of CSA standards course book. Layout was easy to recreate but the last section is the SUMMARY which calculates counts of all previous answers to select_one [satisfaction] questions in this report and calculates if the playground in compliance or not once inspection report is submitted.

Questions 1-33 can be answered: "Satisfactory" and "Unsatisfactory" and based on these answers I would like to calculate a Compliance Rating as a permanent field which I can later use in Dashboard etc.

I made these calculations within a Excel spreadsheet but that will not work once I publish this report. What is my best option to perform these calculations which will mimic the screenshots inserted here? How do I not just count the answer but only count answers that are "Satisfactory".

Thanks!

Inspection Report Page sampleSummary Page

Tags (1)
1 Solution

Accepted Solutions
IsmaelChivite
Esri Notable Contributor

Hi.  You say you are new to Survey123. Well, you did an amazing job so far with the layout. Very impressive!

It looks like you are very close to get this done. The bit where your survey is breaking is the logic to sum the pass-not-pass questions; the summary.  In the XLSForm you attached you included some Excel formulas at the end. The Excel formula syntax is not compatible with the XLSForm specification.  You need to tweak the formulas so they are compatible with XLSForm.  You can find a quick reference of the formulas here: Formulas—Survey123 for ArcGIS | Documentation 

Just a few ideas that came to mind while reviewing your XLSForm:

  • I think it will be easier if you change the [yes_no] list so the values in the name column are 0 and 1 instead of no and yes.  In this manner, it will be much easier for you to do the sum of passed and not passed questions. I am attaching a simplified version of the form so you can see what I mean.
  • It seems like using the pages style could make your survey more readable.

View solution in original post

4 Replies
IsmaelChivite
Esri Notable Contributor

Hi.  You say you are new to Survey123. Well, you did an amazing job so far with the layout. Very impressive!

It looks like you are very close to get this done. The bit where your survey is breaking is the logic to sum the pass-not-pass questions; the summary.  In the XLSForm you attached you included some Excel formulas at the end. The Excel formula syntax is not compatible with the XLSForm specification.  You need to tweak the formulas so they are compatible with XLSForm.  You can find a quick reference of the formulas here: Formulas—Survey123 for ArcGIS | Documentation 

Just a few ideas that came to mind while reviewing your XLSForm:

  • I think it will be easier if you change the [yes_no] list so the values in the name column are 0 and 1 instead of no and yes.  In this manner, it will be much easier for you to do the sum of passed and not passed questions. I am attaching a simplified version of the form so you can see what I mean.
  • It seems like using the pages style could make your survey more readable.
climasig
New Contributor

Hello Ismael

  Is there a way not yo use the default option iqual=0 at all?

Thanks your example really help me.

Grettel

Costa Rica 

0 Kudos
IvanKozoletov1
New Contributor III

Ismael,

Thanks a lot for your assistance on this matter. After I posted this question I had the same idea recording Satisfied values as 1 and Unsatisfied values as 0. Then the counting will be really easy by summing up the fields. And thanks for heads up on using 'pages' style to make it more readable. I found Open Data Kit website where all aspects of XLS forms, functions, calculations and appearance settings are covered in detail. I now know how to get this done! 

sarakid
New Contributor

@IsmaelChivite 

Hi, I'm also new to this field. 

I have three different dates to choose. I want to keep the guest list for each date to a maximum of 10 people. Thus, the constraint has been set by me. >=1 and.=2 for the total number of pax joining. Thus, the dropdown will change based on the number of guests.

On the other hand, I only intended to invite ten people to each date. The date would be as follows: 14-02-2023 for ten people, 15-02-2023 for ten people, and 16-02-2023 for ten people.

I have the list of the organization already. Therefore, the maximum number of people allowed to join each organization is two. If another member of the organization wished to submit another answer, it would not be possible since the maximum number of entries allowed for that organization has been reached.

Is it possible?

 

0 Kudos