Set specific decimal notation and formatting

I have a spreadsheet which I convert to a table, in the Amount column I would like to set the decimal notation to 2 but not sure what is the right formula or action/s do I need to use to get the expected results below.
Expected results:
If Amount value is.
1. 123.4 output should be 123.40
2. 123.456 output should be 123.46
3. 123 output should be 123.00

I have used the following
1. result = fields['x'].toString().replace(/\B(?=(\d{3})+(?!\d))/g, ".")
The output here is as is ex. 123.4 output is 123.4.
2. The ToFixed is not recognized in the field formulas
3. The Field:Set value of a decimal field has the same result as number 1.
4. I also follow the recommendation in the link below but it only rounds up the number.
https://community.pushbot.com/discussion/1360/how-to-round-a-number-when-it-has-both-commas-and-decimals#latest

Hopefully someone can give me some insights.
Thanks

Best Answer

  • Chuck_146211
    Chuck_146211 Posts: 36 admin
    Answer ✓

    Here is a formula that can be used to accomplish this.

    Syntax for using the Formula for the Tables: Apply formulas to columns action:

    columns['Amount'] = (Math.round(columns['Amount'] * 100) / 100).toFixed(2);
    

    Syntax if you need this result for workflow fields rather than a table, using the Field Formula action:

    result = (Math.round(fields["your-field-name-here"] * 100) / 100).toFixed(2);