4 Replies Latest reply on Mar 20, 2012 8:49 AM by FrankSchwarz

    Find out of multiple Values the 10 lowest

    FrankSchwarz

      Title

      Find out of multiple Values the 10 lowest

      Post

      Hi everyone:

       

      Can you help find to create a formula which allows me to find out of a number of values in the same value field the 10 lowest and use those for a calculation on a permanent base, meaning if lower values are added in the same value filed so does the result for finding the lowest 10 change as well.

       

       

      Thanks

       

       

      Frank

       

        • 1. Re: Find out of multiple Values the 10 lowest
          philmodjunk

          It depends on the structure of your data and what you want to do with the 10 lowest values. Are each of these values stored in separate records or do you have multiple values inside the same record?

          in general terms, you can use sorting to access the 10 lowest values if each value is stored in a different record. You can sort a found set, a portal or use a sorted relationship with the list function to refer to the 10 lowest values.

          • 2. Re: Find out of multiple Values the 10 lowest
            FrankSchwarz

            Dear Phil:

             

            Yes the values are stored in seperate records. I need the result be available at all times not only after a sort. For excample, I have 1000 records which are from a total of 30 clients and I need to be able to see the lowest 10 values from each client out of the 1000 records.

            Does that make sense?

             

            Cheers

             

             

            Frank

             

            • 3. Re: Find out of multiple Values the 10 lowest
              philmodjunk

              Yes and a sorted relationship or sorted portal can be used to list the lowest 10 values.

              Here's one example:

              Set up a table of clients with this relationship to your records:

              Clients::ClientID = YourTable::ClientID

              Put a portal to YourTable on the clients layout. Limit the portal to 10 rows and specify a sort order on your field that sorts in increasing order. The portal will list the related records with the 10 lowest values. If you specify this sort order in the relationship, you can use calculations that return the lowest N values from the related records.

              • 4. Re: Find out of multiple Values the 10 lowest
                FrankSchwarz

                Dear Phil:

                 

                Thanks a million. So easy if you know how.

                 

                 

                Cheers

                 

                 

                Frank