4 Replies Latest reply on Jun 3, 2016 8:56 AM by flamecoder

    Filter List


      I need some help in figuring out a better approach to a problem of filtering a list from a table.


      I have 3 tables:





      On the job table record I want to select a department then select a person but filter names to show only people from that department.

      I can get this to work BUT foresee this causing problems down the road.


      For example if John Doe was in Creative Service Department when the record was created now he moved to Engineering so we change his department group in the Person table. When you go back to records once assigned to him I see a primary key displayed because the filter will only show people related to that department.


      See my attached simple db I created to replicate this problem.

      Any ideas of how I can go about this?

        • 1. Re: Filter List

          In our April FMPug meeting, we had a preso about the advantages and disadvantages of various selection methods, including the challenge you're describing regarding pop-up menus showing only second value. You might find this a help in exploring your options. You can find the recording here: FMPUG | Extensitech


          The short and direct method, though, might be to show a field, enterable in find only, that is the related record's description, but have another field beneath it, enterable in browse only, that does what your field does now.




          Chris Cain


          • 2. Re: Filter List



            You can do it this way.

            • 3. Re: Filter List

              Hi flamecoder,


              Karina did a good job sorting out the relationships and showing the road to go.


              What I eventually found most puzzling is that the field Assigned To Person in the job layout actually shows the name of the person, whereas it is not supposed to. The value list on that field is set up to show only the value of the second field. The field keeps showing that  after one selects a person. According to the specs on such value lists, this should not happen: "The field displays the value from the first field. When you click in the field, the drop-down list displays values from the second field. After a selection, the list again displays the value from the first field when the field is exited." (quote from the help of fmp 13) This why Karina (in the JOB layout) uses two fields for Assigned to Department and Assigned to Person: one to make a selection and one to show the name.


              In your example file, when you see only the key the field in the JobID 2 record, the field does what it should do. I think this random behaviour is related to the fact that the value list is set up to show only values seen from Job Occurrance whereas it is being used in a layout based on the Job table occurrence. In my experience such a use gives weird results in the value lists.





              • 4. Re: Filter List

                I spent some time studying Karina's table relationships. One issue I found is that if someone's name changes in the PERSON table the records are not updated. People are not going to figure out to search for someone's job by current name and also maiden name, for example. If I instead avoid the Assigned To Person field in the JOB table and use the field job Person::Person Name these changes are seen when I update a name in the PERSON table.


                Thanks for the input! I'm going to try this approach in my db.