SageForum for UK versions of Sage 50- Help and support.

Hi, is the below possible fort his report:

If invoice contains a stock item with Barcode "R" in its product tab, under MKt field type ROOFING in red.

Just so I can quickly identifty if an invoice is a roofing customer or GRP.



Mnay Thanks

This is the signifier in the background of products

Looking at the report the Mkt field is already set to

SALES_LEDGER.ANALYSIS_1

What you want to do is 2 things, set the value to either ROOFING or ANALYSIS_1 and then colour the field if is ROOFING

To do that you need to replace the field with an expression which says.

If the STOCK.BARCODE is "R" then "ROOFING" Else use SALES_LEDGER.ANALYSIS_1

Secondly you also need to add a conditional format which says

When STOCK.BARCODE is "R" then the font is Red

--------------------------------------------

The "IF" expression in Sage Expressions is outlined in the help file, in short

A typical If statement uses the following format:
Condition ? Instruction 1 : Instruction 2
where:

The ? command is used to mean then.

The : command is used to mean else.

----------------------------------------------------------------------

So the expression would be something like this

STOCK.BARCODE = "R" ?"ROOFING":SALES_LEDGER.ANALYSIS_1

-----------------------------------------------------------------------

The conditional format could be done a number of ways.
You could look at the resulting expression and see if it is ROOFING or look at the barcode and see if it is R
----------------------------------------------------------------------

The BARCODE is a special field which might have repercussions down the road and not my first choice for this. What you are trying to do is to identify the type of product. If I was in your position I would need a good convincing reason to not setup all the roofing materials as a Stock Category and use that to identify the type of product for the expression. If that didn't work then I would look at using the nominal code and i would need to have eliminated all the other more obvious choices to end up using the BARCODE.

-------------------------------

As you are not reporting on all the items on the invoice only the First or Last item will be looked at depending on if this is done in the Header or the Footer.

If you want to find if any product on the invoice that has an R in the Barcode then this is going to be even more complex. The way you would need to do that would be to have a flag expression for each item on the invoice (in the details section) and set it to 1 if it had a R in the Barcode and otherwise a 0. Then add the flags up in the footer if they are are more than 0 then it has a roofing item somewhere on the invoice.

-----------------------------

Looking at the report there are references to Excel. If this is something you want to have in Excel in the end, then writing the report in Excel using the ODBC drivers to read the data directly from the Sage data tables might produce an easier to use end result if a little more involved to write.

------------------------------

Sorry it my reply is so long and I hope this all helps.

Hi Bruce, yeah that makes a lot of sense. Sorry im so late getting back to you Ive not had time to re-visit.

Many Thanks