filter field with "[]"

[EricLi]

My collection fields include brackets like "AA[bb]", how to write the filter script? "[AA[bb]]='1'" looks do not work. 

And where I can find the documents talking about filter syntax.

[EricLi]

I use "[AA[bb"&"\]]='1'" address this issue. But don't know why the second '[' do not need to use "\".

[EricLi]

new question is that how can I grant the value to a stage. Use [collection.AA[bb]] in a calculate stage does not work.

[Nandhakumar]

Hi @EricLi 

The easiest way is to rename your collection fields by replcing "[" and  "]" with "_"(underscore).

create a custom code stage as below in your collection manipulation utility,

Input will be your collection collection withcolumn names "[]"

code:

' Inputs:
' - Input_Collection (Collection)
' Outputs:
' - Output_Collection (Collection)

Dim tempTable As DataTable = Input_Collection.Clone()

' Replace special characters in column names
For Each column As DataColumn In tempTable.Columns
column.ColumnName = Microsoft.VisualBasic.Replace(column.ColumnName, "[", "_")
column.ColumnName = Microsoft.VisualBasic.Replace(column.ColumnName, "]", "_")
Next

' Copy rows from original to new table
For Each row As DataRow In Input_Collection.Rows
tempTable.ImportRow(row)
Next

Output_Collection = tempTable

[david.l.morris]

@EricLi I see that you already solved the initial question, but I wanted to point out this resource as it is super helpful to me when I write filter expressions: https://www.csharp-examples.net/dataview-rowfilter/ .

That page explains (actually right at the top of the page) that only closing square brackets ] need to be escaped whereas opening square brackets do not. However any square bracket in the name of the column does force you to put square brackets of your own around the column name. Again, I know you figured that out already, but it's nice to see an external source confirm it as well.

Also, for anyone else seeing this post, what we are referring to is in the Utility - Collection Manipulation object. There's an action called "Filter Collection", and the [Filter] input param is basically the WHERE clause of a SQL query, but it has some special syntax.

As for your most recent question about how to access the column from a calculation stage, I'm not sure if that's possible. Maybe there's a way, but I believe Blue Prism would consider that an invalid column name. What you'd have to do instead is rename the column first with an action such as "Rename Field". It should have no trouble doing the rename and then you make the name of the field whatever you want so that your calc stage will work. If you need to rename it back later, that should work as well such as if you're going to write it back out into an external data source and it needs to be the same for some reason.

[EricLi]

Thank you. Yes, I made a code stage to rename headers later. 

[EricLi]

Thank you, David. Your resource is quite helpful to me.