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

    How to maintain releationship between two table.

    dev696

      Title

      How to maintain releationship between two table.

      Post

      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.
          philmodjunk
            

          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 ]

          Else

            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.