How Do I Send a Email Reminder for Dates in Multiple Columns in Excel?

[ccranberg]

Hi All,

Problem/Question:

I am building off of an older flow that worked great and would send me email reminders when a date in a single column in an Excel document was seven days away from the present date. My current flow aims to do the same thing but simultaneously look over multiple columns in an Excel document. I have looked at Filter Query in the "List rows present in a table" item and tried adding a Filter Array item, but neither option seems capable of looking at more than a single column. Is there a way I can have the Flow look through multiple columns and send an email if a cell in any of them contains a date that meets my criteria?

Pictures of Flow:

Other Idea:

I also tried concatenating all of the dates into a single column in Excel and added a Filter Array item that would filter out cells containing my "Reminder Date" variable; however, it seemed to select every single cell and send an email, regardless of date. I wondered if the flow was somehow pulling anything with a date that matched the format of my variable's date, but not the actual numbers of my variable's date. Kind of unsure why this didn't work out either.

Thanks in advance for any help you can provide!

[v-chengfen-msft]

Hi @ccranberg ,

Do you want:
1. Check excel to get the row whose date is listed seven days later
2. Stitch the row together and then send the mail as content?

Here is test for your reference:

Here is excel :

Here is flow:

Here is result:

Best Regards

Cheng Feng

[ccranberg]

Hello,

This seems like a step in the right direction and a more efficient way of sorting for dates days out. However, I am looking to apply this to multiple columns (~20 columns). Would it be possible to somehow expand this so it can send me a notification if a date in any of the 20 columns is 77 days away? That's where I'm having trouble.

[v-chengfen-msft]

HI @ccranberg ,

Contains can only be used once in a FilterQuery of [List rows present in a table]

Please use [Filter array] after [List rows present in a table]

Choose "Edit in advanced mode"

Use @or(equals(),.........) in it

Twenty columns can be used or, if any of the date is equal to the date after 77 days, it will be filtered out

Put the filtered results into the Html table, and the form will be generated

Best Regards

Cheng Feng

[ccranberg]

Oh this seems very promising. I'm trying it out, but I'm getting an invalid expression in the filter array error. Is my syntax correct:

@OR(equals('field_1', variables('VarReminderDate'),equals('field_2', variables('VarReminderDate')))

I'd like to keep adding field_#'s, but it doesn't seem to work with just the two. Perhaps I need to format the expression differently?

Thanks again for your help!

[v-chengfen-msft · Accepted solution]

Hi @ccranberg ,

The cause of this problem is
1. There is an error in the formula, [or] should be lowercase
2. 'field_1' should write item()?['field_1']

Because of the PA itself, there is no hint to write the formula in [Filter Array]

1. I suggest to test this formula with Compose first,
If you can get the execution result in Compose.

2. Then copy this formula and put it in [Filter Array]

Here is a link about Filter Array for your reference:

Power Automate Filter Array + Examples - EnjoySharePoint

Best Regards

Cheng Feng