Here's the scenario:
Many of the URLs have the same owners listed. I want to send an email to the owner of each URL but I don't want to send multiple emails to the same owner.
I want to send one email to each owner that contains a list of the URLs that they own.
Here's some sample data:
URL: google.com Owner(s): Bob Johnson, Sue Brown, Mary Jones
URL: bing.com Owner(s): Jason Low, Sue Brown
URL: ask.com Owners(s): Mary Jones
The point of the workflow is to send out notifications. At the end of the workflow the following emails should be sent
1 Email for Bob Johnson - Stating he owns 1 URL
1 Email for Sue Brown - Stating she owns 2 URLs
1 Email for Mary Jones - Stating she owns 2 URLs
1 Email for Jason Low - Stating he owns 1 URL
If I create a site workflow to parse the list, i feel like it will need to create new variables as it reads new names from the Usernames column. The usernames are not set and can be any employee name across the company. So the workflow would need to read the username and url, store both, then evaluate the next username to see if it matches one that already exsists, if so - add the new URL to the multiline text variable that holding all the URLs for that one owner. If not, create a new variable to start holding urls for this new Username.
Clear as mud?!
Anyone have any ideas on the best way to accomplish this?
This is doable, but little tricky and many operations as mentioned below.
1. Query List action - Query "Column B" and store in a collection variable, "colUsers", (here each item in collection contains a single user name or multiple user names. If multiple user names are there, they are separated by ";").
2. For Each action - place the below actions inside as shown below
For Each action: Loop thru each item in collection, colUsers, store item in a text variable, txtName
Set Condition: Check if txtName contains ";"
Yes - use regular expression to split by ";" and store in another collection, "colAllNames" variable
No - simply "add" txtName value to collection, "colAllNames" variable
By end of this For Each action, you have the collection, "colAllNames" variable with each name separated in case multiple owners(including duplicates)
3. After "For Each" action, use a "Collection Operation" action to "Remove duplicates" and store results in collection, "colUniqueNames", now you have a collection with unique names from "Column B".
4. Again use "For Each" action with "colUniqueNames" , store each item in "txtUserName"
5. Inside "For Each" action, keep a "Query List" and add filter to it, "Column B" contains "txtUserName", store the "Column A" value in collection, "colURLS"
6. Process the collection, "colURLs" and send notification to the user, "txtUserName"
Looks little complicated, but the tricky point is to get the list of unique users from Column B, then query list for each unique user for Column A value.
Hope this helps.