Page 1 of 1

Sage 50 Report Designer Help

Posted: 16 Feb 2018, 22:15
by christina
Hi All,

Wondering if someone can help. I have finished of most of my Sage report but I'm struggling with 2 statements (Any help would be amazing)

1st Statement Issue:

So far I have INVOICE.DEL_NAME LIKE "" ? INVOICE.ADDRESS_1: INVOICE.DEL_ADDRESS_1 which is working fine but I also need to add in IF INVOICE.ADDRESS_1: INVOICE.DEL_ADDRESS_1 are blank as well then it needs to use INVOICE.NAME (Im just not 100% sure how to add in the rest of the statement)

2nd Statement Issue:

Again I have INVOICE.ANALYSIS_1 = "" ? INVOICE.CUST_TEL_NUMBER : INVOICE.ANALYSIS_1 which is working but I need to add in IF INVOICE.ANALYSIS_1 is alphabetical text and not numerical to pull from INVOICE.CUST_TEL_NUMBER and ignore what is in INVOICE.ANALYSIS_1

Also with this one im not sure if this is possible but if someone could clarify that would be great if there is a space within the number for it to ignore the space and just pull the number minus the spaces. So for example 0200 000 1234 pulls through to the report as 02000001234

If anyone can help that would be fantastic.

Re: Sage 50 Report Designer Help

Posted: 19 Feb 2018, 11:14
by brucedenney
Part 1/ Nested if's

I think you are wanting a nested IF like this

Code: Select all

INVOICE.DEL_NAME LIKE "" ? INVOICE.DEL_NAME : INVOICE.DEL_ADDRESS_1 NOT LIKE "" ? INVOICE.DEL_ADDRESS_1 : INVOICE.NAME NOT LIKE "" ? INVOICE.NAME : INVOICE.ADDRESS_1
If the Delivery name is not blank, use it else
if the delivery address is not blank, use it else
if the invoice name is not blank, use it else
use the address

Re: Sage 50 Report Designer Help

Posted: 19 Feb 2018, 11:25
by brucedenney
Part 2

the problem here is that ANALYSIS_1 is a string variable so even if there is a number in there it is still a string. I am guessing from the context that you want to see if there is a phone number there. Well phone numbers could contain spaces, brackets a +44 and so on all of which much things up.

So it is not going to work.

EDIT

Okay, there might be a way to do this different way, if you were to say "if it starts with a 0"

Code: Select all

StartsWith(INVOICE.ANALYSIS_1,"0")?INVOICE.ANALYSIS_1:INVOICE.CUST_TEL_NUMBER

Re: Sage 50 Report Designer Help

Posted: 19 Feb 2018, 11:29
by brucedenney
Part 3

You can use the replace function to replace spaces with nothing.

Code: Select all

Replace(INVOICE.ANALYSIS_1, " ", "")

Re: Sage 50 Report Designer Help

Posted: 05 Apr 2018, 21:52
by christina
Hi Bruce

Thank you for this, this does work so all the spaces no longer pull through but I'm wondering if you can put 2 replace strings in the same variable. I have tried it a few ways and can't seem to get it working but not sure if Im inputting it incorrectly.

Please see below what im trying to do not sure If I can but just want to confirm

Replace(SALES_ORDER.CUST_TEL_NUMBER, " ", "") AND Replace(SALES_ORDER.CUST_TEL_NUMBER, "+ 44", "0")

Thank you for any help you maybe able to give.

Re: Sage 50 Report Designer Help

Posted: 06 Apr 2018, 12:05
by brucedenney
I have not checked this, but I think you need to do something like this

Code: Select all

Replace(Replace(SALES_ORDER.CUST_TEL_NUMBER, " ", ""),"+ 44", "0") 

Re: Sage 50 Report Designer Help

Posted: 06 Apr 2018, 13:01
by christina
Hi Bruce

That worked thank you so much.

Christina