cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Unable to perform oData filter on sharepoint list Person/user column

Hi,

 

I am having issue in running odata filter when filter is on sharepoint list which has person/user column.

 

I need to generate report that will dynamically identify reviewers for any weekly updated submitted by employee. I have 2 list Employee List and Weekly update, while Reviewer can be anyone from employee list. I need to create flow that will run on both but 2nd list will have to be filtered on 1st list person/user column. 

 

Below is structure

List 1: EmployeeList

Columns: Title (text), "Employee Name" (Person/User),....

 

List 2: WeeklyUpdates

Columns: "Employee Name" (Person/User), "Updates CurrentWeek" (text), "Reviewer Name" (Person/User)

 

Filter has to happen on "Review Name" in List 2 comparing with "Employee Name" in List 1.

 

I tried 

Reviewer_x0020_Name/Email eq ....

Reviewer_x0020_Name#Email eq ....

Reviewer_x0020_Name eq ....

 

But none has worked.

 

Can someone help me on how to handle odata filter on person/user column.

 

Thanks in advance.

 

Priyank Bhat

1 ACCEPTED SOLUTION

Accepted Solutions
v-bacao-msft
Community Support
Community Support

 

Hi @Anonymous ,

 

Please try this:

Reviewer_x0020_Name/EMail eq 'test@test.com'

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
RezaDorrani
Dual Super User II
Dual Super User II

Hi @Anonymous 

 

check this

https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Odata-Filters-amp-nested-objects-with-Microsoft-Flow/ba-p/102259

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

v-bacao-msft
Community Support
Community Support

 

Hi @Anonymous ,

 

Please try to replace Email with EMail and you need to make sure that Reviewer_x0020_Name is correct.

You can do not configure Filter Query first, then execute Get items action and see if the field name is correct.

Finally, you need to add single quotes outside the parameters to the right of eq.

Please try again.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks @v-bacao-msft  for your reply.

 

Here is Json extract of List 2 without filter

{
  "statusCode": 200,
  "headers": {
    "Transfer-Encoding": "chunked",
    "Vary": "Accept-Encoding",
    "X-SharePointHealthScore": "0",
    "X-MS-SPConnector": "1",
    "X-SP-SERVERSTATE": "ReadOnly=0",
    "DATASERVICEVERSION": "3.0",
    "SPClientServiceRequestDuration": "523",
    "Strict-Transport-Security": "max-age=31536000",
    "X-FRAME-OPTIONS": "SAMEORIGIN",
    "MicrosoftSharePointTeamServices": "16.0.0.19304",
    "X-Content-Type-Options": "nosniff",
    "X-MS-InvokeApp": "1; RequireReadOnly",
    "Timing-Allow-Origin": "*",
    "x-ms-apihub-cached-response": "true",
    "Cache-Control": "max-age=0, private",
    "Date": "Mon, 16 Sep 2019 21:47:57 GMT",
    "P3P": "CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"",
    "X-AspNet-Version": "4.0.30319",
    "X-Powered-By": "ASP.NET",
    "Content-Type": "application/json; charset=utf-8",
    "Expires": "Sun, 01 Sep 2019 21:47:56 GMT",
    "Last-Modified": "Mon, 16 Sep 2019 21:47:56 GMT",
  },
  "body": {
    "value": [
      {
        "@odata.etag": "\"10\"",
        "ItemInternalId": "18216",
        "ID": 18216,
        "Title": "232857",
        "Project_x0020_ID_1": "1243-XXXXXXX Internal-Other ITO/IS Level initiatives",
        "Project_x0020_Type": "XXXXXXX Internal",
        "WeekNumb": 36.0,
        "Project_x0020_Name": "Other ITO/IS Level initiatives",
        "RAG_x0020_Status": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
          "Id": 0,
          "Value": "GREEN"
        },
        "RAG_x0020_Status#Id": 0,
        "Status_x0020_Notes": "Work with the SO leaders to determine the calendar for SO presentations to the ADM community.",
        "Reviewer_x0020_Name": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
          "Claims": "i:0#.f|membership|priyank.bhat@XXXXXXX.XXX",
          "DisplayName": "Priyank Bhat",
          "Email": "Priyank.Bhat@XXXXXXX.XXX",
          "Picture": "",
          "Department": "XXXXXX",
          "JobTitle": "232020"
        },
        "Reviewer_x0020_Name#Claims": "i:0#.f|membership|priyank.bhat@XXXXXXX.XXX",
        "Review_x0020_Status": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
          "Id": 2,
          "Value": "Completed"
        },
        "Review_x0020_Status#Id": 2,
        "Day": "Tuesday",
        "Created": "2019-09-03T08:58:43Z",
        "Today": "2019-09-07T18:30:00Z",
        "Review_x0020_Comments": "Completed",
        "Author": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
          "Claims": "i:0#.f|membership|shakti.r@XXXXXXX.XXX",
          "DisplayName": "Shakti R",
          "Email": "Shakti.r@XXXXXXX.XXX",
          "Picture": "",
          "Department": "XXXXXX",
          "JobTitle": "232857"
        },
        "Author#Claims": "i:0#.f|membership|shakti.r@XXXXXXX.XXX",
        "Modified": "2019-09-08T20:54:57Z",
        "Editor": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
          "Claims": "i:0#.f|membership|abdul.shaikhXX@XXXXXXX.XXX",
          "DisplayName": "Abdul ShaikhXX",
          "Email": "Abdul.ShaikhXX@XXXXXXX.XXX",
          "Picture": "",
          "Department": "XXXXXX",
          "JobTitle": "203942"
        },
        "Editor#Claims": "i:0#.f|membership|abdul.shaikhXX@XXXXXXX.XXX",
        "{Identifier}": "Lists%252fStatus%2bReport%252f18216_.000",
        "{IsFolder}": false,
        "{Thumbnail}": {
          "Large": null,
          "Medium": null,
          "Small": null
        },
        "{Name}": "232857",
        "{FilenameWithExtension}": "232857",
        "{Path}": "Lists/Status Report/",
        "{ContentType}": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedContentType",
          "Id": "0x0100031D5BA8792E654A960AE52055B84200",
          "Name": "Status Report"
        },
        "{ContentType}#Id": "0x0100031D5BA8792E654A960AE52055B84200",
        "{VersionNumber}": "1.0"
      }
    ]
  }
}

After filter is applied i get below error message.

 

There are 2 filter parameter. First one which is numeric value works file but issue is with second condition only.

 

Using Reviewer_x0020_Name#EMail I get below message

{
  "status": 400,
  "message": "The expression \"WeekNumb eq 36 and Reviewer_x0020_Name#EMail eq 'Priyank.Bhat@XXXXXXX.XXX'\" is not valid.\r\nclientRequestId: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX\r\nserviceRequestId: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
}
Using Reviewer_x0020_Name/EMail I get below message
{
  "status": 400,
  "message": "The expression \"WeekNumb eq 36 and Reviewer_x0020_Name/EMail eq 'Priyank.Bhat@XXXXXXX.XXX'\" is not valid.\r\nclientRequestId: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX\r\nserviceRequestId: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
}
Thanks
Priyank Bhat
Anonymous
Not applicable

Thanks @v-bacao-msft  for your reply.

 

Here is Json extract of List 2 without filter

{
  "statusCode": 200,
  "headers": {
    "Transfer-Encoding": "chunked",
    "Vary": "Accept-Encoding",
    "X-SharePointHealthScore": "0",
    "X-MS-SPConnector": "1",
    "X-SP-SERVERSTATE": "ReadOnly=0",
    "DATASERVICEVERSION": "3.0",
    "SPClientServiceRequestDuration": "523",
    "Strict-Transport-Security": "max-age=31536000",
    "X-FRAME-OPTIONS": "SAMEORIGIN",
    "MicrosoftSharePointTeamServices": "16.0.0.19304",
    "X-Content-Type-Options": "nosniff",
    "X-MS-InvokeApp": "1; RequireReadOnly",
    "Timing-Allow-Origin": "*",
    "x-ms-apihub-cached-response": "true",
    "Cache-Control": "max-age=0, private",
    "Date": "Mon, 16 Sep 2019 21:47:57 GMT",
    "P3P": "CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"",
    "X-AspNet-Version": "4.0.30319",
    "X-Powered-By": "ASP.NET",
    "Content-Type": "application/json; charset=utf-8",
    "Expires": "Sun, 01 Sep 2019 21:47:56 GMT",
    "Last-Modified": "Mon, 16 Sep 2019 21:47:56 GMT",
  },
  "body": {
    "value": [
      {
        "@odata.etag": "\"10\"",
        "ItemInternalId": "18216",
        "ID": 18216,
        "Title": "232857",
        "Project_x0020_ID_1": "1243-XXXXXXX Internal-Other ITO/IS Level initiatives",
        "Project_x0020_Type": "XXXXXXX Internal",
        "WeekNumb": 36.0,
        "Project_x0020_Name": "Other ITO/IS Level initiatives",
        "RAG_x0020_Status": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
          "Id": 0,
          "Value": "GREEN"
        },
        "RAG_x0020_Status#Id": 0,
        "Status_x0020_Notes": "Work with the SO leaders to determine the calendar for SO presentations to the ADM community.",
        "Reviewer_x0020_Name": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
          "Claims": "i:0#.f|membership|priyank.bhat@XXXXXXX.XXX",
          "DisplayName": "Priyank Bhat",
          "Email": "Priyank.Bhat@XXXXXXX.XXX",
          "Picture": "",
          "Department": "XXXXXX",
          "JobTitle": "232020"
        },
        "Reviewer_x0020_Name#Claims": "i:0#.f|membership|priyank.bhat@XXXXXXX.XXX",
        "Review_x0020_Status": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
          "Id": 2,
          "Value": "Completed"
        },
        "Review_x0020_Status#Id": 2,
        "Day": "Tuesday",
        "Created": "2019-09-03T08:58:43Z",
        "Today": "2019-09-07T18:30:00Z",
        "Review_x0020_Comments": "Completed",
        "Author": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
          "Claims": "i:0#.f|membership|shakti.r@XXXXXXX.XXX",
          "DisplayName": "Shakti R",
          "Email": "Shakti.r@XXXXXXX.XXX",
          "Picture": "",
          "Department": "XXXXXX",
          "JobTitle": "232857"
        },
        "Author#Claims": "i:0#.f|membership|shakti.r@XXXXXXX.XXX",
        "Modified": "2019-09-08T20:54:57Z",
        "Editor": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
          "Claims": "i:0#.f|membership|abdul.shaikhXX@XXXXXXX.XXX",
          "DisplayName": "Abdul ShaikhXX",
          "Email": "Abdul.ShaikhXX@XXXXXXX.XXX",
          "Picture": "",
          "Department": "XXXXXX",
          "JobTitle": "203942"
        },
        "Editor#Claims": "i:0#.f|membership|abdul.shaikhXX@XXXXXXX.XXX",
        "{Identifier}": "Lists%252fStatus%2bReport%252f18216_.000",
        "{IsFolder}": false,
        "{Thumbnail}": {
          "Large": null,
          "Medium": null,
          "Small": null
        },
        "{Name}": "232857",
        "{FilenameWithExtension}": "232857",
        "{Path}": "Lists/Status Report/",
        "{ContentType}": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedContentType",
          "Id": "0x0100031D5BA8792E654A960AE52055B84200",
          "Name": "Status Report"
        },
        "{ContentType}#Id": "0x0100031D5BA8792E654A960AE52055B84200",
        "{VersionNumber}": "1.0"
      }
    ]
  }
}

After filter is applied i get below error message.

 

There are 2 filter parameter. First one which is numeric value works file but issue is with second condition only.

 

Using Reviewer_x0020_Name#EMail I get below message

{
  "status": 400,
  "message": "The expression \"WeekNumb eq 36 and Reviewer_x0020_Name#EMail eq 'Priyank.Bhat@XXXXXXX.XXX'\" is not valid.\r\nclientRequestId: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX\r\nserviceRequestId: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
}
Using Reviewer_x0020_Name/EMail I get below message
{
  "status": 400,
  "message": "The expression \"WeekNumb eq 36 and Reviewer_x0020_Name/EMail eq 'Priyank.Bhat@XXXXXXX.XXX'\" is not valid.\r\nclientRequestId: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX\r\nserviceRequestId: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
}
Thanks
Priyank Bhat
v-bacao-msft
Community Support
Community Support

 

Hi @Anonymous ,

 

Please try this:

Reviewer_x0020_Name/EMail eq 'test@test.com'

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (1,428)