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
- - - - - - - - - - - - - - - - - - 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
Expressions
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.
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Expressions
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
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.
Re: Expressions
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.
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.
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Expressions
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.
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.
Re: Expressions
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.
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.
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Expressions
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.
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.
Re: Expressions
It now works as intended.
Thank you very much for your help, knowledge and patience Bruce.
Thank you very much for your help, knowledge and patience Bruce.
Re: Expressions
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)
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)
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Expressions
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)
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.
Re: Expressions
Perfect, THANK YOU!
Re: Expressions
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
Who is online
Users browsing this forum: No registered users and 3 guests