I am fairly new to Nintex, and need to create a workflow to send email based on date from calculated column.
Currently there is a document library with 1500 documents. The documents have a date column named "Valid Until" , and I need to send an email 30 days before that date is reached. I created a Calculated column named "Alert Date" with the formula =[Valid Until]-30
How do I query all the items in the Doc Library and send an email based on the calculated column date equals today? or current date?
Any advise would be appreciated.
Solved! Go to Solution.
Instead of using a calculated column for Alert Date, I suggest creating a date column for this and updating that column with a list workflow. The workflow runs on item create or modify, calculates the Alert Date for Valid Date minus 30, stores that date in a workflow variable, and finally updates the Alert Date for the item.
Then you can create a site workflow and run it on a schedule. The workflow can query the list for Alert Date = Today's Date and store those list item IDs in a collection. Then it can loop through the collection and send out the emails.
It's as simple as what Eric said.
Libraries can be queried with the 'Query List' action. If you already have a datefield with a valid date in it you don't even need a second field.
Just query List - Filtered: Valid Until is smaller than or equal to [Today] + 30
Now there are a number of approaches to continue, depending on the metadata you need.
1. You only need metadata you know 'will' be available (filename, size, creator, )
2. You need metadata that might not have been filled in.
solution to 1:
This is the easy part. just collect the ID Column of those filtered files in colQueryFileIDs, loop through those and use the lookup list action to get all your data using the fileID as handle.
solution to 2:
In this case, depending on what data you need from the library, you might want to create extra collections for the different kind of columns. Loop through the ID collection (while using an index counter to remember where in the set you are) and print out the data from each different collection. The reason for this is that the lookup list action might cause an error when certain types of columns are empty. Most typically a lookup field.
Thanks for the response,
How does the loop action happen?
Do you have any examples or screen shots of what the workflow would look like, and also you are saying that I need to have 2 separate workflows?
Probably can take Valid Until column and take it into Workflow and calculate 30 days before Valid Until.
Then assign the result to a WF Variable and use Pause until Date action to pause the workflow until the date.
Add send an email action after the pause until date action to send the reminder email.
Hi Paul Gemme,
It can be done straight with single site workflow, you do not really need calculated field. It can a simple 4 step workflow like below.
Query list action should look like this , fyi because it is a date field I suggest doing CAML editor option and mentioning includetimevalue="false" for getting more accurate matching.
Final workflow would look like this
I hope this helps you!
Jaya, This seems like a good solution, however I am using Nintex for O365 and do not see Calculate date. Is there a different action I should use?
Looks like in O365 there is something called Add time to date action available. make use of it , may be this gives you little clue to solve
Jaya, Sorry for the delayed response. I have tried this and it sends an email for only the the list item that I start the workflow on. I must be missing something in the Query List action and "For Each" Would it be possible for you to give me screenshots of how these actions are configured and what variables I need to create? Again- I'm really new at this and appreciate your help.