6 Replies Latest reply on Jan 13, 2016 3:02 AM by beverly

    This must be simple

    MarkPage_1

      I have a table with two related tables. One is for Contact_ID and the other is Employer_ID.

       

      Each has a field with either active or inactive in it.

       

      In my main table I want to select (using a drop down box) from the contacts who are only 'Active'.

       

      I'm a bit baffled by this seemingly simple desire. Can anyone help, or point me in the right direction?

       

      I feel like such a numpty...

       

      Cheers

       

      Mark

        • 1. Re: This must be simple
          beverly

          1. what is your "main table" (name)? actually what are the names of all your tables?

          2. what type of field has "active or inactive"? and in what table?

           

          If you have a relationship, you can "show only related records" in a valuelist. But you'll need to have that relationship defined, to make this work. There needs to be a relationship to Contacts with this field and a similar field (global perhaps) in the Employer table (if that's what they are named).

           

          If you provide a screen shot of this relationship, the answer is probably 'simple'

           

          beverly

          • 2. Re: This must be simple
            planteg

            Hi Mark,

             

            in the Specify Fields for Value list.., window, at the bottom you see this:

            only related.jpg

            Create a relationship between Contact::Active and another table where a global field is set to True. Then Select the highlighted option shown in the screen capture and select the global field.

             

            Don't feel numpty, most people getting acquainted with Filemaker bang their head on this one .

            • 3. Re: This must be simple
              MarkPage_1

              Hi Beverly,

               

              Here's the screenshot.

               

              The two table is 'contacts', and the 'status' field in both of them has either active or inactive. I merely want a drop down box to show the active contacts, so I can choose from them when I create a new invoice.

               

              Screenshot 2016-01-12 17.10.44.png

               

              I hope this helps.

               

              Cheers

               

              Mark

              • 4. Re: This must be simple
                MarkPage_1

                Beverly,

                 

                Sorry, I may have caused confusion. I have the same problem with both the tables, but they're not related. I just want one drop down box for contacts, and a similar one for employers.

                 

                Cheers

                 

                Mark

                • 5. Re: This must be simple
                  johnbuckingham

                  Hi Mark.

                   

                  I am sure there are many ways to achieve what you want; some more complex than others. The way I would approach it is probably very 'old school': Every record in every table I create has a number field that is autofilled with number 1 - fwiw I call the field '_z1'. So, in solving the problem I believe you describe, I would also ensure that an 'Active' indicator field contains '1' for active and not '1' for not active AND the field would be indexed! Then I would create a relationship that linked between the _z1 field in the Employers table and the active status value in the Contacts table - maybe with other joins as well - and use that relationship to build the value list.

                  • 6. Re: This must be simple
                    beverly

                    You need a relationship to get the "active" only to show in your value list, as planteg shows in the dialog.