Solved: Re: Unable to perform oData filter on sharepoint l...

Anonymous · ‎09-16-2019

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

v-bacao-msft · ‎09-17-2019

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

RezaDorrani · ‎09-17-2019

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 · ‎09-17-2019

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 · ‎09-17-2019

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 · ‎09-17-2019

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 · ‎09-17-2019

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.

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

Helpful resources

Join us for Microsoft Power Platform Conference

June User Group Leader Call

Register for a Free Workshop

Help make Flow Design easier