Showing results for 
Search instead for 
Did you mean: 

Data Collection 'V-lookup' to create 1 master collection

Level 4

Hi All,

Please can someone help?

I am trying to create a data collection to add to a work queue which contains all the data I require to complete my workflow.


The data I start with is this:



Created from the system I am working in. Each option in the ‘Recommendation’ column/field has a corresponding code defined below.  




What I would like to achieve is a data collection that looks like this at the end. Which I can then pass into a work queue to work from.


Thank you! 





Best Answers

Hi @whitehouse-UoL 

Try to use VBO Millicom

You have the action group by.


Let me know if it's is worked for you 🙂

View answer in original post


Level 4

Hi @whitehouse-UoL 

Try this 


After merging you can remove unwanted column from the main collection by following these steps



in decision box [Collection Fields.Field Name]="not req column name"






Hi @rokkam_saiteja 
Thank you for your reply. I have given this ago and I am not getting the results I need. This just seems to be adding the to collections together.

I need the information in the new collection to contain all the IDs and recommendations and have the Code and Comment column generated on the recommendation column. 


Merge all collection to Main Collection Keep IDs, recommendations and coding fields and delete the other.

then use append field action, mention field name then u can see this below


based on the recommendation column value u can set the comment by using calculation stage to the copy and paste field in main collection



Hi @whitehouse-UoL 

Try to use VBO Millicom

You have the action group by.


Let me know if it's is worked for you 🙂

Hi @whitehouse-UoL ,

Since you are not able to find any solutions yet, I would like to provide you with a custom code stage-based solution that you can implement by extending the Utility - Collection Manipulation VBO. You can simply add another action page to this business object called as Join Collection and then provide three input arguments called as: 

- In_CollectionOne (Type: Collection)

- In_CollectionTwo (Type: Collection)

- In_CommonFieldName (Type: Text)


Map these data items to the Start stage for making them our input arguments:



Now, you can add another data item called as Out_Collection (Type: Collection) and map this data item to End Stage for making it our output argument:




Now, add a code stage and map these data items to the code stage and use the following code in the Code tab:



' Clone structure of In_CollectionOne To Out_Collection:

Out_Collection = In_CollectionOne.Clone()

' Add columns To Out_Collection from In_CollectionTwo that are not in In_CollectionOne:

For Each column As System.Data.DataColumn In In_CollectionTwo.Columns
    If Not Out_Collection.Columns.Contains(column.ColumnName) Then
        Out_Collection.Columns.Add(column.ColumnName, column.DataType)
    End If

' Merge rows from In_CollectionOne and In_CollectionTwo In Out_Collection:

For Each rowA As System.Data.DataRow In In_CollectionOne.Rows
    Dim newRow As System.Data.DataRow = Out_Collection.NewRow()
	Out_CurrentRow +=1
    ' Copy all columns from In_CollectionOne:

    For Each column As System.Data.DataColumn In In_CollectionOne.Columns
        newRow(column.ColumnName) = rowA(column.ColumnName)
    ' Check if there are matching rows in In_CollectionTwo:

    Dim key As String = rowA(In_CommonFieldName).ToString()
    Dim foundMatch As Boolean = False
    For Each rowB As System.Data.DataRow In In_CollectionTwo.Rows
        If rowB(In_CommonFieldName).ToString() = key Then
            foundMatch = True

            ' Copy columns from In_CollectionTwo that are not in In_CollectionOne:

            For Each column As System.Data.DataColumn In In_CollectionTwo.Columns
                If Not In_CollectionOne.Columns.Contains(column.ColumnName) Then
                    newRow(column.ColumnName) = rowB(column.ColumnName)
                End If
            Exit For
        End If

    ' Add New Row Item To Out_Collection:





Once, this has been built, publish the action and save the business object for us to test it on Process Studio.



Testing In Process Studio:

For my example, I have taken two collections namely, Application Details and Rejection Details which follow the same data structure and values that you initially provided in your screenshot.


Now, the first thing we need to do is to make sure that the column name for the common columns in both the collection which in our case are the Recommendation and Assessment Recommendation should be same for our newly created action to work. For this reason, I will use the Rename Field action from the Utility - Collection Manipulation VBO and will pass the values to rename the column name for Assessment Recommendation column in Reject Details collection to Recommendation and store it in a new collection called Rejection Details Renamed:



Once, this has been done, we will end up with the following collection:



Now, we will us our newly created action, Join Collection from Utility - Collection Manipulation while passing the Application Details and the newly created Rejection Details Renamed collections with the common field name as Recommendation and store the results in a new collection called Final Collection:




Once the action has been executed, we will have our desired data in the Final Collection as follows:









Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.

Hi @Mohamad_747 
Thank you for your suggestion. This utility did the job perfectly! I did have to run my info through it twice to get the two columns to fill  but other than this perfect. Thanks you! 

Hi @devneetmohanty07 
Thank you so much for your detailed response. I managed to get the solution I needed from one of the other post.