2 Replies Latest reply on Aug 11, 2016 9:33 AM by pademo57

    Conditional Value List - Location with sub-location

    pademo57

      I know there are lots of these questions but I can't seem to make mine work and would like some help.

       

      I have a WorkLoc table which shows the Full Name, address, as well as a primary key __pk_WorkLocID and a foreign key _fk_DeptID, as well as DepartmentName

       

      I have a second table I call "Dept" which has fields __pk_DeptID, DeptName, DeptFloor, DeptLocation, etc.

       

      I tried having a relationship between WorkLoc::_fk_DeptID and Dept::__pk_DeptID

      I then added a second condition AND WorkLoc::DepartmentName and Dept::DeptName

       

      When I add a field (WorkLoc::DeptLocation onto the WorkLoc layout to try and only show the Departments that are associated with the WorkLoc

      by making a value list which says only include values from WorkLoc it doesn't work: it doesn't show any values.

       

      If I use include all values then it shows all the departments and not just the ones for the WorkLoc.

       

      Could someone please walk me through a way to make this work please?

       

      pademo57

        • 1. Re: Conditional Value List - Location with sub-location
          philmodjunk

          I tried having a relationship between WorkLoc::_fk_DeptID and Dept::__pk_DeptID

          I then added a second condition AND WorkLoc::DepartmentName and Dept::DeptName

          You shouldn't need that second pair of fields. If __pk_DeptID is truly a unique identifier, there's no need to match by department name nor is there any reason that I can see to have a departmentname in your WorkLoc table.

           

          When I add a field (WorkLoc::DeptLocation onto the WorkLoc layout to try and only show the Departments that are associated with the WorkLoc

          By the naming conventions used, there would and could only be ONE department associated with any given WorkLoc but you could have many WorkLocs associated with a particular department. So that suggests that you need a relationship that either matches one WorkLoc to many Departments:

          WorkLoc::__pkWorkLocID = Dept::_fkWorkLocID

           

          or you need a relationship that matches many WorkLoc records to many Department Records, which would require the user of either a Join table (best approach) or a multi-value key.

           

          I'm not 100% sure that you should even be using a value list for this. A portal appears the better option, but if you'd like to take a look at a file that has many working examples of conditional value lists with detailed and extensive documentation intended to teach people how they work, see this file in my Drop Box:

           

          Adventures in FileMaking #1 - Conditional Value Lists

          1 of 1 people found this helpful
          • 2. Re: Conditional Value List - Location with sub-location
            pademo57

            Thanks philmodjunk yet again for your help.

             

            AND yet again I've tried to overcomplicate things and as you pointed out I didn't need the uneccessary added relationships. Works like a charm now.

             

            I also really liked your "Adventures in Filemaking #1", it was and is very helpful.

             

            pademo57