2 Replies Latest reply on Nov 8, 2011 8:37 AM by philmodjunk

    Filter a layout to only related records

    charltoncalhoun

      Title

      Filter a layout to only related records

      Post

      I have a Company table and an Product table and they are related by a CompanyID field. I have a listview layout called CompanyList which lists the Companies. I have assigned a button to the company text that when clicked will change to layout #2 (ProductList), which is a listview of products.

      What I want to do is filter the ProductList layout so that it only shows products that are related to the company that was clicked. I also want to be able to add new products from the same layout with the same Company automatically set as the related record.

      I do not want to use a portal to show related records. Can anyone provide instructions on how to accomplish this?

      Thanks!

        • 1. Re: Filter a layout to only related records
          charltoncalhoun

          Update: I have solved the "show related records" problem by using "GoTo Related Record" in the script for the button click. This takes me to the second layout with only related records showing, which is exactly what I want.

          But I still have some problems:

          1. If there are no related product records, the ProductList doesn't open - the button click doesn't seem to do anything. I want it to open the ProductList with no records so I can add products from there.

          2. Once I'm on the filtered ProductList, I want to be able to add products that are related to the company I clicked to get to that filtered layout. But when I try to Add New Record, it doesn't automatically link to the company. I'm not displaying the CompanyID field on the layout, all I want to see are the products. Is there any way to make a new record default to the currently selected company?

          Thanks

          • 2. Re: Filter a layout to only related records
            philmodjunk

            1) this is known behavoir for Go To Related Records. You need to either add code that checks first for the existance of any related records such as If [ RelatedField::ForeignKeyNumberField ] or use get last error immediately after the GTRR step to see if it found any related records. For more on GTRR, see this thread on the subject:  The Complete Go To Related Record

            2) FileMaker does not have any built in way to keep track of what record is the current record on another layout, that's based on a different table. You can, however, add this capability to your system so that new records on your Product List layout automatically enter the Company ID of the the currently selected companyID.

            In your script that uses GTRR to pull up your list of products, include this step:

            Set Variable [$$CompanyID ; value: Companies::CompanyID ]

            Because we've used $$ at the start of this variable name, this is a global variable and it will retain this value until we either change it or close the file.

            Now, in your ProductsTable, you can open Manage | Database | fields and double click the field definition for company ID and specify $$CompanyID as the auto-enter calculation.

            With this setup, if you run a script from your company layout to pull up a list of products for that company and any new record you create will automatically be linked to that company until you select a different company on the company layout and use it to pull up a different list of products. You can still edit the companyID field in Products to change this in cases where you don't want the ID to be auto-entered. You might, for example, format the field as a drop down list of ID's listed in field 1 and the company names listed in field 2 to be able to select a different company from the drop down when such a need arises.