## Identify missing numbers in from sequence

## Identify missing numbers in from sequence

(OP)

thread149-912093: Identify missing numbers from a sequence

Hi Ibass. I tried your formula. It worked! But can I placed it in details so I could see the series? Thanks.

## RE: Identify missing numbers in from sequence

Suppress details and use the group footer as your detail line.

Ian

## RE: Identify missing numbers in from sequence

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

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

-LB

## RE: Identify missing numbers in from sequence

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

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

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

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

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

whileprintingrecords;

numbervar array x;

redim x[1]; //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

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

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

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[1];

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

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

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

-LB

## RE: Identify missing numbers in from sequence

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[1];

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

-LB

## RE: Identify missing numbers in from sequence

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

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

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

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

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

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

z[1]

Then sort on a second formula {@valID}:

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

val(z[2])

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[1];

numbervar diff := 0;

numbervar i;

stringvar y := "";

numbervar w := len(z[2]);

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

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

Are you sure you used my formula as presented?

-LB

## RE: Identify missing numbers in from sequence

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

Are you all set now?

-LB

## RE: Identify missing numbers in from sequence

## RE: Identify missing numbers in from sequence

Yes. We can have more than 1000 gaps in document no. Yes I am finished with my report now thanks to you.