Blue Prism Product

Expand all | Collapse all

Combining Collections

Jump to Best Answer
  • 1.  Combining Collections

    Posted 09-10-2019 12: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 13: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 09:34
    Thanks Ami.

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



  • 4.  RE: Combining Collections

    Posted 12-26-2019 15: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 03-25-2020 17:02
    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
    ------------------------------



  • 6.  RE: Combining Collections

    Posted 9 days ago
    Hi Ami,

    You left outer join code is awesome. For me its working as expected in c# compiler but not in blue prism code stage. In blue prism its giving me exception - "Could not excute code because Left_Collection.Column1 does not belong to Right_Collection table. It will be great help if this work in bluprism too.

    Thanks in advance

    ------------------------------
    pooja pawar
    Softwear Engineer
    accenture
    Europe/London
    ------------------------------



  • 7.  RE: Combining Collections

    Posted 9 days ago
    Edited by Ami Barrett 9 days ago
    Just to make sure - is the object you're putting this in also set to c#? The out of the box VBOs we supply are in VB.

    Edit: A previous post contains the same stage in VB, if that's more useful. 

    ------------------------------
    Ami Barrett
    Sr Product Consultant
    Blue Prism
    Plano, TX
    ------------------------------



  • 8.  RE: Combining Collections

    Posted 7 days ago
    I rechecked and its C# only.

    ------------------------------
    pooja pawar
    Softwear Engineer
    accenture
    Europe/London
    ------------------------------



  • 9.  RE: Combining Collections

    Posted 7 days ago
    Could you post the inputs that you're passing when the error is thrown?

    ------------------------------
    Nicholas Zejdlik
    RPA Developer
    ------------------------------



  • 10.  RE: Combining Collections

    Posted 7 days ago
    If it works in an external compiler, it sounds like your namespaces and DLL references are missing some items. Try comparing with the Code Options in the original collection manipulation VBO. If it's still missing items, try comparing against the image I'm attaching to this post.


    ------------------------------
    Ami Barrett
    Sr Product Consultant
    Blue Prism
    Plano, TX
    ------------------------------