Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Expression help

Report design issues and solutions.
Post Reply
gazmoz17
User
User
Posts: 20
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Expression help

Post by gazmoz17 » 25 Jul 2019, 14:15

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
You do not have the required permissions to view the files attached to this post.

gazmoz17
User
User
Posts: 20
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Expression help

Post by gazmoz17 » 25 Jul 2019, 14:17

This is the signifier in the background of products
You do not have the required permissions to view the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4159
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Expression help

Post by brucedenney » 25 Jul 2019, 15:42

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

gazmoz17
User
User
Posts: 20
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Expression help

Post by gazmoz17 » 22 Aug 2019, 14:20

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

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest