cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Seuadr
Helper IV
Helper IV

sharepoint http request - filter using an array variable?

Hi all,

 

I'm building a flow that is triggering off an email and certain strings in the email are appended to an array variable.

 

I want to then use either sharepoint's GET or http request to sharepoint to retrieve some information from a list based on the individual array items.

 

Initially, I started using http sharepoint request because there are 5000+ items, and, i was hoping to select 1 column and then filter it in the request by the array variable(s) to only return the appropriate values, which will change from email message to email message.

so, for example, i have an email that comes in and part of the body says:

Alarm Message: OHB_CONTROL_AIR_MONR.alm

i'm appending OHB_CONTROL_AIR_MONR.alm to an array variable, and then i'd like tofilter in the sharepoint list "field_1" (called File Name here) by OHB_CONTROL_AIR_MONR.alm so the response would contain:

Seuadr_0-1646058935413.png

i think i'm close - i have a uri request like this:

_api/lists/getbytitle('Alarm Message Files')/items?$select=field_1&$filter=@{outputs('alarm_name_array')}

which then sends (as an example):
_api/lists/getbytitle('List_name')/items?$select=field_1&$filter=["OHB_CONTROL_AIR_MONR.alm"]

but i get the following error:

{
  "status": 400,
  "message": "{\"odata.error\":{\"code\":\"-1, Microsoft.SharePoint.Client.InvalidClientQueryException\",\"message\":{\"lang\":\"en-US\",\"value\":\"The $filter expression \\\"[\\\"OHB_CONTROL_AIR_MONR.alm\\\"]\\\" is not valid.\"}}}\r\nclientRequestId: 2acfba24-9c83-426a-9eae-806481d04583\r\nserviceRequestId: 082f25a0-50ba-1000-8020-d5411a18803c",
  "source": "https://domain.com/sites/site/_api/lists/getbytitle('List%20Name')/items?$select=field_1&$filter=[%OHB_CONTROL_AIR_MONR.alm%22]",
  "errors": []
}

 i'm completely inexperienced with rest, so, i'm kinda assuming this has to do with the filter i'm just not sure how to change it?


1 ACCEPTED SOLUTION

Accepted Solutions
Seuadr
Helper IV
Helper IV

@shoog thanks for that correction! that makes sense that you wouldn't need to select - i'm so new i just didn't think about it.

so - that still leaves me with the response error:

 "message": "{\"odata.error\":{\"code\":\"-1, Microsoft.SharePoint.Client.InvalidClientQueryException\",\"message\":{\"lang\":\"en-US\",\"value\":\"The $filter expression \\\"[\\\"OHB_CONTROL_AIR_MONR.alm\\\"]\\\" is not valid.\"}}}

which i am assuming is because i'm passing 

_api/lists/getbytitle('List_name')/items?$filter=field_1 eq @{outputs(alarm_name_array)} 

instead of (as an individual string):

_api/lists/getbytitle('List_name')/items?$filter=field_1 eq 'OHB_CONTROL_AIR_MONR.alm'

now, i tried a compose of the current iteration of the apply to each:

_api/lists/getbytitle('Alarm Message Files')/items?$filter=field_1 eq '@{outputs('Compose_Filename')}'

 which actually works - so, i'm not sure why it dislikes the variable, but a compose of the variable is fine, but, i'll take it! 🙂

View solution in original post

4 REPLIES 4
shoog
Super User
Super User

The correct syntax would be something like this:

_api/lists/getbytitle('List_name')/items?$filter=field_1 eq 'OHB_CONTROL_AIR_MONR.alm'

 

select determines what properties of the items get returned by the query, so you don't need that to filter.

Seuadr
Helper IV
Helper IV

@shoog thanks for that correction! that makes sense that you wouldn't need to select - i'm so new i just didn't think about it.

so - that still leaves me with the response error:

 "message": "{\"odata.error\":{\"code\":\"-1, Microsoft.SharePoint.Client.InvalidClientQueryException\",\"message\":{\"lang\":\"en-US\",\"value\":\"The $filter expression \\\"[\\\"OHB_CONTROL_AIR_MONR.alm\\\"]\\\" is not valid.\"}}}

which i am assuming is because i'm passing 

_api/lists/getbytitle('List_name')/items?$filter=field_1 eq @{outputs(alarm_name_array)} 

instead of (as an individual string):

_api/lists/getbytitle('List_name')/items?$filter=field_1 eq 'OHB_CONTROL_AIR_MONR.alm'

now, i tried a compose of the current iteration of the apply to each:

_api/lists/getbytitle('Alarm Message Files')/items?$filter=field_1 eq '@{outputs('Compose_Filename')}'

 which actually works - so, i'm not sure why it dislikes the variable, but a compose of the variable is fine, but, i'll take it! 🙂

shoog
Super User
Super User

The error comes from the missing quotes. All text strings should be enclosed by quotes.

 

The next problem would be the brackets that get added because of the array, which aren't actually part of the text.

 

By using the compose action you've changed it to a regular text, thereby removing the brackets.

You could try to see if the array is necessary at all so just use string variables if you want to optimize.

Oh that makes sense, thanks!

I am using an array because there are potentially 20+ entities per email and there is data coming from several sources so tying them all together seemed to make the most sense with an array.

 

I've always used get instead in the past which has been more straight forward, but impacted by maximum number of results and processing time.

 

Regards,

 

Jared

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.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

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.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,696)