cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

How to create a flow when certain condition is met ?

Hi,

I built the reservation App with DataSource as Excel file. The Excel contains Start Date,End Date, Canceled columns along with other few columns. The dates in Start and End Columns are in dd-mmm-yyyy ex: 02-Mar-2020. I have an update status screen in App where User can select "Y" if they want to cancel the reservation. If they don't cancel the reservation then column will be empty. I would like to create a flow such that EndUser receives reminder emails everyday from their Start to End date. However,if Column "Canceled" has "Y"value then User should not receive reminder email. I am new to flows and has below flow not sure where to proceed from below. 

Any help is appreciated!

Thanks in advance for your time!Flow.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hello @Newbie12 ,

if it's a text column, then you don't need such complicated expression as that one is needed for date columns. For text columns formatDateTime(...) should be enough. You must use 'yyyy-MM-dd' format for comparison in the condition to work.

formatDateTime([ExcelDateColumn]),'yyyy-MM-dd')

To add the expression, add the 'Condition' first and enter the column from Excel.

image.png

Then select the dynamic content, copy and store its value in some text editor.

image.png

Remove the dynamic content from the condition and add the expression, using the value from previous step without the @.

image.png

It should have similar format as below.

formatDateTime(items('Apply_to_each')?['Tri'],'yyyy-MM-dd')

On the right side condition use expression for today's date in the same format

utcNow('yyyy-MM-dd')

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ ... and if you like my response, you might like also the tips and solutions on my Power Automate related blog ]

View solution in original post

6 REPLIES 6
Super User II
Super User II

Hello @Newbie12 ,

'Get a row' action gives you only a specific row in the Excel file that you define by coordinates Key Column (what column to search) and Key Value (what value is in that column on your desired row). To get all rows from your Excel file you should use 'List rows present in a table'. In that action you can also directly filter which rows you want to get, e.g. only rows where Canceled column value is not equal 'Y'.

Canceled ne 'Y'

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ ... and if you like my response, you might like also the tips and solutions on my Power Automate related blog ]

Hi  @tom_riha,

 

Thanks! I am not sure How to proceed from there ? Would it be possible for you to suggest me the steps necessary to complete my task ?

 

Thanks in advance for your help and time!

Hello @Newbie12 ,

then you need a condition that will evaluate each of the rows, and check, if the date fits between the Start date and End date. That will require a few expressions

Get today's date:
utcNow()

Get date from Excel (replace the [DateFromExcel] with the Excel column dynamic content). Dates taken from Excel files are handed over as numbers starting from 30th of December 1899, so you must add the number provided by Excel to that date):
addDays('12/30/1899',int([DateFromExcel]),'yyyy-MM-ddTHH:mm:ss')

 Using these expressions, you can build a condition to filter only rows where the date is inside the boundaries provided by the Start and End date. It's up to you if you use greater/greater or equal and less/less or equal, depending if you want to include also rows where the Start/End date is equal to today.

[Start] is greater than [today]
AND
[End] is less than [today]

image.png



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ ... and if you like my response, you might like also the tips and solutions on my Power Automate related blog ]

Hi @tom_riha,

 

If possible can you please walk me through adding dynamic content for Excel column "Signup Date" (add days formula) ? Adding below formula throws me an error.

 

addDays('12/30/1899',int(['Signup Date']),'yyyy-MM-ddTHH:mm:ss')

 

Note: Dates in Excel are in text data type in dd-mmm-yyyy format (ex:12-Mar-2020)

Thanks in advance for your time and help!

Hello @Newbie12 ,

if it's a text column, then you don't need such complicated expression as that one is needed for date columns. For text columns formatDateTime(...) should be enough. You must use 'yyyy-MM-dd' format for comparison in the condition to work.

formatDateTime([ExcelDateColumn]),'yyyy-MM-dd')

To add the expression, add the 'Condition' first and enter the column from Excel.

image.png

Then select the dynamic content, copy and store its value in some text editor.

image.png

Remove the dynamic content from the condition and add the expression, using the value from previous step without the @.

image.png

It should have similar format as below.

formatDateTime(items('Apply_to_each')?['Tri'],'yyyy-MM-dd')

On the right side condition use expression for today's date in the same format

utcNow('yyyy-MM-dd')

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ ... and if you like my response, you might like also the tips and solutions on my Power Automate related blog ]

View solution in original post

Helper IV
Helper IV

@tom_riha,

 

Great. Thank you!

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (76,166)