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

Test a text field for numeric values

Report design issues and solutions.
Post Reply
KenStarnes
User
User
Posts: 6
Joined: 16 Jul 2013, 10:59
Sage Version: v29 UK/Europe/Africa

Test a text field for numeric values

Post by KenStarnes » 24 Apr 2026, 12:45

Hi
Is there any way in RW you can test a field for numeric values ?
A PO analysis field is being used under certain circumstances for a numeric identifier but can also be text or blank.
I've got a check based on length of the field <> 0 to see if it has any data in it but if it's numeric I'd like to do a a calculation on it otherwise leave it.

If it's not possible then exporting to Excel and doing it there may be the answer
Top
User avatar
brucedenney
Site Admin
Site Admin
Posts: 4672
Joined: 28 Feb 2006, 09:56
Sage Version: v30 UK/Europe/Africa

Re: Test a text field for numeric values

Post by brucedenney » 24 Apr 2026, 14:34

If the number is not 0 then you could convert the field to a number and see if it is greater than zero
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.
Top
KenStarnes
User
User
Posts: 6
Joined: 16 Jul 2013, 10:59
Sage Version: v29 UK/Europe/Africa

Re: Test a text field for numeric values

Post by KenStarnes » 28 Apr 2026, 12:02

Thanks Bruce

I ended up with 2 Expressions

Expr3 = Length(PURCHASE_ORDER.ANALYSIS_1) <> 0 ? Substring(PURCHASE_ORDER.ANALYSIS_1,0,1):"-"

Expr2 = Expr3 <> "-" ? Expr3 <= "9" ? StringToInteger(PURCHASE_ORDER.ANALYSIS_1) : 0 :0

Could I have done this more efficiently ?
Top
User avatar
brucedenney
Site Admin
Site Admin
Posts: 4672
Joined: 28 Feb 2006, 09:56
Sage Version: v30 UK/Europe/Africa

Re: Test a text field for numeric values

Post by brucedenney » 28 Apr 2026, 13:14

this is the most simple method

StringToInteger(PURCHASE_ORDER.ANALYSIS_1)

Returns 0 if not a number else the number.
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.
Top
KenStarnes
User
User
Posts: 6
Joined: 16 Jul 2013, 10:59
Sage Version: v29 UK/Europe/Africa

Re: Test a text field for numeric values

Post by KenStarnes » 28 Apr 2026, 15:26

I'd tried that but it wouldn't return 0 if non-blank and not numeric ie something like ABC in the field also. It failed with an error.
I assume that test only works if there's a numeric value or nothing.

I'm on v29 btw
Top
Post Reply

Return to “Sage 50 Report Designer”

Who is online

Users browsing this forum: No registered users and 0 guests