Finding numerical gaps
Im trying to find a calculation idea that will scan a group of records, and look for gaps in serial numbers, and an idea to generate a list of missing numbers? Any help would be appreciated!
see if this helps:
It needs FM Advanced.
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
What about something like this ?
The calculation ( UNSTORED ) for the field Gaps is:
Let(prev = If ( Get ( RecordNumber ) > 1 ; GetNthRecord ( ID ; Get ( RecordNumber ) - 1 ) + 1 ; 1 ) ;Case(ID > prev ; prev & If ( prev ≠ ID - 1 ; " - " & ID - 1 ) ;))
( records must be sorted ascending by ID )
Edit: Yup, 100 Kudos ! ( Thank you LaRetta ;) )
Retrieving data ...