1 Reply Latest reply on Jul 23, 2010 3:22 PM by philmodjunk

    Related Tables in Browse mode...



      Related Tables in Browse mode...


      I am currently working on a solution where every "customer record" (table 1) can have many "vehicles" (table 2). I have formed a one-to-many relationship via a primary key, an auto-entered serial number in table 1 called customer_ID to a foreign key in table 2 also called customer_ID...

      I thought that by creating this relationship that when a record was created in the "vehicles" table it would automatically insert the customer ID from the "customer record" table... Furthermore, I hoped when viewing a record in the "customer record" table if I went into the "vehicles" table it would only display the related records... Instead it is like they are two completely separate databases... I am still having to input the customer ID generated in the "customer records" table into the "vehicles" table... and even then I would have to perform a find to display only the related records...

      Is there a way that when I search in "table 1" that will automatically only show related records (with the same customer ID) in table 2?

      Sorry if my question is rather basic... I'm rather new at this... Thanks for the help.

        • 1. Re: Related Tables in Browse mode...

          First, if you enable "allow creation of record via this relationship" for your Vehicles table in the relationship on Manage | Database | relationships that links your two tables, you can place a portal to vehicles on your customer layout and you will be able to enter new vehicle data in the bottom blank row and filemaker will automatically create the new vehicle record and enter the customerID for you.

          If you want to enter the vehicle information on the vehicle layout, you'll need to select or enter a customer number. This can be done by formatting the customer ID field with a drop down that lists both customer ID's and their names.

          Second, you'll need to script the move from the customer layout to the vehicle layout if you only want to see the records in the vehicle layout that are related to the current customer record. You can do this with either Go To Related record or by setting your script up to perform a find.

          Using a Find:

          Set Variable [$CustID ; value: Customer::CustomerID]
          Go To Layout [Vehicles ( Vehicles ) ]
          Enter Find Mode []//clear the pause checkbox
          Set Field [Vehicles::CustomerID ; $CustID ]
          Set Error Capture [on] //keeps no records match.. dialog from interrupting your script if no records were found
          Perform Find[]

          Attach this script to a button placed on your customer layout.

          Go To Related records can do all of the above in one step. It has many parameters that you can choose for different results and it can be hazardous to your data if you have steps that modify or delete data/records after you do the Go To Related Records unless you implement it correctly. If you want to try Go To Related Records, look it up in the help system (filemaker 11) or see this link:  Why Record ID's should not be used as Primary Keys in Filemaker Relationships.