Object Studio

Expand all | Collapse all

Combining Collections

Jump to Best Answer
  • 1.  Combining Collections

    Posted 09-10-2019 17:13
    I have two collections that I need combined and added to a queue.  What is the best approach to do this?  

    1)  Combine the collections into one new collection and then add to queue?  If so does someone have an example or guidance on this?
    2)  Is there a way to add to queue without first combining into a new collection?

    Thanks!

    ------------------------------
    Jonathan Holstine
    Systems Accountant
    Interior Business Center
    America/Denver
    ------------------------------


  • 2.  RE: Combining Collections
    Best Answer

    Posted 09-10-2019 18:53

    I suppose it depends on the content, and how you want to combine.

    Are the columns the same? If so, Utility - Collection Manipulation has an action called Append Rows to Collection. If the columns are different, you could instead use the Merge Collection action. If you're looking for something more like a join statement, here's a C# stage that'll do a Left Outer Join.

    Inputs:
    Left Collection - Collection
    Right Collection - Collection
    Key Field - Text

    Outputs:
    Collection Out - Collection

    // Build the Collection_Out columns and validate Key_Field is in both collections.
    if(!Left_Collection.Columns.Contains(Key_Field))
    	throw new Exception("Left Collection does not contain key field '" + Key_Field + "'.");
    else if(!Right_Collection.Columns.Contains(Key_Field))
    	throw new Exception("Right Collection does not contain key field '" + Key_Field + "'.");
    
    DataTable temp = new DataTable();
    
    foreach(DataColumn Column in Left_Collection.Columns)
    {
    	temp.Columns.Add(Column.ColumnName, Column.DataType);
    }
    
    foreach(DataColumn Column in Right_Collection.Columns)
    {
    	if(Column.ColumnName == Key_Field)
    	{
    		// Do Nothing
    	}
    	else if(temp.Columns.Contains(Column.ColumnName))
    	{
    		throw new Exception("Collections contain non-key duplicate field ('" + Column.ColumnName + "').");
    	}
    	else
    		temp.Columns.Add(Column.ColumnName, Column.DataType);
    }
    
    // Cycle through the left collection and add any associated information from the right collection.
    foreach(DataRow OuterRow in Left_Collection.Rows)
    {
    	DataRow[] MatchingRows = Right_Collection.Select("[" + Key_Field + "] = '" + OuterRow[Key_Field] + "'");
    	if(MatchingRows.Length > 0)
    	{
    		foreach(DataRow Match in MatchingRows)
    		{
    			DataRow New_Row = temp.NewRow();
    			foreach(DataColumn dColumn in Left_Collection.Columns)
    			{
    				New_Row[dColumn.ColumnName] = OuterRow[dColumn.ColumnName];
    			}
    			foreach(DataColumn dColumn in Right_Collection.Columns)
    			{
    				New_Row[dColumn.ColumnName] = Match[dColumn.ColumnName];
    			}
    			temp.Rows.Add(New_Row);
    		}
    	}
    	else
    	{
    		// No matching rows, but since it's an outer join, append the left row.
    		DataRow New_Row = temp.NewRow();
    		foreach(DataColumn dColumn in Left_Collection.Columns)
    		{
    			New_Row[dColumn.ColumnName] = OuterRow[dColumn.ColumnName];
    		}
    		temp.Rows.Add(New_Row);
    	}
    }
    Collection_Out = temp;

    On the other hand, if you're looking to add a column to an existing collection of type Collection, this stage may interest you.

    Inputs:
    Collection - Collection
    colName - Text
    colType - Text

    Outputs:
    New Collection - Collection

    DataColumn col = new DataColumn();
    
    if(colType=="Number"){
    col.DataType = System.Type.GetType("System.Int32");}
    else if(colType=="Text" || colType=="Password"){
    	col.DataType = System.Type.GetType("System.String");}
    else if(colType=="Collection"){
    	col.DataType = typeof(DataTable);}
    else if(colType=="Date" || colType=="DateTime" ||colType=="Time"){
    	col.DataType = System.Type.GetType("System.DateTime");}
    else if(colType=="Flag"){
    	col.DataType = System.Type.GetType("System.Boolean");}
    else if(colType=="TimeSpan"){
    	col.DataType = System.Type.GetType("System.TimeSpan");}
    else if(colType=="Image"){
    	col.DataType = typeof(Bitmap);}
    else if(colType=="Binary"){
    	col.DataType = System.Type.GetType("System.Byte[]");}
    
    
    col.ColumnName = colName;
    Collection.Columns.Add(col);	
    
    New_Collection = Collection;


    ------------------------------
    Ami Barrett
    Lead RPA Software Developer
    Solai & Cameron
    America/Chicago
    ------------------------------



  • 3.  RE: Combining Collections

    Posted 09-17-2019 14:34
    Thanks Ami.

    ------------------------------
    Jonathan Holstine
    Systems Accountant
    Interior Business Center
    America/Denver
    ------------------------------



  • 4.  RE: Combining Collections

    Posted 12-26-2019 20:12

    This solution is amazing, @Ami Barrett​ !

    I was looking for a way to simulate a typical Left Outer Join like we do it inside SQL. Blue Prism Collection Manipulation VBO does not have this kind of advanced actions.


    Your solution is a real proof of your high code understanding level, a clear solver mind and an efficient use of resources.

    Of course I am going to use this in my future solutions. I already tested inside an action stage of an object and worked perfectly.

    Replicating this for Join function was easy taking care of your commends.



    ------------------------------
    Raúl Hernández
    RPA Developer
    Millicom
    America/El_Salvador
    ------------------------------



  • 5.  RE: Combining Collections

    Posted 3 days ago
    Loved it, thank you!!

    I translated it to VB as my object is using that.

    'Build the Collection_Out columns and validate Key_Field is in both collections.
    If Not Left_Collection.Columns.Contains(Key_Field) Then
    	throw new ApplicationException("Left Collection does not contain key field '" + Key_Field + "'.")
    else If Not Right_Collection.Columns.Contains(Key_Field) Then
    	throw new ApplicationException("Right Collection does not contain key field '" + Key_Field + "'.")
    End if
    
    Dim temp as New DataTable
    
    for each Column as DataColumn in Left_Collection.Columns
    	temp.Columns.Add(Column.ColumnName, Column.DataType)
    next
    
    for each Column as DataColumn in Right_Collection.Columns
    	if Column.ColumnName = Key_Field then
    		' Do Nothing
    	else if temp.Columns.Contains(Column.ColumnName) then
    		throw new ApplicationException("Collections contain non-key duplicate field ('" + Column.ColumnName + "').")
    	else
    		temp.Columns.Add(Column.ColumnName, Column.DataType)
    	end if
    next
    
    ' Cycle through the left collection and add any associated information from the right collection.
    Dim MatchingRows as DataRow()
    Dim New_Row as DataRow
    for each OuterRow as DataRow in Left_Collection.Rows
    	MatchingRows = Right_Collection.Select("[" + Key_Field + "] = '" + OuterRow(Key_Field) + "'")
    	if MatchingRows.Length > 0 then
    		for each Match as DataRow in MatchingRows
    			New_Row = temp.NewRow()
    			for each dColumn as DataColumn in Left_Collection.Columns
    				New_Row(dColumn.ColumnName) = OuterRow(dColumn.ColumnName)
    			next
    			for each dColumn as DataColumn in Right_Collection.Columns
    				New_Row(dColumn.ColumnName) = Match(dColumn.ColumnName)
    			next
    			temp.Rows.Add(New_Row)
    		next
    	else
    		'No matching rows, but since it's an outer join, append the left row.
    		New_Row = temp.NewRow()
    		for each dColumn as DataColumn in Left_Collection.Columns
    			New_Row(dColumn.ColumnName) = OuterRow(dColumn.ColumnName)
    		next
    		temp.Rows.Add(New_Row)
    	end if
    next
    Collection_Out = temp​


    ------------------------------
    Ignacio de la Fuente
    Robotic Process Automation
    Philip Morris international
    America/Argentina/Buenos_Aires
    ------------------------------