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

1425
10
Jump to solution
05-16-2018 11:22 AM
BrentKinal1
Occasional Contributor III

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?

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
Philip-Wilson
Esri Frequent Contributor

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.

View solution in original post

10 Replies
Philip-Wilson
Esri Frequent Contributor

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.

0 Kudos
BrentKinal1
Occasional Contributor III

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.

0 Kudos
Philip-Wilson
Esri Frequent Contributor

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.

View solution in original post

by Anonymous User
Not applicable

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?

0 Kudos
Philip-Wilson
Esri Frequent Contributor

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.

0 Kudos
by Anonymous User
Not applicable

Would a potential workaround  be to

  1. add a column that has a default value of "1" within it
  2. 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?

0 Kudos
AmandaSandström2
New Contributor III

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

0 Kudos
Philip-Wilson
Esri Frequent Contributor

Hi Amanda,

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

Phil.

0 Kudos
AmandaSandström2
New Contributor III

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...

0 Kudos