Quickest way to move up and down in a data sheet

Panorama "Classic"
1

In Panorama 6, have about 100 databases, each with about 30,000 to 100,000 records. Each includes a record counter field called Kount. For some thousand or more of these records, I need to compare the value of a field in the current record with the values of that field two records up and two records down. Obviously, I need to find the most efficient way to do these comparisons.

The simplistic approach is to do two UpRecord, four DownRecord and two UpRecord commands.

Another is to do a Find for Kount two less than the current record, then for Kount two more and then the current Kount.

Finally, I could put the whole database (only six fields) into an array and use the array( function to extract the values.

I’m in the process of setting up a test procedure to time a couple of thousand of each of these methods but I would appreciate any comments on the options and I’d love to hear about a much more efficient solution.

2

I just tested 10,000 iterations of four UpRecords, four DownRecords and three data extraction statements, triggered from an empty form and it took 7 seconds! Clearly, there is little point in seeking a more efficient solution.

One interesting thing I found is that, if the form contains a data cell for the Kount field, the run time increases over 100-fold to 735 seconds.

3

I don’t have a comparable file to test with so I have no idea if this method would be any more efficient than what you are currently doing. Here is an alternate approach for what it may be worth…

local theKount
theKount=Kount
noshow
find Kount>theKount-3 and Kount<theKount+3
 //comparisson & assignment here of 1st found record
for i,1,4
    nextmatch
    If Kount<>theKount //skips starting record
        //comparisson & assignment here of succeeding records
    endif
endloop
find Kount=theKount  //return to start record
endnoshow
4

You are using a form. IMHO the simplest way to do your comparison ist to use a Text object with a lookup formula like this:

{lookup("YourDatabase","Kount",(Kount-2),"YourComparisonField","",0)}
{lookup("YourDatabase,"Kount",(Kount+2),"YourComparisonField","",0)}

You can combine this with a calculation to deliver the result of the comparison. The result could be e.g. “MATCH” or “NO MATCH”.

5

Thanks guys. Kurt’s approach looks the best suited to my needs - I’ll give it a try.