Multi select from case db

Developers
1

Hello Everyone,

I am trying to produce a training attendance form that pulls a list of enrolled farmers as a checkbox option so that after a training class, the farmers present can just be ticked off. The problems are:

  1. If i create this form as a follow up form, the user will have to select a farmer before they can proceed to filling the form because of the case list item and if i create the form as a registration form then it creates a new case after the training attendance has been filled which then shows up as case ids.

2.) When i pull up a report for the training attendance, rather than see farmer names, what shows up are the case ids.

Kindly assist.

2

Hi Jay

I have an application that does something similar to what you describe. in fact, it does exactly what you describe.

here's how to resolve your issue: make the distinction between a form export and a case export.

your attendance records cannot be associated with a farmer. if you wish to have an attendance record to be associated with a farmer, you will have to record attendance for each farmer separately. and i imagine that that's a very long process with tens of farmers in attendance at a time if not more.

first: let's resolve the issue with the farmer name vs. case id. you can pull the farmer's name in a VLookup or merge query in excel after you download the data. but let's say that you wish you have that name show up in the form or in the attendance records as names instead of id's. in order to do that, you will have to query the case db with an aggregate function such as "concat" or "join". i recommend "Join" as it allows you to pad the nodes it finds with a space character or anything you determine. you can find the documentation here CommCare Functions - CommCare Public - CommCare Public

but how does that help? Ok. so, just as you were able to determine which cases to show up in your advanced multi-select, you can use a similar query to feed the join function. let's imagine you want to show up the names of all the farmers (without excluding anyone) in one hidden calculation. you would have to do something like join( casedb path to name field, " ") which will return all the names in your case db, separated by a space.
in your scenario, you want to point to the name property in casedb, with the exclusion of farmers you're not interested in. that means adding a filter to the casedb path where the cases are farmers, and whose caseID's are selected in the form question with all the farmer's case id's in attendance.

so theoretically, it's possible to show you the names of all the farmers that are in attendance as you export the attendance data, but in reality it's a little complex, and can actually slow down your forms if you have a lot of farmers.

so what's the alternative you ask? there is none for your specific application. I've done something similar in the past where i get the details of a case that was selected in an advanced multi-select through a hidden calculation. but that is not ideal when you're selecting multiple cases as it's difficult to guarantee that the order of values will be the same or correct. if your ID's are ordered as case1, case2, case3, you might for some reason get the names as name2, name1, name3. you CAN however set the ordering field in both places i believe.

so, if you're after the functionality to be working on the application side, and willing to do a little extra effort outside commcare, you're most likely to have success. Keep things the way they are, but work outside of commcare for reporting.

what we do on our side is slightly different from what you're describing.

i have activities that are defined as cases. and i enroll participants in activities. when the time comes for attendance tracking, i select the activity case first, and inside the attendance form i show only the participants that have been enrolled in this activity as checkbox options. i then save that list as another property i call "last attended" and i use that as a default value in the form. what this does is basically make the participants that attended the last session show up as pre-selected on the subsequent sessions to make the trainer's lives easier. they might have to press on each farmer the first time they attend, but since they are likely to be the same people that show up for the second and subsequent sessions, they're already pre-selected. the user just has to mark the ones that are no longer there, or are attending for the first time.

when it comes to finding out who attended what activities, how many times, and when, i 100% rely on reporting outside of CommCare. I use a combination of case data and form exports. i have the case data for the activities and the participants to get details such as their names, age, where the activity was supposed to happen, who the facilitator is supposed to be, and a bunch of other information. but when it comes to finding out who attended which session, i download the form data for the attendance form and then i reformat it in a way that allows me to build the relationship between these objects. once i've done that, it's fairly straight forward to find out those statistics.

you can reach out if you would like to discuss what we've done a little further.

Mazz

3

Hi Mazz,

Thanks so much for your explanation. I think i have a clearer picture of how you have implemented your application especially since i have had projects with similar structures but i am gradually moving them away into a one-to-one type structure for client training(on a project that has a reduced number of clients). In that scenario, i am creating the activities as cases too but you have definitely taken it a step further i think.

For my present issues, the walk around i just implemented for issue 1 is to force the form to be a registration form that does not create cases except a condition is met. For option 2, still struggling to find a way to make my column reference the right field in the farmer casedb. I have had this issue before but honestly cannot recall how i resolved it.

4

No problem at all

have you tried making the form as a survey? that doesn't require you to specify a case for it, or select one. however you are not going to be updating any cases with any information and the only way to get this data is through a form download on CommCareHQ via any of the available methods.

as for pulling a property of a case that was selected in a multi-select in a form, i'm using this code in one of my apps
concat(instance('casedb')/casedb/case[selected(#form/enroll/session_enrolled, @case_id)]/start_time)
i'm basically using the concat function to concat 1 node. so, it's like using the "=" operator in affect. the reason i'm using it is in order to capture any potential errors that the expression might throw. i would probably return a blank in that case instead of throwing an error. using an "=" operator can often confuse CommCare, even if it is only looking at one node.
what this code does is bring back the start time of session that participants were enrolling in and then show it to the user on their screen. the reason this code returns what i expect it to is because i'm leveraging the "selected" function. it's going to return a false if the "@case" its evaluating from casedb is not equal to the session_enrolled which is also a case ID. but it comes from the form, not from the database.

in your scenario, in place of "session_enrolled" you will have to replace it with the form element that contains the "farmers" that you were talking about. the same expression should work. however, it'll just spit the names out next to each other. replace "Concat" with "Join" and add a
,","
before the ) sign at the end. this will create a comma separated list of names that even if not in the same order as selected in the form, at least contains all the names of the individuals that were in attendance.

I just personally think that it's a lot of processing load to put on a phone, for a result that you will likely use an actual computer to get any value out of :slight_smile:

Mazz