Using CAML query to filter a SharePoint list in Power Automate

I came across an instance where I wanted to filter a REST API call on a SharePoint hyperlink field. However, this cannot be done using your traditional Odata filter. Therefore, I had to filter my query using CAML query.

CAML query was used quite frequently in older versions of SharePoint but has taken a backseat recently. However, if you wanted to brush up on your CAML query skills, there is a handy tool you can download which creates the query for you by connecting to your environment and building the query based on your requirements. U2U Caml Query Builder for SharePoint 2013 can also be used on SharePoint Online and is a handy tool to have.

Once you have created your CAML query, it’s time to plug it into your API call. However, as I discovered with using the tool I mentioned above, filtering on a URL was more difficult than first thought. It turns out that the query does not like periods (.) in the query. If I tried to filter the list based on the whole URL, I would receive no results. If I filtered on the last bit of the URL (sans periods), I received my expected result.

Additionally, hyperlink fields in SharePoint have two elements; the URL and the description. Therefore, in the CAML query, you need to use either contains or begins with filters. This may mean that you will have to do extra filtering after you receive your result back from your API call. For example, If you have a URL that contains /sites/IT and another URL that contains /sites/ITHelpdesk and you are only filtering on /sites/IT, both results will be returned.

It’s not perfect, but currently Microsoft doesn’t offer us a way to filter the hyperlink field through Odata so we have to make do. If you would like to filter on the hyperlink field using CAML query, this is the API call I have used. I decided to use no metadata to reduce the amount of information I receive back in the API call as I was only interested in the values of the item.

_api/web/Lists/GetByTitle('Sites')/GetItems(query=@v1)?@v1={"ViewXml":"<View><Query><Where><Contains><FieldRef Name='SiteURL' /><Value Type='URL'>sites/IT</Value></Contains></Where></Query></View>"}

{
  "Accept": "application/json;odata=nometadata"
}