I'm trying to get a count of parent-cases that have at least one subcase with a particular value.
- Parent case_type is "foo" and sub case case_type is "bar".
- Each case_type "bar" has a variable "degrees"
- I need the count of all "foo" with at least one "bar" where degrees < 100
- I do not want to double-count a "foo" if it has multiple "bar" that meet the degrees requirement.
- I expect a high count (2,000+) of case_type foo per device
- There could be from 0 to 10+ case_type bar per foo
- I'm currently attempting this in a form that edits cases case_type bar
I've attempted to do this with a casedb bar "count" inside a foo "count" but I couldn't figure out how to phrase the interior casedb parent @case_ID reference.
I wonder if this could be done with a nested repeat group, but I'm confused by the fact that the number of "bar" per "foo" is dynamic.
Before I invest significant more time in this I'm wondering if anyone has done something similar and what your solution looked like.
Best Answer
Create a repeat group with model iteration over the
foo
casetype. The model iteration ID query would be:This will create one repeat per
foo
case.Inside the repeat group, you'd have a count of the number of
bar
cases are under the currentfoo
case:This returns 1 if there are any
bar
cases under thatfoo
case with degrees < 100.Then outside of the repeat group, you can sum the above calculation over all the
foo
cases to get your total:I don't know about the performance of this solution with the case load you have. You probably have to load test it.