1 Reply Latest reply on Oct 19, 2009 8:50 AM by philmodjunk

    How to maintain releationship between two table.



      How to maintain releationship between two table.


      Filemaker pro 8.5


      Table and relation are given below:


      Table                            Fields

      Employee               (Emp_ID,....)


      Reviewer                (Reviewer_ID,....)


      Perfomance Review (Employee::Emp_ID, Reviewer:: Reviewer_ID, Created _By, Modified_By......)


      Created_By field contain the Account Name which is  auto entered and this name is also equal to the Reviewer_Name.


      So the problem is that how i can maintain the relationship between two tables (Reviewer and Perfomance Review) because i dont want to ente data manualy in Performance Review :: Reviewer_ID and also dont want to show this field on layout.


      how i can compare Account Name with Reviewer_Name and pick up Reviewer_ID and auto enter in Performance Review::Reviewer_ID.

        • 1. Re: How to maintain releationship between two table.

          If you want to create a new Performance Review Record and automatically enter the Reviewer ID number matching the Account Name, Run this script:


          Go to layout ["YourReviewerLayout" (Reviewer)]

          Enter Find Mode []

          Set Field [Reviewer::ReviewerName ; Get (AccountName)]

          Set Error Capture [on]

          Perform Find []

          Set Error Capture [off]

          If [get (foundcount) > 0 /* a reviewer record was found */]

            Set Variable [$RevID ; Value: Reviewer::ReviewerID ]

            Go To Layout ["Performance Review" (Performance Review)]

            New Record/Request

            Set Field [PerformanceReview::ReviewerID ; $RevID ]


            Show custom dialog ["No Reviewer records with " & Get(accountname) & " was found."]

          End If


          You can also create a relationship and define a lookup to do this.

          Define an unstored calculation field that returns text, cAccountName : get (accountname).

          Define a relationship

          PerformanceReview::cAccountName = Reviewer::ReviewerName


          Set an auto-enter field option on ReviewerID to lookup Reviewer_ID from the Reviewer table to automatically copy that ID number into any new record created in Performance Review.