topic Re: ArcGIS 123 Connect - Sum of integer Value in Repeat based on a Select One Choice in ArcGIS Survey123 Questions

ArcGIS 123 Connect - Sum of integer Value in Repeat based on a Select One Choice

LindsayLewin — Tue, 20 Feb 2024 16:22:54 GMT

I have a repeat group where the prompts the user to select one type of item found, there are many types to choose from. Once type is selects, they need to identify the quantity - example: 1. Type: NS , Quantity : 3; 2. Type Seed, Quantity: 1; 3. Type NS, Quantity: 5

In a summary group, I am trying to Sum the total amounts for each type selects. So from the example above the summaries should read:

Total NS: 8

Total Seed: 1

I've been searching all weekend on the technical forums, but have tried multiple combinations in vain. Can anyone help with this - it seems like it should be pretty basic but I am obviously missing something.

Please help!

Re: ArcGIS 123 Connect - Sum of integer Value in Repeat based on a Select One Choice

abureaux — Tue, 20 Feb 2024 17:02:14 GMT

sum() for a repeat

Where qty is an item in your repeat, sum(${qty}) outside the repeat will give you a total. Just be sure that both questions have the correct type (e.g., bind::type = int)

Re: ArcGIS 123 Connect - Sum of integer Value in Repeat based on a Select One Choice

abureaux — Tue, 20 Feb 2024 17:04:30 GMT

Sorry, misread.

In your repeat, you need a formula for each item in the select_one:

Then outside the repeat, you can sum each of those calculates.

Re: ArcGIS 123 Connect - Sum of integer Value in Repeat based on a Select One Choice

LindsayLewin — Tue, 20 Feb 2024 18:08:03 GMT

Thanks for this. I gave this a go, and while the calculation works for the total count or time that a type is submitted, The total quantity of each still eludes me.

I need to sum the total of each quantity submitted for a single type. When I look at the data in the App, the total FRAG should be '22' but the calculation I tried above returns '2'.

Type is a String and Quantity is an Integer, when i change the bind on type is returns an error.

Re: ArcGIS 123 Connect - Sum of integer Value in Repeat based on a Select One Choice

abureaux — Tue, 20 Feb 2024 19:18:08 GMT

Does this help? Or am I missing something here?

XLSX setup:

Choices tab:

End result is a sum() outside the repeat for each of the select_one items that gives me the total number of times each individual item was selected.

Re: ArcGIS 123 Connect - Sum of integer Value in Repeat based on a Select One Choice

LindsayLewin — Tue, 20 Feb 2024 21:45:11 GMT

Hi - I really appreciate your feedback.

Each individual item has some quantity associated with it. I need that quantity summed, not how many times the item was selected

example

Item A was chosen and a Quantity of 3 was submitted

Item B was chosen and a Quantity of 2 was submitted

Item A was chosen and a Quantity of 5 was submitted.

The summary should say Item A has a total quantity of 8

Essentially I want to query an item choice and then sum the quantities that were submitted for those, not a count of how many times that item was selected.

I attached my form, line 28 would be the choice list that needs to be isolated and then line 33 needs to be summed from that.

Re: ArcGIS 123 Connect - Sum of integer Value in Repeat based on a Select One Choice

abureaux — Tue, 20 Feb 2024 21:59:09 GMT

Oh, opps. My bad! That can still be done though.

I'll have a look at your XLSX!

Re: ArcGIS 123 Connect - Sum of integer Value in Repeat based on a Select One Choice

abureaux — Tue, 20 Feb 2024 22:36:17 GMT

A couple things:

Required only has two states: <yes> and <null>. There is actually no "no" option for "required", so you don't need to specify that.
${STUXO} was not formatted properly. You had an extra item attached to the end which broke the calculation:
```
, sum(${qty})
```
I had to fight with line 28, ${subtype}. While it was in the document, I kept getting "Error converting XLSX". I finally realized it was because you had the bind::type set to int for it. It is a text field, so that was causing a type mismatch (I wish it would just say that!)
The first thing I do to surveys I am looking at (once I get them into Connect), is run Tools > Analyze survey (I highly recommend this). It noted two items which I recommend you look into:
And lastly, a question. Because this XLSX was working for you, I am wondering if your Connect is up to date? Current version is 3.19.104. If you don't have at least that, I'd also recommend reinstalling Connect through the Microsoft Store (MS Store version auto-updates Connect for you).

Here is what the set-up would look like for getting totals of ${qty}:

You just need to expand this to all available options.

Now, I need to go on and tell you about a quirk of calculates and repeats... Essentially, if you type a number into a text box within a repeat, and then immediately add a new item to that repeat, your value is ignored by calculates! This is problematic... The value will calculate properly if the end user: 1) Manually scrolls back through the repeat, 2) the user to presses "enter" after typing the value, or 3) the user clicking into another text box. But these aren't reliable options in my opinion. Luckily, you are working with numbers and not text! That make this easier...

To eliminate this issue, I recommend setting ${qty} to the numbers appearance:

Basically, the numbers appearance enters the value, and then presses "Enter" for you, ensuring all calculates work as intended.