2 Replies Latest reply on Dec 13, 2014 3:32 AM by JonathanPhillips

    Selective Max and Min Values

    JonathanPhillips

      Title

      Selective Max and Min Values

      Post

      Hi,

      I am trying to use a calculation to find the MIN and MAX values from a certain field. But I only want to use the values in that field that are present when another field has a specific value.

      Table 1: So, in one field I have a value list, where I pick a shop: Grocery Store, Bakers etc. Then another value list field with products: Apple, Pear, Orange etc. Then I have a number field where I insert the price.

      Table2: Now, in the Grocery Store table, I want to create a field that will tell me the maximum price paid for an apple. But how do I create a calculation that will only select the values in the price field when the product field says "Apple"? 

      Any help with this would be greatly appreciated,

      Jonathan

        • 1. Re: Selective Max and Min Values
          philmodjunk

          Define a relationship between an occurrence of table 1 and 2 like this:

          Table2:SelectedProduct = Table1::Product

          In Table2, when you select "Apple" in the Selected Product field, this relationship matches to all records in Table1 where Product = Apple. And then a calculation field in table 2 defined as Max ( Table1::price ) will return the maximum price paid for an apple.

          You can also replace SelectedProduct with a calculation field, constApple defined to always have the value "Apple" and now your calculation returns the max price for apples, only and always apples.

          The relationship can include a second match field if you want the max price for a specific store.

          But note that this only returns the maximum price for the specified product. Other means would need to be used to access other informaiton in the same record as that Max price--such as the name of the store.

          • 2. Re: Selective Max and Min Values
            JonathanPhillips

            Thanks very much, that works great.