There's probably a better method, but one way I can think of offhand is to have a utility table that has all the possible numbers in it, one number per record. Then, establish a relationship from that table to the table you want to monitor for gaps. Do a Find for all records in your "every number" table that don't have a match in the "to be monitored" table. The lowest number resulting from that Find will be your lowest number.
If I understand the question:
This sample file has a calculation to mark a gap in the peg numbers.
Mind you, only because you declared that there is a relatively small and fixed number of pegs is this approach reasonable.
Naturally, you can jazz it with the List Function etc., up but this might help get you started.
test sequential.fp7.zip 6.7 K
Thanks Mike, I created the utility table and populated the first 100 records with 1 to 100 values.
The relationship is:
Site::PegNumber = Utility::Numbers
A button on the Utility table fires this script:
Allow User Abort [ Off ]
Show All Records
Enter Find Mode [ Specified Find Requests: Find Records; Criteria: Utility::Numbers: “= Site::PegNumber” ]
[ Restore ]
Perform Find [ ]
Exit Script [ ]
The above doesn't work and when I use the Script Debugger, the 'criteria' statement is displayed in the field just before the find, which can't be right. How do I write this find request properly?
Thanks Chuck, I'll have a look at your file now...
Peg number 94 is missing from my Site::PegNumber field but I can't get the script to return this number unfortunately.
I'm wondering whether I've got the relationship wrong?
Brilliant, I added your setup to my file and created a scripted find to locate all records with a "Missing" value of 1
Sorted these records in ascending order by the PegNumber field, jumped to the 1st record and set that value as a variable
It works great, many thanks Mike...