6 Replies Latest reply on May 16, 2009 2:00 PM by raybaudi

# Finding numerical gaps

### Title

Finding numerical gaps

### Post

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!

• ###### 1. Re: Finding numerical gaps

Hi Larry

see if this helps:

http://www.briandunning.com/cf/609

• ###### 2. Re: Finding numerical gaps
Any solutions that will work as far back as FMP 8?
• ###### 3. Re: Finding numerical gaps
It will work for FileMaker Pro 8 - you just need FileMaker Pro Advanced to create the custom function.
• ###### 4. Re: Finding numerical gaps

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

Another option:

Create a master list of all serial numbers from the existing lowest to the existing highest

Issue a find command to find the missing serials

Import the missing ones into your existing records

• ###### 5. Re: Finding numerical gaps
Good plan. I will try that. Thanks!
• ###### 6. Re: Finding numerical gaps

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 ;) )