6 Replies Latest reply on Nov 1, 2011 2:22 PM by MikeF

    Filter one of one-to-many



      Filter one of one-to-many



      tblCompanies relevant fields: CompanyID / CompanyTypeID / Company [text]

      tblCompanyType fields: CompanyTypeID / CompanyType [text]

      CompanyTypeId / CompanyType =

      1 / Accounting

      2 / Advertising

      3 / HealthClub     [and so on…]


      In an Access form – frmAdvertising based on tblCompanies – I can use a Filter in the properties for CompanyTypeID as the number “2” [Advertising] and only those records from tblCompanies will be represented in that form.

      ***Could also base the form on a query of tblCompanies, with criteria as “2” in CompanyTypeID.

      Either way, anything in that form including Sub-Form [Portal] records would only pertain to CompanyTypeID “2” [Advertising].

      How can this be accomplished in Filemaker Pro 11?


      -          Mike

        • 1. Re: Filter one of one-to-many

          The found set for a given layout in filemaker is a much more dynamic thing than the record source you can define for an Access form. The same layout can easily display completely different subsets of the records in it's underlying table due to scripts or user actions performing finds or other manipulations listed in the Records menu.

          What you describe can be done in several different ways depending on what you need.

          A script can be written that either performs a find for ID = 2 or that constrains the found set for CompanyTypeID = 2. If you want to "lock" your layout to just those records, you can use various layout based script triggers such as onLayoutLoad to run this script.

          In Filemaker 11, you can use a portal with a portal filter expression that limits the records to just those where CompanyTypeID = 2.

          You can also define a calculation field, constType2 as just the value 2. Then use this field in the relationship on which you plan to base your portal or in scripts that use Go To Related Records to bring up a found set of "Type 2" company records.

          • 2. Re: Filter one of one-to-many


            Think it's best to "lock" a layout to CompanyTypeID ="2", just like in Access.

            How specifically is that accomplished?


             - Mike


            • 3. Re: Filter one of one-to-many

              Enter find mode[] //clear the pause check box
              Set field [tblCompanies::CompanyTypeID ; 2 ]
              Set Error Captur [on]
              Perform Find[]

              Use the ONLayoutEnter script trigger to perform this script and you'll only see type 2 records when you first access the layout. But the user can still perform a find or do a show all records to see other records where the type is not 2.

              You can use this very similar script:

              Enter find mode[] //clear the pause check box
              Set field [tblCompanies::CompanyTypeID ; 2 ]
              Set Error Captur [on]
              Constrain Found Set[]

              And use the OnModeExit trigger with the FInd Mode option to perform this script every time the user exits find mode.

              This still leaves the two show options. If you need to lock those down, you'll need to use FileMaker advanced to set the layout up with a custom menu that either does not include these options or which  perform scripts similar to the above ones to limit the "show all" records to those of type 2.

              "...just like Access"

              That's often an approach that leads to frustration. The two systems use a sufficiently different approach that you are better off designing your solution to exploit the strengths of FileMaker than to try to replicate the way you would do this in Access.

              • 4. Re: Filter one of one-to-many

                 I will have a portal that is pertinent *only* to CompanyType 2 on this layout.  So ideally would like to lock it down altogether.

                However, since there will be portals with specific fields/information relevant to each CompanyType --- it occurs to me that if I had a layout based on tblCompanies, and a drop-down list or popup menu based on a Value List from tblCompanyTypes on that layout ...

                Is there any way to set up the layout where it would provide a different portal [on that same layout] relevant to whatever CompanyType is in the drop-down list?

                Have managed to do this in Access, but do you have a more ideal suggestion based on moving forward utilizing Filemaker's particular strenghs in this regard?


                 - Mike

                • 5. Re: Filter one of one-to-many

                  Unfortunately, in FileMaker you would either switch layouts with each such change to present the appropriate portal, or you'd use a tab control with each different portal on a different tab. The tab can be made invisible, and if you give each tab panel an object name, (Use the Name box in the top of the Inspector's position tab), you can use go to object in a script to select the appropriate panel.

                  Thus you can set up an OnObjectSave (drop down list) or OnObjectModify (pop up menu) trigger to perform a script that either selects the approprieate layout or that selects the appropriate tab control panel.

                  • 6. Re: Filter one of one-to-many

                     Ok, thanks Phil.

                    Am engrossed in numerous FM tutorials at present, concurrently with figuring out how to build a solution.

                    Understand most of what you said [the tutorials are starting to sink in], so will try a few builds and let you know what happened.

                    Gotta say, am glad you're out there ..!!

                     - Mike