10 Replies Latest reply on Aug 12, 2016 6:52 AM by philipHPG

    Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes

    adampwsmith

      Let's say, hypothetically, that I have a database of people. And for each of those people I have records in related tables - addresses, companies they work with, roles they've played, etc.

       

      And say I decided I wanted to divide those people up into two, broad categories - people I knew to be aliens in disguise, and those for whom their planet of origin is probably earth. (And just to be clear, I'm using this as an example - I couldn't give a tinker's cuss about extraterrestrial life.) So I have a table called Person and for each record there is a field called IsActuallyAnAlien  that is either empty or contains the word Yes.

       

      I want to be able to populate value lists with either just aliens, just non-aliens, or everyone in the database. I'd like to be able to have layouts based on those collections, and/or display a pop up menu of aliens, non-aliens, or everyone.

       

      The Person record includes a pk_PersonID as the key. It also has the IsActuallyAnAlien field which is either Yes or empty. And it has a global field called IsYes which is the word Yes.

       

      In my relationship diagram I have the Person table connected to various other tables, using the pk_PersonID field as the key.

      If I create layouts and use "Go To Related Record" this all works fine. It shows all the Person records and everybody is happy.

       

      But I wanted to be able to work with just one type of Person or the other, and things got difficult.

       

      I created three instances of the Person table - PersonHuman, PersonAlien, and PersonAll.

       

      PersonHuman does a match on the pk_PersonID field using a cartesian join (X) and then matches records where IsActuallyAnAlien = ""

      PersonAlien does a match on the pk_PersonID field using a cartesian join (X) and then matches records where IsActuallyAnAlien = "Yes"

      PersonAll just does a match on the pk_PersonID field using a cartesian join (X)

       

      I would have assumed that I could work with the PersonHuman table and only see the humans, PersonAlien and only see the aliens, etc. What I get is less easy to understand.

       

      If I create a layout using the original Person table and put a portal on it linked to one of the three instances, it displays correctly. A portal linked to the records in PersonAlien shows just the aliens, PersonHuman shows just the humans, and PersonAll shows everyone. This led me to the clearly foolish assumption that those filtered tables contained just the records I want.

       

      But if I create a value list using one of those filtered tables, I get ALL the records. And if I create a layout using one of those filtered tables, I get ALL the records.

       

      I can't find a way to show only the aliens, only the humans, or all the persons in a pop up menu. Or work with only those records in a layout. I know I can do a "find" to narrow down a layout, but the popup has me stymied. I've spent two days and tried at least a dozen different approaches.

       

      So the question is twofold - what am I misunderstanding about filtered tables, and how do I make a pop up list that shows just one of those groups of records?

       

      (Oh, and I'm using the pop up to select the index field while displaying the person's name, in case that's relevant)

        • 1. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
          philipHPG

          Hi, Adam,

           

          Thanks for such a clear explanation of your situation.

           

          The main thing to point out is that table occurrences occur in context. The relationships you defined determine the relationship between Person and PersonHuman, PersonAlien and PersonAll. The table occurrences by themselves provide full access to the Person table (as you discovered). It is only when the table occurrences are used in a relationship that the restrictions come into play.

           

          So for the value list, set them up using the PersonHuman and PersonAlien table occurrences, but then select Include only related values starting from: Person. (Just use the Person table for the PersonAll value list.)

          • 2. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
            philmodjunk

            When it comes to value lists, what you describe is called a conditional value list.

             

            If you set up your value list to list values from fields in the PersonAlien table occurrence and then select "Show only related values starting from Human, you should get a value list of just the aliens.

             

            That's the most basic type of value list that you can set up in FileMaker. There are quite a few other ways to set up conditional value lists, however. And the "hardwired" version might be useful to you in the above situation.

             

            Here's a demo file that contains many different types of conditional value list examples with very extensive documentation explaining how they were set up and how they work:

             

            Adventures in FileMaking #1 - Conditional Value Lists

            • 3. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
              adampwsmith

              "So for the value list, set them up using the PersonHuman and PersonAlien table occurrences, but then select Include only related values starting from: Person. (Just use the Person table for the PersonAll value list.)"

              No luck I'm afraid.


              I have a layout called PersonLayout that uses the Person table.

               

              On that layout I have a field that is defined as a pop up menu.

               

              The pop up menu uses a value list called PersonHumanList

              The list is set to use values from the PersonHuman table - the pk_PersonID as primary and PersonName as secondary

              The list is set to display only the second value

              The list is set to include only related values from the Person table.

               

              When I load it all up the pop up shows "No values defined"

              • 4. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
                adampwsmith
                "If you set up your value list to list values from fields in the PersonAlien table occurrence and then select Show only related values starting from Human"

                 

                I tried conditional value lists but didn't get anywhere.

                 

                I have nothing called "Human" anywhere. Can you clarify what you mean by this?

                 

                Do you mean I should get my values from PersonAlien table and then "Show only related values" from the PersonHuman table? That sounds completely counterintuitive.

                 

                (Yep, just tried it an I got "No value defined" in the popup)

                • 5. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
                  philmodjunk

                  Please look at the example file as it goes into far more detail while providing working examples.

                   

                  I meant whatever table is on the other side of your relationship. But I see that I also misunderstood your original description as I now notice that you said "cartesian join". A filtered portal isn't a filtered relationship so a relationship that matches to all records regardless of values in the match field won't work for the purpose of a conditional value list.

                   

                  What you'd need for the standard conditional value list is a relationship that matches only to the records you want to serve as a source of values such as:

                   

                  MainTable::ConstYes = PersonAlien::IsActuallyAnAlien

                   

                  Then, to restate my original description in this context, you'd display values from PersonAlien and specify "MainTable" as the "starting from" table.

                   

                  ConstYes would be a field that always contains the text "Yes" such as you might set up with a calculation field. This is one version of a "hardwired" conditional value list. The demo file describes two methods.

                   

                  Note that MainTable can be an occurrence of any table in your database but in most cases should be the table occurrence you set up as the basis for your layout.

                   

                  if You changed ConstYes into a global text field, on the other hand, you could make your conditional value list one where you can select to see either aliens or humans in your list. but you'd need to change the data in the IsActuallyAnAlient field to have values of "Yes" or "No" rather than "yes" or <blank> as you originally described.

                  1 of 1 people found this helpful
                  • 6. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
                    adampwsmith
                    I meant whatever table is on the other side of your relationship. But I see that I also misunderstood your original description as I now notice that you said "cartesian join". A filtered portal isn't a filtered relationship so a relationship that matches to all records regardless of values in the match field won't work for the purpose of a conditional value list.

                    I didn't filter the portal. When I start with a Person layout and put a portal on it that simply connects to one of the related instances of the Person table, I only get the values from that table. (I suppose I shouldn't have called them filtered tables, sorry if I caused confusion).

                     

                    What you'd need for the standard conditional value list is a relationship that matches only to the records you want to serve as a source of values such as:

                     

                    MainTable::ConstYes = PersonAlien::IsActuallyAnAlien

                    That's already there in my original description. I have a global field (IsYes) with the value Yes, which I'm using to match against the IsActuallyAnAlien field.

                     

                    you'd need to change the data in the IsActuallyAnAlient field to have values of "Yes" or "No" rather than "yes" or <blank> as you originally described.

                    I'll try filling all the empty fields with "No" and see if that makes any difference. Seems odd that it would - I'm just using the filed as a boolean value. (Why is there no built-in boolean field type in Filemaker?)

                     

                    Oh, and I had a look at your example file. There's a helluva lot of stuff to dig through. I think I tried all the things it suggested - including the calculation fields - but none of it worked.

                    • 7. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
                      philmodjunk

                      When I review your relationships, I don't quite see why you are using a cartesian join at all.

                       

                      Here's a simple test that's gotten others over the conceptual "hump" involved:

                       

                      On one layout place a non-filtered (no filter set up in portal setup) portal to the table that has the values you want to appear in your value list. If you can correctly get the records you want to appear in a non-filtered portal (meaning only the relationship matching is controlling what records appear), then your conditional value list should specify fields from the portal's table occurrence and then the "include only related .. starting from" option should specify your layout's table occurrence.

                       

                      And make sure that whatever field you select as the first (left side) field in your value list setup is an indexed field. Global or unstored calculation fields won't work as that field. This may also be a requirement for the second field if you select the "show only value from second field" or "sort on second field".

                      • 8. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
                        keywords

                        For what it's worth, I prefer to set up boolean fields (Yes/No–Blank) such as your IsActuallyAnAlien as number fields using 1 or 0 (or empty), rather than text fields. (NOTE: you can still set them to display as Yes or No if you want to for aesthetic reasons.) I also usually create a Constant field containing 1 (I usually call it @one and it is a calc with the fixed formula 1)—this field can then be used for many different purposes, including what you are trying to achieve. This method:

                        1.     removes the need for quotation marks in formulas and relationships (field = 1, instead of field = "yes")

                        2.     I suspect is a simpler (and perhaps therefore faster) process for FM, as it is mathematical, not text evaluation

                        3.     eliminates any possibility of mistyping messing with the evaluation—even a rogue space (" yes" or "yes " instead of "yes") will cause a text match to fall over

                        4.     removes any possibility of empty field and "no" being evaluated differently, as mathematically 0 and empty are treated the same.

                        • 9. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
                          adampwsmith
                          For what it's worth, I prefer to set up boolean fields (Yes/No–Blank) such as your IsActuallyAnAlien as number fields using 1 or 0 (or empty), rather than text fields.

                           

                          You're absolutely right. I've done this in the past but for some reason I slipped away when working on this project. It probably had something to do with making formulas that were quick and easy to read but I should go back to using 1 and 0.

                          • 10. Re: Filtering the contents of a popup menu - and why filtered tables don't seem to filter sometimes
                            philipHPG

                            To make sure I'm not crazy or missing something obvious, I re-created the system you described: with the Person table with three table occurrences: Person, PersonHuman and PersonAlien. I created the IsActuallyAnAlien and IsYes global field in the table (along with the pk_PersonID and a Name field). I created the relationships between Person and PersonAlien ( Person::IsYes = PersonAlien::IsActuallyAnAlien ) and Person and PersonHuman ( Person::IsYes ≠ PersonHuman::IsActuallyAnAlien ). [The cartesian join is not necessary in those relationships, although I tested it with the cartesian join and it didn't cause any problems.] I then created the value lists based on those relationships, including only related values starting from Person (for the Aliens and Humans value list). And it all works as I would expect. So there is something more going on here.

                             

                            Have you verified that the field types are the same for the IsYes and IsActuallyAnAlien fields?.