×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Identify missing numbers in from sequence

## Identify missing numbers in from sequence

### RE: Identify missing numbers in from sequence

You could try grouping by a field which is Unique, ie returns a group for each record.
Suppress details and use the group footer as your detail line.

Ian

### RE: Identify missing numbers in from sequence

Try using this formula instead of the earlier solution. Place it in the detail section:

whileprintingrecords;
numbervar array x;
numbervar diff := 0;
numbervar i;
stringvar y := "";

if {table.number}<>next({table.number})-1 then (
diff := next({table.number})-{table.number};
for i := 1 to diff-1 do(
redim preserve x[i];
x[i]:={table.number}+i;
if i=diff-1 then
exit for
));

for i := 1 to ubound(x) do(
if {table.number}=next({table.number})-1 then
y := "" else
y := y + totext(x[i],0,"") + ", ";
);
if len(y)>=2 then
left(y,len(y)-2)

This will display the missing numbers between the current value and the next.

-LB

### RE: Identify missing numbers in from sequence

(OP)

Hi LB!

This is better. Is there a way I can put the formula in the same column as the series? Thanks.

### RE: Identify missing numbers in from sequence

Why do you want to do this? You could probably do this, but you would not be creating new rows, you would just be growing the row to accommodate the missing numbers. Knowing the purpose would be helpful.

-LB

### RE: Identify missing numbers in from sequence

(OP)

I just want the missing series in the same column with the existing series even if its the only field in the row.

### RE: Identify missing numbers in from sequence

Place this formula in the detail section and format it to "can grow":

whileprintingrecords;
numbervar array x;
numbervar diff := 0;
numbervar i;
stringvar y := "";

if {table.number}<>next({table.number})-1 then (
diff := next({table.number})-{table.number};
for i := 1 to diff-1 do(
redim preserve x[i];
x[i]:={table.number}+i;
if i=diff-1 then
exit for
));

for i := 1 to ubound(x) do(
if {table.number}=next({table.number})-1 then
y := "" else
y := y + totext(x[i],0,"") + chr(13);
);
if len(y)>=1 then
totext({table.number},0,"")+chr(13)+left(y,len(y)-1)

-LB

### RE: Identify missing numbers in from sequence

(OP)

In your earlier formula, this message appears in my report?

"An array's dimension must be an integer between 1 and 1000."

Then if I close it, this part of formula is highlighted.

redim preserve x[i]

### RE: Identify missing numbers in from sequence

(OP)

Uhm.. Don't mind my last post. I tried your recent formula and no message appeared but it also shows the number before the missing number so it appears twice in the report.

### RE: Identify missing numbers in from sequence

(OP)

I changed my parameter. The message still appears on a particular page and if I click on next page it goes back to page 1. I prefer your recent formula.

thanks.

### RE: Identify missing numbers in from sequence

Remove the original field and replace it with the formula, since the field is already built in. Change the formula to this:

whileprintingrecords;
numbervar array x;
redim x; //this resets the array
numbervar diff := 0;
numbervar i;
stringvar y := "";

if {table.number}<>next({table.number})-1 then (
diff := next({table.number})-{table.number};
for i := 1 to diff-1 do(
redim preserve x[i];
x[i]:={table.number}+i;
if i=diff-1 then
exit for
));

for i := 1 to ubound(x) do(
if {table.number}=next({table.number})-1 then
y := totext(x[i],0,"") else //this makes the field display if there is no gap
y := y + totext(x[i],0,"") + chr(13);
);
if len(y)>=1 then
totext({table.number},0,"")+chr(13)+left(y,len(y)-1)

-LB

### RE: Identify missing numbers in from sequence

(OP)

The formula returns the last series blank. This what my report looks like:

5447649 is the missing series. The formula returns it together with the series before it.

date check no
5/12/17 5447647
5/12/17 5447648
5447648
5447649
5/15/17 5447650
5/15/17 5447651
5/17/17 5447652
5/17/17 5447653
5/17/17 5447654
5/17/17

Thanks.

### RE: Identify missing numbers in from sequence

Please copy the formula into this post, so I can troubleshoot it. I did test this here, and it didn't repeat the value.

To correct for the last record, change the last section to start with:

For i := 1 to unbound(x) do (
If onlastrecord or
{table.number} = next({table.number})-1 then
Y := totext(x[i],0,"") else //etc.

Onlastrecord will work if you are not doing this within a group section.

-LB

### RE: Identify missing numbers in from sequence

Still need to see your formula to tell why the number is duplicated. Can you confirm that if you remove the formula there is no duplication?

Please use the following formula instead of earier versions--there was one more change necessary so that the last row shows properly.

whileprintingrecords;
numbervar array x;
redim x;
numbervar diff := 0;
numbervar i;
stringvar y := "";

if not onlastrecord and //added this
{table.number}<>next({table.number})-1 then (
diff := next({table.number})-{table.number};
for i := 1 to diff-1 do(
redim preserve x[i];
x[i]:={table.number}+i;
if i=diff-1 then
exit for
));

for i := 1 to ubound(x) do(
if onlastrecord or //added this earlier
{table.number}=next({table.number})-1 then
y := totext(x[i],0,"") else
y := y + totext(x[i],0,"") + chr(13);
);
if len(y)>=1 then
totext({table.number},0,"")+chr(13)+left(y,len(y)-1)

-LB

### RE: Identify missing numbers in from sequence

(OP)
There's no more blank series. Below is my formula for missing series. I placed it in GFb then suppress section if blank. My report is grouped by APPJH.IDRMIT. My fields in section GFa are APPYM.DATERMIT, your most recent formula, APPYM.TEXTPAYOR, APPJH.TEXTREF, APPYM.AMTPAYM and APTCR.TEXTRMIT.

whileprintingrecords;
numbervar array x;
numbervar diff := 0;
numbervar i;
stringvar y := "";

if {@check no 2}<>next({@check no 2})-1 then (
diff := next({@check no 2})-{@check no 2};
for i := 1 to diff-1 do(
redim preserve x[i];
x[i]:={@check no 2}+i;
if i=diff-1 then
exit for
));

for i := 1 to ubound(x) do(
if {@check no 2}=next({@check no 2})-1 then
y := "" else
y := y + totext(x[i],0,"") + chr(13);
);
if len(y)>=1 then
totext({@check no 2},0,"")+chr(13)+left(y,len(y)-1)

Thanks.

### RE: Identify missing numbers in from sequence

What is the current issue?

Also, you are not using my most recent formula. Please update the formula and then report back on the remaining issue.

However, my formula was not designed to work in a group section. Please show the content of your formula {@check no 2} so I can tell how it relates to your fields.

-LB

### RE: Identify missing numbers in from sequence

Okay, I just tried my most recent formula (NOT the one in your most recent post) in GFa and it worked perfectly. Assuming your group is on the field referenced in my formula ({table.number}), just remove the group field from GFa. My formula will return that field plus the missing the numbers.

-LB

### RE: Identify missing numbers in from sequence

(OP)

Do I still need your first post in July 3? I forgot to tell you that unbound was highlighted and there was an error saying "A number, currency amount, Boolean, date,time, or string is expected here".

{@check no 2} is tonumber({APPJH.IDRMIT})

I cannot put everything in details section. Data is repeated.

My report is still grouped by check no.. I placed your 2nd post in July 3 in GFa but it did not show the missing numbers.

whileprintingrecords;
numbervar array x;
redim x;
numbervar diff := 0;
numbervar i;
stringvar y := "";

if not onlastrecord and
{@check no 2}<>next({@check no 2})-1 then (
diff := next({@check no 2})-{@check no 2};
for i := 1 to diff-1 do(
redim preserve x[i];
x[i]:={@check no 2}+i;
if i=diff-1 then
exit for
));

for i := 1 to ubound(x) do(
if onlastrecord or
{@check no 2}=next({@check no 2})-1 then
y := totext(x[i],0,"") else
y := y + totext(x[i],0,"") + chr(13);
);
if len(y)>=1 then
totext({@check no 2},0,"")+chr(13)+left(y,len(y)-1)

### RE: Identify missing numbers in from sequence

Did you format the formula to 'can grow'?

-LB

### RE: Identify missing numbers in from sequence

(OP) No. I thought its for 6/29 formula only. Can you do this on strings? Like your series is alphanumeric?

Thank you so much. I'm finished with my report.

### RE: Identify missing numbers in from sequence

So you have it working now, right?

Regarding the strings--there might be a way, but I'd have to see specifics to know how doable it would be.

-LB

### RE: Identify missing numbers in from sequence

(OP)

Yes its working.

For example, ARIBH.IDINV is a string. We manually enter alphanumeric document number like SI-0001, SI-0002 ... . Can you also identify missing series here?

Thanks.

### RE: Identify missing numbers in from sequence

Do the strings always start with the same two letters or can there be various letters? Would you need to determine whether there were missing letter combinations? Or only missing numbers within strings starting with specific letters?

You can easily remove the letters and then identify missing numbers. If you want more help on this, you should provide an example that includes all variations and also show the expected results for that example.

-LB

### RE: Identify missing numbers in from sequence

(OP)

I need to see the missing numbers of the alpha numeric series. Document number differs every branch so it can be various letters and numbers. There can be 2 to 4 letters separated by 4 to 6 numbers.

Example:

ABC-00200
ABC-00201
ABC-00202
DC-4568
DC-4569
DC-4570
ZB-001210
ZB-001211
ZB-001212

Thanks.

### RE: Identify missing numbers in from sequence

You could insert a group on this formula {@letterID}:

stringvar array z := split({table.ID},"-");
z

Then sort on a second formula {@valID}:
stringvar array z := split({table.ID},"-");
val(z)

Then use the earlier method to create a formula that fills missing values and format it to can grow:

whileprintingrecords;
stringvar array z := split({table.ID},"-");
numbervar array x;
redim x;
numbervar diff := 0;
numbervar i;
stringvar y := "";
numbervar w := len(z);

if not onlastrecord and
{@valID}<>next({@valID})-1 then (
diff := next({@valID})-{@valID};
for i := 1 to diff -1 do (
redim preserve x[i];
x[i]:={@valID}+i;
if i=diff-1 then
exit for
));

for i := 1 to ubound(x) do(
if onlastrecord or
{@valID}=next({@valID})-1 then
y := totext(x[i],0,"") else
y := y + {@letterID}+"-"+replicatestring("0",w-len(totext({@valID},0,"")))+totext(x[i],0,"")+chr(13);
);

if len(y)>=1 then
{table.ID}+chr(13)+left(y,len(y)-1)

-LB

### RE: Identify missing numbers in from sequence

(OP)

There's an error.

"An array's dimension must be an integer between 1 and 1000"

Then this part is highlighted:

redim preserve x[i];

### RE: Identify missing numbers in from sequence

So there can be more than 100 values between missing between two rows?

Are you sure you used my formula as presented?

-LB

### RE: Identify missing numbers in from sequence

(OP)

It's working. I just need to change my parameter so I won't encounter missing series more than 100.

Thank you so much!

### RE: Identify missing numbers in from sequence

I actually meant 1000 -- that's the limit. It would surprise me if you had gaps that large, so that made me wonder if you had used the formula exactly as shown.

Are you all set now?

-LB

### RE: Identify missing numbers in from sequence

FYI: From what I can tell Crystal Reports for Enterprise no longer has the 1000 element limit.

### RE: Identify missing numbers in from sequence

(OP)

Yes. We can have more than 1000 gaps in document no. Yes I am finished with my report now thanks to you. #### 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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!