1 Reply Latest reply on Oct 15, 2008 10:29 AM by AndreasT

    Display two records

    doug7856

      Title

      Display two records

      Post

      I need to display a current record and a related record at the same time.  This is a performance review that would display the current year and last year's records for the same individual.  How can I do this?  I'm using FileMaker Pro 9 Advanced.

        • 1. Re: Display two records
          AndreasT
             Doug,

          You need to set up a relationship that returns the record(s) from the last year for this person.

          Assuming you have the person records and performance records in separate tables, the relationship would look something like this. 

          You need a field in the person table that calculates to last year:

          LastYear= year(get(currentdate)) -1

          Make sure you set indexing to None in the Storage section as we need this field to be dynamic.

          Make a field in your performance table that contains the year of the review.

          ReviewYear=year(DateOfReview)

          This must be an indexed field.

          Next, set up a new relationship between your person table and the performance table.
          You probably already have a relationship for these tables already, but make another just to get at last years record(s).

          This relationship will have 2 criteria.

          1. Set your ID's to match for the two tables for the first criteria (this makes sure we get the right person)
          2. Add the Person:LastYear to match Performance:ReviewYear

          That's it.

          Place your fields from the new relationship on any Person based layout and you will see last years record for that person. Use a portal if more than one record exist for this person for last year.