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 https://confluence.dimagi.com/display/commcarepublic/CommCare+Functions#CommCareFunctions-concat
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.