topic Re: sum() Aggregate function, appends values, doesnt add them in ArcGIS Survey123 Questions

sum() Aggregate function, appends values, doesnt add them

danbecker — Sat, 14 Oct 2017 00:16:39 GMT

Here's the general layout of the form. If selField = a, I want to set calcField to 1 for the repeat record.

Then, the calculation in sumVal field should tally all the repeat records that have a value of 'a'.

Well, the first sumVal is 1 if 'a' is selected, then you add a repeat select 'a' again, now the sumVal is 11.

So, the sum() calculation is concatenating the 1's and not adding them. How do you get around this??

I have calcField and sumVal fields set to ESRI type Integer.

type	field_name	calculation
integer	sumVal	sum(${calcField})
begin repeat	relatedTable	List of Repeats
select_one choiceList	selField
calculate	calcField	if(selected(${selField},'a',1,0)
end repeat

Re: sum() Aggregate function, appends values, doesnt add them

danbecker — Sat, 14 Oct 2017 01:54:55 GMT

Edited

This is a bug. Change calcField from type calculate to integer and everything works.

I just added a bogus relavent expression for calcField so its never displayed to the user.

Adding column bind::type and setting it to Integer also works. In this case you can leave the calcField to be type calculate.

Re: sum() Aggregate function, appends values, doesnt add them

JamesTedrick — Mon, 16 Oct 2017 17:18:04 GMT

Hi Dan,

Many question types (calculate, hidden, selects) are automatically typed as strings (text). To have them be considered another data type, you will need to add a column bind::type and set the value for the calculate question to be integer. The esriFieldType relates to publishing the service and how the data is submitted (as opposed to how it works inside the form).

Re: sum() Aggregate function, appends values, doesnt add them

danbecker — Mon, 16 Oct 2017 17:25:41 GMT

thanks James, confirmed. I edited my comment above.

Re: sum() Aggregate function, appends values, doesnt add them

AliciaPerez — Tue, 09 Jul 2019 12:55:03 GMT

Hi James Tedrick‌, in Survey123 Connect 3.3.51, there is a column bind::type. I have a repeat question, containing a select_one question, which I want to be a considered as an integer question in order to sum all the values for this repeat record. The problem, is when I set the value for the select_one question to be integer in the column bind::type (I set the value to int), I get an error in my form. I'm attaching screeshots of my survey123 form.

This is the error I get:

Thank you for your help!!!

Alicia

Re: sum() Aggregate function, appends values, doesnt add them

JamesTedrick — Wed, 10 Jul 2019 05:36:05 GMT

Hi Alicia,

Unfortunately, you cannot set the bind::type of a select_one question. I would recommend having a calculate question that converts the value of the select_one to the appropriate type (using a conversion function and bind::type setting) as a workaround; you could set the select_one question's bind::esri:fieldType setting to null to prevent having 2 columns with essentially the same information.

Re: sum() Aggregate function, appends values, doesnt add them

AllenDailey1 — Thu, 29 Aug 2019 16:52:33 GMT

Hi James,

I have the same problem as the original post. I also have the additional problem that when I try to enter something into the bind::esri:fieldType column OR the bind::type column, I get this error, and it happens every time I try.

I've tried typing:

esriFieldTypeInteger

integer

int

Anything I type results in this same error message. Also, absolutely nothing happens or displays when I click the little dropdown arrow in the selected cell, which I believe is supposed to show the things you can put in cells in that column.

Edit:

I just discovered that I am able to enter a field type into the esriFieldType column if I create a brand-new row in the spreadsheet. So...are you not allowed to change the field type from string to integer for an already-existing row/question?

Re: sum() Aggregate function, appends values, doesnt add them

DougBrowning — Fri, 30 Aug 2019 14:30:24 GMT

Looks like your Excel validation got messed up. This happens when you copy/paste cells and forget to use right click Paste Values. It is just using simple Excel constraints looking up via the types tab.

Options are

Fix the validation by looking at another cell.

Copy paste (not using paste values so you get the validation to come over) from a cell that does work then type your value back in.

Start a brand new form, copy all the old form in using Paste Values, then save over the top of the original. This is what I do to "update" my old forms when new features come out.

Turn off Excel validation for that cell. I do this a lot when using grid or it will yell at you constantly. I also use this to get rid of the annoying yellow popups.

Re: sum() Aggregate function, appends values, doesnt add them

AllenDailey1 — Tue, 03 Sep 2019 15:23:11 GMT

Thanks, Doug!

I'd never heard of right-click Paste Values, so that's good to know.

In the meantime, I also discovered that certain rows, I guess ones where I had not copy/pasted incorrectly, did allow me to select a value from the dropdown. Then I could copy this value that I chose from the dropdown and paste it into one of the rows/cells where I had unsuccessfully tried to enter the value before. I imagine this connects to the issues you posted about.

Thanks!

--Allen