Filter Collection - Filter by value that contains '

[JoanneC]

I'm trying to use the Collection Manipulation - Filter Collection action.

For the filter I have the following:

[Column Name] = '"&[Referral Source]&"'"

This is working for everything but a couple of Referral Sources are text containing a single quote for example "Let's"

So when I'm running the process I'm getting error:

Internal: Could not execute code stage because exception thrown by code stage: Syntax error: Missing operand after 's' operator

Is there a way I can get this to work? Thank you in advance

[Daniel_Sanhueza · Accepted solution]

Hello @JoanneC 

single quotes is a special character and when filtering, closes the condition and what is after doesnt recognize and throw that error. 

To filter with a condition that has a single quotes, you have to replace it by two single quotes (single quotes '' <> double quotes ", so keep in mind to use single quotes.)

I recommend to put a condition before filter on [Referral Source], ask with a decision if [Referral Source] have single quotes in it, something like this;

InStr([Referral Source];"'")>0

If yes, then replace it by two single quotes;

Replace([Referral Source];"'";"''")

The result should be something like Let''s, I know its weird. But filtering like "%let%s go%" with the wildcard inside, doesnt work.

Hope it helps

[asilarow]

The Filter collection from the Legacy object has oh so many bugs.

Use the alternative action below (simply copy & paste into your object as a new action):

<process name="__selection__AS Utility - Collection Manipulation" type="object" runmode="Background"><stage stageid="93a10579-9749-4330-b0fc-803ab51698c9" name="Filter Collection" type="SubSheetInfo"><subsheetid>d4c0f119-6e50-4e9c-9825-c58c440f7414</subsheetid><display x="-195" y="-105" w="150" h="90" /><font family="Tahoma" size="10" style="Regular" color="000000" /></stage><stage stageid="12fdffdc-cfae-49d2-a53c-7f8e880d9132" name="Start" type="Start"><subsheetid>d4c0f119-6e50-4e9c-9825-c58c440f7414</subsheetid><loginhibit /><preconditions><condition narrative="None." /></preconditions><postconditions><condition narrative="The collection will have been sorted." /></postconditions><display x="15" y="-105" /><font family="Tahoma" size="10" style="Regular" color="000000" /><inputs><input type="collection" name="Collection" stage="Collection" /><input type="text" name="Filter" stage="Filter" /></inputs><onsuccess>a8ae0b69-1932-4242-8b01-343fd61161f2</onsuccess></stage><stage stageid="dc59e57f-6731-471e-87cd-381589cb5c5b" name="End" type="End"><subsheetid>d4c0f119-6e50-4e9c-9825-c58c440f7414</subsheetid><loginhibit /><display x="15" y="90" /><font family="Tahoma" size="10" style="Regular" color="000000" /><outputs><output type="collection" name="Sorted Collection" stage="Collection Out" /><output type="flag" name="Success" stage="Success" /><output type="text" name="Message" stage="Message" /></outputs></stage><stage stageid="930a0c07-6ee6-4dbe-8909-bea773ead0b2" name="Collection Out" type="Collection"><subsheetid>d4c0f119-6e50-4e9c-9825-c58c440f7414</subsheetid><loginhibit /><display x="-195" y="135" w="150" h="60" /><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>collection</datatype><private /><alwaysinit /></stage><stage stageid="a8ae0b69-1932-4242-8b01-343fd61161f2" name="Filter Collection1" type="Code"><subsheetid>d4c0f119-6e50-4e9c-9825-c58c440f7414</subsheetid><loginhibit /><display x="15" y="-15" /><font family="Tahoma" size="10" style="Regular" color="000000" /><inputs><input type="collection" name="Collection" expr="[Collection]" /><input type="text" name="Filter" expr="[Filter]" /></inputs><outputs><output type="collection" name="Filtered_Collection" stage="Collection Out" /><output type="flag" name="Success" stage="Success" /><output type="text" name="Message" stage="Message" /></outputs><onsuccess>dc59e57f-6731-471e-87cd-381589cb5c5b</onsuccess><code><![CDATA[Try

Collection.DefaultView.RowFilter = Filter
Filtered_Collection = Collection.DefaultView.ToTable
Success = True
Message = ""

Catch e As Exception
	Success = False
	Message = e.Message
End Try]]></code></stage><stage stageid="4d3d16a2-e35c-4ce5-875a-984d9b716e7f" name="Message" type="Data"><subsheetid>d4c0f119-6e50-4e9c-9825-c58c440f7414</subsheetid><display x="-195" y="210" w="150" h="30" /><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>text</datatype><initialvalue /><private /><alwaysinit /></stage><stage stageid="daaa6425-fe73-4da0-a202-5a712af87809" name="Success" type="Data"><subsheetid>d4c0f119-6e50-4e9c-9825-c58c440f7414</subsheetid><display x="-195" y="180" w="150" h="30" /><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>flag</datatype><initialvalue /><private /><alwaysinit /></stage><stage stageid="5ef3d2e5-3278-4dcb-b2a5-ba3c34288aa8" name="Collection" type="Collection"><subsheetid>d4c0f119-6e50-4e9c-9825-c58c440f7414</subsheetid><display x="-195" y="-15" w="150" h="60" /><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>collection</datatype><private /><alwaysinit /></stage><stage stageid="5732d266-00c5-496e-a477-6119c8fffa57" name="Filter" type="Data"><subsheetid>d4c0f119-6e50-4e9c-9825-c58c440f7414</subsheetid><display x="-195" y="45" w="150" h="30" /><font family="Tahoma" size="10" style="Regular" color="000000" /><datatype>text</datatype><initialvalue xml:space="preserve">Field1='1234561' AND Field2='bbb'</initialvalue><private /><alwaysinit /></stage></process>

[JoanneC]

Thank you, that worked! 

[Vikram.R]

Hi I am trying to do for DESC in a Rg column collection and the query I have given is 

I am getting error like

can you please help me on this

[John__Carter]

You should be using the Sort action instead of Filter

[Vikram.R]

Yes i did that only finally thanks