Find missing numbers

[JMart]

hi all,

need desperate help in putting together a script to find missing numbers between the lowest and highest.

e.g.  1, 2.4,6,10

need a report to show the missing numbers 3,5,7,8,9

help is greatly appreciated

Thanks. 

[comment]

Where are the existing numbers? 

[JMart]

thanks for the quick reply,

The database is very simple; there are 5 fields the two key fields are "invoice No" and "Invoice Date", a users populates these two fields, i basically need a script that will find the missing invoice numbers between the lowest and highest numbers.

 

Thanks 

 

[comment]

So they are in individual records? If so, you could do something like:

# FIND THE RELEVANT RECORDS
Show All Records
# SORT BY INVOICE NUMBER, ASCENDING
Sort Records [ Restore; No dialog ]
Set Variable [ $existingValues; Value:YourTable::sList ]
Set Variable [ $n; Value:GetValue ( $existingValues ; 1 ) ]
Set Variable [ $max; Value:GetAsNumber ( GetValue ( $existingValues ; ValueCount ( $existingValues ) ) ) ] 
Loop
  If [ IsEmpty ( FilterValues ( $n ; $existingValues ) ) ]
    Set Variable [ $missingValues; Value:List ( $missingValues ; $n ) ]
  End If
  Set Variable [ $n; Value:$n + 1 ] 
  Exit Loop If [ $n > $max ]
End Loop

At the end of the loop, the $missingValues variable should contain a return-separated list of the missing numbers. The sList field is a summary field defined as List of invoice No. If you prefer, you can eliminate it by doing a loop among the found set to collect the existing numbers.

Caveat:
You did not say how this is going to be used. It is good practice to use an auto-entered serial number to number invoices. It is also good practice to make sure there are no missing numbers by (a) assigning the number on record commit only and (b) preventing the deletion of committed records. It is certainly not good practice to try and reuse any numbers that may be missing.

 

[JMart]

thanks for the script, i tried the above, but for some reason when i create a layout and place the sList field to show the list of missing invoices it shows the first number only repeated.

Thanks for the advice, in this instance all we need to do is collect a all invoices generated by the ERP and enter the number to keep track if the sales person turned the invoice into the accounts department, they were using excel but it got to be too many invoices.  

[comment]

9 minutes ago, JMart said:

when i create a layout and place the sList field to show the list of missing invoices it shows the first number only repeated.

The sList field is not for showing missing invoices. It is meant to list the existing invoices. The script takes this list and generates the list of missing numbers into a variable.

See if the attached demo makes it clearer.

 

MissingNumbers.fmp12