cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Syntax for using rounding inside a 'select' action for rounding

Can someone help with syntax, please. 

I want to round the significant figures out a calculated SharePoint column to 0. As in this post, the SharePoint returns a text value and I want to convert the dynamic field item()['DaysLeft'] inside a 'select' action. 

 

How do I make the following expression work?

div(float(int(mul(div(variables('Sum'),5),10))),10)

I tried substituting  variables('Sum') with item()['DaysLeft] but no luck.
Is there a more elegant solution?

 

Any help appreciated. Thanks. 

10 REPLIES 10
Community Support
Community Support

Re: Syntax for using rounding inside a 'select' action for rounding

Hi @Anonymous,

Could you please share a screenshot about your flow's configuration?

Could you please share a bit more about the result the item()?['DaysLeft'] formula returned? Is the DaysLeft column a Calculated type column?

Further, could you please share a bit more about your scenario?

I agree with @jan-dolejsi's thought within the thread that you provided. The float function would convert a string version for a floating-point number to an actual floating point number.

In addition,  int() and float() only accept string arguments.

On your side, please take a try with the following formula:

div(int(mul(div(item()?['DaysLeft'],5),10)),10)         /* <-- I assume that item()?['DaysLeft'] return a number value */

If the item()?['DaysLeft'] formula return a text string value, please take a try with the following formula:

div(int(mul(div(float(item()?['DaysLeft']),5),10)),10)  

Or you could consider take a try with @jan-dolejsi's thought within the thread that you mentioned.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Syntax for using rounding inside a 'select' action for rounding

Hello Kris,

 

Thanks for your reply. I still get the error 'The template language function 'div' expects its first parameter to be an integer or a decimal number. The provided value is of type 'String'' with both your suggestions. The column 'DaysLeft' is a calculated column in Sharepoint to subtract the due date with the actual date. 

 

Below a screenshot of the simplified Flow:

 

Capture.PNG

 

Thanks for helping. 

 

Community Support
Community Support

Re: Syntax for using rounding inside a 'select' action for rounding

Hi @Anonymous,

Based on the error message that you provided, it seems to tell that the result the item()?['DaysLeft'] formula returned is a string value, but the div() function needs its first argument to be an integer value or decimal value.

Please consider take a try with the following formula within your "Select" action:

div(int(mul(div(float(item()?['DaysLeft']),5),10)),10)  

then check if the issue is solved.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Syntax for using rounding inside a 'select' action for rounding

Hello @v-xida-msft

 

Thanks for the quick response. Here is the error message:

'InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "DaysLeft": "@item()['DaysLeft']", "DaysLeftRounded": "@div(int(mul(div(float(item()?['DaysLeft']),5),10)),10) " }' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'

 

Let me know what else can help troubleshooting. Thanks.  

Anonymous
Not applicable

Re: Syntax for using rounding inside a 'select' action for rounding

Hello @v-xida-msft

 

Thanks for the quick response. Here is the error message:

'InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "DaysLeft": "@item()['DaysLeft']", "DaysLeftRounded": "@div(int(mul(div(float(item()?['DaysLeft']),5),10)),10) " }' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'

 

Let me know what else can help troubleshooting. Thanks.  

Community Support
Community Support

Re: Syntax for using rounding inside a 'select' action for rounding

Hi @Anonymous,

Could you please share a bit more about the "Select" action within your app (expand the "Select" action)?

I have made a test on my side, and don't have the issue that you mentioned.

In addition, you could also take a try with the solution @jan-dolejsi  mentioned within the thread you provided, then check if the issue is solved.

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Syntax for using rounding inside a 'select' action for rounding

Hello Kris, 

 

Same issue with the solution proposed by @jan-dolejsi, when introducing the dynamic field "item()?['DaysLeft']" - the expression works well with a number. See error message below:

 

InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "DaysLeft": "@item()['DaysLeft']", "DaysLeftRounded": "@div(int(first(split(string(mul(item()?['DaysLeft'], 10.0)),'.'))), 10.0)" }' failed: 'The template language function 'mul' expects its first parameter to be an integer or a decimal number. The provided value is of type 'String'. Please see https://aka.ms/logicexpressions#mul for usage details.'.

 

It appears that the calculated column in SP is seen as a string but the formula inside the 'select' action cannot convert it. 

The flow in use (with the formula above in bold):

Capture.PNG

 

 

The SharePoint column 'DaysLeft' is an operation between two dates that returns a number with no decimals displayed:

Capture.PNG



 

Anonymous
Not applicable

Re: Syntax for using rounding inside a 'select' action for rounding

Hello @v-xida-msft

 

Did the information above help? Thanks. 

degvalentine Advocate V
Advocate V

Re: Syntax for using rounding inside a 'select' action for rounding

An easy way to drop a decimal is to subtract the modulo of 1 (i.e. the remainder after dividing by 1).

The latest error you posted is caused by "mul()" choking on the "DaysLeft" string; easily fixed with "float()".

Putting that together with your rounding strategy, I think this could work:

 

div(
  sub(
    mul(float(item()?['DaysLeft']), 10),
    mod(mul(float(item()?['DaysLeft']), 10), 1)
  ),
  10
)

 

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (7,387)