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?
Solved! Go to Solution.
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.
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.
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.
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.
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?
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.
Would a potential workaround be to
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?
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
Hi Amanda,
Can you please share you survey xlsx file so we can take a look?
Phil.
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...