Printing a label multiple times based on the Qty received in Crystal reports

You can add a command to the crystal report to pretend to be a table.

select 1 as number
union all
select 2 as number
union all
select 3 as number

Then follow the solution in the link.

Would it be possible for you to elaborate a bit on this for me, where do I put the command to pretend to be a table ?
 
select 1 as number
union all
select 2 as number
union all
select 3 as number

Thanks
Simon

In database expert, where you select database tables, "Add Command"

My option to Add Command is greyed out, I've tried looking in the Crystal help files but I appear to be trying to do it from the correct location.
crystal.jpg

BTW, it will complain that two data sources is not recommended. But it will work.

Ido, the link he referenced in the question is to your solution/FAQ on another site.

He can't change the database and is looking for another workaround.

mlmcc

Sorry, didn't notice it.

If the number is low enough, he can clone sections and suppress based on quantity.

He states it could be 100s so that really isn't an option.

mlmcc

What size are the labels?

How many lines per label?
Any specific font / size you are required to use?

mlmcc

You don't necessarily have to create a new table.  If you happened to have a table with a record number field (1 for the first record, 2 for the second, etc.), you can use that, as long as it has at least as many records as the maximum number of labels that you'll need to produce.

 Otherwise, there's PCIIain's suggestion of using some other datasource, like an Excel file.  That's an option, but note that if you're connecting to something like an Excel file, then probably only one person will be able to run this report at a time, because CR won't want to share the Excel file.

 Another option may be to use a subreport.  The subreport would read one of your existing tables that had at least as many records as the maximum number of labels that you'll need to produce, and produce one label for each record, up to the specified number.  The basic problem with this approach is that if you don't have a record number field, the subreport will have to read all of the records in the table and use suppression to only produce X number of labels, so you'd ideally want a table that had enough records, but not too many more than you need.

 James

IdoMillet:
I've now created the excel repeater table can you elaborate on how to set up the join condition,
 
Now, in your report, add the Repeater Table and add a join
condition of:
CODEOrder.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:
CODE{Order.quantity} >= {Repeater.How_Many}

Thanks
Simon

In the links tab, join codeorder.quantity to Repeater.How_many.
Right click on the link and select Link Options, now select >= in the box on the right. (Link Type)

Thanks everyone for your help, this works excellent now :-)

The above worked fine on my machine, however when I try to run it on any other machine I get a 717 error 'Failed to load database information'.  In the report I've set the location of the RepeaterTable.xls to be the root of the C drive so that any user that uses this report will need this will to be located there.  Our software/pervasive passes the database location to the report when it is run.

i cannot figure out why this error is occuring !?
717-Error.JPG
DB.JPG

Hmmm, any chance you could put the spreadsheet on a global share somewhere?
Reference this with the actual \\computername rather than a drive letter. Irons out hidden UNC paths (Crystal keeps sticking my machine name in the path unless i Hack it to \\localhost)

The problem is I need to write the report here and then put it on a customers site, which is why I thought if I kept it simple and just required the RepeaterTable.xls to be on C:\ of the users PC as everyone has a C:\  !

Then try \\localhost\c$\

I've set the path to RepeaterTable.xls as \\localhost\c$\RepeaterTable.xls, but unfortunately I still get exactly the same error :-(

Hmmm, is the user a local administrator? c$ is only available for local admins.

I've just tested this on another machine here and the user can access \\computername\c$\RepeaterTable.xls without any problems.  But the error remains, except on the machine that I created the report on?!

Damn. I do believe it's something to do with it managing to put that machine's ID into the data connection somewhere.

Are you running the report through the application on your machine. Our app resets the datasources in reports, and we've had to be careful that it doesn't reset datasources which don't match our original datasource....

If it's that, then we're back to having to get a table in the Btreive database that has numbers in it....

I'm creating the report in crystal reports, running it against the customers database.  Once I'm happy with it there I copy it over in there reports folder in their databse and then fire up our application and try the report from with the app.  It works fine on the machine that I created it on but not on any other machine.  This is the same process that we would use when writing other reports but normally we wouldn't be referencing the .xls datasource.

Going back to using the btreive database we don't have a table that has a column that would go from 1 to 1000 (which is the number of possible labels that may be requried) and it's not currently possible to add a table to the database.

I really appreciate your help with this.

Simon

To try to sort out the problem with the Excel file, can you try creating a new report that just reads that file and see if that works on another system?

 James

Works fine locally on my machine but as soon as I try it on another machine get exactly the same error.  I've tried using the path of c:\RepeaterTable.xls and \\localhost\c$\RepeaterTable.xls

That was with a report that just read the Excel file?  FWIW, I took a simple test report that just reads an Excel file from C:\, copied the report and Excel file to another computer and had no problem running the report.  I run reports from CR 10, not using some other application, so I've got to think that PCIIain may be right and it has something to do with running the report from another application.  If so, I don't know what to suggest, because I just don't run reports that way.  Hopefully someone else will come up with something.

 Failing that, there is the option I mentioned earlier where you read some other table that has the number of records you need, and use suppression to control how many labels you produce from that table (since you don't have a record number field).  Since you're using suppression, the key there is trying to come up with a table that has enough records, but not too many more than you need.

 A variation on that would be to produce multiple labels for each record in the table (eg. duplicate the label 5 times, so you only need 200 records in a table to produce up to 1000 labels).

 James