6 Replies Latest reply on Aug 1, 2011 2:17 PM by philmodjunk

    Filtering a portal by a checkbox

    buck_5648798798

      Title

      Filtering a portal by a checkbox

      Post

      I have a database with a list of people. Each person has a checkbox for "current" meaning that person has paid their dues.

      I want to have a portal in another table that only shows people with the checkbox checked. I'm totally stuck..

      Thanks

        • 1. Re: Filtering a portal by a checkbox
          philmodjunk

          What version of FileMaker are you using?

          In FileMaker 11, you can set up a portal filter with an expression such as:

          ValueCount ( FilterValues ( LayoutTO::CheckBoxField ; PortalTo::Name ) )

          And only related records where the text in Name matches a value selected in the CheckBoxField will appear.

          You'll need a script to commit the record and refresh the window each time you click a check box which can be done with an OnObjectModify trigger on this field:

          Commit Record
          Refresh Window [Flush Cached Join results]

          With older versions of FileMaker (and 11 ) where a portal is possible, you can also use the check box field as a key field that's part of the relationship. This method may require adding an additional table occurrence, but doe not require any scripting to refresh the window.

          LayoutTO::PrimaryKey = PortalTO::ForegnKey AND
          LayoutTO::CheckBoxField = PortalTO::Name

          How this works is that a value in name will match to any one value in the list of selected values stored in the check box field. If you select "Tom" and "Harry" in the check box field, records with "TOM" OR "Harry" will appear in the portal.

          • 2. Re: Filtering a portal by a checkbox
            buck_5648798798

            Thanks!!! I'm trying now..

            • 3. Re: Filtering a portal by a checkbox
              buck_5648798798

              I'm using FM 11 but some of the users are on 10. I  still can't get it to work trying both methods.

              I have my check box field using a custom value of "1" which I suspect is wrong....

              I must confess as a noob i don know what the "TO" in LayoutTO/PortalTO means.

              Thanks

              • 4. Re: Filtering a portal by a checkbox
                philmodjunk

                TO stands for "table occurrence". When you refer to a field in FileMaker, the text to the left of :: is the name of one of the "boxes" found in Manage | Database | Relationships. Those "boxes" are called "table occurrences". These are the same "table" names you see in many drop downs such as the "Show Records From" drop down found in Layout and Portal setup... dialogs.

                If some users have 10, do not use the first method. It only works with 11.

                For the second method, what is the relationship you would use for you portal before you add in the checkbox filtering.

                I assumed that your check box field used a list of names for it's value list. Using the value "1", indicates that you likely are using a different design approach here. Please describe what you have set up in more detail.

                If "table occurrence" is a new term, you may want to read this tutorial: Tutorial: What are Table Occurrences?

                • 5. Re: Filtering a portal by a checkbox
                  buck_5648798798

                  Thanks for your patience and the tutorial on TOs.

                  So far my design approach has so far been shooting arrows in the dark. I'm open to any ideas....

                  What I have is a MEMBERS Table and a duplicate Table Occurance called MEMBERS2. There is  a name field, Primary and Foreign key fields and a checkbox field. The checkbox field is a member of a value list. The value list has the number 1 as a custom value.

                  I don't know what value to assign the CheckBoxField.

                  Thanks

                  • 6. Re: Filtering a portal by a checkbox
                    philmodjunk

                    Since this is a single value check box, I'm not sure how you want it to affect what records display in the portal.

                    I assumed you might have a value list with several names and you were selecting a name from that list so that records with a name matching one of the selected names are the ones that then appear in the portal...

                    When you click this check box what is suposed to happen in this portal? When you click it again to clear it, what should happen?