cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
priyankbhat
Level: Powered On

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
Community Support Team
Community Support Team

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

 

Hi @priyankbhat ,

 

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
Dual Super User
Dual Super User

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

Hi @priyankbhat 

 

check this

https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Odata-Filters-amp-nested-objects-w...

 

Regards,

Reza Dorrani

 

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

Community Support Team
Community Support Team

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

 

Hi @priyankbhat ,

 

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.
priyankbhat
Level: Powered On

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

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
priyankbhat
Level: Powered On

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

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
Community Support Team
Community Support Team

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

 

Hi @priyankbhat ,

 

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 296 members 5,906 guests
Please welcome our newest community members: