Servoy Forum

Until today I did not pay attention to the fact that Servoy creates a double precision data type when I add a numeric field to a table.
When I imported Euro amounts today from a CSV file into a table I use in Servoy, I noticed that some amounts showed up with more than two digits after the point (see screenshot).

Now I worry if that could lead to potential problems in a business software that mainly deals with amounts that have exactly two decimal places.
My feeling is that such amounts should not be stored in double precision fields.

So I would like to ask about experience regarding this matter. Should double precision fields be changed to numeric ones in the data structure?
Especially accounting software must be always exact up to the cent.

Numeric stores data more precise than double precision, but can still store more than 2 decimals (depending on the definition)
I wouldn't limit my database to 'just' 2 decimals if there's a possibility I could sell it to a customer using more than 2 decimals.

Apart from DB storage issues, javascript has its share on this as well
Have a look at this older (but still applicable) post:
viewtopic.php?f=22&t=14057

So I think you need to change your focus, to fully cover this issue.

Hi Bernd,

I would not trunkate the number by changing the field type.
If you do that, you can risk to lose at least one decimal into the writing phase.

Ex:
If you try to write the number 39.345 on a "two digits" field, you risk to obtain 39.34 that it's not correct.
This behavior can happen on certain database type (I don't know if you are using postgresql or other kind
of database).

To avoid that or other kind of exceptions you can:

1 - Leave the field as it is (allowed to store more than two decimals) and convert the number on reading operations.
1.1 You can do that by 'Conversion' functionality built in Servoy on every field
1.2 Or you can create a view on your database as short representation of your full data.

2 - You can intercept by code the writing operation on the requested fields to have a rounded number to write on the database

I suggest you to choose the option 1.1 if you use a lot the foundset as Connector to your database.
Instead if you use a lot of datasets and the rawplugins to write/update data, I suggest the option 1.2 or the 2

Hope this help

Hi Berndt,

Regarding internal calculations I suggest to use a rounding routine similar to the one mentioned by marc Boegem in viewtopic.php?f=22&t=14057 .

Regarding Database data types, I suggest to use numeric with 2 decimals for money amounts; the rule here is : store with the precission your end user expects to see data.

This will also avoid future problems when doing reports in which you add multiple rows (Servoy aggregations, Jasper totals, etc.)

Servoy only has a few data type choices (TEXT, NUMBER, DATETIME, BLOB, etc) each of which defaults to one specific type depending on your database vendor and JDBC driver version. Your database has many options for each of these types. So if you're just creating columns via Servoy you're going to run into all kinds of issues with all of your column types — from calculations, formatting, indexing, performance, etc. Know your database and use the correct types — don't rely on Servoy's defaults.

For monetary stuff, always use an exact number type with two decimals. For MySQL we go with DECIMAL(19,2). Doing simple calculations always start by multiplying by 100 to get to an integer, do your simple maths on integers, then divide by 100 to store back as a decimal type.

Recommend using JS libraries (many out there) for more advanced financial calculations, currency formatting and currency conversion instead of rolling your own. Integrating into Servoy can be done with litte work usually — an example integrating a fractions library: https://www.servoyforge.net/projects/fractions/wiki

Thanks for all your valueable contributions!

Ok, for Servoy beginners, I posted the way to add other data types you can not create inside Servoy here:
https://www.servoy.com/forum/viewtopic.php?f=12&t=21299