Report on Missing Numbers

Meeting place for R&R customers and clients to share tips and ideas. Post your questions, messages or problems here.
Post Reply
Will_(Guest)
Posts: 7
Joined: Tue Oct 10, 2017 12:44 pm

Report on Missing Numbers

Post by Will_(Guest) » Tue Sep 10, 2002 9:42 am

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
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

=> RE: Report on Missing Numbers

Post by kfleming » Tue Sep 10, 2002 3:24 pm

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)
Posts: 7
Joined: Tue Oct 10, 2017 12:44 pm

==> RE: Report on Missing Numbers

Post by Will_(Guest) » Tue Sep 10, 2002 3:32 pm

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

M._Hoffmann_(Guest)
Posts: 23
Joined: Tue Oct 10, 2017 12:44 pm

=> RE: Report on Missing Numbers

Post by M._Hoffmann_(Guest) » Wed Sep 11, 2002 5:25 am

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
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

===> RE: Report on Missing Numbers

Post by kfleming » Wed Sep 11, 2002 7:27 am

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)
Posts: 7
Joined: Tue Oct 10, 2017 12:44 pm

==> RE: Report on Missing Numbers

Post by Will_(Guest) » Fri Sep 13, 2002 9:01 am

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

M._Hoffmann_(Guest)
Posts: 23
Joined: Tue Oct 10, 2017 12:44 pm

===> RE: Report on Missing Numbers

Post by M._Hoffmann_(Guest) » Wed Sep 18, 2002 3:52 am

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.____

Post Reply