Printing multiple labels based on a quantity field

[Former Member]

I have created some combination bar code/text labels for stock items, but need to have the number of labels printed be based upon the quantity field in my purchase order. For instance, on PO 123 for product A, there are a quantity of 3, for product B, there is 1, for product C, there are 4, etc.

At the moment, when I print the labels, I only get one per line item. How can I use a quantity field to cause the printing to repeat for the corresponding quantity of each product on my PO?

[Former Member · Accepted solution]

It looks like this is the right direction, but I'm running into a glitch. Since the table from which I am reading records has multiple records per PO number (i.e. multiple products with varying quantities on the same PO), the procedure works when just displaying the Quantity for each line item, but reports back an error when I try to store the Quantity for each line item ordered:

Specifically, this line works...

select Quantity from PurItem where PONumber = @PONmbr

and produces a list of Quantities in the output.

While this one produces the error message below..

set @Qty = (select Quantity from PurItem where PONumber = @PONmbr)

So, I can't seem to set the declared variable @Qty and, therefore, can't decrement it to get the proper number of labels to print.

Error Message:

Running [dbo].[StockLabels] ( @PONmbr = 8249 ).

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE = -6

Finished running [dbo].[StockLabels].

Any hints on how to solve this?

[ido_millet · Accepted solution]

Did you try the method I described 2 messages above?

[Former Member · Accepted solution]

The problem has been solved, thank you for your assistance.

My last remaining issue was that I was trying to get CR to access the data within the temporary table and I was not returning a record set to CR.

Once I added a Select statement to output the two columns of data, CR could read that and now my program works as desired.

Again, thanks.

Edited by: Bob Amiral on Mar 28, 2011 1:22 AM

[former_member292966]

Hi Bob,

Crystal will only print the number of records passed in the result set. In order to run the multiple copies of the same record, I've created a stored procedure that will loop and create the duplicate records into a temp table.

Now you can create the label with the Stored Procedure and you should have the number of labels for each PO.

Good luck,

Brian

[Former Member]

Thanks for the reply. Would you mind sharing the code for that so that I can get an idea of how I might solve my problem? Or, would you provide some more information about Stored Procedures as I am not familiar with them?

Edited by: Bob Amiral on Mar 11, 2011 8:09 PM

[Former Member]

Bob,

It doesn't actually have to be a Stored Proc, you can use a View of a Command written directly in CR.

Here is an example using SQL Server... I've included my sample tables so that you drop it straight in and see the results before you start modifying it to fit your own data.

-- Create testing data --
IF OBJECT_ID('tempdb..#OrigTable') IS NOT NULL DROP TABLE #OrigTable
CREATE TABLE #OrigTable (PoID INT, ProductID INT, Quantity INT)
INSERT INTO #OrigTable Values(123,1,3)
INSERT INTO #OrigTable Values(123,2,1)
INSERT INTO #OrigTable Values(123,3,5)
INSERT INTO #OrigTable Values(123,5,2)
INSERT INTO #OrigTable Values(123,7,2)
INSERT INTO #OrigTable Values(123,9,4)
INSERT INTO #OrigTable Values(123,11,6)
INSERT INTO #OrigTable Values(123,15,1)
INSERT INTO #OrigTable Values(123,21,2)
INSERT INTO #OrigTable Values(124,2,1)
INSERT INTO #OrigTable Values(124,4,5)
INSERT INTO #OrigTable Values(124,6,2)
INSERT INTO #OrigTable Values(124,7,6)
INSERT INTO #OrigTable Values(124,9,7)
INSERT INTO #OrigTable Values(124,13,8)
INSERT INTO #OrigTable Values(124,24,1)
INSERT INTO #OrigTable Values(124,25,1)
INSERT INTO #OrigTable Values(124,31,3)

------------------------------------------
------------------------------------------
IF OBJECT_ID('tempdb..#AddRN') IS NOT NULL DROP TABLE #AddRN
CREATE TABLE #AddRN (RN INT, PoID INT, ProductID INT, Quantity INT);
INSERT INTO #AddRN 
SELECT ROW_NUMBER() OVER(ORDER BY PoID, ProductID) AS RN,
PoID, ProductID, Quantity
FROM #OrigTable

IF OBJECT_ID('tempdb..#QuantExp') IS NOT NULL DROP TABLE #QuantExp
CREATE TABLE #QuantExp (PoID INT, ProductID INT);
	
DECLARE @rn INT, @po INT,  @p INT, @q INT
SET @rn = 1
	
WHILE EXISTS (SELECT * FROM #AddRN WHERE RN = @rn)
BEGIN 
	SET @po = (SELECT PoID FROM #AddRN WHERE RN = @rn)
	SET @p = (SELECT ProductID FROM #AddRN WHERE RN = @rn)
	SET @q = (SELECT Quantity FROM #AddRN WHERE RN = @rn)
	WHILE @q > 0
	BEGIN
		INSERT INTO #QuantExp Values (@po, @p)
		SET @q = @q - 1
	END
	SET @rn = @rn + 1
END

SELECT ot.* 
FROM #OrigTable AS ot
INNER JOIN #QuantExp AS qe ON ot.PoID = qe.PoID AND ot.ProductID = qe.ProductID

Notice that the rows are multiplied by the number in the Quantity field.

HTH,

Jason

[Former Member]

Results from the above SQL...

PoID        ProductID   Quantity
----------- ----------- -----------
123         1           3
123         1           3
123         1           3
123         2           1
123         3           5
123         3           5
123         3           5
123         3           5
123         3           5
123         5           2
123         5           2
123         7           2
123         7           2
123         9           4
123         9           4
123         9           4
123         9           4
123         11          6
123         11          6
123         11          6
123         11          6
123         11          6
123         11          6
123         15          1
123         21          2
123         21          2
124         2           1
124         4           5
124         4           5
124         4           5
124         4           5
124         4           5
124         6           2
124         6           2
124         7           6
124         7           6
124         7           6
124         7           6
124         7           6
124         7           6
124         9           7
124         9           7
124         9           7
124         9           7
124         9           7
124         9           7
124         9           7
124         13          8
124         13          8
124         13          8
124         13          8
124         13          8
124         13          8
124         13          8
124         13          8
124         24          1
124         25          1
124         31          3
124         31          3
124         31          3

[ido_millet]

Create a "REPEATER" table with a single column (How_Many) that looks like this:

How_Many

1

2

3

4

5

6

etc.

Now, in your report, add the Repeater Table and 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}

-

---

This would cause each order to be duplicated as many times as the value of {Order.quantity}.

If you are restricted to using an equal join, you can also modify the repeater table to include N records for each quantity N:

1

2

2

3

3

3

...

This would cause each order to be duplicated as many times as the value of {Order.quantity}.

If you need to print "N of M" modify the repeater table to look like this:

N M

1 1

2 1

2 2

3 1

3 2

3 3

...

[Former Member]

Hi Ido,

I read your post in Tek-tips as well. Our situation is just partly different in that, our labels must be repeated as x = (order qty) / (pack size).

While trying this solution i have 3 problems:

1) I created repeater table in notepad and added that to CR report. Is this correct or I need to creat the table in SAP B1 data base?

2) I dont know too much about SQL coding and all, so could not work out how to write the inner join statement to repeat the label to be printed? Can you provide a code?

3) Do I need to write the Inner Join in SAP B1 or CR?

Your help is greatly appreciated.

Thanks!

YP

[former_member11696]

Moved to Report Design Forum