Need a list of consecutive numbers 2

[amoeba102]

I think this is one of those deceptively simple problems. The desired end result is 1 page of 5 columns of consecutive numbers, beginning with a number obtained via a parameter.

I've been sick, so maybe my brain is still in bed, but I haven't been able to get this done.

 

[synapsevampire]

Crystal isn't designed to fabricate data well, and doing this many numbers would require several formulas for CR 8.5 and below as there is a maximum of 254 chars per formula.

Generally people would use a programming language or create a table of numbers to handle this.

Alternatively a Stored Procedure might serve you well.

Anyway, post meaningful information about your environment with an example of expected output.

-k

 

[lbass]

There are at least three approaches.

1-If you are adding other fields, then just add recordnumber to the details section along with the other fields, format the details section with a suppression formula:

recordnumber < {?startno}

...and then format the details section for multiple columns.

2-You could create a table of consecutive numbers, e.g., in Excel, and use a record select of:

{table.no} >= {?startno}

Add {table.no} to the details section and then go to format->section->details->use multiple columns->layout and specify column widths to result in five columns.

3-Another approach would be to create formulas like the following:

{formula1}:
whileprintingrecords;
numbervar counter1 := 0;
stringvar nos1 := totext({?startno},&quot;000&quot;

for counter1 := {?startno}+1 to {?startno}+ 49 do(
nos1 := nos1 + chr(13) + totext(counter1,&quot;000&quot);
nos1;

{formula2}:
whileprintingrecords;
numbervar counter2 := 0;
stringvar nos2 := totext({?startno}+ 50,&quot;000&quot;

for counter2 := {?startno}+51 to {?startno}+ 99 do(
nos2 := nos2 + chr(13) + totext(counter2,&quot;000&quot);
nos2;

{formula3}:
whileprintingrecords;
numbervar counter3 := 0;
stringvar nos3 := totext({?startno}+ 100,&quot;000&quot;

for counter3 := {?startno}+101 to {?startno}+ 149 do(
nos3 := nos3 + chr(13) + totext(counter3,&quot;000&quot);
nos3;

Do the same for formulas 4 and 5. Then format each formula to &quot;can grow&quot;. This assumes that each column has 50 numbers.
Place the fields next to each other in the details section.

The disadvantage of the last approach is that the series of numbers in each column is actually only one field, so if you plan to add other fields, this approach probably won't work for you.

-LB

 

[amoeba102]

Thanks! #3 is exactly what I needed!!

 

[Ngolem]

the only problem with lbass's method is that you are limited to numbers that are 254/50 = 5 characters each and possibly 4 characters since the <CR> should probably be included.

There is no protection in his formula for exceeding 254 chars....so in order to determine if 5 formulas are required or 10 formulas or more....will have to depend on the type/size of number that you are proposing.

This critical planning information has not been provided by you

In fairness to Lbass...he is describing a concept...not a final answer....this will work and is simple to do...once you have given us proper information as it stands there is a good chance it will eventually fail

Give us example numbers and the max size you intend to use.

I have a hard time imagining the usefulness of a single page of numbers...unless they are tickets of some kind and I doubt you start at 1,2,3,4...

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[amoeba102]

They are tickets of a sort - simply batch numbers that are marked off as they are used. They will never exceed 99999, and then will start over again at zero. Low tech, but it works well for our application. Sorry about the incomplete problem statement. Thanks for your response.

 

[Ngolem]

well I believe you will have problems using these formulas with numbers greater than 9999 due to the 254 char limit... the numbers that are 10000 and greater have six characters when you include the <CR> so you will be able to have only 254/6 = 41 numbers before you break the 254 char limit.

You should test this if you are going to cast this in stone

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[nagornyi]

There is always a simple SQL solution, the syntax may depend on the DB though. For example, in Oracle:

select rownum from TABLE

returns

1
2
3
4
... and so on to the total number of recored in the TABLE.
If we do the following:

select rownum+1000 from TABLE where rownum<6

we get

1001
1002
1003
1004
1005

It is clear how to extend this approach to introduce the parameter and to get the desired report.

 

[synapsevampire]

Simple in Oracle, though Oracle can be a real pain in the arse for some functionality (% of rows for instance). And SQL Server doesn't handle row numbers well.

But that's the best belly laugh I've had in a month &quot;There is always a simple SQL solution&quot;. That must be why real SQL coders (not the avg. dba's) all get minimum wage, and there aren't any books on the market.

-k