Skip Navigation
Expand
Storing and manipulating money (decimal) values in a custom field
Answer ID 5570   |   Last Review Date 05/10/2021

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

Environment:

Custom Fields, Data Types
Oracle B2C Service Versions 20C and earlier

Resolution:

The Decimal Custom Attribute Field Type has been added to versions 20D and later. For details on this feature, see the What's New document for Oracle B2C Service 20D.

In versions prior to 20D, 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.