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!
Helpful Answers
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"
Thanks
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
Thanks
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
Next
' 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)
Next
' 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
Next
Exit For
End If
Next
' Add New Row Item To Out_Collection:
Out_Collection.Rows.Add(newRow)
Next
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:
