How to Find missing records?

[aproddut]

I am trying to write a crystal report which lists missing records.... The following eg would be useful

I have a Ora Table which has 3 records . Assuming the column name is serial number it has the following records

SNo
----
1
5
10

From the above data the min is 1 and max is 10. My report should be able to print the missing serial numbers as follows

Output Should be
----------------
2
3
4
6
7
8
9


How can I achieve this ? Any tips ? help?

Thanks,
Ash

 

[goranm]

Hello !

I have had similar problem and solved it like this:
(maybe it isn´t the best way, but it worked for me)

Create a formula:

NumberVar missing_no := {Table.Serialno} +1;
Local StringVar output_string;

if not OnLastRecord then
(
if next({Table.Serialno}) > missing_no then
while next({Table.Serialno}) > missing_no do
(
output_string := output_string + totext(missing_no,0) + chr(13);
missing_no := missing_no + 1;
);

output_string;
);

Place the formula in the detail section and format it with "Can Grow".

If it is a big gap between some of your numbers you may have trouble with the limitation of 255 charcters in a string.

Hope it will help you.

/Goran

 

[chelseatech]

We did a whole series of items on missing data in prior issues of Crystal Clear. Download the back issues as the subject was developed over several months.

To solve your specific problem, you can use Next() and Previous() functions to identify the missing items.

if onfirstrecord then "" else // Doesn't work on first rec
If {table.Sno} = Previous({table.Sno}) + 1 then "" else
If {table.Sno} = Previous({table.Sno}) + 2 then
totext({table.Sno}- 1,0) //one item missing
else
totext(Previous({table.Sno}) + 1,0) + " to " +
totext({table.Sno} - 1,0) //several missing

You could replace the final else clause with a "For" loop to build up seperate lines for each missing item, but I've found it sometimes creates a string of more than 254 chars Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[aproddut]

thanks for the answers but my work place is using crystal 7. Is there a possibility to solve this issue in crstal 7 ?

thanks

 

[dgillz]

chelseatech's solution should work in CR v7. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com