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!

Sage 50 Report Designer Help

Report design issues and solutions.
Post Reply
christina
User
User
Posts: 6
Joined: 16 Feb 2018, 22:08
Sage Version: v2018 24 UK/Europe/Africa

Sage 50 Report Designer Help

Post by christina » 16 Feb 2018, 22:15

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.

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

Re: Sage 50 Report Designer Help

Post by brucedenney » 19 Feb 2018, 11:14

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
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

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

Re: Sage 50 Report Designer Help

Post by brucedenney » 19 Feb 2018, 11:25

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
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

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

Re: Sage 50 Report Designer Help

Post by brucedenney » 19 Feb 2018, 11:29

Part 3

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

Code: Select all

Replace(INVOICE.ANALYSIS_1, " ", "")
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

christina
User
User
Posts: 6
Joined: 16 Feb 2018, 22:08
Sage Version: v2018 24 UK/Europe/Africa

Re: Sage 50 Report Designer Help

Post by christina » 05 Apr 2018, 21:52

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.

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

Re: Sage 50 Report Designer Help

Post by brucedenney » 06 Apr 2018, 12:05

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") 
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

christina
User
User
Posts: 6
Joined: 16 Feb 2018, 22:08
Sage Version: v2018 24 UK/Europe/Africa

Re: Sage 50 Report Designer Help

Post by christina » 06 Apr 2018, 13:01

Hi Bruce

That worked thank you so much.

Christina

Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests