Get Items Filter Query for email

[P_Molitor]

I am needing to set up a filter query for a Get items function that filters for a YES/NO column and a column with email addresses in my Sharepoint list. I tried the following

MoveSlipComplete eq 'NO' and NewKo-Cho eq 'name@company.com'

I would like the email to be the dynamic responders email, but while troubleshooting I am just using my own email. When using this filter query, my process fails the Get Items function for :

"The expression "MoveSlipComplete eq 'NO' and NewKo-Cho eq 'name@company.com'" is not valid. Creating query failed.
clientRequestId: 4ab935dd-3af2-49bb-92d4-39571df20b3d
serviceRequestId: 4ab935dd-3af2-49bb-92d4-39571df20b3d

When this failed I thought that the special characters in the email were causing the issue so I tried this:

MoveSlipComplete eq 'NO' and NewKo-Cho eq 'name@company.com'

Unfortunately, I still get the same error. the purpose of this flow is so that an individual can submit a Form that will then check my list for any lines that have an incomplete status and their email listed as the New Ko-Cho. The Flow will then compile the data returned into an HTML table and email it to them. I just can't get this filter to work when I add an email. I thought I may be able to accomplish the same thing using just the eq 'NO' filter then a union function to trim the return down to items that match their email address, but I honestly do not understand the union function. I appreciate any help. Thank you!

[v-dezhili-msft]

Hi @P_Molitor ,

What would the MoveSlipComplete and NewKo-Cho columns look like in your Sharepoint table?
You can try to use filter array action to query the data you want.
Or you can try to turn on the Experimental Features setting of power automate.

Best Regards,

Dezhi

[P_Molitor · Accepted solution]

First, let me thank you for taking the time to offer a suggestion!

Like I said in the OP, I was having trouble with the filter query accepting an email address as a filter item because the "New Ko-Cho" column is email addresses. I ended up doing the following:

Even though I needed the filter to search for a "NO" in the incomplete column and the responder's email address in the "New Ko-Cho" column, I ran the GET ITEMS process with only the filter of:

MoveSlipComplete eq 'NO'

Then I used a FILTER ARRAY process to take the [value] of the GET ITEMS process and check for items where the "New Ko-Cho" is equal to the [Responders Email].

{
    "inputs": {
        "from": "@outputs('Get_items')?['body/value']",
        "where": "@equals(item()?['NewKo_x002d_Cho'], outputs('Get_response_details')?['body/responder'])"
    },

This gave me the rows that I needed but many more columns than I needed or that made it feasible to put in to a table and email it to the responder. I solved this by using the SELECT process. This took some learning to figure out how to make it select the correct <tBody> items from the array. This is what it ended up looking like-

The formula I used for the value side looked like this:

item()?['TMName']

This would take the Body Value from my Array in the set of [TMName] (the column name in my sharepoint list) and pair it with a new Header, "Name". After the SELECT process I had all the Rows I needed and stripped out all of the Columns I didn't need leaving me with 5 columns of information from the necessary Rows returned by the ODATA Filter and the FILTER ARRAY. All I needed to do after this was create the HTML Table from the SELECT output and email it. I added a COMPOSE process before the email to make the columns of the Table center aligned. 

 

And voila! It works just as I had desired. Hopefully this solution, as NOOB as it is, will help someone else!

[P_Molitor]

Just now, thinking about the SELECT process, I may have been able to accomplish the same thing by simply selecting "Custom" for the *Columns in my CREATE HTML TABLE process and using the same formula for the Value side as I did in the SELECT process and get rid of the SELECT process all together. I'll probably test that later.

[P_Molitor]

Confirmed, I just removed the SELECT action and set up my CREATE HTML TABLE action for custom Columns and used the formula value from my SELECT action and it worked perfectly without using the SELECT.