2 Replies Latest reply on Dec 29, 2013 2:15 AM by macmesser

    searches on related table



      searches on related table


           I have a database which contains two identical tables, Table A and Table B, which are related to one another by a unique valued key field called "Item Number." Before adding the relationship I had created some scripts which performed searches on several fields in Table A. After adding the relationship the searches work on layouts with fields from Table A only, with fields from Table A and Table B, and with  fields from Table B only. I conclude that once a relationship is added linking two tables, any search on one of the tables is really a search on the join of the tables. Correct or must this statement be qualified in some way? Thanks for any insights.

        • 1. Re: searches on related table

               I'd need a bit more detail before I could post a specific answer so what I am posting is generally true and you'll have to figure out how it applies to the actual layouts, tables and relationships in your database.

               Every layout is based on a specific table. This is specified by selecting a Tutorial: What are Table Occurrences? name in the "show records from" drop down found in Layout Setup and this is also specified in the new layout wizard when you first create a new layout.

               When you perform a find on a given layout, you are finding records in that layout's underlying table, not in any other table. If you specify criteria in fields from a related table, you are telling FileMaker to find any records in the layout's table that have at least one related record that matches the specified criteria for the related records.

               Where this often confuses a new user is that once the find is performed and the found set of records update to show the records matching the specified criteria, the window returns to Browse mode and the layout elements update to show related data as they have been designed to do. The criteria specified for a find will have no effect on how the layout displays this data once your find has been performed and you are back in Browse mode.


               Say you have two tables in this relationship:

               MainTable----<Details (---< means "one to many" )

               You place a portal to Details on your MainTable layout and use it to create multiple records in the portal for several different MainTable records.

               Then you enter find mode and enter "Apple" as the search criteria in a text field in the portal--a field defined in Details, not MainTable.

               You perform the find.

               The resulting found set will consist of all records in MainTable, where there is at least one record in the portal with "Apple" in that text field, but all related records for each found record still show in the portal. Thus, one record in the found set may list "apple" ; "pear" ; "grape" in the portal and another may list "carrot" ; "celery" , "apple". In each case there is at least one "apple" entry in the portal, but all related records still display.

               This can be particularly confusing if you have multiple related records but the fields from that related table have been placed on your layout without being placed in a portal. In that situation, the fields show data from the first related record only. If that were the case in our example, you'd see "carrot" in that field in the second example record and this can create the illusion that the wrong record was found.

          • 2. Re: searches on related table

                 Thanks. What confused me is that the script performing a find on Table A worked on a layout with only fields from Table B, but I didn't know precisely how it worked. Now I see it's going to display the first related record in Table B for every record in Table A that matches the search.