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:
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?
Solved! Go to Solution.
@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! 🙂
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.
@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! 🙂
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
User | Count |
---|---|
93 | |
45 | |
21 | |
17 | |
16 |
User | Count |
---|---|
137 | |
50 | |
42 | |
39 | |
29 |