7 Replies Latest reply on Mar 30, 2011 12:33 PM by Dittim

    Get the last 3 records

    Dittim

      Title

      Get the last 3 records

      Post

      I am creating a golf database and handicap is bases on the last 3 previous scores.  I tried using a portal to filter the records but I can't make that work.  How do I select the previous 2 scores to calculate handicap? Thanks for your help...

        • 1. Re: Get the last 3 records
          K|Z

          if u wanna select the last 3 records of your entire table you could do

          go to record (last)             select the last record of your table

          go to record (previous)      select the last-1 record of your table

          go to record (previous)      select the last-2 record of your table

          but i u wanna select the last to records for every selected record (the 2 records that comes just before the selected record ), you could use a script and a multiple "Set Variable" in a "loop".

          K|Z 

          • 2. Re: Get the last 3 records
            philmodjunk

            If you sort your portal records by date or an auto-entered serial number in descending order and limit the portal to three portal rows, your portal will display only the three most recent scores.

            • 3. Re: Get the last 3 records
              Dittim
              Thank you for you reply Phil. If there was a way to run calculations on the limited portal, I think this would be the best answer but everything that I've read says that calculations will be bases on the entire table, not just the rows that are shown... Thanks,
              • 4. Re: Get the last 3 records
                philmodjunk

                Actually, the calculations will refer to the first related record. Which can be the most recent record if you sort your portal records by specifying the sort in the relationship rather than in the portal.

                You may be able to get this to work in the portal. If not, it will work in a sorted found (Sorted so most recent score is first) set of one player's golfscores by defining this unstored calculation in the scores table:

                (Score + GetNthRecord ( Score ; 2 ) + getNthRecord ( Score ; 3 ) ) / 3

                That's just the average, but you you should be able to figure out the handicap from there.

                • 5. Re: Get the last 3 records
                  Dittim

                  This calculation looks really close, but I can't get it to work.

                  In the main layout, I sorted the data by score date (decending).

                  If I do a find for a specific PlayerID.  The find shows a single player with newest scores on top.

                  I have a field in the table called Calc and set an "Auto-Enter, Calculated Value" to the calculation you suggested (I removed the "/3" so that it would show a total aggregate score that i could check against).  I don't get any errors or warnings, but the field doesn't show a correct total.  It seems to be pulling from the entire list (without any sort).  If I remove the sort and total the first 3 rows, the calculation is correct...

                  I'm still trying different variations, but wanted to let you know that I appreciate your help and will keep trying...

                  • 6. Re: Get the last 3 records
                    philmodjunk

                    This should not be used with an auto-entered calculation. This should be a field of type calculation and storage options must be used to set it up as Unstored.

                    • 7. Re: Get the last 3 records
                      Dittim

                      Whooooaaaa!  Now we're getting somewhere...  This seems to do the trick.  

                      I do have the Calc field set up as a calculation with the formula you gave me, actually I changed it to look like below so that I could be sure that I was getting the first 3 records.

                      ( GetNthRecord ( TotalScore ; 1 ) + GetNthRecord ( TotalScore ; 2 ) + GetNthRecord ( TotalScore ; 3 ) )

                      No matter how I sort the records, this formula seems to be adding the first 3 records!

                      Now I just have to figure out how to make this work for the different layouts that I'm using for data entry and reports.

                      Thank you again for your help, it is most appreciated...!