Solved how to join two collections?

growler

Member
Hi, I am working with Email POP/SMTP VBO and want to fetch the messages and store it in excel report. I am using two collections one that stores only email address, message id, from name and date sent, the other is used to store the body of the mail using message id of the first collection. Now I want a single collection containing field names: email address, message id, from name, date sent and body. Please tell me how can it be done? I have tried merge collection and append rows to collection and append to field too but none of them works.

Also can you please tell me the use of append to field action with example?
Thank you.
 

cs.andras

Active Member
Hi,
Depending on how large is your email list (collection) I would create a loop within a loop and if the inner loop (body) ID matches the outer loop (header) ID as you described, just add the body into the header, or make a third collection and merge them there. (In case you're not creating a third collection, you'll obviously need to extend the collection with a body field.)
If your collection is very large (say 1000+ items) it might not be worth doing this loop and would need another approach. It might be simpler just to transfer the collections to Excel, do the merging there using VLOOKUP, and transfer the Excel data back into a collection.
 

VJR

Well-Known Member
Hi growler,

Are you able to have ID1 as the column name in Collection 1 and ID2 in Collection 2?
I assume both the collections have the same number of message IDs and the actual same IDs too?
Also IDs in both of them are in the same order? If not then first sort the collections on the ID1 and ID2 columns.
Then use the Merge collection action.
This will have columns ID1, ID2 and the rest of the columns in the output collection Collection 3.
Then you can use the Delete column action to delete ID2 column from Collection 3.
Does that work for you?
 

growler

Member
Hi,
Depending on how large is your email list (collection) I would create a loop within a loop and if the inner loop (body) ID matches the outer loop (header) ID as you described, just add the body into the header, or make a third collection and merge them there. (In case you're not creating a third collection, you'll obviously need to extend the collection with a body field.)
If your collection is very large (say 1000+ items) it might not be worth doing this loop and would need another approach. It might be simpler just to transfer the collections to Excel, do the merging there using VLOOKUP, and transfer the Excel data back into a collection.

Thankyou for replying. Right now I have not more than 10 mails to fiddle with but I am expecting exponential increase in their number, so I think using nested loops won't be a good choice as you had mentioned. And I will have to use third collection, it seems I can't reuse any of the "in collections" to store the result and the third collection has to be undefined otherwise Blue Prism will throw an error.

Thanks to @VJR for the reference.
 

growler

Member
Hi growler,

Are you able to have ID1 as the column name in Collection 1 and ID2 in Collection 2?
I assume both the collections have the same number of message IDs and the actual same IDs too?
Also IDs in both of them are in the same order? If not then first sort the collections on the ID1 and ID2 columns.
Then use the Merge collection action.
This will have columns ID1, ID2 and the rest of the columns in the output collection Collection 3.
Then you can use the Delete column action to delete ID2 column from Collection 3.
Does that work for you?

Thank you, your solution worked great!!
On a separate note, there should be a Join functionality in Blue prism like we have in SQL Queries, merging two collections into a separate third collection seems like a waste, maybe they will provide it in future versions.
 
Top