1 Reply Latest reply on Oct 24, 2014 9:44 PM by philmodjunk

    aggregate MIN function not working in calculation



      aggregate MIN function not working in calculation


      I have a portal on table 1 showing related records from table 2

      Table 2 has two fields field 1(Number field with numbers) and field 2(text field with names)

      I have a field on table 1 that is a calculation. I would like the calculation to find the lowest number in Table2:Field1  but to display the corresponding name from Table2:field2

      I tried everything i can think of. I have no trouble getting the lowest # using the MIN function.




        • 1. Re: aggregate MIN function not working in calculation

          Which means that the min function IS working. It's doing exactly what it was designed to do, not what you want it to do. wink

          Here are three ways to get what you want:

          1) Presumably, your initial relationship is similar to this:

          Table1::PrimaryKey = Table2::ForeignKey

          Add another table occurrence of Table2. Use your calculation field that uses Min ( Table2::numberField ) to return the smallest value of numberField from table 2 as a match field in a relationship to this new occurrence of table2. We'll call it Table2|min.

          Table1::PrimaryKey = Table2|min::ForeignKey AND
          Table1::minCalcField = Table2|min::NumberField

          then Table2|min::TextField will return the text of corresponding to the smallest

          2) take the original relationship:

          Table1::PrimaryKey = Table2::ForeignKey

          double click the relationship line and specify a sort order Table2 that sorts on NumberField to sort your related records in ascending order.

          Then the reference Table2::TextField will refer to the first related record which will be the textfield value of the related record with the smallest value in the number field.

          3) ExecuteSQL could be used to also return the minimum related record.