1 Reply Latest reply on Oct 18, 2010 11:25 AM by philmodjunk

    Defining database

    MysticRiverWatershedAssociation

      Title

      Defining database

      Post

      I'm somewhat new to FM Pro and I have a question. I have a list of payment amounts in reverse date order. I'd like to create another field to automatically give me the most recent payment amount. I know how to define databases and I thought I'd find it under calculations but none of the criteria seems to work. Any help would be greatly appreciated.

        • 1. Re: Defining database
          philmodjunk

          It depends on what you mean by "automatically give me". If your records are sorted in descending order on a list or table view layout, simply placing the payment field in the header will show the most recent payment as long as the first record on your layout is the current record.

          If you need something more than that...

          I assume that each payment is a separate record so that you have one date field and one amount field in each record. Thus, you need something that will select the most recent record so that you can then refer to the most recent payment amount stored in that record.

          You can relate a table to itself. This is sometimes called a "self join" relationship.

          In Manage | Database | Relationships, click the "box" for your table, then click the button with two green plus signs to make a new occurence of this table. Change it's name to LatestPayment and create a relationship linking the two. The relationship details depend on your design needs. It may be that you need the most recent payment record of all records in the table or the most recent payment for a specific group of records. The basic concept is the same, but the details will differ a bit. Here's what you'd use for most recent payment of all the records in the table:

          PaymentsTable::Date X LatestPayment::Date

          Specify a descending sort order by date option for this relationship.

          Now you can place Payment amount and other fields from LatestPayment on your layout or you can refer to such a field in a calculation in order to get to the most recent payment regardless of which record is the current record on your layout.