Filter SharePoint list by 2 columns

[marko198]

Hi there

I have a SharePoint list that contains info about pledged gifts.  There is a column for "Expected Date" as well as one called "Status".  I am looking to create a flow that will send a weekly email of the gifts that have a status of "Not received" and anything past the "Expected Date".  I would also like to ensure that items in the list are only related to the person that created the items in SharePoint.   That way the account manager can focus on their outstanding pledges only.

If I could have some pointers on how to improve what I have done so far that would be great.  See screenshots.

[abm]

Hi @marko198 

Check my video tutorial about how to filter SharePoint date columns.

so in your case extend the filter query by

and ExpectedDate le '@@formatDateTime(utcNow(), 'yyyy-MM-dd')'

Make sure the schema name is correct for the column ExpectedDate.

Thanks

Thanks

[marko198]

Great thank you that worked.  

As it turns out we need to filter on the above as well as date = blank.  How would I incorporate this into the solution?

Also the secondary part to this notification is to only items created by the user to the user.  That way they are not seeing items created by other people that is not relevant to them.

[abm]

Hi @marko198 

Extend your query by adding 

and GiftOutstandingNotificationDate eq null

Thanks

[marko198]

Thank you for that I think that worked.

Do you have a solution for limiting the list to only those people who created the item in SharePoint?

So if I logged 5 items in the SharePoint list but there are 200 in total I would only need to see the specific 5 in the email notification.

[abm]

Hi @marko198 

This is what you need to do. First Get all the items. Use union() expression to get unique values. Then iterate the results with each users email and filter against your SharePoint created by and other date condition you mentioned. Finally using the results you can email to each user via notification.

Thanks

[marko198]

Thank you for this.  I am very new this all so if you are able to write out the steps for me that would be really helpful thank you.  I will try my best to follow them.

[abm]

Hi @marko198 

How many records are in total? Believe you looking to get filtered by Created By field? Please confirm?

Thanks

[marko198]

There are maybe 100 records in the list.  Yes we are looking to filter by creates by.

[abm · Accepted solution]

Hi @marko198 

Have a look at this video

https://www.youtube.com/watch?v=NaaKdgWbgTM&t=261s

Then follow the below steps

Above expression is as follows:

Author.Email expression is

item()?['Author']?['Email']

union(body('Select'),body('Select'))

Let me know how it goes.

Thanks

[marko198]

Great thank you that worked for me now.  I have managed to filter this down and its now just listing the SharePoint items needed in the Create HTML table in the email.  Thank you!