Can I use a formula to increment a numerical series value by 1? 1

[qu1ncy]

Hello,
Our business uses Crystal Reports XI. I’m hoping someone here has done this already and can assist us.

We require 2 barcode printing scenarios. The first one which is completed, prints barcode labels pulling a coverage number from an Oracle database and prints the coverage number as a 3 of 9 font barcode on sheets of Avery 5160 labels (30 to a sheet) using the built-in CR label printing functionality. This I’ve been able to do successfully using an Oracle view on the backend and pulling that into CR.

The 2nd scenario I don’t know how to do in CR. I need to print barcodes (same font, same labels) in an incremental series such as starting at 50000, incrementing by 1, up to a number chosen by the client. The starting number (e.g. 50000) and ending number may differ in different departments. I’m guessing that providing the users with numerical parameters such as “Series_Start” and “Series_End” and passing that to a formula will resolve the "flexible series range" requirement. The barcoded labels will start at 50000, 50001, 50002, etc, up to whatever was indicated for Series_End.

I would like to create a formula that will perform the task of incrementing the series number as it’s printing these barcode labels. I need help with that please.

I'm thinking the solution may need to be in a formula as we may be concatenating other numerical values to these series numbers in the future, which could be done on the CR side.

This solution as described above should be a good starting point to manage individual department barcode series requirements. Any suggestions based on previous experience to better manage this process are welcome.

Thanks,
Quincy

 

[qu1ncy]

Hello,
I have a correction to my series number label printing requirement. We need 2 labels (one after the other) of each incremented series number to be printed. In other words, 50000, 50000, 50001, 50001, 50002, 50002, etc
Thanks,
Quincy

 

[chelseatech]

My cViewCALENDAR utility will let you create a data set with a counter with a sequential series of numbers. Contact me for a trial version. It will up on my web site soon.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[qu1ncy]

Hello Chelseatech,

Thanks for replying to my enquiry. Thank you for your offer of a trial version of cViewCALENDAR utility. I've contacted you via your website address.

Quincy

 

[qu1ncy]

Further on this post:
I've resolved the task of obtaining a source of sequential numbers by basing my report (Avery Label 5160) using an Oracle view with the following statement:

Select
to_char(rownum + 49999) as BARCODE,
From (anytable with sufficient records in your schema)
where rownum < 50001

What remains is formatting 2 labels (one after the other) of each incremented series number to be printed. In other words, 50000, 50000, 50001, 50001, 50002, 50002, etc

Thanks,
Quincy

 

[lbass]

faq767-3227 explains a method for duplicating records.

-LB

 

[qu1ncy]

LBASS, thanks for pointing out that faq post by IDO. We'll investigate adopting a variation of IDO's solution. If we come up with something, we'll gladly share!
Quincy

 

[qu1ncy]

My duplication of IDO's FAQ doesn't work.

IDO's solution is applicable to a sales environment containing an Order_Detail table with a Quantity column. I don't have that db structure.
I substituted that by adding 2 columns to my view, "Quantity" with a default value of 2, and "How_Many", also with a default value of 2. It's not clear why IDO has incrementing values in his/her How_Many column.

My SQL view now looks like this:
Select
TO_CHAR(rownum + 49999) SERIES_NUM,
TO_CHAR(2)QUANTITY,
TO_CHAR(2) as HOW_MANY
From a TABLE
where rownum < 10001

The data returned looks like this:
SERIES_NUM QUANTITY HOW_MANY
50000 2 2
50001 2 2
50002 2 2

On the CR report, the Record Selection condition is:
{VU_CSS_HSERV_BARCODES.QUANTITY}>={VU_CSS_HSERV_BARCODES.HOW_MANY} and
{VU_CSS_HSERV_BARCODES.SERIES_NUM} in {?Series_Start} to {?Series_End}

The end result continues to be one barcode label for every series number.

 

[lbass]

That wasn't really what Ido intended for you to do. Instead, using your original query, also add an UNLINKED command that returns only two records, e.g., the equivalent of the following in Oracle:

Select distinct table.ID
from table
where table.ID <= 2

Add the ID field to your report and suppress it. It should force all records to appear twice. You can use any field that has only two instances.

-LB

 

[qu1ncy]

Thanks.

If I understand correctly, I need to reference any separate table with only 2 records in it. Then using your Select statement as a view, add view to my report without a join, add the ID field to the visible label area and suppress it.

Q

 

[lbass]

Yes.

-LB

 

[qu1ncy]

Thanks lbass. I'll update this post with an outcome as soon as our DBAs build this table! Protocol, priviledges, etc...

Q

 

[lbass]

You should be able to just create a quick command that limits records to two in the where clause.

-LB

 

[qu1ncy]

I came up with this in a view:
Select
TO_CHAR(rownum + 0) QUANTITY
from a table
where rownum < 3

The resulting dataset is:

Row # QUANTITY
-----------------
1 1
2 2

 

[qu1ncy]

Hello lbass,
Works beautifully! Great solution. Your help with this is much appreciated.

In summary, we have 2 views
1.) VU_Barcode:
Select
TO_CHAR(to_date(sysdate), '*yyddd')||'='|| to_char(rownum + 49999)||'*' as BARCODE,
TO_CHAR(rownum + 49999) SERIES_NUM
From ANY LARGE TABLE
where rownum < 10001
(For our client's specific format. The "Barcode" value consists of a Julian date concatenated to an incremented series number. The 2 asterisk denote the start & finish point in code 3 of 9 font. The = sign denotes a space)
2.) VU_Label_Quantity
select
TO_CHAR(rownum + 0) QUANTITY
from ANY LARGE TABLE
where rownum < 3

Pull both views into the report (Database Expert)
The Barcode field is added to the label-formatted report. Change the font to code 39 on your report. You can download this font from various websites. You'll need to install it.
Lastly, add the Quantity field on top of the Barcode field and suppress it.
The end result is a barcode value with it's numerical value visible below the barcode, printed on 2 consecutive labels.

Quincy