Combining Collections

[JonathanHolstin]

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
------------------------------

[AmiBarrett · Accepted solution]

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
------------------------------

[JonathanHolstin]

Thanks Ami.

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

[RaúlHernández]

This solution is amazing, @AmiBarrett​ !

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
------------------------------

[Ignaciode_la_Fu]

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
------------------------------

[poojapawar]

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
------------------------------

[AmiBarrett]

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
------------------------------

[poojapawar]

I rechecked and its C# only.

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

[NicholasZejdlik]

Could you post the inputs that you're passing when the error is thrown?

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

[AmiBarrett]

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
------------------------------