Single Email - Multiple List Items


Badge +6

I currently have a workflow that will loop through a list using the Query List Action, and use a combination of a For Each Loop and Collection operation to send contents from list items to a user specified in a list column.

what I'm trying to accomplish is send a single email to a specified user, with a collection of list items. After much searching, and experimentation I have been unsuccessful in creating this functionality.

here is the current configuration

2016-01-22_14-30-36.png

2016-01-25_16-24-11.png

2016-01-22_14-31-00.png

2016-01-22_14-31-25.png

2016-01-22_14-32-21.png


27 replies

Badge +11

Hi Jason,

i recommend using the "Build string" action in your loop. The action you configure to store the respective varTaskName and varDueDate in whatever format you like. Which means at the end of the loop you have one variable storing all information about task names and due dates.

The build string action could look like this:

Capture.JPG

And in the body of the mail, you just put your varMailString.

Greetings

Philipp

Badge +6

Followed your recommendation, and now I get all of the items in a single email. I'm attempting to add a link to allow the user to navigate to the list item. The link is building properly, but the list item id is taking the last list item number (36), and adding 1 to each item.

notification workflow.png

string builder.png

task notification.png

Badge +11

Hi Jason,

I dom't see what you are trying to accomplish there. When building the link to the list item, you enter a 36 yourself and then try to add the variable "ListItemID". You should probably delete the 36, shouldn't you?

Please clarify if i misunderstood your question.

Greetings

Philipp

Badge +6

Hi Philipp,

     that was a typo on my part, once I removed the extra values I was able to build a link to the list items

I have built up a new collection for the user that the task is assigned to. How could I send an email to the assigned user with all of the included tasks?

task notification2.png

Thanks,

Jason

Badge +11

I think I dont get the question right now.

Since a Task in SharePoint is nothing but a list item, you can do the exact same thing you have done before for the list items, or do you have any certain requirement for the tasks?

After you have built your string, you send it per mail to whatever user you want.

Badge +6

We have built a collection of all of the list items. How can I send an email to a user that is specified in the Assigned To, with items that are assigned to them?

Badge +11

The same way you did it before? What is the difference now?

You can create a loop for your collection, use a build string action to format your mail text and after the loop you send the notification to the user.

Or clarify your question wink.png

Badge +2

Hi Philipp,

Actually, I am encountering a similar issue. I have a test table like the below and I would like each Relationship Manager (RM) to receive an email with their corresponding undocumented accounts.

189229_pastedImage_0.png

The way I approached this in my workflow is as follows:

     1) First Query List Action: "RM" - store in collection variable "col_RMs".

     2) In the collection operation I remove duplicates from the col_RMs variable and store it once more in the col_RMs variable.

     3) The "For Each" is intended to perform the following tasks for each RM in the coll_RM variable.

              3.1) The second "Query List Action" below is intended to filter out ALL the items (account number, name, doc status) that pertain to a single "RM". For that reason, I set the filter as the image below displays (i.e. When RM is equal to coll_RM variable). However, this gives me the following error: The execution returned an unexpected error. Exception from HRESULT: 0x80131904. I also tried to set up a collection operation before the second query to "get the RM name" in an item variable and later use the item variable (item_RM) in the filter of the second Query List. This didn't work either.

Do you know how can I filter all accounts that pertain to the specified

189255_pastedImage_3.png

          

189256_pastedImage_4.png

"Second Query List"

189254_pastedImage_2.png

Badge +11

Hi Ruben,

inside your "for each" action, you provide the name of your collection variable ("col_RMs") in the "Target collection" field. You also provide another variable for the "Store result in" field. This field will hold the currently processed collection item at runtime. So let's call this variable "singleRM".

In your second query list, you need to provide the "singleRM" variable as a filter and not the "col_RMs" as this is a collection.

If this still doesn't work, I reccomend you let the workflow display the values of your variables by logging this info to the workflow history or a dedicated sharepoint list. Unfortunately the error message is quite useless = but if you provided your collection variable in the filter, this should be the reason for the error.

Let us know if this is working for you.

Regards

Philipp

Badge +2

Hi Philipp,

Thanks for your prompt response. Your answer helps. I have used the "singleRM" variable as a value in the  "Store result" field of the "For Each" action.

However, it now gives me an error in the "get collection variable" operations: Index property is greater than the number of items in the collection.

I suppose this is because the Query is not picking any results. I placed a log-history action to give me the coll_AcctName; coll_AccNumber and the result after the query is just a plain blank.

However, the history log before the Query specifies that the "singleRM" variable = Item Propert "RM" (ADuser).

I'm not sure why is the query not collecting the specified fields in the collection variables that I created. That's why the "collection operations" have nothing to "get".

Any ideas?

189259_pastedImage_4.png

189266_pastedImage_5.png

Badge +11

Hi Ruben,

this could be a formatting issue. SharePoint normally stores users with their id in a format like "<userID>;#<userLoginName>" and your filter may need this format for the input to work properly.

Have a look here (see the comments for how to retrieve user id): Inserting an item with web service call (people)

and also here (in case you need the claims encoding): Claims Encoding Values in SharePoint

Hope this helps

Regards

Philipp

Badge +2

Thanks Philipp.

Before I have a look, the second log-history action retrieves the same value for "RM" item list as well as for "singleRM" variable. It returns AD:ruco in both. Is it possible that this is still a formatting issue, like you mentioned?

Thanks,

Ruben

Badge +11

Sorry, I'm a little confused right now. I thought second query wouldn't return anything?!

Badge +2

That's correct, the second query does not return anything. However, the value of "singleRM" and "RM" before the query takes place reads the same in the history log: "AD uco". This confuses me because if both values are equal the filter should work and the query should return the values of the other columns in the collection variables.

Badge +11

But isn't "RM" what you query in your first list and save inside a collection variable?

If "AD uco" ist the only one in this collection, it may seem like the same value of "singleRM". However, it is still a collection.

Badge +2

Hi Philipp,

I keep trying and trying different ways but it still doesn't do what I want. The issue is as follows:

1) First "Query List" picks all available 'RMs' (Column with 'person or group' type) into collection variable 'col_RMs'

2) "Collection Operation" action removes the duplicates from the variable 'col_RMs' and stores it in 'col_RMs' again. A log in history confirms that the name of each RM appears only once.

3) The "For Each" action picks 'col_RMs' as a target collection and the result is stored in 'item_RMs' which is a "Person or Group" type variable. A log in history confirms that the 'item_RMs' variable and the 'RMs' (from the item properties) values are the same.

4) The second "Query List" action is intended to collect all the accounts that pertain to each RM (item_RM). For that purpose, a filter should say that items should be shown only if "RM" is equal to "item_RMs". However, this filter fails to query any information from the accouts. If I remove the filter, I can get all the accounts - but this includes the accounts of other RMs. Using the filter breaks it down. Not using it defeats the purpose of the workflow because I don't want all accounts to be displayed in the query results. I only want the accounts of each particular RM so they receive an email with their multiple list items.

5) The "collection variable" operations are intended to 'get' the data from the collection variables in the second Query Action list so that I can put them in a "Build a string" action. However, this operation always fails because the second Query returns nothing.

Appreciate your help.

Badge +11

If you query your second list without filter and display the result, how are the names of the RMs saved in this list?

Doest the pattern match your "item_RMs" pattern? If it does, the filter should work. If it doesn't you need to prepare your "item_RMs" to match the pattern.

Badge +2

I do not extract the names of the RMs on my second query. However, to answer your question I have added the "RM" field to the query and stored them in a different collection variable: "col_displayRMs".

     The names appear as follows: AD uco; ADmger

This matches with the "item_RMs" pattern completely. They are equal. However the filter results in the query not returning anything.. I really wonder what is going wrong here!

Badge +11

I would need to test this myself, but not sure when i will have the time.

One other idea: It looks like you have multiple RMs in your column. This means a "is equal to" filter won't work but a "contains" could do.

("AD uco; ADmger") is equal to ("AD uco") ==>FALSE

("AD uco; ADmger") contains ("AD uco") ==> TRUE

Could this be the problem?

Badge +2

Thanks. I have tried with "contains" but it also doesn't work. I started to think whether this has to do with the way that the CAML query is being built. I've read online that <Where> statements shouldn't be inside <Query></Query> elements. However, this is what the Query List Action is currently building:

<Query>

  <Lists>

    <List ID="{E5CBA3C6-F845-42F0-9378-E3B45AC1B1E4}" />

  </Lists>

  <ViewFields>

    <FieldRef Name="AccountNumber" />

    <FieldRef Name="AccountName" />

    <FieldRef Name="DocStatus" />

  </ViewFields>

  <Where>

    <Contains>

      <FieldRef Name="RM" LookupId="TRUE" />

      <Value Type="User">{WorkflowVariable:item_RMs}</Value>

    </Contains>

  </Where>

</Query>

Badge +2

Well, things are getting really strange. I have added a column to my list called "RM name". I have also added a "Query User Profile" action to the nintex workflow. The Query User Profile action gets the Last Name of the "RM" column and stores it in 'item_RMname' variable) A history log confirms that the last name is obtained in the following format: "Cohen Pellico". I have ensured that the values in my new column "RM name" (text) reads in such format (i.e. Cohen Pellico again).

Subsequently, when I use the Query List action to filter all items where column "RM name" equals (or 'contains') variable "item_RMname", I get no results at all. However, if I type in "Cohen", I do get the results.

I have no idea why is this happening but it seems to me that variables do not work in Query List filters. Have you experienced this before?

Badge +11

Is this new "RM name" column a user field? As mentioned before, sharepoint stores user information in user fields in a different format.

That would explain why comparing the field to your variable doesn't work but does work when you try "Cohen".

Variables are definitely working in Query List filters.

Badge +2

Hi Philipp,

No, the purpose of the “RM Name” is that it would be a “text” field as an alternative to the “user” field.

I can see the format in which SharePoint stores the variables and user information by using “history logs”.

The RM Names literally read “Cohen Pellico”. The Query User Profile (last name) also reads “Cohen Pellico”. However when using the Query List Action and filter When “RM names” contains “Last name variable”, it doesn’t pull anything. However, if I say filter When “RM Names” contains ‘Cohen’, it’s working.

Ruben

Badge +2

This thread seems dead. My colleague and I have invested a great deal of time in trying to make the workflow that I described above. We are not able to filter the "Query List" action using people's/group's fields - neither variables. We are hoping that someone will want to test this to help out.

Ruben.

Userlevel 5
Badge +14

I haven't read the thread into very details, since there are many trials ans fails, but I have feeling you mix user identifiers.

user identifier like "AD uco" is one that SP gets from AD (authentication provider). but it internally maps it to its own identifier. and if you query a list for a specific user you have to use that internal ID.

if I take your CAML from post dated Aug 9, 2016 4:03 PM, it should at the end look like

  <Where>
    <Eq>
      <FieldRef Name="RM" LookupId="TRUE" />
      <Value Type="User">12345</Value>
    </Eq>
  </Where>
‍‍‍‍‍‍

the value of '12345' is SP's user ID.

you can get it if you configure people column in list to display "ID"

in workflow you will get it so that you define return type to 'User ID number' 

(note usr=people or group variable, usrid=single line of text variable)

Reply