1 2 Previous Next 22 Replies Latest reply on Oct 3, 2011 10:32 AM by philmodjunk

    Filtered Portal with global fields

    user15130

      Title

      Filtered Portal with global fields

      Post

      I was wondering...

      How do you filter a portal based on a global field on the same layout. I have about 5000 records I would like to filter based on 3 - 5 fields placed above the portal (drop down menus).  I saw it done based on filemaker pro 11's new filtered portal but cannot remember  how the technique is done.

      Any help or links would be appreciated.  Video would be perfect. :)

      Marty

        • 1. Re: Filtered Portal with global fields
          philmodjunk

          Let's set this up with one example and then you can extend that to more fields if needed:

          Say you have this relationship:

          Layout::PrimaryKey = Portal::ForeignKey

          ("layout" and "portal" would be table occurrence names in manage | database | Relaitonships.)

          You have this global field: gSelectedFruit defined in the layout table. (since it's global, you can define this in any table you want to use for this purpose.)

          And you format it with a pop up menu of three fruit types: Apple, Lemon, Peach

          In portal setup, you define the following portal filter expression:

          IsEmpty ( Layout::gSelectedFruit ) or PatternCount ( Portal::DescriptionField ; Layout::gSelectedFruit )

          This will list all related records if gSelectedFruit is empty. If it has a value, only related records where the description field contains the same text as the global field will be shown.

          Since the portal will not automatically refresh to show different records when you select a different fruit type here, you have to add a script to update the portal:

          Commit Record
          Refresh Window [Flush Cached Join Results]

          For a pop up menu, the onObjectModify trigger is a good option. For Drop Down Lists where tying in the field is possible, use OnObjectSave.

          Here are some other portal expressions you might use here

          IsEmpty ( Layout::gSelectedFruit ) or ( Portal::DescriptionField = Layout::gSelectedFruit )  // exact,, non case sensitive match

          IsEmpty ( Layout::gSelectedFruit ) or ( left ( Portal::DescriptionField ; Length ( Layout::gSelectedFruit ) ) = Layout::gSelectedFruit ) //Starts with match

          IsEmpty ( Layout::gSelectedFruit ) or Exact ( Portal::DescriptionField ; Layout::gSelectedFruit ) //case sensitive exact match

          • 2. Re: Filtered Portal with global fields
            user15130

            :)

            Going to work on this one now.  Thanks. This is much more complicated...

            Marty

            • 3. Re: Filtered Portal with global fields
              user15130

              So I got everything set up and checked it 4x's. :) But the portal remains blank and no filter action happens.  I really think I have checked it that many times.

              PK = FK

              gGlobalField is drop down and is GLOBAL

              Portal filter: IsEmpty ( Layout::gSelectedFruit ) or PatternCount ( Portal::DescriptionField ; Layout::gSelectedFruit )

              Uhh...  :)

              Marty

              • 4. Re: Filtered Portal with global fields
                philmodjunk

                Is gSelectedFruit of type "number" or "text"? Should be text. And it is a field with global storage enabled in field options correct?

                • 5. Re: Filtered Portal with global fields
                  user15130

                  Yes

                   

                  gGlobalFruit is text and is set to Global

                  • 6. Re: Filtered Portal with global fields
                    philmodjunk

                    Hmm you have a different field name, did you update the portal filter expression to use this name in place of gSelectedFruit? I imaging that you have or you'd get an error message when you try to dismiss the portal filter calculation dialog box.

                    If you disable the portal filter expression by clearing its checkbox in portal setup... do you see all the related records in your portal?

                    • 7. Re: Filtered Portal with global fields
                      user15130

                      No I do not see the values.

                      • 8. Re: Filtered Portal with global fields
                        philmodjunk

                        Then something is wrong with the relationship instead of the portal filter.

                        You'll need to double check field types and the values stored in the key fields to see why the related records don't appear. Once you have that working, you can re-enable the portal filter and it should now work for you.

                        • 9. Re: Filtered Portal with global fields
                          user15130

                          Why with my relationship does [=] display nothing and [X] displays all records. I get what [X] does but do not understand why [=] results in nothing displayed. Your instructions say display [=] but no matter what 'I' do, i cannot display any records with [=] relationship.

                          Layout::Main_ID = Portal::fk_Main_ID

                          They are both 'number fields' indexing is on. The Main_ID is serial and fk_Main_ID is not.

                          Marty

                          • 10. Re: Filtered Portal with global fields
                            philmodjunk

                            If you do not see any records then fk_Main_ID is not storing a value that matches Main_ID in at least one record.

                            Try this test.

                            Make Main_ID visible on your layout based on "Layout". Make fk_Main_ID visible on your layout that is based on "portal". Do a show all records and switch to table view on both layouts. Inspect the values in these two fields and see what is stored in each. I predict that you won't find matching values in these two fields--probably one of the two fields is empty.

                            • 11. Re: Filtered Portal with global fields
                              user15130

                              Yes I did that last night.  It is blank, why?  Do I need create record in relationship set up checked.  Please tell me NO! :))

                              Marty

                              • 12. Re: Filtered Portal with global fields
                                philmodjunk

                                The "Create" option makes it possible to use a portal to add related records and not need to also enter the matching ID number as it would be added for you. This can be very useful, but is not strictly necessary. However you choose to fix it, both fields need matching values before the basic portal set up can work.

                                • 13. Re: Filtered Portal with global fields
                                  user15130

                                  OK So now I checked it and I have one record and no others.  This is a really big lightbulb moment for me so what is next?  This is the lightbulb moment for me on one of the biggest areas of FM.  Relationships...

                                  Why is there only one record showing?

                                  Marty

                                  • 14. Re: Filtered Portal with global fields
                                    philmodjunk

                                    How many related records have a Fk value that matches the PK for the current record showing in Layout? I would assume only one record if you have only one showing and you still have the portal filter disabled.

                                    1 2 Previous Next