Skip to main content
Nintex Community Menu Bar
Answer

Formula Fixed Decimal

  • September 8, 2022
  • 5 replies
  • 251 views

Forum|alt.badge.img+6

I have variables that sum up dollar amounts that I display in a label. I am trying to figure out how to add two-digit fixed decimal to whole dollar amounts, or amounts with only one decimal, but haven't been able to figure it out. Does anyone know how I could update the variable formulas to do this? I could use pad right, but I can't figure out how to evaluate the number to know whether to add ".00" or "0".

 

In my example below, I want it to show 172.50 for the second line, 1100.00 for the third.

26059i7414202A94D2EDDB.png

 

I know I could add read-only number fields for each value with 2 decimal places and create rules to display my variables instead, but that takes more than double the screen real estate and has extremely limited formatting options (no bold or italic, text size, etc..)

 

I would even be okay with editing the JSON and importing it in, but I don't know how to format a number that way either - everything I find shows how to do it with JS, but that went away after the Classic form.

Best answer by AnneC

After spending (wasting?) an entire workday on it, I finally figured out how to do it without regex, just using the form functions. I created a text variable to format my control, [Form.TotalExpense] using this:

 

ifElse(substring(convertToString([Form].[TotalExpense]),
length(convertToString([Form].[TotalExpense]))-2,1)==".",
(convertToString([Form].[TotalExpense])+"0")
,
ifelse((not(contains(convertToString([Form].[TotalExpense]),"."))),
convertToString([Form].[TotalExpense])+".00",
convertToString([Form].[TotalExpense])))

 

 

Works beautifully!

5 replies

bamaeric
Forum|alt.badge.img+14
  • Apprentice
  • September 8, 2022

Forum|alt.badge.img+6
  • Author
  • Answer
  • September 9, 2022

After spending (wasting?) an entire workday on it, I finally figured out how to do it without regex, just using the form functions. I created a text variable to format my control, [Form.TotalExpense] using this:

 

ifElse(substring(convertToString([Form].[TotalExpense]),
length(convertToString([Form].[TotalExpense]))-2,1)==".",
(convertToString([Form].[TotalExpense])+"0")
,
ifelse((not(contains(convertToString([Form].[TotalExpense]),"."))),
convertToString([Form].[TotalExpense])+".00",
convertToString([Form].[TotalExpense])))

 

 

Works beautifully!


Forum|alt.badge.img+6
  • Author
  • September 9, 2022
Yes, I think I could have used a Replace function with a regular expression inside of my nested ifElse for my 'true' sections, but in this particular example, it seemed easier to just concatenate because there were only 2 possible search items and just one replace value for each. If I had more variable data, I think the opposite would be true.

Either way, thank you for getting me on the path to the answer, and for teaching me that a regex can be used in a replace.

bamaeric
Forum|alt.badge.img+14
  • Apprentice
  • September 9, 2022

Nice job! It works great.

 

I took it a step further using the video I previously shared and updated the formula to add a leading dollar sign ($) and format with commas:

 

"$" + replace(ifElse(substring(convertToString([Form].[TotalExpense]),length(convertToString([Form].[TotalExpense]))-2,1)==".",(convertToString([Form].[TotalExpense])+"0"),ifelse((not(contains(convertToString([Form].[TotalExpense]),"."))),convertToString([Form].[TotalExpense])+".00",convertToString([Form].[TotalExpense]))),"\B(?=(\d{3})+(?!\d))",",")

 


Forum|alt.badge.img+6
  • Author
  • September 9, 2022
Wonderful! In my example, the requestor didn't want it 'cluttered up' with the dollar sign and commas, but I am sure at some point, someone will want it. Thank again for your help!