Skip Navigation
Storing and manipulating money (decimal) values in a custom field
Answer ID 5570   |   Last Review Date 11/14/2018

How can we store and use money variables in a custom field?


Custom Fields, Data Types
Oracle B2C Service


To store money data which can use decimal values, you have to select text data-type for that specific custom field.

Now when using these values in report and manipulating them with math functions, they need to be converted to number type first. The c$custom_field should have at least two digits after the decimal point.  This can be done using a formula as below within reports:

to_number(incidents.c$custom_field) + to_number(substr(incidents.c$custom_field, instr(incidents.c$custom_field, '.') + 1))/100

Where c$custom_field for incidents table holds the money data (up to two decimals) with type text.

Use the following formula to handle any number of digits after the decimal point.

to_number(incidents.c$custom_field) + to_number(substr(incidents.c$custom_field, instr(incidents.c$custom_field, '.') + 1))/power(10, length(substr(incidents.c$custom_field, instr(incidents.c$custom_field, '.') + 1)))

For additional information, refer to the 'Functions' section in online documentation for the version your site is currently running. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.