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.

 

[IanWaterman]

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

 

[lbass]

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;
x:={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,0,"") + ", ";
);
if len>=2 then
left(y,len-2)

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

-LB

 

[ejastia]

Hi LB!

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

 

[lbass]

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

 

[ejastia]

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

 

[lbass]

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;
x:={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,0,"") + chr(13);
);
if len>=1 then
totext({table.number},0,"")+chr(13)+left(y,len-1)

-LB

 

[ejastia]

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

 

[ejastia]

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.

 

[ejastia]

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.

 

[lbass]

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[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;
x:={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,0,"") else //this makes the field display if there is no gap
y := y + totext(x,0,"") + chr(13);
);
if len>=1 then
totext({table.number},0,"")+chr(13)+left(y,len-1)

-LB

 

[ejastia]

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.

 

[lbass]

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,0,"") else //etc.

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

-LB

 

[lbass]

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[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;
x:={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,0,"") else
y := y + totext(x,0,"") + chr(13);
);
if len>=1 then
totext({table.number},0,"")+chr(13)+left(y,len-1)

-LB

 

[ejastia]

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;
x:={@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,0,"") + chr(13);
);
if len>=1 then
totext({@check no 2},0,"")+chr(13)+left(y,len-1)




Thanks.

 

[lbass]

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

 

[lbass]

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

 

[ejastia]

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;
x:={@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,0,"") else
y := y + totext(x,0,"") + chr(13);
);
if len>=1 then
totext({@check no 2},0,"")+chr(13)+left(y,len-1)

 

[lbass]

Did you format the formula to 'can grow'?

-LB

 

[ejastia]

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.