Report on Missing Numbers

[Will_(Guest)]

I need to report on missing item numbers.__The master database has mutiple entries for each item number, so I have sorted and grouped by item#. I tried to use the Previous() function, but this does not help because of the multiple records with the same item#. Also, there may be blocks of numbers missing.__Any ideas?__Thanks,__Will

[kfleming]

Here is how I did this.__I have a table with a numeric item field containing the following values:__Item__10015__10015__10017__10018__10019__10019__10022__10022____I used this as the master table. I then created an exact lookup relation from this table back to itself using a linking field with the expression:__RECNO(itemtable)+10014__and a flexlink index of the item field.__Item Link RelatedItem__10015 10015 10015__10015 10016__10017 10017 10017__10018 10018 10018__10019 10019 10019__10019 10020__10022 10021__10022 10022 10022____I then queried to include all records where the related item field was blank and printed the link field to show the missing records of:__10016__10020__10021____For this to work you need to have sufficient records in the master file so that the last record^s calculated link matches the highest value you are seeking and really any file with enough records will serve as the master.____Sort of funky but it works.____Kathleen__R&R Support

[Will_(Guest)]

I will give it a try.__But what^s a flexlink index?__BTW, I^m using R&Rv8.1__Thanks!

[M._Hoffmann_(Guest)]

A flexlink index is available in Version 9. It allows you to build an index on the fly. For Version 8.x you would need to build the index using your database software.____Kathleen__R&R Support

[kfleming]

Hi Will,____I think the PREVIOUS function is the best solution here. I^d check__if item# and PREVIOUS(item#) are identical or if their delta is 1. If not, one ore more items must be missing.____Kind regards,____Michael.

[Will_(Guest)]

I tried to use the Previous() function, but this does not help because of the multiple records with the same item#.

[M._Hoffmann_(Guest)]

Hi Will:____If item numbers can occur multiple times, then item#-PREVIOUS(item#) must be either 0 or 1. Any other value indicates a missing item. ____I once did a report for "Missing delivery note numbers" and I chose this method. ____Kind regards,____Michael.____