Less that or equal to a number in a SharePoint List column

[jbrines]

I have create a column that calculates the number of hours since an item was assigned. I want to check if that value is less than or equal to 4

But that doesn't work. What am I doing wrong?

J

[Pstork1]

Calculated columns are normally stored as a string.  You'll need to use Int() or Float() to cast the column to the right data type to be able to do the compare.

int('Hours Old')

[leyburn19]

I would put the Column into a compose first before the condition.  It is feasible for the calc field to be a null or a decimal.  At least,  the compose will show you the result as if it is null you need to handle that as well.  

It is my experience that if null or decimal,  the int() will fail.  Float caters for decimal,  not sure on null but if the calc is 4.0001 etc it will not trigger the condition.

You may need a second compose step to fine tune outcomes

You can then reference the compose in the condition

[jbrines]

@Pstork1 Thanks worked a treat

[jbrines]

Sorry @Pstork1 but @leyburn19 was right and if it has a null or decimal it fails the flow.

@leyburn19 how do you put a column in to compose before the condition?

[Pstork1]

@leyburn19 is correct that if the value in the column is null or not an integer that the simple formula I gave you will fail.  But you don't need a compose, although that makes it easier to see what is going on for troubleshooting, you just need to make the formula more complex.  Instead of using a Compose I would use a combination of Coalesce() and the format number action.  The format Number would look like this

The number field would then be filled in using the following function.

float(coalesce('Hours Old column','0'))

The coalesce will take care of the null condition by substituting '0' and the format number will output a result that will work with Int().

[jbrines]

@Pstork1 would i put this just before my condition or further up the flow?

[Pstork1]

It can go just before the condition and then use the output from it inside an Int() in the condition.  The original problem stays the same.  You need to evaluate an Int with an Int or a Float with a Float in the condition.  The format number an coalesce will let you handle Nulls and converting floating numbers to ints by rounding them off.

[jbrines]

@Pstork1 getting this error when putting in the number format.

[Pstork1]

What is the formula you are using for Format Number?  Did you pick your column off the dyanmic content screen to build the function I gave you or just cut and paste?  The syntax of the formula I gave you is correct, but you need to insert your own values

[jbrines]

@Pstork1 

I copied, pasted and edit the column field.

[Pstork1]

You need to pick the 'Hours Old" of the Dynamic content screen.  You can't just reference the name of the column directly.  

[jbrines]

I only see ID and current item in the dynamic contact scree.

[jbrines]

@Pstork1 If I type hours old the dynamic contents appears but the minute you clear tha and start typing the expression and go back to dynamic content its gone.

[Pstork1]

Sometimes the dynamic content overfilters.  Try this workaround. Put the Hours Old field into the Number field by itself.  If you hover your mouse over it you will see what the JSON representation is that you need to use.  If you then go to Peek Code on the '...' menu you can find that JSON in the code.  You can copy it from there and then go to your Expressions tab, do the rest of the function and then paste that JSON in the right place.  I know its kind of strange, but it will work.

[leyburn19]

Can you show the value of that step in your failed flow?  If Not add the compose as I said and run the flow again.  Flow will fail,  but at least you can expand the compose to show the failed value

[jbrines]

@Pstork1 

Dynamic Content code.

"@items('Apply_to_each')?['Hours_x0020_Old']"

So from this I used the following

float(coalesce('Hours_x0020_Old','0'))

If this is correct it didn't work.

[jbrines]

@leyburn19 

See below.

[Pstork1]

No, you have to use the full JSON representation, not just the column name.  The formula should read

float(coalesce(items('Apply_to_each')?['Hours_x0020_Old'],'0'))

[Pstork1 · Accepted solution]

The Format Number action rounds down the number so it doesn't have decimals, but it still outputs the number as a string.  So you still have to use INT() in the condition to translate it from a string to an integer.  Your condition should look something like this

int(outputs('Format_number')?['body'])

 when entered through the expressions tab.