7 Replies Latest reply on May 20, 2010 2:39 AM by LaRetta_1

    filter "all"



      filter "all"




      To restict the number of records shown (in a portal for instance), one can create a self-join and link the second-instance of the table to a global field in the first-instance (the global flield should then contain the selection criterium).


      I want to set the content (and therefore the filter) of this global through a script, after pushing a button.


      Now, what do i set the field-content to when i want to see ALL records in the portal?




        • 1. Re: filter "all"

          You could define a value list using values from the matchfield, and set the global to ValueListItems ( Get (FileName) ; "YourValueList" ).




          "criterium" ?? 

          • 2. Re: filter "all"

            Hi Piet


            I am not an expert on this if I am understanding correctly - I just had this issue last week if you want exact details refer to subject

            'filtering portals by alphabet' 


            Basically you are correct to filter a portal by criteria you create a TO and your global field etc etc


            To display 'all' do another TO but the relation here is going to be X which means display all records- Go from Parent Table id X TO # 2 id


            Then on the layout where you are intending to use this set up tabs and place a portal from each of these TO's on different tabs naming one "All" and the other one by your filer criteria.


            I hope this helps




            • 3. Re: filter "all"

              You're getting some good advice.


              There is another approach that keeps a single portal but works with a more sophisticated field of values to make "All" a viable option.


              If you want to try that, here's how.


              Say you have the following table


              CategoryField | NameField:

              Fruit | Strawberry

              Fruit | Apricot

              Vegetable | Lettuce

              Vegetable | Carrot


              You've already figured out you can set up a global field and match it to CategoryField. Then, entering Fruit displays the first two records while enterig Vegetable displays the second two. But you also want to see all 4 records.


              define a new calculation field that returns text:

              CategoryKey with the expression: CategoryField & "<P>" & "All"

              In place of <P> you'll need to enter the paragraph symbol.


              Modify your relationship to link to this new field instead of category.


              Now, if you enter Fruit or Vegetable you get the same results as before. If you enter/select All, you will see all the records in your portal.


              How it works:

              When you use text fields as key fields in a relationship in Filemaker, different values (item1, item2, item3 ...) separated by carriage returns will match values in the other tables field as follows:


              The records will match if item1 or item2 or item3 or ...


              • 4. Re: filter "all"

                Hi Phil


                Going back to display all -  I have now my tours displaying by port


                Tours table    TO tours Table


                This relation above allows me to drop down see the ports select a port or city and display the tours being offered in that city


                Now the tours table has also a field called Status = active or inactive (radio button


                So I have created


                gstatus -----=Status calculation to display status


                So my relationship works


                Tours Table   TO Tours Tabel

                gcities ---=----cities



                Two drop downs one I select the city and then another field drop down I select active or inactive and it filters accordingly. 


                I know you have explained how to display all but I am not being able to display all on the drop down.  Please advise


                • 5. Re: filter "all"



                  I believe the suggestion I made for "show all" was based on a single field with multiple match values instead of two fields. That detail does complicate things. You might find it easier to define a second relationship for "All records", create a second portal and then place each portal (one for filtering and one for "all" ) on separate tabs of a tab control.


                  If you want the all approach but with a single portal you'll need to make several changes so that selecting and "all" value will update both global fields with values that match all your key fields in the Tours table.


                  Define calculation fields in Tours:

                  cCityAll = cities & "<p>" & "All"

                  cStatusAll = Status & "<p>" & "All"


                  (Where you see <p> in the above example, put in Filemaker's paragraph symbol when specifying the calculation expression.)


                  Update your relationships so that your global fields connect to these new fields in place of Cities and Staus respectively.

                  Now, If you select all in BOTH global fields, you will see all possible portal records.


                  You can set a script that assigns "ALL" to both global fields and either assign it to a button, or (if you have FMP 10) set a script trigger to run a script that checks for "all" in the global field and assigns all to the second field if that is the case. With that approach, you can put "All" in your global fields value list and use the triggered scripts to synchronize the fields with out a button.


                  To put "All" into your cities value list, specify the above cCityAll instead of the cities field in your value list definition.

                  • 6. Re: filter "all"

                    This was good advise. It worked for me. However, when I attempt to add a new record in the portal window, I get a message " The action cannot be performed because the field is not modifiable". Any way around this?

                    • 7. Re: filter "all"

                      As Comment suggests, simply setting the global with all entries in the value list is the simplest and requires least amount of resources and work to configure.