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:
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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.