Looping in Crystal 4

[TheShookster]

I have a simple problem but it seems that it takes me forever to solve it.
I created a report to print labels. I am passing an input from the user that says the number of labels he/she would like to print.
My question is: how can I loop in Crystal in order to print the passed number of labels.
I am using Crystal 8.5, Windows XP, SP2.

 

[vidru]

See: faq767-3227

-dave

 

[TheShookster]

Thanks,
I will try to implement it this way.

 

[TheShookster]

I created the table and added it to the report but this part was not clear to me:
"...add a join
condition of:
-----------------------------------
Order.quantity >= Repeater.How_Many
-----------------------------------

If the ">=" join option is not available, remove the join to the REPEATER table and create this condition in the Record Selection condition:
--------------------------------
{Order.quantity} >= {Repeater.How_Many}
--------------------------------
..."
if you can clarify it more...
thanks

 

[vidru]

That technique might not work for you. What kind of database are you using? You might have to design the query or a View for this on the db side. For example, if you're using an Access database, a query like this would work:

SELECT *
FROM YourTable, Repeater

In Crystal, you would create a parameter field {?How_Many} that's a number, and create a Record Selection formula like:

{QueryName.How_Many} <= {?How_Many}

If you're using a 'real' SQL database, you could create a View joining your table to the Repeater table like this:

SELECT YourTable.YourFields, Repeater.How_Many
FROM YourTable
JOIN Repeater ON 1 = 1

The record selection formula in the report would be the same as above:

{ViewName.HowMany} <= {?How_Many}

-dave

 

[TheShookster]

Thanks, I am looking into that right now.
TheShookster

 

[TheShookster]

Working!
one last question: if I were to choose 20 labels to print, at the end of the page it cut the label in the middle. Is there any way to avoid uncomplete labels from appearing whenever it displays.
Thanks,

TheShookster

 

[vidru]

Can you explain the layout of the report? It could be that you just need to check the 'Keep Together' for your Details section.

-dave

 

[TheShookster]

Solved,
Thanx

 

[chelseatech]

When I've done reports like this, I sometimes get a bit of drift and find the labels at the bottom of the page don't fit in the same position on the label as the ones at the top.

So create an empty text box on your label, then use Size and Position to set it to X and Y to 0, and the height to the hight of your label. Width doesn't really matter.

Then you can resize your section to get the label at the right hight for printing on all the sections.

Editor and Publisher of Crystal Clear

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

 

[MTSNIDER]

Is there any way to create a dynamic repeater table within the Crystal Report?

I have the same need to print a user supplied number of labels with each label stating label #M of N (total labels). I understand your repeater table to be a static table with the maximum number of labels. Please advise. Thanks!

 

[IdoMillet]

Intertesting discussion, folks.

Shook, I'd like to update the faq to reflect the join tweak you had to use for your data source. Please clarify: your data source and how exactly you ended up implementing your join.

MISNIDER, yes there is another approach that can achieve this without the need for a REPEATER table. What is your data source and Crystal version?

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[MTSNIDER]

Thanks for the quick reply! Wow!

I'm actually using 2 different data sources for my labels, one is SAP data (for Batch data) accessed thru the InfoSet Driver provided thru the BO/SAP interface and the other is Oracle data (for MSDS data) accessed via the Oracle Wire Protocol. The 2 data sources are joined by the Material id.

Let me know if you need any additional information.

 

[IdoMillet]

If you can achieve a UNION query from these data sources, you can do something like:

SELECT ... 1 as 'copy
FROM...
UNION ALL
SELECT ... 2 as 'copy
FROM...
UNION ALL
SELECT ... 3 as 'copy
FROM...
etc.

Then, simply add a record selection formula condition of:
{Copy} <= {Order.quantity}

BY the way, one of the REport Managers listed at:

http://www.kenhamady.com/bookmarks.html

provides a number of copies functionality that can be controlled by any field/formula in the report. So, it can burst your report (let's say by line item) and print each line item the number of times specified in your quantity field. That solution requires no changes to your data source or query.

Cheers,
- Ido



Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[MTSNIDER]

Thank you... you sure know a lot about Crystal....I tried to investigate your two solutions, but, was unsuccessful. I don't know how to create a UNION query. Is this basic Crystal functionality? Additionally, I saw the burst functionality under Crystal Reports Distributor, but, I'd like to find a solution with basic Crystal functionality.

I think I can use the original approach of creating a static file with an indexed field for a counter. My concern with this approach is potential inefficiencies. Do you know if Crystal joins to ALL records and then checks the selection criteria or is the selection criteria checked while processing the join?

For example... My counter file has 500 records... 001 to 500. If the user only requests 20 labels and I have a check in the selection criteria for Label Counter <= User Requested Labels, will Crystal join to all 500 records first and then process the selection criteria or will it join to only 20 labels.

Thank you again for all your help!

 

[IdoMillet]

There are several tools that provide bursting functionality, but that is not the same as dynamically controlling the number of copies based on a Crystal field or formula. As far as I know, only one of these Report Managers provides both.

In terms of performance of the joins using the "counter" table, it depends on the DBMS and the size of your other tables. You can look at the WHERE clause of the SQL query generated by Crystal to see if the condition is being passed to the DBMS. I suggest you simply test.

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com