1 2 3 Previous Next 33 Replies Latest reply on Mar 29, 2012 11:36 AM by MikeF

    Filter layout with relationship / global



      Filter layout with relationship / global


      Have a table occurence Deals, which contains a field DealStatusID.

      Objective on a certain layout with all deals currently in it, is to have that layout contain only records where DealStatusID = 3.

      Created a field in Globals named DealStatusID_Confirmed, which contains the number 3.

      Related DealStatusID in table occurence Deals to DealStatusID_Confirmed in Globals.

      Seems that should filter the layout accordingly, but it does not.

      What am I doing wrong??



        • 1. Re: Filter layout with relationship / global

          Unlike other database systems where you can define a SQL query as the record source, FileMaker layouts are based on a specific occurrence of a table (Table occurrences are the boxes found in Manage | Database | Relationships). When you set up a layout to Deals, you create a direct connection to the Deals table. It's like a Select * query with no Where and no sorted by clause. It doesn't "go through" any relationship. You can perform find, do show all records, show omitted only, etc and you are working with all the records in the table. The relationships you define to the layout's table occurrence control how you can access data in any other tables--not Deals.

          If you based your layout on Globals, a portal to Deals could be created to list all records from Deals with status = 3. The Globals to Deals relationship then works to control what records appear in the portal.

          If you want to base your layout on Deals, you'll need to use scripts to restrict the records shown to the specified status.

          A script can perform a find or use Go To Related Records to pull up the desired found set. Script triggers can catch the transition from find back to browse mode to kick in a script that constrains the found set to only records with status = 3. An if you have FileMaker advanced, you can use custom menu sets to either disable the show all, show omitted options or replace them with scripts that also filter out all but status = 3.

          • 2. Re: Filter layout with relationship / global

            Many layouts are based on a Companies table, which has a CompanyTypeID [1 = Business, 2= Hotel, 3 = Restaurant, etc.].

            Have built a layout for Hotels, where the script does a Perform Find which filters the layout for CompanyTypeID = 2.  But if I try to find a hotel that starts with the same few letters as a business or restaurant, those records that are not *only* hotels [companyTypeID "2"] also appear.

            How can I completely exclude anything other than the preferred CompanyTypeID??

            Thought the relationship method using a global field might work, but it doesn't.

            Need to find a rock-solid method of filtering layouts.

            Any assistance will certainly be sincerely appreciated.




            • 3. Re: Filter layout with relationship / global

              To quote from my last post:

              Script triggers can catch the transition from find back to browse mode to kick in a script that constrains the found set to only records with status = 3.

              In layout setup, click the script triggers tab and select "OnModeExit" and specify the "Find" mode option.

              Have it perform this script:

              Enter Find Mode[]
              Set field [YourTable::CompanyTypeID ; 2]
              Set Error Capture [on]
              Constrain Found Set[]

              WIth this trigger in place, any find that you perform will automatically constrain down to just records of type 2.

              It's something of a Kludge, but I also worked out a different approach that can be used if you prefer:

              Define a table, HotelTypes, and link it to your current table like this:

              YourTable::PrimaryKey = HotelTypes::ForeignKey

              Enable "allow creation" for HotelTypes in this relationship. Enable the delete option for it as well.

              Use the OnObjectSave trigger on YourTable::CompanyTypeID to perform this script:

              If [YourTable::CompanyTypeID = 2 ]
                 Set Field [HotelTypes::ForeignKey ; YourTable::PrimaryKey ]
              Else If [Not IsEmpty ( HotelTypes::ForeignKey) // company type used to be 2 and has been changed.]
                     Freeze Window
                     Go To Related Record [Show only related records; From table: HotelTypes; Using layout: "HotelTypes" (HotelTypes)]
                     Delete Record [no dialog]
                     Go to layout [original layout]
              End If

              Since this table only contains records for companies of type 2, you can now base your layout on HotelTypes, but include all needed fields from YourTable as this is a one to one relationship, but you can only see and access type 2 records.

              1 of 1 people found this helpful
              • 4. Re: Filter layout with relationship / global




                Will try both and see what works most efficiently.


                On the second choice's script, a little uncertain of why "DeleteRecord" is there??




                • 5. Re: Filter layout with relationship / global

                  It's there to maintain data integrity. Say you create a new record and mistakenly identify it as type 2. Then you discover your mistake and correct the error by changing the type to a different value. If the script does not delete the related record from HotelTypes, you will still see this record listed on the HotelTypes layout even though it is no longer a type 2 company.

                  • 6. Re: Filter layout with relationship / global


                    Looking at your second choice ...


                    I have a table already, named Hotel Details. It works in conjunction with the Companies table, like a sub-form in Access.

                    It's related to Companies via CompanyID in both tables, and has only records relative to hotels.

                    Any way I could use that as the "filter" ?

                    *** The overview is the the Companies table handles generic info, ie name, company type, address, etc, where the Detail tables - relevant to each company type - handle specifics.   Ie Hotel Details will have rate & type of room fields, where Restaurant Details will have a field outlining type of cuisine.




                    • 7. Re: Filter layout with relationship / global

                      Yes, provided that it's a one to one relationship.

                      • 8. Re: Filter layout with relationship / global

                        Ok, have the second choice working with my Hotel Details table.

                        Just added the CompanyTypeID to it, and made it a calculated field, value = 2.


                        One thing though -- when I go to Find in Company field, it's still showing entries that are not hotels.

                        Any ideas on how to alleviate that?




                        • 9. Re: Filter layout with relationship / global

                          On what layout are you performing the find? If the layout is based on Hotel details, this should not be possible.

                          • 10. Re: Filter layout with relationship / global


                            The layout is based on HotelDetails, but the Company field ... which is not a field in HotelDetails table, it's in Companies ... is the field I use to find hotel names.

                            Have a button running a script that's simply...

                            GoToField: Companies

                            Enter Find Mode [Pause]

                            Perform Find[]

                            Show All Records


                            The Companies field has Auto-Complete enabled, which is where I'm seeing records other than hotels.

                            Wondering if there's any way to filter for CompanyTypeID = 2 within that script that will still work, considering the Show All Records.

                            Thanks Phil.




                            • 11. Re: Filter layout with relationship / global

                              One addendum to the above ... the non-hotel records show in Auto-Complete, but if I choose one the message says "Record Cannot Be Found".

                              • 12. Re: Filter layout with relationship / global

                                Ok. You have a value list defined for that field (or just an auto-complete setting) and that pulls values from the main table and is not in any way affected by the current layout. Once you have selected a company and perform the find you are telling Filemaker, find all Hotel Details records that link to the specified company. Since there are no Hotel Detail records that link to that company, you get a records not found result.

                                You need a conditional value list on that field that only lists companies that are linked to a record in the Hotel Details table.

                                Forum Tutorial: Custom Value List?

                                Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                                Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

                                Hierarchical Conditional Value lists: Conditional Value List Question

                                Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                                • 13. Re: Filter layout with relationship / global


                                  Thanks Phil.


                                  Will review all the material you sent.

                                  • 14. Re: Filter layout with relationship / global


                                    Am not getting anywhere with conditional value lists.  

                                    The script trigger indeed does "filter" everything but CompanyTypeID 2, it's only when I use the "Find" script [or just plain click on Find] that the Companies field shows all records in the auto-complete.   I believe this makes sense, as the Find is looking at the entire field in Companies table.   Or table occurrence??


                                    Is there any way to "filter" the table occurrence of Companies itself, so that the Companies field in that table occurrence already excludes anything but CompanyTypeID  2  from ever showing up in it?


                                    And if so, would that work for the Find problem??



                                    1 2 3 Previous Next