cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
GarageSale
Nintex Newbie

Report reminder email when due date is <=7 days away

Jump to solution

Hello everyone and happy 4th if you are in the states!

 

Complete workflow newbie in need of some assistance. We have SharePoint 2013 and Nintex Workflow designer 2013. I have some programming experience from school, so I’m familiar with the basic concepts at play here like for loops, if then else, variables etc. I know my way around SharePoint for the most part, I’d say I’m not quite intermediate level but getting closer every day so I didn’t think this would be terribly difficult but I’m having some trouble wrapping my head around structuring this.

 

We have a custom list that is used to track reports, who is responsible for generating/sending them, when they are due, and when the report was in-fact submitted by an employee.

 

My boss has asked if it is possible to build a workflow or two to generate some reminders about when reports are due. These reminders would be generated based on two criteria:

 

  • When any report in the list is <=7 days away from the due date, email responsible staff (single email sent per report due).
  • Every 1st of month generate a single email with a list of all reports due that month and send to all responsible staff that have a report coming up listed in the email (hope that makes sense).

 

 

This is where I’m at with trying to build the first workflow:

 

1) Using a List Query, go through the entire list filtering for anything WITH a due date set and WITHOUT a submission date set. Any row where this is true the workflow should gather the following information from the row:

 

(These are all columns in the list)

-Report Title

-Responsible staff

-Due Date

 

I store each of these in their own collection variables, output from the list lookup query I managed to build. Could I possibly do it with one variable and still easily parse the data?

 

Responsible staff is going to be a problem currently as the data isn’t email addresses that I can use directly but rather it comes out as account names (ie John.Doe) since everything is tied to Active Directory. I can probably just change how we input this as it would probably be a ton of trouble to parse emails from this…or not what do I know.

 

Hitting run now produces the data I want to see in a long list one column of data at a time (all report titles, then all responsible staff, then all due dates). I just need to figure out how to get it concatenated, formatted and stuck into individual emails.

 

From here I’ve been reading through your knowledgebase and forums and I have a good idea of what components I need to make this work, I’m just not quite sure how to put it all together.

 

2) I need to setup a for each loop to crawl through each of the variables (excluding responsible staff), but here is where I become unsure of the best approach due to my workflow ignorance.

 

It looks like with my separate variable approach above I would need a separate for loop per variable to crawl through the data following a global index value (since each collection variable should contain the same number of lines). Then store the results of every iteration of each loop into temporary string variables that I can then feed into the build string object combining them into lines for storage in another collection variable. Hopefully on the right track so far.

 

I want the strings to end up looking like “ReportTitle is due on DueDate” for the single email alerts.

 

3) Then I know I would need another loop (possibly two?) to iterate through the stored strings variable and email variable and combine both of those into the send notification object in Nintex somehow.

 

Am I on the right track? Any guidance would be greatly appreciated!

 

I will be checking in frequently to answer questions and provide further information.

Labels: (1)
0 Kudos
Reply
7 Replies
emha
Nintex Newbie

Re: Report reminder email when due date is <=7 days away

Jump to solution

basicaly you're on the right track, you just need to tune few things

 

 

1) Using a List Query, go through the entire list filtering for anything WITH a due date set and WITHOUT a submission date set.

extend the query by the date condition cluase (DueDate <= CurrentOrSomeDate; DueDate > 1.x.YYY AND DueDate < 31.X.YYYY)

so that you get returned just items that really need to be notified and so that you do not need to bother/filter (as waste resources) with ones that do not.

 

 

 

 

I store each of these in their own collection variables, output from the list lookup query I managed to build. Could I possibly do it with one variable and still easily parse the data?

that's correct and only possible approach

 

 

 

 

Responsible staff is going to be a problem currently as the data isn’t email addresses that I can use directly but rather it comes out as account names (ie John.Doe) since everything is tied to Active Directory

if store Responsible in a people or group field you need not to do any conversion on your own.

nintex/sharepoint will automatically recognize email address for a given user account.

 

 

 

 

It looks like with my separate variable approach above I would need a separate for loop per variable to crawl through the data following a global index value 

 

no, you only need a single loop action.

chose one of the returned collection variables as a driving one, and setup the for each loop over it.

configure the loop action to store current collection element into a scalar value, configure as well to store current index value.

the use collection operation action to current elements from all the other collections. use index variable populated by loop action to address 'current' element.

now you have all the values of a single item read in into a scalar variables and you can process them as you need - you can directly use them to send out a notification, or you can append them to a larger text that step by step builds your notification body

 

 

 

3) Then I know I would need another loop (possibly two?) to iterate through the stored strings variable and email variable and combine both of those into the send notification object in Nintex somehow.

no further loops are needed.

 

 

 

 

Reply
GarageSale
Nintex Newbie

Re: Report reminder email when due date is <=7 days away

Jump to solution

@emha wrote:

basicaly you're on the right track, you just need to tune few things

 

 

1) Using a List Query, go through the entire list filtering for anything WITH a due date set and WITHOUT a submission date set.

extend the query by the date condition cluase (DueDate <= CurrentOrSomeDate; DueDate > 1.x.YYY AND DueDate < 31.X.YYYY)

so that you get returned just items that really need to be notified and so that you do not need to bother/filter (as waste resources) with ones that do not.

 

Could you elaborate on this with some examples?

 

What I have now just gets me a list of everything that hasn't been submitted regardless of how far away the submission date is which is not ideal. I tried filtering it down but ended up with no results returned at all when I use Run Now.

 

 

I store each of these in their own collection variables, output from the list lookup query I managed to build. Could I possibly do it with one variable and still easily parse the data?

that's correct and only possible approach. 

 

Cool at least I got something right!

 

 

 

 

Responsible staff is going to be a problem currently as the data isn’t email addresses that I can use directly but rather it comes out as account names (ie John.Doe) since everything is tied to Active Directory

if store Responsible in a people or group field you need not to do any conversion on your own.

nintex/sharepoint will automatically recognize email address for a given user account.

 

Well that is nifty, I didn't even realize there was a people/group variable type. I have added an output to the list query that pulls from the responsible staff column and stores what is returned in a people/group variable called "responsibleStaffVar".

 

 

 

It looks like with my separate variable approach above I would need a separate for loop per variable to crawl through the data following a global index value 

 

no, you only need a single loop action.

chose one of the returned collection variables as a driving one, and setup the for each loop over it.

configure the loop action to store current collection element into a scalar value, configure as well to store current index value.

 

What is a "scalar value"? A variable type within Nintex?

 

the use collection operation action to current elements from all the other collections. use index variable populated by loop action to address 'current' element.

now you have all the values of a single item read in into a scalar variables and you can process them as you need - you can directly use them to send out a notification, or you can append them to a larger text that step by step builds your notification body

 

For the collection operation action, is the target collection the collection I want my combined data stored in?

Then I would use the variable name I stored results in from the for each loop as the "value" passed to the collection operation?

 

 

3) Then I know I would need another loop (possibly two?) to iterate through the stored strings variable and email variable and combine both of those into the send notification object in Nintex somehow.

no further loops are needed.

 

 

 

 

 

 


EDIT: Apparently I also don't know to quote and reply properly. I can't seem to figure out how to make my latest reponses not appear inside the quote blocks...I pulled everything down here to make it easier to tell what my latest questions are.

 

In response to your list query suggestions:

 

Could you elaborate on this with some examples?

 

What I have now just gets me a list of everything that hasn't been submitted regardless of how far away the submission date is which is not ideal. I tried filtering it down but ended up with no results returned at all when I use Run Now.

 

In response to to you about the responsible staff: 

 

Well that is nifty, I didn't even realize there was a people/group variable type. I have added an output to the list query that pulls from the responsible staff column and stores what is returned in a people/group variable called "responsibleStaffVar".

 

In response to your for each loop suggestion:

What is a "scalar value"? A variable type within Nintex?

 

Finally, in regards to the collection operation:

 

For the collection operation action, is the target collection the collection I want my combined data stored in?

Then I would use the variable name I stored results in from the for each loop as the "value" passed to the collection operation?

0 Kudos
Reply
emha
Nintex Newbie

Re: Report reminder email when due date is <=7 days away

Jump to solution

Could you elaborate on this with some examples?

Query list's filter condition may look like

 

i.jpg

 

 

 

 tried filtering it down but ended up with no results returned at all when I use Run Now.

have you replaced variables/references with actual value in Run Now?

 

 

 

Well that is nifty, I didn't even realize there was a people/group variable type. I have added an output to the list query that pulls from the responsible staff column and stores what is returned in a people/group variable called "responsibleStaffVar".

I meant, you can directly reference "Staff" list field . (usually you don't need to copy over its value into a workflow variable)

I meant "Staff" list field being of Person&group type.

but that's in general.

in your scenario you query "Staff" into a collection variable. depends on your requirements - if you want to send notification to all the people in a single notification, you could directly use collection variable as notification addressee. if you want to send separate notification per each person, you will need to pick single element (people) from Staff collection into a scalar variable and use this variable as notification addressee. in your case it doesn't make too much difference whether you use a person&group variable or a text vartiable

 

 

 

 

What is a "scalar value"? A variable type within Nintex?

sorry for my mistype, it should have been a "scalar variable".

scalar variable can only hold one single value at a time (eg. number, string/text, date...)

unlike of scalar variables, collections hold multiple value of given type (set of numbers, texts, dates,...)

 

 

 

 

For the collection operation action, is the target collection the collection I want my combined data stored in?

the target collection is a collection you need to perform the action on.

in your context, you need to pick a single value from collection(s) returned by query list action. so in you case the target collection is one you need to pick/read data from.

 

 

 

 

Then I would use the variable name I stored results in from the for each loop as the "value" passed to the collection operation?

as mentioned, for each loop operates just on single collection. since you return multiple collections from query list action, to get values from all the other collections (except of one configured for each loop)  you need a separate collection operation action

eg. if you let for each loop iterate over "Report Title" collection, you need further collection operation actions for "Responsible staff" and "Due Date"

 

 

Reply
GarageSale
Nintex Newbie

Re: Report reminder email when due date is <=7 days away

Jump to solution

You have been extremely helpful so far.

 

I added a calculate date before the list query and setup two variables: startDate (which gets initialized with current date) and endDate (which the calculate date field stores the result of startDate + 7 days into so I have my 1 week range). 

calc_date.png

I don't get anything returned currently when running now using the variables. However replacing with actual dates like you suggested returns ONLY the results I want which is progress.

query_list.png

 

Ah I understand now, you were referring to what type of field the column was. I verified it is a person/group column.

 

I want to send an individual email per report to the responsible staff listed in the responsible staff column. I assumed pulling them into their own collection and using for each to step through them using the same global index value would be the best/easiest course of action.

 

You are saying I could skip the collection variable for the responsible staff all together and pull each address into a scalar variable one at a time? Using that address to send the notification then overwriting the value with the next address...How would I accomplish this without another for each loop? I tried to build this out but I don't think I did it correctly.

 

 

 

I have the workflow built but currently it seems to run endlessly until it errors out. Do I need to define a "stop processing" variable for the for each loop? Or will it simply end when the collection(s) no longer have any values to return?

 

EDIT: Posted my workflow and snapshots of all settings and components below so you can get a better overall picture of where I'm at and what I'm doing.

0 Kudos
Reply
GarageSale
Nintex Newbie

Re: Report reminder email when due date is <=7 days away

Jump to solution

This is what I currently have, which seems to get stuck in the For each loop and run endlessly until SharePoint kills it. I believe I need to work on the structure of a few pieces, like the send notification should be in the for each section so it sends an email per iteration now that I'm looking at it.


Overall workflow:

 

overall.png

 

Vars defined in Workflow (a few of these are old and not in use):

vars.png

Calculate date field:

calc_date.png

Query List:

query_list.pngquery_list_out.png

 

For Each loop:

for_each.png

Collection operation (report titles):

collect_titles.png

Collection operation (due dates):

collect_dates.png

 

Build string:

build_string.png

 

Collection Operation (current recipient from staff):

get_staff.png

 

and finally, send notification:

notification.png

0 Kudos
Reply
GarageSale
Nintex Newbie

Re: Report reminder email when due date is <=7 days away

Jump to solution

@emha 

 

Since I can't get this workflow to not run as an infinite loop I currently have the for each loop run just once by setting a bolean variable to true at the very end of the first iteration that is used for the stop processing variable.

 

How would I go about finding out how many reports I have collected at the start of the workflow so I can appropriately define a condition that stops the for each loop after the final report is processed and the notification is emailed?

 

 

As a debug of sorts I placed the send notification inside the for loop and have the following in the email body so I can see what is stored in the collections and other vars after the first loop (all lines in italics are 'variables <- with descriptions' of what the variables are for):

 


builtString <- this should be my fully built report due string once I have all my issues worked out.

If that doesn't work here are collection contents:

reportTitles <- reportTitles collection var. Should dump all report titles pulled from my list query

titleColVar <- report title the for each loop is currently iterating through

due dates:
dueDates <- dueDates collection var. Should dump all due dates pulled from my list query.
 
dueColVar <- due date for the report the for each loop is currently iterating through

staff:

responsibleStaffVar <- entire responsible staff collection

currentStaff  <- current staff member responsible for current report for each loop is iterating through.

 

 

This produces some interesting results that I would like some assistance with:

 

Report: test_report is due on: 1/1/1900 12:00 AM

If that doesn't work here are collection contents:
Report Titles:
test_report;test_report;test_report2; 

test_report 

due dates:
1/1/1900 12:00 AM;7/15/2019 12:00:00 AM;7/17/2019 12:00:00 AM; 
 
1/1/1900 12:00 AM 

staff:

;i:0#.w|contoso\john.doe;i:0#.w|contoso\john.doe;

 

1) For my report titles collection I end up with 2 of the first report name being displayed when outputting the "reportTitles" collection variable: test_report;test_report;test_report2

 

Why am I ending up with the first report listed twice?

 

2) Why do I get this "empty" [1/1/1900 12:00 AM] due date as the first result in my dueDate collection variable? 

 

3) The staff collection appears to contain "domain\user.name", is this what I am supposed to be seeing per your suggestions about the staff field earlier?


In addition my final variable in the email, "CurrentStaff", does not produce any output but is setup the same as my other collection operations. Is it due to my collections not having the same number of items (not even sure how this is possible)? ie: titles and due dates all have 3 "items" where as staff only outputs 2 "items."

 

Or could it be due to the variable type being a single line of text instead of a "person or group"?

0 Kudos
Reply
GarageSale
Nintex Newbie

Re: Report reminder email when due date is <=7 days away

Jump to solution

I think I figured it all out finally. Thanks for your help.

0 Kudos
Reply