1 Reply Latest reply on Jan 2, 2012 10:28 AM by philmodjunk

    Is this a relational, calculation or summary problem?

    StaceyFingerle

      Title

      Is this a relational, calculation or summary problem?

      Post

      Seems like this should be so easy, but I just can't manage it...

      Simply put, I need Field C to show the value of Field B for the record with the maximum value in Field A.

      For example:

      Date (B) Amount (A)
      12/20/11 100
      12/25/11 2000
      12/31/11 50

      Field C should show the date on which the amount was highest, i.e. 12/25/11.  Or, turned around, the amount for the most recent date.

      Any suggestions?

        • 1. Re: Is this a relational, calculation or summary problem?
          philmodjunk

          Field C should show the date on which the amount was highest, i.e. 12/25/11.  Or, turned around, the amount for the most recent date.

          Those are two very different queries to set up. Would that second option be the "largest amount for the most recent date?"

          Make a new occurrence of this table in Manage | Database | relationships by clicking your table to select it, then clicking the duplicate button (two green plus signs). How you relate your original occurrence to the new one depends on exactly what you need here.

          YourTable::anyField X YourTable 2::anyField

          Will match any record in YourTable to all the records in YourTable 2. If you double click the relationship line, you can specify a sort order that sorts the records in YourTable 2 by field A in descending order. Then you can place fields from YourTable 2 on a layout based on YourTable and you will see fields for the record with the largest amount in Field A.

          If you use:

          YourTable::Field B = YourTable 2::Field B

          And specify the same sort order for this relationship, Then you are able to access fields of the record with the same date, but with the largest amount in Field B.

          Other variations of this approach are possible.

          It's also possible to display this info from YourTable 2 in sorted (and possibly filtered) portals.

          Note that all references to YourTable 2 refer to the same set of records found in YourTable as you have not created a new table--just a new reference to the same already existing table.