3 Replies Latest reply on Nov 4, 2010 1:57 PM by philmodjunk

    Related Record from other table

    SyedHussain

      Title

      Related Record from other table

      Post

      Hi.

      I have "Person" table and related table "surveys". Each record in person table can have multiple related record in surveys table. We use portal to view those multiple record of same person. I know Max and Min function can give me first and last record of survey table (assume I am using Max(Creationdate) or Min(CreationDate)) . Now question is I like to see Max -1 record. I mean second last record. I don't know how to access or find 2nd last record.

      Person Table

      ================

      Person1

      Person2

      .....

      ...

      Surveys

      ==============

      Person1

      .........CreationDate1

      .........CreationDate2

      .........CreationDate3

      Person2

      .........CreationDate1

      .........CreationDate2

      .........CreationDate3

      These creationDate records keeping adding/removing by users. How at given time I can access any related creationDate

      Any help

      Thanks

        • 1. Re: Related Record from other table
          philmodjunk

          What version of FileMaker are you using? I think your best bet is to filter the portal--which can be done for any version, but the details vary for FileMaker 11.

          • 2. Re: Related Record from other table
            SyedHussain

            It is FileMaker Pro 8

            • 3. Re: Related Record from other table
              philmodjunk

              I don't have that version so the exact details to set this up may be a little different from my version, but here's what I am suggesting you do:

              Define a date field, SelectedDate, in the person table. This can be a global field if you want it to be.

              Create a new relationship to use with your portal like this:

              Person::PersonID = Surveys::PersonID AND
              Person::SelectedDate = Surveys::CreationDate

              If you also need a relationship that soley matches by PersonID, you'll need to create a second table occurrence of Surveys so that you can set up two different relationships to surveys.

              You can now enter or select a date in the SelectedDate field and the portal will only show the survey records for the current person that have that creation date.

              If FileMaker 8 allows you to use inequality operators such as < and > in your relationships, you can set up two dates and a relationship that then allows your portal to display a range of records.