SageForum for UK versions of Sage 50- Help and support.

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.

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

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.

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.

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.

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.

It now works as intended.

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

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)

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)

Perfect, THANK YOU!

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