Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Sequencial numbers / barcodes Post Reply Post New Topic
Author Message
IanShorrock
Newbie
Newbie


Joined: 25 Jan 2008
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote IanShorrock Replybullet Topic: Sequencial numbers / barcodes
     Posted: 25 Jan 2008 at 8:00am
Hi
I am using Crystal 9 to develop reports from an ERP system sql database.
I need to create a report which will output as labels for warehouse management showing an item part number and a barcode based on the part number and a sequencial number. The sequencial number is derived from the number of parts being booked into the warehouse.
 
For example Item part number A1234    qty 10 to book into warehouse.
Report to generate 10 labels as follows:
 
  A1234 -001
  A1234 -002  etc up to 010. plus a barcode based on this.
 
Any Ideas ?
 
I have a formula field with some VB which will generate a bar code but how do I get the report to print 10 labels ??
 
Thanks
Ian Shorrock
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 25 Jan 2008 at 3:31pm
Is there a maximum number of items that can be booked into the warehouse for any given part?
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
IanShorrock
Newbie
Newbie


Joined: 25 Jan 2008
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote IanShorrock Replybullet Posted: 28 Jan 2008 at 12:42am
Hi
About 500 per works order.
 
Thanks
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 28 Jan 2008 at 6:27am

With that many, the original method I was thinking of won't work - it's good for up to 10-15....  However, there may be another way to do this.

Do you have the ability to add a new table to the database?  This would be a "Sequence Number" table that would have a single numeric field in it called Sequence_Number.  It would have as many records in it as you think you would need for sequence numbers. 

From there you would include only that table in your report (unless you need more info than the part number, in that case you'll probably have to use a CommandText because you can't "link" to this table and Crystal will complain about it.)  Add two parameters to the report - one for the part number and one for the number of parts to be checked in.  Your selection criteria will be something like {Sequence_number.Sequence_number} <= {?NumberOfParts}.  Use the part number parameter on the report instead of a database field.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
IanShorrock
Newbie
Newbie


Joined: 25 Jan 2008
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote IanShorrock Replybullet Posted: 28 Jan 2008 at 6:52am
Hi Hilfy
Many Thanks for your reply.
 
Yes I think I can add another table to the database.
A post in the Tips and Tricks section seemed to suggest something similar.
I have done some testing in Excel. I have created an "orders" table and a  a "sequence number" table and linked the qty field from the order table to the number in the sequence table. When linked correctly Crystal produced multiple "records" based on the qty which could be sequenced, barcoded, and used as labels. Whether this works in real life is another matter.
 
 Just one more thing - just in case I cant create another table can Crystal work with multiple datasources ? EG the sql database and an Excel spreadsheet holding the sequence numbers
 
Thank You very much for your help so far 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 28 Jan 2008 at 7:41am

I know that Crystal will work with multiple data sources, but I don't think you can create a CommandText (SQL Query) that will pull from both a database and an Excel file at the same time.  You'll have to do some testing with it.

-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
IanShorrock
Newbie
Newbie


Joined: 25 Jan 2008
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote IanShorrock Replybullet Posted: 29 Jan 2008 at 7:15am
Hi,
I have now got this to work.
I created a new table on the sql database called SequenceNumber. This contained one numeric field called num with records 1-500. The Works Orders table qty field was linked to the num field using an inner join >= . This forces Crystal to duplicate records based on the qty. I used the formula WhileprintingRecords; Shared NumberVar Counter := Counter + 1; to generate an incrementing sequence number for each duplicated record.
   I can now print the correct number of labels based on Works Order qty each with a unique barcode based on Works Order Number and the sequence number.
 
 Thanks to all who offered advice. 
 
   Ian Shorrock
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.