topic Re: count() and sum() functions for repeat in Inbox in ArcGIS Survey123 Questions

count() and sum() functions for repeat in Inbox

BrentKinal1 — Wed, 16 May 2018 18:22:07 GMT

Is it possible for count() and sum() functions to work for ALL repeats reported with a survey, including those previously sent, when revisiting a survey via the Inbox?

Re: count() and sum() functions for repeat in Inbox

Anonymous User — Wed, 23 May 2018 05:53:00 GMT

Hi Brent,

It is not currently possible to use the count() and sum() functions across all repeat records for the survey. The count() and sum() functions only work across the repeat records related to the same parent record.

For further analysis of count() and sum() across all repeat records in the same related table, regardless of the parent record, you would need to do this outside of the app.

Phil.

Re: count() and sum() functions for repeat in Inbox

BrentKinal1 — Wed, 23 May 2018 18:42:20 GMT

Hi Philip, Thanks for your response, I think I could have worded my question a little bit better. What I had meant was that the count() and sum() functions are not working within the same parent record when used with a repeat. It appears that these functions only work on the repeat records currently on the device, when the parent record is opened via the Inbox it does not count repeated records that were submitted when the record was previously sent . My current form is set up to not include previously submitted related records when brought into the inbox, I haven't had time yet to test by pulling in all of the previously sent repeated records in to the Inbox by setting the bind parameter to "query", I am hoping downloading all the previously sent repeated records will solve the issue.

Re: count() and sum() functions for repeat in Inbox

Anonymous User — Thu, 24 May 2018 01:11:22 GMT

Hi Brent,

Thanks for clarifying, yes you are correct, the count() and sum() calculations are only working on the related records that are stored in the local database on the device which is created when new surveys are collected with a new collect, or when surveys are downloaded via the Inbox. These calculations are done locally on the device and do not reference other existing data in the same table stored in the feature service. You will need to use the query parameter to ensure you are downloading all related records to the Inbox for that survey to be able to use count() and sum() calculations with all the available data.

Phil.

Re: count() and sum() functions for repeat in Inbox

Anonymous User — Thu, 24 May 2018 12:44:31 GMT

Philip, just for clarification - you are stating that if we enable the "query" and allowAdds=Yes for all repeats, that the count() and sum() will function correctly based on what is pulled into the Inbox and also for any additional records?

If so, then this is great news for some of the surveys that we are going to enable within the next couple of weeks.

One additional question about this the count(); can I use it in conjunction with the once() in order to populate a record count field within the repeated records? Something like once(count(${repeatQuestion})), that will then sequentially add a running number to a field?

The logic is that I can use this to create a count for each record that is entered into the repeat for each time that a record is entered. If there is 3 records within a repeat then the count() function would have a value of 3 within all records. What I was thinking is that by using the once() with count() that count would be calculated for each record placing the number of records at that time within the designated field and not hold the total number of records?

Re: count() and sum() functions for repeat in Inbox

Anonymous User — Sun, 27 May 2018 23:01:57 GMT

Hi Mike,

Yes, if you use query allowAdds=True in the esri parameters column then all the related records in a repeat will be downloaded to the for the parent record. Therefore when you open that survey (parent record), the sum() and count() will be calculating across all those previous and new related records in that survey.

The use of once() with count() is not supported as you suggest due to the way calculations run when a value is changed or a new repeat add, therefore count will keep incrementing with each new repeat even when once() used.

Phil.

Re: count() and sum() functions for repeat in Inbox

Anonymous User — Fri, 01 Jun 2018 14:51:27 GMT

Would a potential workaround be to

add a column that has a default value of "1" within it
Use another field that has a once(sum(${Question})) to get a count?

I know that it could cause issues with the same as you pointed out, but as long as folks do not delete records, then it should work or would it note the deletion and recalculate the sum?

Re: count() and sum() functions for repeat in Inbox

AmandaSandström2 — Mon, 20 May 2019 06:34:04 GMT

HelloPhilip Wilson,‌

I also have this problem. I have set the query to allowUpdates but the sum() function for repeats will still not work properly in the Inbox, it works just fine if I open the survey from Sent though. Can you help me?

Best regards,

Amanda

Re: count() and sum() functions for repeat in Inbox

Anonymous User — Mon, 20 May 2019 09:28:46 GMT

Hi Amanda,

Can you please share you survey xlsx file so we can take a look?

Phil.

Re: count() and sum() functions for repeat in Inbox

AmandaSandström2 — Mon, 20 May 2019 12:23:55 GMT

I can't find where I can attach the xlsx file. I'll describe what I've tried to do in the survey that fails when I open it from the inbox. I have a repeat for result areas. For every repeat the user should enter which result area it is and since automatic numbering isn't possible yet I've written an if statement that controls the sum of these result areas and if the sum is wrong a note will appear. They can choose between area A, B, C and D but since I want them to choose them in the right order and never choose the same answer more than once I've written this if statement:

if((${sum_result}='A'), 1, if((${sum_result}='AB'), 1,if((${sum_result}='ABC'), 1,if((${sum_result}='ABCD'), 1,if((${sum_result}=''), 1,2)))))

If the user names the repeats in the wrong order a note will appear. This works fine as long as you don't open the survey from the inbox because for some reason it only seems to sum and count new repeats that you create...

Re: count() and sum() functions for repeat in Inbox

JamesTedrick — Tue, 21 May 2019 22:50:46 GMT

Hi Amanda,

Can you see if the sample attached properly represents the workflow you are trying to implement? It appears to be working properly in this sample. BTW, you can attach files by going into the advanced editor in the comment box.

Re: count() and sum() functions for repeat in Inbox

AnthonyJonesRSK — Fri, 13 May 2022 13:57:47 GMT

Hi Phil, sorry for piggy backing on an old post but I'm still experiencing issues with this now. I have a count calculation in my parent record for repeats and when accessing surveys from the inbox it won't take into account those repeat records from previous surveys. If I add new repeat records these are included in the calculation. I have "allUpdates=true query" for all my repeats so I would have expected all repeat records to be included in the calculation. Is this the expected behaviour? Thanks