Remove Item from collection

0

Hi,

are you referring to an item list when you say collection?

Not really. Collection meaning a choice field with more than one option selected or a text field with value like '1#value1;4#something;18#another choice'.

Thanks for clarifying. Unfortunately, I don't know if this is possible and how one would go about it, sorry.

Hi Michał,

couldn't you put a replace around it and replace the string you dont want in the collection?

Replace(Replace('1#value1;4#something;18#another choice','4#something',''),';;',',')

That's a working solution and much simpler than putting javascript to work! Thanks for the idea!

On the other hand "SELECT Replace(Replace('1#value1;44#something;18#another choice','4#something',''),';;',',')" will not work in certain cases. If you will try to remove "'4#something" i this case you will end up with: "1#value1;4;18#another choice" - not acceptable.

True ;) You need to know what might be in the collection to remove values. How do you know what you want to remove?

Also there was a typo in my post, it would replace ';;' with ','.

SELECT Replace(Replace('1#value1;44#something;18#another choice','4#something',''),';;',';')

Hi everyone,

I'm joining the conversation, also I'm not sure whether there's really something left to do here and the biggest question is, how to determine which element is unwanted. Nevertheless I will leave another approach:

In my case changing the "Without id" value will execute the form rule, which in turn will execute a business rule. The values of "SourceField" and "WithoutId" are passed to the business rule. The rule, will split the string into a table, "remove" the row with the provided id an merge the result back to a single string.
SQL statement
select STUFF(
(
select ';'+dbo.ClearWFElemId(item)+'#'+ dbo.ClearWFElem(item)
from dbo.SplitToTable('{BRP:13}',';')
where dbo.ClearWFElemId(item) <> '{BRP:12}'
FOR XML PATH('')
),1,1,'') as cleanedCollection

Instead of

select ';'+dbo.ClearWFElemId(item)+'#'+ dbo.ClearWFElem(item)
This should also work but I haven't tested it

select ';'+item

This happens when you don't clean up after you have a working solution. On the other hand, maybe someone doesn't know either function. So I will leave them there. :)

Best regards,
Daniel

Daniel, you have just guided me to the solution that finally suites my needs (I forgot there is something like SplitToTable)! Thanks a lot!

My Business Rule SQL command definition in the attached picture!