CDS (current environment) List records - contains filter and square bracket issue

[bdomzalski]

Hi,

I've just encountered a problem when using CDS (Current Environment) List records action.

I tried to filter all flows (Process/workflow entity) whose name contains a string within square brackets (tagging convention: [TaggedFlow]). When I use that action and specify the filter query like "contains(name,'[OnBoarding]')" I got a full, not filtered list of items. When I change it by removing the opening square bracket it works fine.

The startswith behaves the same.

incorrect results

flow showing issue

[v-bacao-msft · Accepted solution]

Hi @bdomzalski ,

I am afraid that we may not be able to obtain the data you need through the Process entity.

I used List records action and List Flow as Admin action to test separately and used Filter array to filter the flow with [Test] in the name, the latter performed as expected, and the result of the former was 0. Filter array does not ignore any Symbol, but it still cannot get the desired record from the processes entity.

Although I am not quite sure whether we can rely on the processes entity to filter Flow, I recommend that you use List Flow as Admin action to filter Flow.

Image reference:

Spoiler

Please take a try.

Best Regards,

[bdomzalski]

Thanks for pointing the List Flow as Admin action. I was so focused on flow-runs that it slipped my mind. I tried to use CDS to have the data filtered remotely but I guess there won't be so many flows that filtering on client-side will make it too slow.

My further foundings are that at Data -> Entities -> Process -> Views -> All Processes when filtering is adjusted to the presented case it behaves the same incorrect way. So that's definitely something deeper than the connector.

[HeadInTheCloud · Accepted solution]

I had a similar problem and was able to escape the brackets by doubling them. The resulting query looked like:

field1 eq 'something cool' or contains(field2,'[[something even more cool]]')

and it returns the correct results.

I opened a ticket to get Microsoft's help and the response was "You can use OR operators to apply dfferent filters on the same field (columns) but you cannot use OR to apply filters on 2 different fields(columns)". How ridiculous was that answer?

I figured it out myself after a couple hours.

[bdomzalski]

I bumped onto this issue again. This time guided by the previous answer about escaping went deeper and found the solution.

When using the contains operator (and probably others) you have to escape special characters as it would be a sql LIKE statement. It's not a bug, that's how the CDS works.

So opening square bracket, percentage sign, underscore ([, %, _) have to be escaped and the easiest is to enclose them by a pair of square brackets ([[], [%], [_]). And probably also a single quotation mark should be at those special characters list...