Make datatype of rate field as "Float" instead of "Currency"

[lukidoescode]

Why float? Currency has higher accuracy in MySQL.

[nabinhait]

In frappe framework, Float and Currency both uses Decimal(18,6) datatype in mysql. It only differs in user interface, Currency field display data as per currency format (precision mostly 2) and Float field display as per Float Precision.

[lukidoescode]

Why do you use Decimal for Currencies? Decimal has an amount of data loss when calculating with decimal numbers.

[anandpdoshi]

It should be Float. I suppose it is there for legacy reasons. We will discuss this and get back to you.

On Thu, Mar 20, 2014 at 3:04 PM, Lukas Wagner notifications@github.comwrote:

Why do you use Decimal for Currencies? Decimal has an amount of data loss when calculating with decimal numbers.

— Reply to this email directly or view it on GitHubhttps://github.com/frappe/erpnext/issues/1475#issuecomment-38148272 .

[lukidoescode]

Ah, sorry, I was wrong, DECIMAL is actually the best type to take for MySQL. Haven't been working with MySQL for a while. In MSSQL-Server (which I'm forced to use at work) has a special type named MONEY. This type works with absolutely minimal loss to ensure high accuracy. I don't know how accurate DECIMAL is, but the MySQL webpage actually says it has the highest.

[jevonearth]

Will this change/fix make it into 4.0?

[nabinhait]

Till now we could not manage time to implement it in v4.0. We will try to imllement it soon. On 3 Apr 2014 19:59, "jevonearth" notifications@github.com wrote:

Will this change/fix make it into 4.0?

Reply to this email directly or view it on GitHubhttps://github.com/frappe/erpnext/issues/1475#issuecomment-39457496 .

[jevonearth]

Now that 4.0 is out, will you consider this improvement?

[rmehta]

@jevonearth I don't quite get the context for this. Does not seem to be a common question. Can you give an example of how you want this to be fixed?

[rmehta]

Ok from the forum:

As the subject says, my system has Float Precision set to 4, but when creating a PO, the Line Item unit price always rounds to two decimal places. There appears to be java script code that is forcing the rounding.

Seems like that a fix in required in the JS code.

[rmehta]

@jevonearth I checked with @nabinhait and it seems that if you convert your "rate" Currency Field to Float via Customize Form View, then you should be good.

It seems most users are okay with the current settings of 2 or 3 decimals based on Currency Format, so we don't need to make the update in the product.

Please check if this works for you, if there is some force JS rounding, please let us know which transaction it happens.

[trics]

hi, I have the problem, we purchase items for 0.025 usd, but in PO it rounding to 0.02 can you please guide me where I can change this?

[nabinhait]

You can change "field type” of the rate field from "Currency" to “Float” via Setup -> Customize Forms -> Purchase Order Item.

On 17-Jul-2014, at 10:24 pm, trics notifications@github.com wrote:

hi, I have the problem, we purchase items for 0.025 usd, but in PO it rounding to 0.02 can you please guide me where I can change this?

— Reply to this email directly or view it on GitHub.

[jevonearth]

By changing the data type on the Purchase Order, do we also need to change the corresponding fields on Supplier Quotation, Purchase Receipt, Purchase Invoice etc...?

[nabinhait]

Yes.

On 17-Jul-2014, at 11:26 pm, Jev Björsell notifications@github.com wrote:

By changing the data type on the Purchase Order, do we also need to change the corresponding fields on Supplier Quotation, Purchase Receipt, Purchase Invoice etc...?

— Reply to this email directly or view it on GitHub.

[trics]

I just tried changing, no luck, see the screenshot, still rounding to 2 decimals :(

[nabinhait]

Set “Float Precision” as 3 from Setup -> System Settings and check after clearing cache.

On 17-Jul-2014, at 11:28 pm, trics notifications@github.com wrote:

I just tried changing, no luck, see the screenshot, still rounding to 2 decimals :(

— Reply to this email directly or view it on GitHub.

[jevonearth]

Leaving the filed data data type aside for a moment. I have Float Precision set to 3 in system settings, and cache is cleared, but when entering three decimal places on a Purchase Invoice, it still rounds to two decimal places.

[nabinhait]

Float precision only works for data type Float. Did you set data type of Rate field as Float?

On 20-Jul-2014, at 6:01 am, Jev Björsell notifications@github.com wrote:

Leaving the filed data data type aside for a moment. I have Float Precision set to 3 in system settings, and cache is cleared, but when entering three decimal places on a Purchase Invoice, it still rounds to two decimal places.

— Reply to this email directly or view it on GitHub.

[anandpdoshi]

@jevonearth I changed the fieldtype of Rate field in Sales Invoice Item to Float. And, it shows upto 3 decimal places on my setup.

[jevonearth]

This still doesn't sit right with me.

In System Settings, there is a 'Float Precision' setting, but that has no functional value unless I change the datatypes throughout the system.

I feel nervous about changing the data types. Rounding is something I feel should really have unit test coverage.

I will need to change the data type in Quotations, Sales Orders, Purchase Orders, Delivery Receipts. I don't mind doing that work, but I worry about future regressions, and divergence of my systems datatypes from the core product. Do I also need to change related unit price fields in the Taxes & Charges lines?

[anandpdoshi]

Hi Jev

There are many companies who have changed the field type of rate field to Float. So far, I haven't come across an issue that couldn't be solved.

Once rate and qty are multiplied, the amount value is a currency field. So it gets rounded. So the possible issues can only be restricted to wherever rate field is used.

So better change to float field wherever item rate is concern. It has to be both rate and base_rate field, the latter value being in company currency. Also the fields concerning price_list_rate and it's base currency counterpart. Also check in Stock Entry Detail doctype.

There are unit tests related to calculations in each doctype. You can check test_sales_invoice.py to see if it suffices.

It shouldn't be difficult to setup a test environment with your existing database and trying this change before you go live.

Sent from my phone

On 22-Aug-2014, at 3:36, Jev Björsell notifications@github.com wrote:

This still doesn't sit right with me.

In System Settings, there is a 'Float Precision' setting, but that has no functional value unless I change the datatypes throughout the system.

I feel nervous about changing the data types. Rounding is something I feel should really have unit test coverage.

I will need to change the data type in Quotations, Sales Orders, Purchase Orders, Delivery Receipts. I don't mind doing that work, but I worry about future regressions, and divergence of my systems datatypes from the core product. Do I also need to change related unit price fields in the Taxes & Charges lines?

— Reply to this email directly or view it on GitHub.

[ftorreti]

Folks a proposal to bring this to closure is to use a unit price multiplier in the item master as several ERP system do regarding currency type with only 2 decimals. For example let's say my unitary price before tax is: 3.223324 (yes unfortunately it can be that crazy) all I had to do in the past was to set a multiplier to 10000 and the unit price would be recorded as: 32,233.24 in a currency field.

Any rendering of the price in any reports would be currrency field value/multiplier.

We are having a hard time with the changes to data types because due to the large number of vendors, and constantly changing rates, we often have the need to work with 5 or 6 digits.

Having changed all the field types to Float, you can imagine what that does to all our reports, invoices, quotations...

What is your idea about implementing a multiplier and leave all currency fields as such?

[anandpdoshi]

This will complicate things. Rather, I've added the ability to set precision per field, which should give a lot of flexibility. https://github.com/frappe/frappe/pull/824

[cgpurbaugh]

Can this be closed then?

[jevonearth]

From my perspective, as a user, what is offered is still not clean. It's complicated for the user, and is rather messy. Precision on unit price needs to be a first class feature in my humble opinion. Not a field setting workaround.