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
Dual Super User
Dual 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
Dual Super User
Dual 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 Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (6,166)