Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

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: 4616
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

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: 4616
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot] and 11 guests