I’m using foundset filters in a double-list type display.
Suppose table 1 (upper) is for teachers and table 2 (lower) is for teaching assignments. A particular user is restricted to seeing only selected teachers and teacher-related items:
success_B = forms.teacher_browse.foundset.addFoundSetFilterParam('teach_teacher_id', "IN", globals.aAllowedTeacherIDs, 'teacher_filter);
success_B = forms.assignment_browse.foundset.addFoundSetFilterParam('assign_teacher_id', "IN", globals.aAllowedTeacherIDs, 'assign_filter');
The problem comes in when the user clicks on one or more teachers in the upper list, and the program joins to display the corresponding assignments in the lower list.
If I leave the filters in place, then after the join to display assignments, the program must join back from the assignments to the teachers to perform the filter, correct? So I turn off the filters during the join to display the assignments… But then don’t I have to loadAllRecords() or clear(), which disturbs the selection?
How can I turn filters off and on before/after the join without disturbing the displayed selection?
Thank you,
Don
I’m probably missing something here but don’t you just need a filter (per user) on the teachers? And if so doesn’t that mean that that filter is permanent for that user? In other words a user is restricted to just seeing a particular group of teachers AND any teaching assignments belonging to those teachers. So if the teacher list is already restricted then the assignments will also be automatically filtered based on the teacher filter and the relationship between teacher and assignments.
John
Generally you are right. But because of the dual browse list display, they can search assignments independently of the teachers, directly from the browse list. The program displays a context-sensitive search dialog, with drop-downs for the field names.
If, for example, they want to find all assignments that are active on a certain day, after the query completes the program would need to filter based on the allowed teachers for those assignments. So a filter on teachers alone would not be sufficient, I also need a filter on assignments. Is that correct?
I think (but am not sure) I have figured it out. I don’t need to remove and then re-apply the filters, because all that is going on with the filters is that the program looking at a list of foreign keys (teach_teacher_id) in the array…it’s not doing any joining.
Things are still not fully working.
Thank you,
Don
I think I get it but let me give an example here to see if that corresponds. I believe you have a filter on teachers per user. Those teachers have related records in teacher_assignments. The user might search by teacher or might search by assignments but in either case what is shown should be limited to teachers who are in the filter.
Here I have distinct disease groups sharing the same database. When someone in ‘kidney’ opens up the solution they only see ‘kidney’ patients not bladder for example. If they search in the patient list they will only find patients with kidney disease. All patients also have courses of treatment that of course relate back to the patient (one patient can have many courses of treatment). Instead of searching the patient list, the user can instead search the courses of treatment list. There are many courses of treatment that are the same for bladder and kidney and with no filter both types of patients would show up in the search for common courses of treatment. But because of the FilterParam (and the relationship), even though the user searched for courses of treatments that have related bladder patients, the list shown will be limited to kidney patients with those courses because of that filter.
Hi John,
Yes, I think that is similar. So in your case, you are applying separate, kidney filters to each browse where the user could perform these searches?
Course of treatment list –
success_B = forms.treatment_browse.foundset.addFoundSetFilterParam('treatment_type_id', "=",'[kidney_id]', 'treatment_rstrs');
Patient list –
tempText = "SELECT patient.patient_id FROM patient WHERE patient.patient_id IN (SELECT treatment.patient_id FROM treatment WHERE treatment.treatment_type_id = '[kidney_id]')";
success_B = forms.patient_browse.foundset.addFoundSetFilterParam('patient_id', "IN", tempText, 'patient_rstrs');
Thank you,
Don
Hi Don,
In fact our schema is completely hierarchical. No records are created in any tables until there is a parent record in the ‘patient’ table thus all records in other tables are children, grandchildren, great grandchildren, etc. to the patient table. This means that in fact we only need a ‘filter id’ for each record in the patient table. So even if I am searching in one of the related tables, the filter on the patient table alone always comes into play and I only see patients for the disease group that I am looking at. So we only have to have that disease_group_id on the patient table. In fact I end up using it in other places and with views mainly to use ‘related valuelists’ and what not. But in terms of searching we only need that one top patient table to have the filter. In SQL terms there is ALWAYS a ‘FROM PATIENT’ and always a 'WHERE PATIENT.DISEASE_GRP_ID = ?"
In your case I can see that that might be more complicated as it seems you can be doing a selection from multiple angles: teachers can have multiple classes, teaching assignments, students; assignments can have multiple teachers; students can have multiple classes, teachers, etc. I’m sure I don’t understand all the various permutations but essentially you’ll have multiple many-to-many relationships. But in simplest terms, and I don’t know whether this model would suit what you want to do, as long as the parent form has a filter on it, any searches on related ‘child’ data will automatically have that filter applied and you don’t have to worry about the child data. So going back to your original example, if you are on the teacher_browse form and you have a teacher-assignment relationship (or perhaps a join table in between to handle many teachers to many assignments?) and you have a related tabless tabpanel for those assignments, my thinking would be that you wouldn’t need the ‘assign_filter’ (which just contains the same teacher IDs as ‘teacher_filter’). The teacher_filter would automatically restrict what was shown through the allowed TeacherIDs and the relationship teach_teacher_id=assign_teacher_id (I’m guessing that that is what the relationship is). On the other hand if it were the other way around (assignments are the parent form and teachers are the sub-form/related tabless tabpanel) then that assignment filter would just be applied to the assignments and only those allowed teachers would show up regardless of whether you are searching in assignments or in the related teacher form.
My curiosity gets the better of me… What is the overall goal/purpose of your application? As I said I might really be missing something basic here! And partly I’m hesitant because I don’t quite understand what overall goal is. Sounds fun though! ![:)]( "Smile")
Hi John,
So your “other” lists are done through related tab panels, so that a list of treatments always appears in a related tab panel from a student form?
Suppose your application had a slightly different interface, where the user could directly access the list of treatments. In a tab panel that is on a form with no data source. Would the single filter on the patients still restrict the user to a selection of patients?
Regarding the application itself, I’m trying to convert an application to Servoy that I wrote in 1999. It’s used by counties to plan and to review services provided to certain types of students. Records are submitted to the state in return for reimbursements.
Thank you,
Don
So your “other” lists are done through related tab panels, so that a list of treatments always appears in a related tab panel from a student form?
Correct although with us of course it is not a ‘student’ form but a patient form. ![:)]( "Smile")
Suppose your application had a slightly different interface, where the user could directly access the list of treatments. In a tab panel that is on a form with no data source. Would the single filter on the patients still restrict the user to a selection of patients?
Don’t know the answer directly from experience because with our hierarchical structure that never comes up since everything hangs off of patient. But thinking about it not quite sure why you would have a form for this with no data source. Wouldn’t it make sense to have one of your tables be the data source whatever it might be: teachers, students, assignments, classes…? If that’s the case (i.e. there’s a relationship - inner join - that Servoy knows and uses) then the filter will always work for both parent and child regardless of which table has the filter, wouldn’t it? (In other words any time the table with the filter is queried there will always be a ‘where’ clause on the related tables whichever direction the join goes, limiting the result to joined records having that ID).
If you want it to be dynamic where the data source can be determined at run time then I suppose you could use the solution model to achieve that as well, just bringing in the filters and relationships at the time…
Hi John,
Certain categories of users perform batch operations. For example, a district user might advance the grade level for a selection of students at the beginning of a new year; that selection might be based upon attendance at a particular school, or taking classes currently under a particular teacher/provider. The user might also drop/continue a selection of student services, or reassign them to a new teacher.
The relations are somewhat fluid, depending upon what needs to be done, and can be more than one table away…they don’t seem to lend themselves to related tab panels.
So far it appears that assigning a filter to each displayed browse list works as expected.
Thanks for your feedback,
Don