How to merge column values from different collections in Blue Prism?

Question

I have a collection(Coll1) like below-

Name , Notes

Kathie , "Doing good in English"

Samuel , "Bad in English"

Mac , "Well done in testing"

Kathie , "Average in Math"

Mac , "Good at coding"

Now, I want this data in other collection (Coll2) like below-

Name , Notes

Kathie , "Doing good in English, Average in Math"

Samuel , "Bad in English"

Mac , "Well done in testing, Good at coding"

Basically, I want to merge all the notes for common names here by separating them with comma or bullets, and then send an email including all the notes for that person. I tried merge collection and some other solutions but they just merge the rows and not just the values in a specific field.

Can anybody please help? Thanks in advance.

Answer 1

Think what you need here is a simple loop that pushes values into a new collection. the new collection contains the same columns as the first collection.

When you hit the loop first you make a check, does this [Col1.Name] exist in [Col2.Name] which you can do with collection contains Value

If it doesn't contain the value then you add a row to col2 and add the values in to the newly entered row.

Loop back around on the collection, make the same check again. Does [col1.name] exist in [col2.Name]. This time it does exist, so instead of adding a row into the new collection you instead set the collection field

The row index can just be a simple row counter as you loop through the code. The test value will be [Col2.Notes]&[Col1.Notes] so they are preserved. This will preserve the text as you go through it.

That should do it then, good luck!

Answer 2

Lets say you have a main collection Coll 1 and you want to create a new collection coll2 with merging the data in coll1 according to the names. Please find the suggested steps.

1. Apply a loop on Coll one with a like filer for values before, Row : Kathie , "Doing good in English" Filter by Kathie.
2. Take the filtered data and append them together by String manipulation VBO "Join Lines"
3. Similarly let the loop run.
4. Add extra checks to see if data "Name" is already present in Coll2 then skip it. As we are applying loop in whole Coll1.

Alternatively, you can apply logic to "Get Distinct" in collection by name. and then directly applying filter. (Avoiding the loop on Coll1).

Let me know if this logic works for you.