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

[Anonymous]

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. 

[v-xida-msft]

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

[Anonymous]

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:

Thanks for helping. 

[v-xida-msft]

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

[Anonymous]

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]

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.  

[v-xida-msft]

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

[Anonymous]

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):

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

[Anonymous]

Hello @v-xida-msft, 

Did the information above help? Thanks. 

[degvalentine]

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
)