Struggling to filter an array (SharePoint Get Items output)

[ChadVKealey]

I have a list of action items with an "AssignedTo" column (of type multi-select person), an "AssigneeLastNotified" column (of type date/time) and a "Status" column (of type choice). The end goal is to have a flow that runs each morning, retrieves all items where Status is not complete and AssigneeLastNotified is more than 7 days prior, then send each unique Assignee a list of their items needing completion.

I have a Get items action that is successfully returning items where Status ne 'Complete' and AssigneeLastNotified is lt the current date/time minus 7 days. I'm building an array of unique assignee emails and would like to filter the Get items output to return the items assigned to each assignee.

I'm using an Apply to each loop running on the array of assignee emails. Within that loop, I'm trying to use the Filter array action. The input for the filter action is [body/value] output of the Get items action. In the left side, I've tried "AssignedTo" as well as "AssignedTo\EMail" and in the right side, I'm using item() (ie: the current item in the loop, which is just the unique assignee email address). For the operator, I've tried "is equal to" (which may not work since it's a multi-select person field) and "contains" (which I thought would work). In any case, I NEVER get any matches. 

Now, what does work (but I'd prefer not to do because of API call limits and throttling) is adding another "Get items" action with the same filter as the one at the start of the flow, but adding "AssignedTo\EMail eq 'item()'" to the filter query. So, yes, this is a solution, but if this process grows to hundreds of these action items (which is possible), that's a LOT of calls to SharePoint over and over. I'd much rather filter the data that's retrieved in the initial Get items action, but just can't figure out how. 

[wskinnermctc]

Can you show all of the steps? Just looking at the filter is difficult to determine. 

It appears like you are saying that you currently have two Get Items steps. You have a first Get Items 1 that is just the Emails and you are using an apply to each on that Get Items 1. Then you have another Get Items 2 within the apply to each that is looking at the whole list? 

[ChadVKealey]

Honestly, I'm not sure how showing more of the flow would help to clarify my question or issue. In simplest terms, I want to have one call to SharePoint at the start of the flow to get the relevant items, then filter those results based on a specific user being in the "AssignedTo" field (a multi-select person column). 

Basically, in the "oData filter query" field of a SharePoint "Get items" action, I can use the expression AssignedTo\EMail eq '<user-email>', but I cannot find the appropriate way to apply that same filter in the "Filter array" action. 

[grantjenkins · Accepted solution]

Hopefully this will get what you're looking for. Note that I've excluded the AssigneeLastNotified part as you already have that working. Also, due to the Multi-select Choice field called AssignedTo, I ended up using some XPath expressions to get what was needed.

For this example, I'm using the following list. I've got three tasks assigned to me, but one is completed, so I should only get two items in the email sent to me (Task 001 and Task 004).

See full flow. I'll go into each of the actions.

Get items retrieves all the items where the Status is not Completed. You would also have your date condition here.

Status ne 'Completed'

XML is a Compose that converts our data to XML so we can use XPath expressions. Note that I've also added a root node so it's value XML. The expression used is:

xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))

Assigned To is another Compose that uses XPath to extract out all the Email Addresses from the Assigned To field. It wraps the results into a union expression to remove duplicates, so we have a unique list of emails. The expression used here is:

union(xpath(outputs('XML'), '//AssignedTo/Email/text()'), xpath(outputs('XML'), '//AssignedTo/Email/text()'))

If we ran the flow now, we would get the following output.

Compose HTML Table Style contains some CSS for styling the Table when we send it via email.

<style>
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 6px 20px;
        text-align: left;
    }
    table th {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Apply to each iterates over each of the emails from Assigned To.

Filter array uses the output (value) from Get items, then uses some XPath to extract out each of the email addresses from the current item's Assigned To field and checks to see if that array of email addresses contains the current email address we are iterating over. The expression used is:

xpath(xml(json(concat('{"root": { value:', item(), '}}'))), '//AssignedTo/Email/text()')

Create HTML table uses the output from Filter array and maps Title and Created. The expression used to format the date is:

formatDateTime(item()?['Created'], 'dddd, dd MMM yyyy')

Send an email uses the Current item (the current email address we are iterating over) for the To field, and in the body, we include both the Compose HTML Table Style and Create HTML table.

When the flow runs, each person will get a list of all their items. Below is an example of what I would have received.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[ChadVKealey · Accepted solution]

I really need to learn more about xpath....seems like it makes things a LOT simpler. However, trying to replicate that Filter array action is giving me the same result I was seeing before: no matches. For example:

Here's how the action is configured: 

and here's the peek code showing the relevant expressions: 

{ "inputs": { "from": "@outputs('Get_tasks_not_completed_and_needing_reminder')?['body/value']", "where": "@equals(xpath(xml(json(concat('{\"root\": { value:', item(), '}}'))), '//AssignedTo/Email/text()'), items('Apply_to_each_assignee_email'))" } }

[grantjenkins]

In your Filter array you need to use contains as below. Effectively, you're checking to see if the array of emails for the current item contains the current email.

[ChadVKealey]

D'oh! Sorry, I overlooked that. Making that change it's working great now!