Interfaces: create a record in a table drawing on data from two source tables

[James_Donaldson]

I suspect this is not yet possible:

I have three tables:

• one holding the details of 1000 students,
• a second holding details of 120 courses,
• and a third recording each placement of a linked student in a linked course.
  (The third is necessary because we need to be able to record and report on each student's academic record over 5+ years)

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.

• Is there any way to apply a filter that limits selection from linked fields in a new record?

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.

• Is there any way to create an automation that would create a new record, drawing on data from two record pickers?

 Does anyone have a better way to do this?

[Kaytee]

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. 

[TheTimeSavingCo]

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!

[James_Donaldson]

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?

James

[James_Donaldson]

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?

[TheTimeSavingCo]

Hm, so you've added lookup fields to your "Placements" table, but you can't add lookups to those lookups in the "Rollup" table?