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

Expression. Text field

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

Expression. Text field

Post by gazmoz17 » 14 Sep 2020, 14:40

Hi,

Audit split.Details Occasionally this field will have tonnage figures in e.g. 22,000Kg

Is it possible to treat this as a number.

Wish to form an expression:

Auditsplit.Net/Auditsplit.Details

I dont know if can do this with text fields and also with the added complication of the"Kg" at the end which I would like to keep in if possible.

Thanks
Gareth

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

Re: Expression. Text field

Post by brucedenney » 14 Sep 2020, 15:20

Yes, you can.

you need to start by removing the KG.

then you can convert the string to a number.

these are the functions you need.

Code: Select all

Replace("The String to alter.", "A String to be replaced.", "A String to replace all occurrences of the specified String.")

Code: Select all

StringToFloat("A string containing a number to convert.")
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.

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

Re: Expression. Text field

Post by gazmoz17 » 14 Sep 2020, 15:43

Cheers Bruce will have a look tomorrow.

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

Re: Expression. Text field

Post by gazmoz17 » 14 Sep 2020, 15:55

Hi Bruce,

Works great thanks. Can I encompass variations of Kg, kg, KG etc within replace?

Thanks

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

Re: Expression. Text field

Post by brucedenney » 14 Sep 2020, 16:31

You could do multiple separate expressions or force it all uppercase first.
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.

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

Re: Expression. Text field

Post by gazmoz17 » 15 Sep 2020, 08:40

Thanks Bruce...

Ive tried to add on the data joiner The project reference fields & cost code field to the below report. So when I import the below fields I can run this report & see all the fields.

Really struggling with the join tried a few variations but think the sage help material is cr*p!
Import snippet.JPG
Purch inv cntrl proj & cost code refs.report
Thanks
Gareth
Login to access the files attached to this post.

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

Re: Expression. Text field

Post by brucedenney » 15 Sep 2020, 14:23

The issue lies in the expression that is generating the price.

The expression called "Details" does not always have something that can convert to a number so it fails.

I am not sure how you can get around this.

I would do this sort of thing in Excel and link the data in with ODBC.
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.

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

Re: Expression. Text field

Post by gazmoz17 » 15 Sep 2020, 15:57

Hi Bruce,

Yeah stumbled across this might be why then. So if ive got "Materials" in Details its trying to make this a number.

Ok might get rid of that kg price end field.

Thanks

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

Re: Expression. Text field

Post by brucedenney » 15 Sep 2020, 18:33

You could put is somewhere else?
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: No registered users and 12 guests