cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

Looking to create a workflow to send email based on date from Calculated column

Jump to solution

Hello,

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.

Thanks,

PaulG

Labels: (1)
0 Kudos
Reply
12 Replies
Workflow Hero

Re: Looking to create a workflow to send email based on date from Calculated column

Jump to solution

Hi Paul,

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. 

Reply
Workflow Hero

Re: Looking to create a workflow to send email based on date from Calculated column

Jump to solution

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.

0 Kudos
Reply
Workflow Hero

Re: Looking to create a workflow to send email based on date from Calculated column

Jump to solution

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?

0 Kudos
Reply
Workflow Hero

Re: Looking to create a workflow to send email based on date from Calculated column

Jump to solution

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.

0 Kudos
Reply
Workflow Hero

Re: Looking to create a workflow to send email based on date from Calculated column

Jump to solution

Hi ‌, have you tried my suggestion? or you have gotten the solution for this? Would be great if you can share it here.

0 Kudos
Reply
Workflow Hero

Re: Looking to create a workflow to send email based on date from Calculated column

Jump to solution

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!

0 Kudos
Reply
Workflow Hero

Re: Looking to create a workflow to send email based on date from Calculated column

Jump to solution

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?

0 Kudos
Reply
Workflow Hero

Re: Looking to create a  workflow to send email based on  date from Calculated column

Jump to solution

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 

https://community.nintex.com/thread/15007-setting-the-task-due-date-3-days-from-submitting-the-form?... 

Reply
Workflow Hero

Re: Looking to create a workflow to send email based on date from Calculated column

Jump to solution

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.

Thanks,

Paul 

0 Kudos
Reply