how to set the decimal field type with 6 decimal?

[yandp]

Hello all, 

In a custom application, i have a formula field to calculate amount formula below

Qty (integer)*price(decimal, max length=21, scale=6) = amount

when 

300 * 0.017273=5.1819 -> correct

3000 * 0.017273=NaN-> why this is happening?

another issues i have as below;

price(decimal, max length=21, scale=6) when the document saved , the value change from 0.017273 => 0.02

300*0.02=> 6, this is not acceptable by user.

how should i resolve this two issues? please help me , thank you very much.

[OlaN · Accepted solution]

Hi,

I haven't worked that much with formula fields, so I can't say what the issue is in this specific case.

But as a workaround you could instead set the value using a business rule, that calculates the field when the quantity and/or price fields changes.

Something like this should work:

var price = parseInt(current.getValue('u_field_for_quantity'));
var amount =  parseFloat(current.getValue('u_field_for_amount'));
var sum = price*amount;
current.setValue('u_your_sum_field', sum.toFixed(6));

[Satishkumar B]

Hi @yandp 

to ensure accurate calculation and maintain the decimal precision for your custom application:

Client Script for Accurate Calculation and Precision

1. Field Configuration:
- Ensure the `price` field is configured with a `Max Length` of 21 and a `Scale` of 6 in the table schema.

2. Client Script:

function onLoad() {
// Ensure the calculation runs initially and on change
calculateAmount();

// Add event listeners for real-time updates
g_form.getControl('qty').addEventListener('change', calculateAmount);
g_form.getControl('price').addEventListener('change', calculateAmount);
}

function calculateAmount() {
var qty = parseInt(g_form.getValue('qty'), 10);
var price = parseFloat(g_form.getValue('price'));

if (!isNaN(qty) && !isNaN(price)) {
// Calculate amount with high precision
var amount = (qty * price).toFixed(6); // Ensure the result has 6 decimal places
g_form.setValue('amount', amount);
} else {
g_form.setValue('amount', 'NaN'); // Handle invalid inputs
}
}

// Ensure the precision of price field is maintained on submit
function onSubmit() {
var price = parseFloat(g_form.getValue('price')).toFixed(6);
g_form.setValue('price', price);
}

Explanation:
- calculateAmount Function: Performs the calculation with proper parsing and sets the `amount` field to 6 decimal places.
- onSubmit Function: Ensures the `price` field is saved with 6 decimal places.

-----------------------------------------------------------------------------------

Please consider marking my reply as Helpful 👍 and/or Accept Solution ✔️, if applicable. Thanks!

[Community Alums]

Hi @yandp ,

Issue 1: NaN Calculation
The NaN result likely occurred because the price and/or quantity fields were not being parsed correctly as numbers. If either of these values were not valid numbers, the multiplication would result in NaN.

Solution: Ensure both fields are correctly parsed as numbers before performing the calculation. Use parseInt for integer fields and parseFloat for decimal fields.

Issue 2: Precision Issue
The price field was being rounded to two decimal places (0.02) when saved, instead of retaining six decimal places (0.017273). This rounding caused incorrect calculations.

Solution: Ensure that the price field retains its precision of six decimal places when saved and used in calculations. This can be achieved by formatting the field to six decimal places using .toFixed(6).

Business Rule Script

var qty = parseInt(current.getValue('u_field_for_quantity'));
var price = parseFloat(current.getValue('u_field_for_price'));
if (!isNaN(qty) && !isNaN(price)) {
    var amount = qty * price;
    current.setValue('u_field_for_amount', amount.toFixed(6));
}

Client Script -

    var qty = parseInt(g_form.getValue('u_field_for_quantity'));
    var price = parseFloat(g_form.getValue('u_field_for_price'));
    if (!isNaN(qty) && !isNaN(price)) {
        var amount = (qty * price).toFixed(6);
        g_form.setValue('u_field_for_amount', amount);
    } 

If my response has resolved your query, please consider giving it a thumbs up ‌‌ and marking it as the correct answer‌‌!

Thanks & Regards,

Sanjay Kumar