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

Expressions

Report design issues and solutions.
Post Reply
ryanair10
User
User
Posts: 1
Joined: 27 Jul 2014, 15:52
Sage Version: v2012 18 UK/Europe/Africa

Expressions

Post by ryanair10 » 27 Jul 2014, 16:00

How do I make an expression to display the following:
in Sop_Item "Comment_1" in sales ordering I type 600*600 can I make a expression that will multiply 600*600 as a value

Geordie
User
User
Posts: 2
Joined: 25 Jun 2014, 22:11
Sage Version: v2014 20 UK/Europe/Africa

Re: Expressions

Post by Geordie » 28 Jul 2014, 11:23

While it is possible, I wouldn't recommend what you are trying. Easier would be to use both comment fields. So comment 1 is 600 and comment 2 is 600.

You can the do Stringtointeger(Invoice_item.comment_1) * Stringtointeger(invoice_item.comment_2)

This will the. Give the result.

Otherwise you would be looking at 3 separate expressions breaking down the original 600x600 into two parts then being multiplied.

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

Re: Expressions

Post by brucedenney » 01 Aug 2014, 14:24

Geordie has assumed that the numbers are always whole numbers and is using StringToInteger if this is not the case ie 45.5 * 123.6 then you need to use StringToFloat.

There is no reason not to use expressions to chop up the comment to extract the parts you want (although it is more simple to use 2 fields).

You could use Substring("The String to retrieve a substring from.", "The index of the start of the substring.", "The number of characters in the substring.") to chop it up.
You need to know where the "*" is to get the length for the first part and the start for the second part.
You can use IndexOf("The String to search in.", "The String to seek.") IndexOf(INVOICE_ITEM.COMMENT_1,"*")

Part 1 = Substring(INVOICE_ITEM.COMMENT_1, 0, IndexOf(INVOICE_ITEM.COMMENT_1,"*"))
Part 2 =Substring(INVOICE_ITEM.COMMENT_1, IndexOf(INVOICE_ITEM.COMMENT_1,"*"),60)

the 0 may need to be a 1 you may need to add/subtact 1 from the index of part to not include the * )

You can then turn them into numbers.
Part 1 = StringToFloat(Substring(INVOICE_ITEM.COMMENT_1, 0, IndexOf(INVOICE_ITEM.COMMENT_1,"*")))
Part 2 =StringToFloat(Substring(INVOICE_ITEM.COMMENT_1, IndexOf(INVOICE_ITEM.COMMENT_1,"*"),60))

and multiply the two together

StringToFloat(Substring(INVOICE_ITEM.COMMENT_1, IndexOf(INVOICE_ITEM.COMMENT_1,"*"),60))*StringToFloat(Substring(INVOICE_ITEM.COMMENT_1, 0, IndexOf(INVOICE_ITEM.COMMENT_1,"*")))

I have not checked this I included the warnings above on how you may need to tweak this.

You want to build up the expression bit by bit and check each bit as you go.
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 13 guests