2 Replies Latest reply on Jan 15, 2015 8:21 AM by StevenNardi

    finding two (or more) lowest values in a field



      finding two (or more) lowest values in a field


      This is for a database tracking student grades. I have a field where I enter the points scored on individual quizzes, and then a layout that totals the number of possible points compared with the total points scored by each student. 


      I would like to make it possible to forgive each student's lowest scores. I know I can use the "min(field)" function  to find the lowest value in the field holding the marks, but how do I identify the lowest two (or more) values? I need a way to find the bottom two (or three or four--ideally I could decide later how many to drop).  

        • 1. Re: finding two (or more) lowest values in a field

          By sorting the records for one student--either in a found set or in a sorted portal, you can use a script to go to the last record or portal row and move up from there to capture the values of each "lowest score" that you want.

          WIth a sorted relationship sorting scores in ascending order, List ( RelatedTable::Score ) would list those lowest scores at the beginning of the list and you could use something like the LeftValues function to extract the N lowets scores.  (If your sort in Descending order, the RightValues function can be used.)

          And ExecuteSQL() could be used to extract a list of N Lowests scores as well, but without the need for establishing a relationship for the purpose.

          • 2. Re: finding two (or more) lowest values in a field

            That's brilliant. And if I use a portal I can also solve the next problem--which is I have to know how many possible points need to be subtracted from the total as well. Thanks Phil. I'll try to write the script.