3 Replies Latest reply on Jan 8, 2015 6:55 AM by philmodjunk

    Filtered dropdown list from related table

    Stu412

      Title

      Filtered dropdown list from related table

      Post

      Hi there

      I'm looking to create three dropdown lists which will display staff members depending on their work role, such as manager, senior, driver.

      I have many customers and many staff, therefore a join table exists between my customers table (where I want the dropdown list to be) and the staff list table.

      The staff list table is simple enough, consisting of name and type (manager, senior, driver etc).  The staffrelationships table is customerIDFK, staffIDFK, staffname (calculation from staff table)

      On the layout for client, I need three dropdowns, one for manager, one for senior, one for driver.  Each dropdown should filter, based on the detail in the staff table, for only one particular type of staff.

      I've been through lots of forums and I'm struggling to get something I can work from.  I can generate a list of ALL staff, that's not a problem!  Staff filtered by work role is something I am stuck with.

       

      Thanks in advance

        • 1. Re: Filtered dropdown list from related table
          philmodjunk

          I'm not sure that i see how 3 different fields for three different staff members fit with the use of the join table to link staff to clients. Are these three separate fields in the Clients table? If so, why do you need the join table?

          There are two different ways to get your value lists of role specific staff. One method uses a relationship between client and a new occurrence of the staff table with calculation fields defined in Client that match to just one Job role in staff. Another method would use calculation fields defined in Staff as the source of values for your value list (no additional value list needed in this case.)

          These are both referred to as "hard wired conditional value lists" in "Adventures in FileMaking #1 - Conditional Value lists" You can find working examples with detailed explanations for both types of hardwired conditional value lists in this file.

          • 2. Re: Filtered dropdown list from related table
            Stu412

            Hi Phil

            Thanks for this, most useful, just picking up on this again now.

            What I have and need is all my staff on one table:

            Staff ID | Name | Role

            I've related this to the Customer table via a join table:

            Customer ID | Staff ID

            The roles are Senior, Manager, Assistant and every customer will have one of each.  For data integrity I will use the dropdown method to enter this information against a customer.

            I think my options are limited to a portal because you can filter that.  As far as I know, I cannot filter a standard field which would serve as a dropdown.

            If that's true, I'd need a Senior portal, Manager portal and an Assistant portal.

            I'm struggling to get this to work insofar as I want:

                   
            • A role filtered portal;
            •      
            • With a dropdown;
            •      
            • Showing a pre-populated list of staff from the main staff table;
            •      
            • One of which (for that particular portal) will be assigned to the customer

            Sorry if this sounds dense!

            Thanks in advance

             

            • 3. Re: Filtered dropdown list from related table
              philmodjunk

              What I have and need is all my staff on one table:

              Nothing that I have recommended has assumed anything different.

              Did you look at the examples of "hard wired" conditional value lists in the demo? They produce exactly what you describe. A portal could be used as the selection tool, That's a method explored in a number of examples in "Adventures in FileMaking #2 - Enhanced Value Selection" But I do not see the need for a portal just to limit the list of choices to one category.

              Nor do I see an absolute need for a join table given the fixed number of contacts that you have to select in each case, but a join table will do the job and may offer some flexibility for some things you may need to do with this data. The alternative is simply to have three fields in your customer table, one for type of staff.

              Using a join table, you can use a basic conditional value list where you select the Role in Field 1 and then the drop down list for staff in Field 2 will filter to just staff members of that role.