INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Formula Help

Creating Multiple Labels in Crystal by HowardHammerman
Posted: 23 Apr 04

Creating Multiple Labels in Crystal

Our client was a wholesale plant nursery. They shipped plants on pallets. A field in the products table indicated how many plants could fit on a pallet. A field in the orders table indicated the number of plants to be shipped. The clients wanted to print pallet labels that included the name of the customer, the name of the plant, the number of plants to be shipped, the total number of pallets and the sequence number of each pallet.  For example if a customer ordered 100 plants that could fit ten plants to a pallet they would get ten pallets. The first label would say ô1 of 10ö, the second would say ô2 of 10ö and the last would say ô10 of 10ö.

Unlike R&R, Crystal does not have a simple dialog box in which the user can enter a field that will control the number of repeats for each record.  The only way to accomplish the goal is link to another table.

If the controlling table includes a field that has number of repeats, you can accomplish your task in the following way:

1.    Create a table with one numeric field. Include the number of records equal to the increasing number of the field. For example:

Record Number    Field Value
1    1
2    2
3    2
4    3
5    3
6    3

Make sure that you have included enough records to cover the maximum number of repeats.

2.    Link the main table to this new table using the field containing the number of repeats linked to the numeric field.  Make sure you pick an ôequal joinö if you are using an SQL or ODBC connection.  The records in the main table will be repeated the correct number of times.

In our case the controlling table did not have a field with the number of repeats (pallets). We had to calculate the number of pallets by taking the integer value of the number of plants divided by the maximum number of plants per pallet and then adding one. Crystal does not allow linking on a formula field.

The solution was to create a dummy table containing more records than the maximum number of pallets that could ever be required.  We could have used an existing table with roughly the correct number of records.  

In the main report, we grouped the records by shipment. We created two group header sub-sections and suppressed all other sections.  We also suppressed group header 1a.  We created a formula field that contained shared variable versions of all the information we wanted on the label.  For example:

    WhilePrintingRecords;
    Shared stringvar cust := {customer.name};
    Shared stringvar ord := {order.order_num};
    Shared numbervar pallets := Truncate({order.quantity}/{product.maxpallet});
    If remainder({order.quantity}/{product.maxpallet}) > 0 then pallets := pallets + 1;

We placed this in the group header 1a section.

Next we created an unlinked sub-report using the dummy table.  This report had no fields from the main report. We created one formula field for each of the shared variable from the main report.  For example, we created a formula field named Lab with the following expression:

    WhilePrintingRecords;
    Shared numbervar pallets;
    Pallets

We placed these fields in the record section and formatted the record section as needed for the label format.  

Next, using the section expert, we selected the details section and entered the following formula in the conditional suppression dialog box:

    {dummy.number}>{@lab}

If the dummy table contained 100 records and the field contained the numeric value, the report would print 100 copies of each and every label if we did not suppress the section.  By using this formula, only the correct number of labels will be printed and the remaining records will be suppressed.   Since the Number field in the Dummy table holds the record number, we can use it to create the X of Y notation.

If you do not have the ability to create your own table, you can accomplish the same results by using any table in the database and creating a sequence number using the recordnumber function in Crystal.

Howard Hammerman
Hammerman Associates, Inc.
www.hammerman.com
800-783-2269





Back to Business Objects: Crystal Reports 1 Formulas FAQ Index
Back to Business Objects: Crystal Reports 1 Formulas Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close