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!

Splitting text

Report design issues and solutions.
Post Reply
tomvassie
User
User
Posts: 7
Joined: 11 Dec 2016, 22:36
Sage Version: v2016 22 UK/Europe/Africa

Splitting text

Post by tomvassie » 20 Aug 2020, 10:05

Hello all,

I use the line information(JobNumber) field to record some information on an invoice,

The numbers could be as follow
1-1-1 ~ 1 up to 14-10-99 ~ 3000

Is there a way in the report to split these at the ~ and remove the ~ Leaving me with two parts to the JobNumber field on the report?

Thanks in advance

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

Re: Splitting text

Post by brucedenney » 20 Aug 2020, 13:21

You need to write expressions to extract the bit you want.

For the first part

Code: Select all

Substring(SALES_LEDGER.ANALYSIS_3, 0, IndexOf( SALES_LEDGER.ANALYSIS_3,"~"))
For the Second

Code: Select all

Substring(SALES_LEDGER.ANALYSIS_3, IndexOf( SALES_LEDGER.ANALYSIS_3,"~")+1,99)
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

tomvassie
User
User
Posts: 7
Joined: 11 Dec 2016, 22:36
Sage Version: v2016 22 UK/Europe/Africa

Re: Splitting text

Post by tomvassie » 20 Aug 2020, 17:08

Hi Bruce,
Thank you, that’s absolutely brilliant.
I have been trying to workout substring all day but haven’t succeeded... will try later.

Is there any documentation regarding the operators and exactly to use them??

Thanks again

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

Re: Splitting text

Post by brucedenney » 21 Aug 2020, 09:42

There is a KB article on the sage website but it is not detailed.

https://ask.sage.co.uk/scripts/ask.cfg/ ... 78&DIV=SBD
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

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest