Collection Query

Martin76

New Member
Hi,

I am working on an automation that collects a rota, manipulates the data (creating an email address from the names) and sends out a calendar invitation using both the date and now converted email field.

The 2 fields it collects from excel are date (dateTime) and name (text). This has given me 30 rows (a months worth of data) stored in collection 'rota'.

My issue is that some of these name fields contain the names of 2 people (most only contain 1 which is fine). To get around this I have built an object that first uses 'utility strings -split text' to check for an '&' and store to a new collection called 'split values'.

It then loops the split values collection, adds a row to a new output collection called 'recipient contacts', uses a calc stage to convert this into an email address and stores both the names and email addresses in this new collection.

My issue is that I now have the original rota collection which has 30 rows (date and names) and a new collection which contains 36 (text and email). The 6 extra rows are the split names from the rota collection.

Ideally I want to merge them both into a collection that contains 36 rows with 2 columns of date and email address as this is all I will need to run the 'add calendar appointment' object.

How do I do this? Or was there an easier way to do this that I'm missing?

TLDR version : I want to merge a collection with 30 rows to a collection with 36 but keep the date column from collection 1 (duplicating 6 dates) and the email column from collection 2.

Further explanation. Imagine collection 1 is...
  1. 01/11/2020 - Person A
  2. 02/11/2020 - Person B
  3. 03/11/2020 - Person C & Person D
And collection 2 is...
  1. Person A - PersonA@email.com
  2. Person B - PersonB@email.com
  3. Person C - PersonC@email.com
  4. Person D - PersonD@email.com
I need collection 3 to be...
  1. 01/11/2020 - PersonA@email.com
  2. 02/11/2020 - PersonB@email.com
  3. 03/11/2020 - PersonC@email.com
  4. 03/11/2020 - PersonD@email.com
and so on.

Phew! Thanks :)
 

sahil_raina_91

Active Member
Hi,

I am working on an automation that collects a rota, manipulates the data (creating an email address from the names) and sends out a calendar invitation using both the date and now converted email field.

The 2 fields it collects from excel are date (dateTime) and name (text). This has given me 30 rows (a months worth of data) stored in collection 'rota'.

My issue is that some of these name fields contain the names of 2 people (most only contain 1 which is fine). To get around this I have built an object that first uses 'utility strings -split text' to check for an '&' and store to a new collection called 'split values'.

It then loops the split values collection, adds a row to a new output collection called 'recipient contacts', uses a calc stage to convert this into an email address and stores both the names and email addresses in this new collection.

My issue is that I now have the original rota collection which has 30 rows (date and names) and a new collection which contains 36 (text and email). The 6 extra rows are the split names from the rota collection.

Ideally I want to merge them both into a collection that contains 36 rows with 2 columns of date and email address as this is all I will need to run the 'add calendar appointment' object.

How do I do this? Or was there an easier way to do this that I'm missing?

TLDR version : I want to merge a collection with 30 rows to a collection with 36 but keep the date column from collection 1 (duplicating 6 dates) and the email column from collection 2.

Further explanation. Imagine collection 1 is...
  1. 01/11/2020 - Person A
  2. 02/11/2020 - Person B
  3. 03/11/2020 - Person C & Person D
And collection 2 is...
  1. Person A - PersonA@email.com
  2. Person B - PersonB@email.com
  3. Person C - PersonC@email.com
  4. Person D - PersonD@email.com
I need collection 3 to be...
  1. 01/11/2020 - PersonA@email.com
  2. 02/11/2020 - PersonB@email.com
  3. 03/11/2020 - PersonC@email.com
  4. 03/11/2020 - PersonD@email.com
and so on.

Phew! Thanks :)

1602063236320.png
Result:
1602063418779.png
1. Use Collection contains value with Regex : "(.*&.*)". Output will be a flag + Groups collection
(Idea is to check if & exists in Name Field)
2. Fetch the Row index from the value in [Groups.Group] and store in [Row Index]
3. Read collection field at [Row Index]-1 to fetch Date value and store in [Value Read]
4. Split Text [Groups.Group] and store in Split Values
5. Loop Split Values to use action Add rows in Date collection and store [Value Read] in [Date.Date] (Picture shows incorrect action name, my bad)
(Insert the Date value from step 3 in Date collection. For this create a Date collection with 1 column named Date)
6. Merge [Date] + [Split Values] and store in [Split Values]
7. Append [Split Values] to [Excel] collection
8. Delete Row at index using [Row Index]-1
(so after adding new rows in EXCEL collection, we need to delete the original row containing c & d too)
9 Remove all rows from [Date] Collection and connect back to Step1


*The actions marked in Red may not be available in the default VBO provided by Blue Prism*
Let me know if you need help with writing .NET code for those actions
 
Last edited:

Martin76

New Member
Thank you for this Sahil. Much appreciated. :)

I have started to put this together and you are correct that are no default VBO's for the items marked red. Would appreciate any further help you can provide with this.
 

sahil_raina_91

Active Member
Thank you for this Sahil. Much appreciated. :)

I have started to put this together and you are correct that are no default VBO's for the items marked red. Would appreciate any further help you can provide with this.

For action Delete Row at Index :
Input : Collection_In(Collection), Row_Index (Number)
Output : Collection_Out(Collection)
CODE:
Collection_In.Rows.Remove(Collection_In.Rows(Row_Index))
Collection_Out = Collection_In.Copy

For action Get Row Index from Value :
Input : Collection_In(Collection), Field_Name (Text), Value (Text)
Output : Row_Index
CODE:
Dim i As Integer
For i = 0 To Collection_In.Rows.Count - 1
If Collection_In.Rows(i)(Field_Name) = Value Then
Row_Index = i
Exit For
End If
Next
Row_Index = Row_Index +1
 

Martin76

New Member
For action Delete Row at Index :
Input : Collection_In(Collection), Row_Index (Number)
Output : Collection_Out(Collection)
CODE:
Collection_In.Rows.Remove(Collection_In.Rows(Row_Index))
Collection_Out = Collection_In.Copy

For action Get Row Index from Value :
Input : Collection_In(Collection), Field_Name (Text), Value (Text)
Output : Row_Index
CODE:
Dim i As Integer
For i = 0 To Collection_In.Rows.Count - 1
If Collection_In.Rows(i)(Field_Name) = Value Then
Row_Index = i
Exit For
End If
Next
Row_Index = Row_Index +1

That's brilliant.

I've got it all up and running (Still don't think I'll ever really understand the coding aspect in VBO) and it's working a treat :)

Had to make one slight tweak as my initial collection seemed to be increasing exponentially and then I noticed that on line 8 you said to "Delete Row at index using [Row Index]-1 and realised this should be +1 as I'm continously adding 2 extra rows to the collection (minus the one I'm removing).

Thanks again for your help.
 

sahil_raina_91

Active Member
That's brilliant.

I've got it all up and running (Still don't think I'll ever really understand the coding aspect in VBO) and it's working a treat :)

Had to make one slight tweak as my initial collection seemed to be increasing exponentially and then I noticed that on line 8 you said to "Delete Row at index using [Row Index]-1 and realised this should be +1 as I'm continously adding 2 extra rows to the collection (minus the one I'm removing).

Thanks again for your help.

You are welcome. However, I still feel it should be [Row Index]-1 for line 8.
The reason being that indexing starts from 0th position for datatable, which is a collection in .Net.
If you need to delete 3rd row from the image, the index should be 2

If it's working for you with +1 instead of -1, then we can call it a day :)
 
Last edited:

Martin76

New Member
You are welcome. However, I still feel it should be [Row Index]-1 for line 8.
The reason being that indexing starts from 0th position for datatable, which is a collection in .Net.
If you need to delete 3rd row from the image, the index should be 2

If it's working for you with +1 instead of -1, then we can call it a day :)

Haha. I'll run some further tests with different data sets tomorrow and let you know. ;)
 
Top