Send one email with all (SharePoint) list items one month before end date

[StefanBreedveld]

Hi all,

I'd like to create a flow that sends one email with all the list items that expired one month before the end date.

So far I have created this flow.

1. Recurrence - Every day

2. Get items from SharePoint list -> Filter query 'Status eq Active'

3. Initialize variable for New Calculated Enddate

4. Then I perform a calculation on field 'Enddate' (this is a calculated column in SharePoint).

5. Then i convert the time zone (for Netherland)

6. Then I want all the new calculated enddate items in a variable (is this possible?)

7. Then I perform a condition. Variable 'New calculated Enddate' equals Today's date (see Expression in image above)

8. If yes, create HTML table (to get all the SharePoint list items in 1 email)

9. Send email (Output HTML table in body email)

Now my problem in this flow is. When I change the filter query to search for one list item that meets my condition my flow works well. I receive an email with the list item i filtered in step 2. But when I want to run the flow through the entire SharePoint list I get no result back. The flow is succesfully executed but the expression result in step 7 (condition) is false. So there are no items that meet my condition.

I think this is because the variable can only store 1 SharePoint item. Bacause then the flow is executed successfully. So is it possible that multiple SharePoint list items are stored in a variable, for later use in a condition? Is yes, how?

Thanks!

[fchopo]

Hello @StefanBreedveld 

Instead of calculating a date for each item of the SharePoint list, why don't you calculate a date based on today's date and then filter the items? Here it is an example:

I filter the items based on a column called "Expense Date" (which in your case should be "Enddate") and  their status. Therefore, it's not necessary create another condition.

Hope it helps!

Ferran

[StefanBreedveld]

Hi Ferran,

Thanks, but when I recreate your solution I get the following error in the filter query of step ' Get Items in the SharePoint list'

"message": "The field 'Einddatumx' of type 'Calculated' cannot be used in the query filter expression.

I search the forum and is it possible that I can't add a Calculated column from SharePoint in the filter query of step Get Items from SharePoint list? I could add a new date column that copies the value from the Calculated column to the new column, but that is not the best solution. 

Can I perform the filter for this Calculated column in a different way?

[fchopo]

Hello @StefanBreedveld 

Maybe you could create a view in the SharePoint list with the filters you want applied, and use it in the Get Items Action (you can do it, specifying the view name in the limit by columns field).

 Hope it helps!

Ferran

[StefanBreedveld]

Thanks! That's a possible solution. I have to test this.
Another option I found is to use a Filter Array. (see image)

Anyone experience with this when working with a Calculated column?

•