Power Automate

Answered

Unable to obtain integers

(0) Share

Report

Posted on by vsriv90

Hello,

I am trying to create a very simple flow, where I get a weekly Teams notification when:

(1) If a project is not finished

(2) The deadline is today, or was in the past.

The formula used here is:

---------------------------------

My automate flow is also very straightforward:

Here the condition is:

Somehow, I keep getting the following error:

I don't know why I am getting this error, as the output file shows that the "Deadline Met/Was" option is always an integer:

I have tried converting the input to integers (based on the comments in other posts), but it does not seem to be working. Can someone simply tell me either:

(1) What EXACT formula to use in my Excel; (or)

(2) What to change in my Automate flow?

I simply want a notification (which is working with other conditions) where if the project is not finished and the deadline is today/was already in the past.

Thanks a lot, I have been breaking my head on this small issue for hours, and really think it's time I ask for some expert help 🙂

Categories:

General topics

I have the same question (0)

All responses (32)

Answers (1)

DJ_Jamba 2,837 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi @vsriv90
According to your screenshot the Deadline/Met column contains either a number or text, so you need to cater for that in the condition action.
Add a row in your condition before this one:

which should be configured like this:
Deadline/Met Column is not equal to Met

... which leaves you two criteria rows in the AND block.
Then the flow will only consider the rows that have a number and will ignore the rows that have the word Met

Was this reply helpful? Yes No

vsriv90

36 on at

Like (0)

Report

Copy link

Link copied!

Hello @DJ_Jamba, thanks a lot for your reply! I had tried that already. The issue is that I start getting updates even if the deadline is not today or in the past (i.e. the number of days till the deadline is positive).

I even tried to get around the issue with an additional text "Still Time" if the deadline is in the future:

I set up a condition where I should get notified only if either one of "Achieved" or "Still Time" is not met:

And yet, I somehow get notified about EACH ROW in the excel....

Again, there should be a simple way, I just can't find it in 3 days of research 😂

----------------------------------------------

P.S.: Here's the original excel, in case you want it (I have intentionally left a few tabs empty, because the original excel is huge and might not have every column filled up).

Name	Deadline	Project Finished	Deadline Met/Was
Deadline 1	21/03/2024	Yes	Achieved
Deadline 2	21/03/2024	No	0
Deadline 3	25/03/2024	No	Still Time
Deadline 4	15/03/2024		-6
Deadline 5	18/03/2024	Yes	Achieved
Deadline 6	19/03/2024	No	-2
Deadline 7	23/03/2024	Yes	Achieved
Deadline 8	01/06/2024		Still Time

The formula used in column D is:
=IF([@[Project Finished]]="Yes", "Achieved", IF(INT(B2-TODAY())>0,"Still Time", INT(B2-TODAY()) ) )
----------------------------------------------

Was this reply helpful? Yes No

DJ_Jamba 2,837 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @vsriv90
Your OP was related to an error because you were comparing an int to text and I advised how to correct that error.
Did that solve the error?

Now, I think you are asking for something else, along the lines of you are receiving emails in an undesired way because the flow might have one or more incorrect if statements.
If that's the case, I need to see the whole flow if possible (omitting any sensitive data)

Was this reply helpful? Yes No
vsriv90 36 on at

Like (0)

Report
Copy link

Link copied!
Hello again @DJ_Jamba,

I was actually talking about your original answer itself. I had tried the flow that you suggested:

But it gave me the same error:
Hence I tried the same thing that you suggested, but in a slightly different way, where I added an extra test. But you can ignore that for now, and we can go back to discussing this error 🙂

-------------------
Again, just for additional info, the output from "List rows present" is:

{"body":{"value":[{"@odata.etag":"","ItemInternalId":"80d8d372-650a-4424-b4bf-9d8ce5fab243","Name":"Deadline 1","Deadline":"45372","Project Finished":"Yes","Deadline Met/Was":"Achieved"},{"@odata.etag":"","ItemInternalId":"28aff3b5-b572-4651-a564-d6589a6eb5ea","Name":"Deadline 2","Deadline":"45372","Project Finished":"No","Deadline Met/Was":"0"},{"@odata.etag":"","ItemInternalId":"08d40502-aaf8-4924-8508-1500c9c4fd4e","Name":"Deadline 3","Deadline":"45376","Project Finished":"No","Deadline Met/Was":"4"},{"@odata.etag":"","ItemInternalId":"a7806b49-58f8-49e7-8c94-be3ce3729fea","Name":"Deadline 4","Deadline":"45366","Project Finished":"","Deadline Met/Was":"-6"},{"@odata.etag":"","ItemInternalId":"33ca2ab3-907a-436d-97de-a704079dce5c","Name":"Deadline 5","Deadline":"45369","Project Finished":"Yes","Deadline Met/Was":"Achieved"},{"@odata.etag":"","ItemInternalId":"3f25b32d-2243-4b5e-99a3-e66900e4ce63","Name":"Deadline 6","Deadline":"45370","Project Finished":"No","Deadline Met/Was":"-2"},{"@odata.etag":"","ItemInternalId":"5964d4a2-da7f-47f9-ae02-3f785ab4fc03","Name":"Deadline 7","Deadline":"45374","Project Finished":"Yes","Deadline Met/Was":"Achieved"},{"@odata.etag":"","ItemInternalId":"bf7ad988-2750-41f7-b4b1-3492589750dd","Name":"Deadline 8","Deadline":"45444","Project Finished":"","Deadline Met/Was":"72"}]}}

Was this reply helpful? Yes No
DJ_Jamba 2,837 Moderator on at

Like (0)

Report
Copy link

Link copied!

OK - I think there are 2 scenarios that can cause the error so without getting into regex, can you try the following to solve the error?

Make sure the 3 rows are in an AND block

Was this reply helpful? Yes No
ThijsB 2 on at

Like (0)

Report
Copy link

Link copied!

Hi, you can use your condition checking if it is not equal to "achieved" or "still time", but you need to change the condition expression to use AND instead of OR. Otherwise, you will always get a "true" output, as it's always either not "achieved", or not "still time" (or both).

Was this reply helpful? Yes No
vsriv90 36 on at

Like (0)

Report
Copy link

Link copied!

Well, the flow is working, but it is giving me ALL the rows as notification (I don't see why not too, it's working for each row, not just the "Condition Met/Was" column.

Was this reply helpful? Yes No
DJ_Jamba 2,837 Moderator on at

Like (1)

Report
Copy link

Link copied!

OK good. First problem solved.
However, you may need to add a couple more rows because there could be other Text values e.g. Still Time

Now we can look at the condition/if statement for the notifications.
Could you expand on this please?
"I simply want a notification (which is working with other conditions) where if the project is not finished and the deadline is today/was already in the past. "
What if the project finished column does not have a value in it? How should the flow treat that column?
The way I see it is we should only be concerned with:
Project Finished = No
Deadline <= Today
Could you confirm if that is what you are looking for or advise if otherwise?

Was this reply helpful? Yes No
vsriv90 36 on at

Like (0)

Report
Copy link

Link copied!

Exactly what I am looking for!

Was this reply helpful? Yes No
DJ_Jamba 2,837 Moderator on at

Like (1)

Report
Copy link

Link copied!
So seeing as I don't know if the Deadline excel column is text or an excel date (which is actually a number), could you try this first?

The formula in the less than or equal to row is:
formatDateTime(utcNow(),'yyyy-MM-dd')
Both of the fields on the left are looking at the Deadline column (not the Deadline Met/Was column)

Was this reply helpful? Yes No