cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
priyankbhat
Helper III
Helper III

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 @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
RezaDorrani
Dual Super User II
Dual Super User II

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

v-bacao-msft
Community Support
Community Support

 

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.

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

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 @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.

Helpful resources

Announcements
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.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (1,000)