Should the expression be placed within my hidden field?
I believe this should be the expression:
instance('casedb')/casedb/case[@case_type = 'benibiz_training_group_new'][@case_id = #form/name_and_date_group/group_name]
That is, I changed
@case_id. Your screenshot above includes curly quotes
’, which I replaced with simple quotes
'. That can sometimes happen if you copy/pasted from a rich text editor like Word or email. The “Lexical error” in your screenshot refers to that issue.
Can you also tell me how i can pull the actual name of the group in the report? Right now in the survey the group name is being properly referenced but when i pull the data into excel or power BI, its the case id thats still showing up.
It looks like that case ID is being saved as the answer to that lookup table question, is that correct? You need a hidden value with the calculate set to:
instance('casedb')/casedb/case[@case_id = ID_EXPRESSION]/case_name
But instead of
ID_EXPRESSION, drag and drop the lookup table question there.
Thanks so much, this works well. I have also saved it as a case property and pulling that property in subsequent follow up forms so it makes sense to the end users of the data.
Can you also help me with another issue, i have submitted a ticket for it earlier today. It has to do with archiving forms. I archived all the forms in my surveys so that we could test the surveys with fresh data. The archived forms do not show up in a regular form data export but they still show up when i do an excel dashboard report.
That’s good to hear. One word of caution about saving the name as a case property. You’re saving the name at the time the form is filled out - it’s not a reference to the group case’s name. This means if the name is later changed, it won’t be updated on this case. The alternative is to save the group case ID instead, and look up the name every time. Depends on what’s more appropriate for your use-case.
I don’t have a good answer to your other question, sorry. Archiving forms is intended to hide them from reports and exports, not sure whether there’s a straightforward way around that.
Yes i am still pulling the case ids alongside the case names as well.
Thanks so much @Ethan_Soergel
Can you please assist again with this same issue? For some reason, its not working when i try to implement this in another application.
I need to use a multi select to enter all farmers present at a training. If i set the form as a follow up then the user is forced to pick a farmer before entering the form so i decided to set it up as a registration form but i placed an open case condition on it. Any alternative solutions to this method are highly welcome please
When i download the report in excel, the question “select which farmers are present” produces the case ids while the case_name_label does not pull the actual name
I need the column referncing the name
current syntax inside my case_name_label hidden field is
instance(‘casedb’)/casedb/case[@case_id = #form/farmers_present]/case_name
Hello again Jay
You’re almost there. it’s the same thing as i was explaining to you on the other post.
a multi-select will not return a single value. it’ll return a set of caseID’s separated by spaces. so, the “=” operator will always not find the exact caseID. it’ll always have some other caseID’s with it.
so what you want to do is
this will return a string of case_names, separated by commas, for the cases selected in farmers_present.
then you save that into a case property that will be available in a case download.
P.S: The correct forum for this type of inquiries is “users” section. The developers section is designed for those who have in-house implementations of CommCare and/or building other software that works WITH commcare. the users space is designed for questions and discussions the community has about building and using CommCare apps and exports.
Thanks so much for the response but i am still getting errors here. been trying to tweak the syntax but its still not working.
Been replacing those curly quotes but the system still complains
I think that last parenthesis is superfluous, actually. You may also want to play with the separator, I changed it to “comma space”:
join(instance('casedb')/casedb/case[selected(#form/farmers_present, @case_id)]/case_name, ", ")
Unfortunately, the syntax still doesn’t work. Please see error below
I think i was the one that made the error
the first argument in the “Join()” function should be a single value to join with the rest.
so in our case, it should be
join(", ",instance(‘casedb’)/casedb/case[selected(#form/farmers_present, @case_id)]/case_name)
I think that might solve this error
it explains it here
Hi @Mazz, Thanks so much . it seems to be working well now. I was just about to ask for a link that explains the syntax.
that’s great Jay! glad we could help
Question though: can you check if the join is returning the names in the same order as the order selected or in reverse order? just curious
They are showing in the order of selection.
Also, do you have any idea why cases that have been reassigned or archived still show up on the user’s device? I have experienced this issue multiple times while trying to clean test data before real data collection starts.
that’s interesting but it looks like you’re working off of the emulator
trying clearing user data and syncing
that might solve the problem. I’ve had issues like that with lookup tables before but it might be similar.