cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dmistry
Post Prodigy
Post Prodigy

How to default Flow Selection to output number field to two decimal places

Hi,

 

In a SPO list, I have a number field that logs total expenses and I have limited it to two decimal places from the list settings. 

556.JPG

Inside my flow, I am simply fetching these field values by using get items->select-create html table->compose and then using send an email action with html enabled, I am sending it for approval,  see sample below

896.JPG

Now as shown above, I want to make sure that 146.4 is represented as 146.40, is this something possible to do? See flow screenshot below. 

7745.JPG

Thanks in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
ScottShearer
Super User
Super User

@dmistry 

I've seen this question before and there really is no clean way to accomplish this.  That said,  I was able to construct an expression that can be used for this purpose.  It is, without a doubt, the ugliest expression that I have ever composed.  In addition, there is no telling how much this will slow down your Flow.   In short, I did this mainly to see if I could - it works but I'm not sure it is a good idea to use this in a production Flow.

One caveat is that I am not doing rounding - I am just taking the 2 characters to the right of the decimal point if there are more than 2.

I tested the following expression in a variety of situations with input from a SharePoint column formatted as per your example.

Here is my expression:

 

if(equals(mod(variables('varNumber'),1),0),concat(variables('varNumber'),'.00'),concat(first(split(string(variables('varNumber')),'.')),'.',substring(concat(last(split(string(variables('varNumber')),'.')),'0'),0,2)))

 

if(equals(mod(variables('varNumber'),1),0),concat(variables('varNumber'),'.00'),concat(first(split(string(variables('varNumber')),'.')),'.',substring(concat(last(split(string(variables('varNumber')),'.')),'0'),0,2)))
 
 
 
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

7 REPLIES 7
ScottShearer
Super User
Super User

@dmistry 

I've seen this question before and there really is no clean way to accomplish this.  That said,  I was able to construct an expression that can be used for this purpose.  It is, without a doubt, the ugliest expression that I have ever composed.  In addition, there is no telling how much this will slow down your Flow.   In short, I did this mainly to see if I could - it works but I'm not sure it is a good idea to use this in a production Flow.

One caveat is that I am not doing rounding - I am just taking the 2 characters to the right of the decimal point if there are more than 2.

I tested the following expression in a variety of situations with input from a SharePoint column formatted as per your example.

Here is my expression:

 

if(equals(mod(variables('varNumber'),1),0),concat(variables('varNumber'),'.00'),concat(first(split(string(variables('varNumber')),'.')),'.',substring(concat(last(split(string(variables('varNumber')),'.')),'0'),0,2)))

 

if(equals(mod(variables('varNumber'),1),0),concat(variables('varNumber'),'.00'),concat(first(split(string(variables('varNumber')),'.')),'.',substring(concat(last(split(string(variables('varNumber')),'.')),'0'),0,2)))
 
 
 
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

Thanks a lot for your answer @ScottShearer  , as always, appreciate it.

Yes I was reading in some forums and could not find a better way to resolve this. I will try this and see if it works well. Although my flow is very complex and so won't be ideal if this expression would slow it down. Additonally I have about 5 more fields for which I would have to go for the expression appraoch so believe shouldn't go for it as far as prod flow is concerned, thoughts?

@dmistry 

I suggest giving it a try and see if slows down the Flow as much as I think that it might (I've been wrong before...).  Try it with just one column first.

Please let me know how it goes.

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

Thanks for the valuable comments @ScottShearer , it works well and I don't see any lag at all for the flow run.

@ScottShearer  hey scott I am seeing a small issue with the formula you provided, I m using formula: 

if(equals(mod(item()?['Amount'],1),0),
concat(item()?['Amount'],'.00'),
concat(first(split(string(item()?['Amount']),'.')),'.',
substring(concat(last(split(string(item()?['Amount']),'.')),'0'),0,2))) which works for almost all cases.

 

however recently I noticed that for an amount 40.325, it resolved it as 40.32 which is off by 0.01, in theory it should resolve as 40.33, any thoughts to this? Can you please help revising the formula?thanks in advance.  

@dmistry 

The bottom line is that Flow doesn't have a round expression - I wish it did.  The only way I see to do this might be to subtract the string of decimals that won't be use from 1.  So, in your case, subtract .5 (you'll need to move the decimal) from 1.  If the result is greater than or equal to .5 then round up.  Otherwise, disregard.  The expression to do this would be ugly and should likely be done in a series of steps.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

Thanks a lot @ScottShearer . Would it be possible for you share the expression, it would help a lot.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,276)