Identify missing numbers in from sequence 1

[ejastia]

thread149-912093


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

 

[lbass]

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

 

[ejastia]

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.

 

[lbass]

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

 

[ejastia]

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.

 

[lbass]

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

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;
x:={@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,0,"") else
y := y + {@letterID}+"-"+replicatestring("0",w-len(totext({@valID},0,"")))+totext(x,0,"")+chr(13);
);

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

-LB

 

[ejastia]

There's an error.

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


Then this part is highlighted:

redim preserve x;

 

[lbass]

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

Are you sure you used my formula as presented?

-LB

 

[ejastia]

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

Thank you so much!

 

[lbass]

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

 

[kray4660]

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

 

[ejastia]

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