I do this in a report to find missing check numbers in a checkbook application. But, you can set it up faster in a Mass Update. Here's how:
1. Sort your records in ascending order on the field you want to check.
2. Place this in the
Global Code event of the Mass Update spec:
stat sPrevNo as int
stat sCounter as int
stat sList as string
3. Place this in any other Mass Update event:
var vMissing as int
sCounter += 1
If sCounter > 1
{
If @TN(Check No) > (sPrevNo + 1)
{
vMissing = (@TN(Check No) - sPrevNo) - 1
sList += "There's " + vMissing + " number(s) missing, starting with " + @Str(sPrevNo + 1) + @NL()
}
Else If @TN(Check No) = sPrevNo
sList += @Str(sPrevNo) + " is a duplicate!" + @NL()
}
If sCounter = @ResultSetTotal()
WriteLn(sList)
// Create a delayed copy of Check No to compare with next record.
sPrevNo = @TN(Check No)
You will need to replace "Check No" with the name of the element that contains the numbers you want to check. BTW, I enclosed Check No inside of @TN() because, in my app, that element is bound to a TEXT field. If the element you are checking is bound to a NUMBER field, you can remove the @TN()'s, but it wouldn't hurt to leave them in there in any case.
That's it. Just run the Mass Update, and if there are any duplicates or missing numbers the slate (WriteLn window) will display them when the update is finished.
Good luck!