INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Identify missing numbers from a sequence

Identify missing numbers from a sequence

(OP)
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

RE: Identify missing numbers from a sequence

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

RE: Identify missing numbers from a sequence

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

RE: Identify missing numbers from a sequence

(OP)
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

RE: Identify missing numbers from a sequence

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

RE: Identify missing numbers from a sequence

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;

RE: Identify missing numbers from a sequence

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close