Filter Multi-Select from another Table

What is the relationship between Talent and Shoots? It sounds like many-to-many which means you need an intermediary table, let's call it "Talent at Shoots". So users would have to save a record for Shoot and then add Talent at Shoots records as children.

Another option is to create multiple parallel one-to-many relationships between Shoots and Talent. One relationship for each of photographer, stylist, etc.

With either option, you can create a report in Talent that filters to just records with a value of "photographer". Then set this report as the record picker for when users are selecting the photographer on either the Shoot or Talent at Shoot form. You'll make a second report in Talent that filters to values of "stylist", and so on.

Thanks Adam. You're right, Talent and Shoots is a many-to-many relationship. I have an intermediary table now called Talent Assignment that I'm trying to use for this.

Using Multi-Select Text as a filed type I don't see anywhere the ability to choose the record picker. Is that function limited to certain types of fields?

Still not having any luck with this. The only option I've found that works is to create a table for Photographers, another table for Stylists, another table for Art Directors and so and, and then use these tables as a picker for the dropdown menu.

What I don't like about this method is that I'm going to end up with 10 different tables, one for each of the crew member types. I would prefer a master table that includes them all and for them to simply have what their crew member type is in another field. This will make the app much easier to use.

I'm playing with the idea of pulling all of the records from each of the 10 tables into a separate master table but the problem there is that it's going to be hard to create relationships between so many tables.

Surely there is an easier way of creating a dropdown that says "if CrewType=Photographer, show the key field (ie. photographer's name) in a dropdown menu". It doesn't sound like it's that hard but for some reason this is turning into an ordeal.

This should not be complicated. You want to be using a conditional drop down.

In the Talent Table I assume that each Talent entry Record has a drop down for Talent Type.

Then in the Shoots table, instead of having a field dedicated to photographer, you can have a field called Talent Type 1. So you would choose say Photographer. Then you will choose the Talent Name 1 and on the field for [Related Talent 1] you will look for the box that is about Conditional drop down and make it so after choosing the field Talent Type 1, then limit choices to where the Talent has that Talent Type.

I don't think this is going to work for me. I've set up a conditional dropdown but the problem is that on a form I'm limited to only using it once. I want to be able to add a Shoot record and on the form have fields to include one photographer, two stylists, one art director, one digital tech etc. etc.

To do that I'm going to have to create a vast array of fields inside Talent and Shoots in order to create and link together conditional dropdowns for multiple Talent Name and Talent Type fields for use on a single form. It seems incredibly tedious and hard to manage.

Honestly I think it would be easier to have a table for each of the Talent Types and do it that way. Perhaps I can still have a master Talent table but instead use Actions or Relationships to feed child tables with each Talent Type.

If you want to have a single master table of Talent (people) and fixed slots for each talent type for each shot, then yes you will need a relationship for each slot.  You can still have a Conditional Drop down, but cheat the process by making a formula text field called [Photographer words] with the formula of 

"Photographer"

Include that field on the form in a hidden section an admin only section.

Set the [Related Talent] field (which you can rename to be [Related Photographer] to be conditional on only where the talent type matches the value in he field [photographer words].

Get that drop own working on the form.

Then you can copy the field for [Related Photographer].  Most conveniently, Quick Base will also duplicate that relationship for you.  So you just rename that field to [Related Stylist 1] and carry on to look up the name of he Talent and make that field [Stylist 1 Name] the proxy for [Related Stylist 1].
 
It's not really tedious, it is just that you have never worked with CDDs before, so they are new to you.

Mark