2 Replies Latest reply on May 7, 2011 5:40 PM by gwjfyo

    Highlight/tag record with lowest value in a sorted group?



      Highlight/tag record with lowest value in a sorted group?


      I have a basic price tracking database. A quick report enables me to see the lowest price within each group when sorted by type. What I want to do next is set a field within the lowest record in each group to highlight the fact that it's currently the lowest historical price. But I can't figure out how.

      The list is quite dynamic, so I would rather not have to run a script each time, but would do so if that's the only/best way to get it done.

      A quick example:

      Let's say I have three items (A, B and C) with historical prices as follows: A (10, 12, 14, 9, 11), B (11, 9, 10, 11, 11, 12) and C (1, 2, 3, 2) then I would want these to be highlighted in some way: the fourth A record, the second B record and the first C record.

      If anyone has a good idea about how to go about this, I'm all ears!

      Thank you.

        • 1. Re: Highlight/tag record with lowest value in a sorted group?

          To view and compare all records within the same table, you need to use a self-join.

          Go to your price tracking table in the graph and select it then click the ++ at the bottom to duplicate it.  It will create the same name with a 2 at the end.  Then join them as:

          PriceTracking::ItemField = PriceTracking 2::ItemField

          At the bottom in this dialog, sort on the PriceTracking 2 side ascending on price.  Now when you are in your PriceTracking table and on any layout, it will see the first related record (same item) so A will see all A records from same table, B will see B records etc.  And since you have the self-join table sorted in ascending on price then the first related record (in PriceTracking 2) is the lowest price within that Item.

          Knowing all of this, you now simply need to use conditional formatting on the price field on your layout.  Conditional formatting results in a Boolean test of 1/0 (true/false or yes/no).  So for the highlight to work, we need to produce a 'true' which could be formula calculation such as:

          PriceTracking::Price = PriceTracking 2::Price

          And below choose whether to colorize the text or the background.

          OOOPS ... in a sorted group?  This uses all records in your table so it would not work.  But yet you indicate comparing it to 'historical' prices.  Please clarify if it should only consider items and prices within the current found set or look across the entire table for the low price.

          • 2. Re: Highlight/tag record with lowest value in a sorted group?

            Perfect! Thank you so much - and for the quick answer. Each group of identical items now shows the lowest price with a highlight bar.

            I can see that I'm going to have to look deeper into how FM Pro works as I would never have thought of that in a thousand years.