Identify missing numbers from a sequence 1

[ianoctdec]

Hello everyody,
Can somebody to give me an idea on how to create a Crystal report that will show and print all the missing values from a given range of numbers?
I have a table with a field named Invoice_ID. The report will receive as parameter the Invoice Number Range. The output has to be: starting with the lowest number all invoices numbers that are missing from the sequence.
Ex:
InvoiceID
1
3
6
9
Invoice Number Range Parameter 1 - 10.
My output should be: 2, 4, 5, 7, 8, 10

I'm using Crystal 10
Database Oracle9, ODBC connection.

Many thanks,
Dana

 

[lbass]

I think this solution is an expansion/variation of one presented by SynapseVampire quite a while ago. Create the following formulas:

//{@?startno_to_first_number} - this captures invoice numbers
//missing between the start parameter and the first record:
whileprintingrecords;
numbervar start := {table.invoice_ID};
numbervar x;
stringvar result1;

if start - {?startno} > 0 and
start = minimum({table.invoice_ID}) then
for x := 0 to (start - 1- {?startno}) do(
result1 := result1 + totext(({?startno} + x),0,"")+ chr(13)
) ;
result1;

//{@between_first_and_last_record}:
whileprintingrecords;
numbervar start := {table.invoice_ID};
numbervar end := next({table.invoice_ID});
numbervar x;
stringvar result2;

if end - start > 1 then
for x := 1 to (end-2 - start +1) do(
result2 := result2 + totext({table.invoice_ID} + x,0,"")+chr(13)
);
result2

//{@between_last_record_and_?end} - this captures numbers
//between the last record and the end parameter:
whileprintingrecords;
numbervar end := next({table.invoice_ID});
numbervar x;
stringvar result3;

if {?endno} - end > 0 and
end = maximum({table.invoice_ID}) then
for x := 1 to ({?endno} - end) do(
result3 := result3 + totext(end + x,0,"")+chr(13)
);
result3;

Place the above three in the details section and suppress them. Then create a fourth formula:

//{@display} to be placed in the report footer:
whileprintingrecords;
stringvar result1;
stringvar result2;
stringvar result3;
result1 + result2 + result3

If you want to use a range parameter, you would substitute:

minimum({?numberrange})//for {?startno}

//and

maximum({?numberrange}) //for {?endno}

-LB

 

[ianoctdec]

Thank you lbass for your quickly and detailed replay. I'll try it and let you know about my progress.
Thank you again,
Dana

 

[ianoctdec]

The formulas are working ok, i'm getting the correct results. My problem is that if the report has more than one page, first page is empty. Is there any way to fix that?

Thank you
Dana

 

[synapsevampire]

Try turning off the "Keep Together" option for the section.

If that fails, try creating 3 formulas, each containing:

//{@display} to be placed in the report footer:
whileprintingrecords;
stringvar result1;

whileprintingrecords;
stringvar result2;

whileprintingrecords;
stringvar result3;

Drop them all into a text object and turn off the keep together, that might do it.

-k

 

[nvtjellis]

I'm basically entering the suggested code as is, with the exception I must convert my IDCodes.IDCode to numeric. However, during the syntex checking process the numbervar X comes up as needing to be a string in the following code.

//{@?startno_to_first_number} - this captures invoice numbers
//missing between the start parameter and the first record:
whileprintingrecords;
numbervar start := {table.invoice_ID};
numbervar x;
stringvar result1;
///// It is in this code I get the message
hileprintingrecords;
numbervar start := tonumber ({IDCodes.IDCode});
numbervar x;
stringvar result1;

==== the message I get is "A string is required here" for the numberva X below. ========

whileprintingrecords;
numbervar start := tonumber ({IDCodes.IDCode});
numbervar x;
stringvar result1;
//
if start - tonumber ({?startno}) > 0 and
start = minimum ({@startnum}) then
for x := 0 to (start - 1- tonumber ({?startno}))
do(result1 := result1 + totext(({?startno} + x),0,"")+ chr(13)) ;
result1;

 

[lbass]

Why are you making {?startno} a string parameter? I intended it to be a number parameter. If you need it to be a string, you must use it that way consistently. note that in the next to last line you are treating it as if it is a number by using totext--but you need to make it a number first, as in:

if start - tonumber ({?startno}) > 0 and
start = minimum ({@startnum}) then
for x := 0 to (start - 1- tonumber ({?startno}))
do(result1 := result1 + totext((val({?startno}) + x),0,"")+ chr(13)) ;
result1;

But I think it makes more sense to start with {?startno} as a number parameter and to use the original code.

-LB