Is it possible to display the count of child and grandchild cases in the case list as a calculated property? I tried the following, but without luck: count(instance('casedb')/casedb/case[index/parent = instance('commcaresession')/session/data/case_id][@status = 'open'])
Thanks!
Ralph
Hi Ralph!
The issue you are encountering is that the case ID hasn't yet entered the session while on the case select screen, as referred to in
instance(‘commcaresession’)/session/data/case_id
The 'base' reference in the case select screen is the root node in the casedb virtual document for the individual row being displayed. You can pretend like the case list is a Repeat Group in a form, and the calculated properties are like Hidden Value calculations inside the repeat group. As such you can use the current() reference to refer back to the case model for the current row.
You should be able to refer to the count you are looking for with the following expression
count(instance(‘casedb’)/casedb/case[index/parent = current()/@case_id][@status = ‘open’])
-Clayton
Hi Clayton,
Thanks for your feedback. I tried putting in the expression you suggested, but I’m still being presented with an error.
Hi Ralph,
Can you replace all of the single quotes in the input with ones you've typed? I think since those are copied from the browser it is using "fancy" single quotes instead of the raw ones that your keyboard would input if you typed them.
-Clayton
Hi Clayton,
It was the fancy quotes and it works perfectly now. Is there a similar way to count the number of grandchild cases?
Thanks!
Hi Ralph,
The rabbit hole can get kind of deep on this one. I'm going to write up a pretty complex way to achieve this below, but I won't be able to test it live.
If you want to achieve something like this I strongly suggest that you read up on using the Web Apps Data Preview system's ability to test XML expressions live, and test the expression inside of a form (replacing the 'current()/@case_id' expression with the appropriate expression inside of a form) before applying to your app. You can break down the expression into pieces to make sure they are outputting what you expect to test them.
Counting Grandchildren
The logic currently being expressed is "Count the set of cases who have a parent index with the ID "current()/@case_id"
instance(‘casedb’)/casedb/case[index/parent = current()/@case_id]
You can break down the new request in a more complex manner: Take the set of cases who have a parent index with the ID "current()/@case_id" (what we are counting here), and count the set of cases who have a parent index which is in that set.
We can create a set of child case IDs with this subexpression
join(' ', instance(‘casedb’)/casedb/case[index/parent = current()/@case_id]/@case_id )
which will give a space-separated list of those case ID's. You can use the selected() function to then see whether another case's parent index happens to be included in that set, and then count that list
count(instance(‘casedb’)/casedb/case[selected(LIST_OF_CHILDREN, index/parent) ])
resulting in the final expression
count(instance(‘casedb’)/casedb/case[selected(join(' ', instance(‘casedb’)/casedb/case[index/parent = current()/@case_id]/@case_id), index/parent) ])
Quick note: I expect this will be fast due to parts of the xpath query engine that pre-analyse and speed up these kinds of expressions, but those optimizations can fail if expressions don't meet an expected pattern. I'd also strongly recommend testing the performance of these queries at full load before deploying them in a live app.
-Clayton
Amazing Clayton! Thanks so much for this! I will try it out asap and let you know if it works!
I tried following Clayton's recommendations up here in vain. I am trying to count the number of child cases in my followup form. My interest here is that i want to be able to know how many cases i have in a particular category not the number of times those cases have been modified. For now that's what it gives me and i am using this: count(instance(‘casedb’)/casedb/case[index/parent = instance(‘commcaresession’)/session/data/case_id][@status = ‘open’]) ,
However when i tried Clayton's suggestion: count(instance(‘casedb’)/casedb/case[index/parent = current()/@case_id][@status = ‘open’]), it returns zero output. What could i be missing?
Hi Irene,
In the base expression, whether
current()/@case_id
Or
instance(‘commcaresession’)/session/data/case_id
is the right way to reference the ID is highly situational.
If you are in a normal form after selecting a parent case, the expression you provided would be the right one. Replacing only that part of the expression should allow the rest of it to function the same way.
-Clayton
Dear @Clayton_Sims,
In the same way, it is possible to show the "label" or name (as Commcare is) of the previously selected option in the property case list?
I have already used a formula to show it in the form and it works, but when I selected the property case doesn't show anything:
The formula I have used is the next:
Calculate Condition: concat(instance('table_id')/table_list/table[name = question]/label[@lang = jr:itext('lang-code-label')], "")
Question ID: center_to_visit_label
Best regards,
