Any solutions that will work as far back as FMP 8?
It will work for FileMaker Pro 8 - you just need FileMaker Pro Advanced to create the custom function.
Assuming one serial number per record, it's going to look something like this:
Sort the records into serial number order
Have a script loop through each record
Use a global field to store the current serial number so you can check it when you get to the next record
If it's more than 1 different you start a second loop to generate the missing numbers--I'm thinking into a second global field separated by carriage returns.
After you've looped through all the records, loop through the 2nd global field and create records for all the numbers you've populated in there
Create a master list of all serial numbers from the existing lowest to the existing highest
Create a link from the master list to your existing records
Issue a find command to find the missing serials
Import the missing ones into your existing records
Good plan. I will try that. Thanks!
What about something like this ?
The calculation ( UNSTORED ) for the field Gaps is:
prev = If ( Get ( RecordNumber ) > 1 ; GetNthRecord ( ID ; Get ( RecordNumber ) - 1 ) + 1 ; 1 ) ;
ID > prev ; prev & If ( prev ≠ ID - 1 ; " - " & ID - 1 ) ;
( records must be sorted ascending by ID )
Edit: Yup, 100 Kudos ! ( Thank you LaRetta ;) )