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
skyblues
User
User
Posts: 5
Joined: 08 Jan 2015, 18:44
Sage Version: v2012 18 UK/Europe/Africa

Expressions

Post by skyblues » 08 Jan 2015, 18:51

How do I divide the following information with an expression? It's an invoice layout and the two fields are INVOICE_ITEM.QUANTITY and INVOICE_ITEM.UNIT_OF_SALE. When I try to build the expression I receive undefined errors. Many thanks for taking the time to read this and hopefully responding.

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 » 09 Jan 2015, 14:16

Your problem is that you are trying to do maths with a field that is not numeric (INVOICE_ITEM.UNIT_OF_SALE).

You need to convert the field to a number and use that StringToFloat(INVOICE_ITEM.UNIT_OF_SALE)

So the full expression would be

StringToFloat(INVOICE_ITEM.UNIT_OF_SALE)/INVOICE_ITEM.QUANTITY
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.

skyblues
User
User
Posts: 5
Joined: 08 Jan 2015, 18:44
Sage Version: v2012 18 UK/Europe/Africa

Re: Expressions

Post by skyblues » 09 Jan 2015, 15:32

Thank you for the reply Bruce most appreciated.

Not sure if I'm doing something else wrong but when I run the report the following is displayed "The report could not be generated. Input string was not in a correct format."

Many thanks again for the help.

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 » 09 Jan 2015, 15:50

I suspect a data issue, does unit of sale contain a number?

I hasten to add, I haven't tested this, if you cut and pasted, I might have a typo etc.
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.

skyblues
User
User
Posts: 5
Joined: 08 Jan 2015, 18:44
Sage Version: v2012 18 UK/Europe/Africa

Re: Expressions

Post by skyblues » 09 Jan 2015, 16:35

Thanks again for another reply Bruce.

I built the expression from scratch so your text isn't to blame.

The item unit of sale is stored in the product record. So could this be an issue? I will check the data for this field but it should be 1 to 3 numerical digits with no decimal places.

Many thanks.

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 » 12 Jan 2015, 12:10

If you want to use the UNIT_OF_SALE from the product record rather than the one recorded on the invoice/order then you need to link in the STOCK table (If it is not already) and use STOCK.UNIT_OF_SALE

You need to me sure it always has a numeric value, or need to add logic to handle that scenario.
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.

skyblues
User
User
Posts: 5
Joined: 08 Jan 2015, 18:44
Sage Version: v2012 18 UK/Europe/Africa

Re: Expressions

Post by skyblues » 12 Jan 2015, 17:22

It now works as intended.

Thank you very much for your help, knowledge and patience Bruce.

Charlo
User
User
Posts: 3
Joined: 25 Feb 2020, 11:22
Sage Version: v2016 22 UK/Europe/Africa

Re: Expressions

Post by Charlo » 25 Feb 2020, 11:26

Hi i'm trying to divide the following but it is giving me an error
StringToFloat(INVOICE_ITEM.QUANTITY)/(STOCK.COMMODITY_CODE)

where commodity is a CASE number so i need to divide the quantity of the order item by how much a case has (144/4=36 cases)

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 » 25 Feb 2020, 11:49

Hi

The problem is that the field STOCK.COMMODITY_CODE is not a number INVOICE_ITEM.QUANTITY is a number

You can convert it to a number with the StringToFloat("A string containing a number to convert.") function

So what you need as an expression is

INVOICE_ITEM.QUANTITY/StringToFloat(STOCK.COMMODITY_CODE)
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.

Charlo
User
User
Posts: 3
Joined: 25 Feb 2020, 11:22
Sage Version: v2016 22 UK/Europe/Africa

Re: Expressions

Post by Charlo » 02 Apr 2020, 08:26

Perfect, THANK YOU!

Charlo
User
User
Posts: 3
Joined: 25 Feb 2020, 11:22
Sage Version: v2016 22 UK/Europe/Africa

Re: Expressions

Post by Charlo » 02 Apr 2020, 08:49

Since in the Commodity code i have written C/S X12 (case x12) can do the sum with the string as you said but I leave the words c/s x

Post Reply

Who is online

Users browsing this forum: No registered users and 9 guests