'Distinct' limitations workaround?

by PartieHonteuse » Wed Dec 17, 2014 4:19 pm

Hello,

I'm wondering if there's a type of workaround when using the Plumsail Workflow actions pack to get around the limitations of a "Distinct" function in CAML queries.

Scenario

I have a SharePoint List that lists projects by name and their project managers (along with other things but we'll keep it simple).
---------------------------------------------------
| Project | Project Manager |
---------------------------------------------------
| MyProject1 | John Smith |
--------------------------------------------------
| MyProject2 | Jane Doe |
--------------------------------------------------
| MyProject3 | John Smith |
--------------------------------------------------
| MyProject 4 | John Doe |
-------------------------------------------------
| MyProject 5 | John Doe |
-------------------------------------------------

The basic idea is to somehow get a workflow to go through my list looking at the EMs and emailing them weekly reports based on all their assigned projects. Problem is right now I'm just doing a CAML query - iterating through the results and sending a single email for each item found in the list. I'd rather "group" all the projects for each Project Manager into a single email (possibly using Plumsail RenderTextTemplate) and then shoot it off.

Any workarounds out there?

-Partie
PartieHonteuse
 
Posts: 6
Joined: Thu Dec 04, 2014 10:28 pm

by Anton Khritonenkov » Thu Dec 18, 2014 9:26 am

Hi Partie,

Thank you for your question.

As you know CAML doesn't support distinct operator. For now we can't suggest quick workaround. But you can do following.

You can create string variable and name it like 'ProjectManagers'. Then you can query all your projects. Iterate through them. While you are iterating you can get current project manager and check if 'ProjectManagers' variable contains current manager (Use String Contains workflow action). If not you can append current manager to this string with separator, for example '#' (to do this simply use 'Set Workflow Variable' workflow action). Thus, in the end of the loop you will have list of unique project managers separated by '#'. For example "manager1#manager2#manager3#".

Now you can use Split String workflow action to split this string by '#' and convert it to dictionary variable with collection of unique project manager. Please, read this section of article to understand how this workflow action works. As result you will have collection of unique managers stored in dictionary variable.

Once you have collection of unique project managers you can iterate through them and query projects for each of them using CAML query individually. Then send email message with list of all projects to each of them.
User avatar
Anton Khritonenkov
 
Posts: 219
Joined: Wed Nov 12, 2014 1:33 pm


Return to Workflow Actions Pack for Office 365