Use dates from SharePoint List to send emails (Calculate Mid month /end of month and use them as conditions in a Power Automate flow)

[paulg]

Hi there,

I am new to Power Automate and i have been tasked to do the following:

Create a flow to send an email at mid month (Whenever date of month is 15th) and at the end of the month (Whenever date of the month is either 30th or 31). 

What I have done so far:

1. I set up a SharePoint list with dates in one column and a calculated column to determine whether date is mid or end. If true, return Yes else return No

My Columns:

Column1: Dates

Column2: Yes/No (if date is mid or end/if date is not mid)

2. I have created a scheduled flow to run everyday.

3. I added the "Get Item" function to read-in the SharePoint List

What I would like to achieve:

Condition:

If Column2 = Yes, Then send email.

Else do nothing

When i use the condition, it executes to false even when column2 is set to Yes. I have also tried using true on the right side with no luck. And at some point when i changed the expression, it tells me that the Value i provides is a string yet it expects an array.

Being a newbie, i cant move forward.

Please help me get out of this. Thanks in Advance

[Hardesh15]

@paulg 

What is the Output type of Calculated column in SharePoint?

Thanks

Hardesh

[paulg]

Hi @Hardesh15
The calculated column has a formula which takes in 2 expressions and returns “Yes” if one of them is correct or NO if none is correct.
The formula is an Excel "OR" Formula

 =OR(DAY(Date+1)=1,DAY(Date)=15)
Part1 is: (DAY(Date+1)=1. Checks if the date (in the “Date” column) is the last date of the month.
Part2: DAY(Date)=15). Checks if the date is is the mid date (15th)
Part23: OR operator: If either Part1 or Part2 is true, then return Yes in the Cell else return No

Am not sure if my approach above is right. Was giving it a try see if it would work.
Thanks for your help.

[Mira_Ghaly]

@paulg 

On the right hand side of your expression please try using Yes between single Quotes 'Yes'

If this post helps you with your problem, please mark your as Accepted solution.

If you like my response, please give it a Thumbs Up.

MG (Naturally Curious)

[paulg]

Hi Mira_Ghaly

I have tried the single quotes but it never works.

The problem seems to be with how Power Automate looks at the calculated value. It can't be interpreted as a boolean. For testing purposes, i created another column(not calculated) with the type "Yes/No" and set it to Yes. Then on the right side of the condition, i put the express true and it works well. May be I need a way of converting the Calculated value to a boolean Yes/No!!!

Or, is it possible to calculate it on the fly instead of calculating it inside the SharePoint list?

I really appreciate any assistance. 

[Hardesh15]

@paulg 

Do one thing add compose action and supply calculated column value as input and then run flow and show me output of compose action.

Thanks

Hardesh

[paulg]

@Hardesh15 

Okay. Not so sure where to put the compose, but I removed the condition and put compose just after "Get Items"

And here are the results for a “Yes” in the calculated column and a “No”

[Hardesh15]

@paulg 

So it is taking 0 and 1 as Boolean value. Amend your flow with this value in condition after the compose action. In condition supply output of compose in right side. Pls check for integer and string in this case.

Thanks

Hardesh

[paulg]

My apologies @Hardesh15 

I Don't seem to understand how to check if the output form the compose action is string or integer. 

And if it either of them, how should it pass it in the condition on the right side? Do I include quotes is it as a string?

In the below snap, it executes to false

Output:

Thanks, 

Paul

[Hardesh15]

@paulg 

Go to expression in dynamic window and type int(output ('compose')). When you type int(), move cursor inside bracket and click on dynamic content tab in same expression window and select output of compose from it.

Not infront of laptop otherwise i give you screens. Sorry.

Thanks

Hardesh

[Hardesh15]

@paulg 

In condition block you put compose output in left side. Put your calculated column value in left side and in right side put expression which i gave you in above post.

Thanks

Hardesh

[paulg · Accepted solution]

@Hardesh15 ,

Thanks so much for your help especially with the "compose" action option.

Actually, the 0 and 1 are strings in the face of Power Automate. I learnt that by trying to creating a variable of the  type integer (named it Compose_Output) and tried to assign it the value of compose output.. I got an error . See snap below:

To work around it, i did the following:

1. Initialized a string variable just before the compose action (named it IsSubmissionDate)

2. Created the compose action and passed in the column from SharePoint list as input.

3. Set the value of the variable "IsSubmissionDate" to the compose output.

4. Set the condition. On the left side, it takes in the variable "IsSubmissionDate" and checks if is equal to string(1). String(1) is an expression to concur with the data type from the list.

5. Send email if "IsSubmissionDate" equals to 1.

That solved the problem.

Below is the flow:

Thanks once again for your help.

Paul