label printing

[pblee]

Ok not sure if this is the place to ask a SQL Question ...please point me in the right direction if it isnt....

I have an accounting package that prints labels for purchased stock (these labels can be formatted using a report writer and sql statements).

When I purchase items on a purchase order I can get the label report to show one label per line on the purchase order.

however if the line on the purchase order has qty of 20. I would like 20 stickers and if the next line on the purchase order has 10 items i would like ten stickers...currently i get one sticker per line on the report.....(if the purchase order has 3 lines I get three stickers in the report - not what i want)

Ok here is the question....

Does anyone know a way for me to put a sql statement in the report to look at the qty fields of the of the three stickers that are showing up and then repeat the stickers by the number of times in the qty field...?.this way i get a sticker per item not per line

[ashish21cool]

I think you should count the qty fields using count() function and then add the number of stickers.

Hope this is what you want.

[pblee]

Thanks Ashish

These are the fields .....Obviously laid out in a label form

PURCHASE_ORDER.ORDER_QTY
PURCHASE_ORDER.ORDER_DATE
PURCHASE_ORDER.ORDER_NUMBER
PURCHASE_ORDER.C_ADDRESS_5
PURCHASE_ORDER.C_ADDRESS_4
PURCHASE_ORDER.C_ADDRESS_3
PURCHASE_ORDER.C_ADDRESS_2
PURCHASE_ORDER.C_ADDRESS_1
PURCHASE_ORDER.NAME

Obviously we are dealing with the
PURCHASE_ORDER.ORDER_QTY field to apply the formula to.

With the report writer I can click any of the above fields then play with that fields properties like font etc....one of the properties is Conditional formating (do I put a formula in here to replicate it)....

Or

The whole report has menu's like join,sort,filter and criteria, etc that affect the labels also.

where and in what format would I need to use the count() Function.

thanks for your patience in this...

Peter

[pblee]

I started in the main report filter typing

if POP_ITEM.QTY_DESPATCH Count("The value to count.")then

LOL
then what?

Not real good at this but trying

Peter

[ashish21cool]

Actually you want to count the things in sql then you could do it like this:
select count(column_name) from table_name which I guess you must be knowing.
But I have a question, which technologies are you using?

[pblee]

not really sure of the technology
sage accounting is the package with built in report writer that supports SQL statements....Along the lines of Crystal reports....

the field is singular with just the QTY....So not sure if the count statement would work...as i think count would count occurences in a table...

Need a statement that would look at the one qty field (which let say has the number 20 in it,,,,Then repeat that data (label)20 times.....
then move onto the next label......

Peter

[ashish21cool]

These are actually 2 different things:
a) If you need to count the qty then its possible in the SQL using count() function
b) To get the label repeat as per the qty is something different. This should be tackled using front end or some logic in the code.

But since its a package which you have purchased, I presume the best solution is to ask the sage team itself through their customer support and they should be helping you happily.
Which 1 are u using from the below ones?
Sage Line 50
Sage Payroll
Sage 50 Forecasting (also available to download)
ACT! by Sage

[pblee]

line 50 is the package

although they call it sage 2007 now.....

Very little support from sage on this and i have posted the forums looking for a solution...But no luck so far...

I am sure it could be done through a sql statement.
Just someone smarter than me.. :)

Peter

[pblee]

oohhhhhh

B was the option ,,,to get the label to repeat as per QTY....

Prefer it to repeat in the report/labels.

as opposed to print repeating (Eg 1 label/s on report that prints the number of times in the QTY field.

Peter

[ashish21cool]

Looks like u got the answer to you problem.

[ashish21cool]

Looks like u got the answer to you problem.

[ashish21cool]

Looks like u got the answer to you problem.