display all numbers between 'a' and 'b'

[BizzyLizzy]

Hi guys - hope someone can help.

In my Customer table for each customer record I have two fields which hold 'first number' (eg 10001) and 'last number' (eg 10230). What I would like to be able to do (if it is possible!) is to print all the numbers between "first number" and "last number". I am sure that this is something to do with groups but I am blowed if I can work it out. I am using CR 8.5.

thanks very much

 

[dgillz]

Clarification: Do you want to print all of the numbers between those two values, or do you want to print all of the records in your database that are between those 2 values?





Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[BizzyLizzy]

thanks for the reply.

I would like to print 'all' the numbers between firstnum and lastnum. Having thought about this some more today, I am not actually sure that its possible without creating a temp table or something in the database.

 

[dgillz]

You are correct, crystal only reads the database. Without a table of some type, there is no way of assuring that every number in between the 2 numbers will be displayed.

What is your database type?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[synapsevampire]

You could do this through a series of arrays, but you're better served to do it with a temp table or some such.

CR 8.5 has a maximum 254 output for formulas (character length), so judging by the amount of numbers you want to fabricate, it would take a good number of formulas to accomplish this.

-k

 

[BizzyLizzy]

many many thanks for the replies guys.

I had thought of arrays but as you say - I ran up against the problem of the 254 limit.

At the moment we are only mucking about with this on an access database but it will be an sql database. I will have to look into how to create a temp table I think. The trouble is that I am not really a database expert. I know enough about it to be able to look up data in the table and write simple ish queries but anything more complicated than that is a bit out of my area. Still I have got the SQL for Dummies book - maybe I should look through that!!!!

thanks again for the replies though.

Liz

 

[dgillz]

I would create a table called "numbers" and populate it from 1 to whatever you think is a reasonable maximum. Then you can use it in crystal as a tabke at any time to address issues like this.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[BizzyLizzy]

hmmm its a thought, the only trouble is that we are talking very large numbers here which is why I thought about temp tables that get dropped once the details have been extracted.

Many thanks for your suggestions. This is a great site, shame I didnt discover it sooner, would have saved myself a whole load of aggravation!.

 

[Ngolem]

I have a question here...

It is unclear to me what you really want.

In my Customer table for each customer record I have two fields which hold 'first number' (eg 10001) and 'last number' (eg 10230). What I would like to be able to do (if it is possible!) is to print all the numbers between "first number" and "last number".

So for one record for a customer, you want to list all of the numbers between the first and last?....nothing else???
Why??? and in what format?...a comma delimited string of some sort?

Something like this? for say 10001 to 10026

10001,10002,10003,.....10025,10026

It can certainly be done...easily actually using arrays. What is the maximum spread between the numbers....what is the maximum size of an individual number?

let us say 999999 is the largest number.there would be 6 numeric characters + one character for the comma for a total of 7 chars...or in 254char this translates to 36 numbers per element in an array....there could be more if the numbers could be smaller.

So in your example of 10001 to 10230 you would require 230/36 about 7 array elements to do the job. A Question here is .....is this a hi spread or a low spread...or just a number tossed out of the blue?

So let us plan for 100 array elements for now

Your report would consist of 100 detail subsections...each with one of the Array elements in it in a formula that stretches the width of the page. Each detail section would have "suppress Blank Section" enabled

In the Group header for Customer ID? you would have the formula

//@Initialize
WhilePrintingRecords;
StringVar array numbers := ["","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","",""];
""; //this added to make the formula legal

In the first detail subsection (a) place the formula

//@createnumberlist ( Suppressed in Detail A)

WhilePrintingRecords;
StringVar array numbers;
NumberVar temp;
NumberVar Pointer := 1;
StringVar Warning := "";
booleanVar flag := False;

//I am assuming the "first/last numbers" are numeric

for temp := {table.FirstNumber} to {table.LastNumber} do

(
if length(numbers[Pointer]) < 240 then
numbers[Pointer] := numbers[Pointer] + totext(temp,0) + &quot;,&quot;
else
(
Pointer := Pointer +1;
if Pointer > 100 then
(
flag := True;
Warning := &quot;Data Missing...too many numbers to report&quot;;
)
else
numbers[Pointer] := numbers[Pointer] + totext(temp,0) + &quot;,&quot;;
);
if flag then exit for;
);
//get rid of last comma
if Pointer > 100 then
numbers[100] := Left(numbers[100],length(numbers[100] - 1)
else
numbers[Pointer] := Left(numbers[Pointer],length(numbers[Pointer] - 1);


Now in Detail A you simply place the following formula

@displayNumber1

evaluateAfter({@createnumberlist})
StringVar array numbers;

numbers[1];

in Detail B a similar formula

@displayNumber2

WhilePrintingRecords;
StringVar array numbers;

numbers[2];

you would add 98 more formulas similar to the above.
Each formula would have its &quot;can Grow&quot; enabled....any of the section with array elements that are null (initialized that way) will be suppressed

In the Customer Group footer you would place the following formula

//@warning
whilePrintingRecords;
StringVar Warning;

Warning;

this will let you know if you have to do maintenance on your report to accomodate a larger number spread.

That is how you would do what you want....why you would want to do this is beyond me though

Jim Broadbent

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

 

[BizzyLizzy]

Jim

Many thanks for the reply. The reasons for wanting to do this is - we are producing an outbound mailshot to a whole load of charity donors and we are issuing them with raffle tickets. For some bizare reason - the guy who has produced the database only records the first ticket number issued and the last ticket number issued and he now wants all the ticket numbers in between recorded on the mailshot letter - you know the sort of thing - 'THESE ARE YOUR LUCKY TICKET NUMBERS - CALL THIS NUMBER NOW FOR A FREE DOODAD'. The sort of crap junk mail that we all throw away in the bin!!! Anyway - I have no idea why he produced it the way he did - probably just to make my life difficult I suspect but there you go. I will have a play around with your suggestions when I get into the office tomorrow and see how I go.

Cheers Lizzy

 

[Ngolem]

Well then my approach should work for you...Actually rather than a comma delimited horizontal list...a vertical (column) listing would be better and would also require fewer detail sections...now that I understand your purpose.

So instead of a comma we insert a carriage return after each number

//@createnumberlist (modified from before)

WhilePrintingRecords;
StringVar array numbers;
NumberVar temp;
NumberVar Pointer := 1;
StringVar Warning := &quot;&quot;;
booleanVar flag := False;

//I am assuming the &quot;first/last numbers&quot; are numeric

for temp := {table.FirstNumber} to {table.LastNumber} do

(
if length(numbers[Pointer]) < 240 then
numbers[Pointer] := numbers[Pointer] + totext(temp,0) + chr(13) + chr(10);
else
(
Pointer := Pointer +1;
if Pointer > 100 then
(
flag := True;
Warning := &quot;Data Missing...too many numbers to report&quot;;
)
else
numbers[Pointer] := numbers[Pointer] + totext(temp,0) + chr(13) + chr(10);
);
if flag then exit for;
);


Something like this

Detail subsection A

Place @createnumberlist ( Suppressed) as well as a text field...something like &quot;THESE ARE YOUR LUCKY TICKET NUMBERS - CALL THIS NUMBER NOW FOR A FREE DOODAD&quot;

Now you should be able to put about 10 columns of numbers if you choose a small but readable font

so...instead of 100 detail sections...we reduce that to just 10 ...actually 11 if you include the Detail A described above

Each column is represented by a display formula

@displayNumber1

WhilePrintingRecords;
StringVar array numbers;

numbers[1];

*****************************
@displayNumber2

WhilePrintingRecords;
StringVar array numbers;

numbers[2];

......etc......

Note that unlike the other example All these display formulas are the same except for the Array element displayed

For each of these formulas enable the &quot;Can Grow&quot;...they should grow to the same length if there numbers/ticket are the same length. If there are no numbers in the array element then it will be null....remember to enable the section &quot;suppress Blank Section&quot; and this will collapse unused Detail sections.

This is a much neater and efficient display of your data.
NOTE: each array element will end in a carriage return...so when placing the display formulas in the detail subsection ... tighten the section borders - top and bottom tto the formulas... the carriage returns at the bottom will separate the columns nicely in the printout.

Hope this helps



Jim Broadbent

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

 

[HowardHammerman]

why not just create a table with all the legal numbers and link to it?

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Carts]

i can't see how you would do it with a secondary table. Theres nothing to link between the two tables, and we want to do a number lookup.

 

[vidru]

Carts,

As an example, say I've got two tables:
Customers
-----------
CustomerID INT,
FirstNum INT,
LastNum INT

and

Numbers
--------
Number INT

My numbers table consists of the numbers from 1 to 100000. Data in the Customers table looks like this:
CustomerID FirstNum LastNum
----------- ----------- -----------
1 15 85
2 86 99
3 100 500

This query will give me the numbers between FirstNum and LastNum for a particular CustomerID:

SELECT CustomerID, Number
FROM Customers
JOIN Numbers ON Number BETWEEN FirstNum AND LastNum
WHERE CustomerID = 3
ORDER BY Number

-dave