Shipping Label-Print according to quantity ordered

[blueboyz]

I am using Sage 50 (formerly Peachtree) Quantum 2013 with Crystal Reports 2008. Sage 50 uses Pervasive SQL as the database.

I am creating a report to print shipping labels with the following information:

Customer Name
Sales Order #
Part #

I need to print a shipping label for the number of parts on the sales order. For example, if the sales order has:

Quantity Item Amount
1 A $100
2 B $500
1 C $200

then 4 shipping labels should be printed, 1 for item A, 2 for item B and 1 for item C.

The problem is getting the report to show another label for item B. Currently, the report only lists item B once.

Here's how my design looks:

Details a CUSTOMER: {Customers.Bill_To_Name}
Details b WO# {Jrnlhdr.Reference}
Details c PART# {LineItem.ItemID}

How can I get the data to repeat for each quantity (if quantity is greater than 1)?

Thank you for your help.

 

[CoSpringsGuy]

you could create an excel spreadsheet with one column
QTY
1
2
3
4
5
6
...
...
...
99 (as many as you want or as few if you want just make sure its at least as many quantity you would ever expect to have)

link your table {LineItem.ItemID} to the excel spreadsheet table {QTY} (inner join)

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

i think I linked you wrong ...

link your qty field to the qty field in the spreadsheet created

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[blueboyz]

I tried the solution above by making an excel spreadsheet with the first column numbers of 1-100.
A1 = 1, A2 = 2, A3 = 3, etc.

I linked the excel spreadsheet Sheet1 with the table with an inner join: {JrnlRow.Quantity} = {Qty!Sheet1}.
When I refreshed the report, I got the same results which was one listing for each item sold.

I tried linking the tables as the solution above suggested {LineItem.ItemID} = {Qty!Sheet1} but I got an error that the data types were not compatible.

Is the column in the Excel spreadsheet supposed to be "named"?
Shouldn't I be linking the quantity in the data ({JrnlRow.Quantity}) to the Excel spreadsheet?

 

[CoSpringsGuy]

the linked fields should be the quantity field from your database to the field created in excel

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

AH!! Im thinking back through this and my solution will not work.. let me give it another thought...

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[blueboyz]

Thanks CoSpringsGuy. I did try linking the tables by
{JrnlRow.Quantity} = {Qty!Sheet1} and I also tried {JrnlRow.Quantity} <= {Qty!Sheet1}.

Nothing changed in the results. The shipping label is created once for each item, but it should be the number of shipping labels equals the quantity on the order.

 

[blueboyz]

Here is the query:

SELECT "Customers"."Customer_Bill_Name", "JrnlHdr"."Reference", "LineItem"."ItemID", "JrnlHdr"."JrnlKey_Journal"
FROM {oj (("JrnlRow" "JrnlRow" LEFT OUTER JOIN "Customers" "Customers" ON "JrnlRow"."CustomerRecordNumber"="Customers"."CustomerRecordNumber") LEFT OUTER JOIN "LineItem" "LineItem" ON "JrnlRow"."ItemRecordNumber"="LineItem"."ItemRecordNumber") LEFT OUTER JOIN "JrnlHdr" "JrnlHdr" ON "JrnlRow"."PostOrder"="JrnlHdr"."PostOrder"}
WHERE "JrnlHdr"."JrnlKey_Journal"=11 AND "LineItem"."ItemID"<>' '

 

[CoSpringsGuy]

I have somewhat of a solution but its a big ole pain in the butt!

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

I dont LOVE this solution but it has worked for me in the past.....
create multiple detail sections ( I just show three below) as you have above but in each detail section place the fields required for each label vertically. (dont seperate them like you have in your example. You will have to open the detail section to make it big enough to hold the three fields vertically

Details a
CUSTOMER: {Customers.Bill_To_Name}
WO# {Jrnlhdr.Reference}
PART# {LineItem.ItemID}
Details b
CUSTOMER: {Customers.Bill_To_Name}
WO# {Jrnlhdr.Reference}
PART# {LineItem.ItemID}
Details c
CUSTOMER: {Customers.Bill_To_Name}
WO# {Jrnlhdr.Reference}
PART# {LineItem.ItemID}

In the section expert of detail b click yes to supress and click the x2 and use the following formula
{JrnlRow.Quantity} < 2 (if your qty field came over as test replace it with tonumber({JrnlRow.Quantity}) < 2)

In the section expert of detail c click yes to supress and click the x2 and use the following formula
{JrnlRow.Quantity} < 3 (if your qty field came over as test replace it with tonumber({JrnlRow.Quantity}) < 3)

In the section expert of detail d click yes to supress and click the x2 and use the following formula
{JrnlRow.Quantity} < 4 (if your qty field came over as test replace it with tonumber({JrnlRow.Quantity}) < 4)

I think that is enough to give you an idea of where I am going. You will have to figure out the alignment for your labels



_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

faq767-3227

-LB