Apr 03, 2023 02:19 AM
I suspect this is not yet possible:
I have three tables:
Matching a student with a course is complex - there are about 10+ variables (age, interest, prior knowledge, day and time availability etc) so I need to filter both courses and students.
I've tried using a list view of the placements table and adding a new record, but unless I can apply a filter to the linked student and course fields, matching a class with a student is hit-and-miss.
I've also tried using a filter connected to a record picker. Let's call that a filterpicker, for simplicity. I have one filterpicker for the students, and a second for the courses.
Does anyone have a better way to do this?
Apr 03, 2023 10:13 AM
If I understand what you are asking correctly.
On your student table, I would link to the course table (allow for linking to more than one record), creating a look up field of each column you would like to filter by.
Apr 04, 2023 09:02 AM
Hmm, what if you:
1. Create a single record in another table called "Rollup" or something
2. Link all of the Course records to that single record
3. In the "Placements" table, create a new linked field to the "Students" table and then create lookup fields to display the fields from that new linked field to "Students" that you're going to filter by
4. In the "Rollup" table, create lookup fields to display those fields from step 3
5. In the "Courses" table, create lookup fields to display those fields from step 4
6. In the "Courses" table, create a formula field to see if they match the lookup fields from step 5
7. In the "Courses" table, create a view that will filter based off of the field from step 6
8. In the "Placements" table, modify your linked field to "Courses" to display from the view from step 7
The workflow would then be to link a single Student record in the field mentioned in step 3 in that Placements record, causing the student's filter data to display in the "Placements" table, then the "Rollup" table, then the "Courses" table in turn. The formula field would then run the filter and then populate the view only with courses that matched that student's criteria
You'd have to make sure you cleared the linked Student from that field mentioned in step 3 as well
The above is also doable via an Interface, but you'd need to trigger an automation run for it
There's probably a simpler way to do it and hopefully someone has a better idea!
Apr 09, 2023 08:37 PM
Thanks for your help Adam and Kaytee,
Unfortunately neither worked for me - Adam I think yours might possibly be on the right track, but in your step 4 I couldn't lookup fields from the Placements table. Perhaps I've missed something obvious?
Apr 09, 2023 09:08 PM
Setting aside the filtering aspect, for now, another way to do the record-keeping we need might be to use an automation to create a new record in the 'placements' table whenever a student is added or removed from a 'students' field in Courses to record the change. Is that possible?
Apr 10, 2023 02:13 AM
Hm, so you've added lookup fields to your "Placements" table, but you can't add lookups to those lookups in the "Rollup" table?