Solved

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

  • 25 September 2017
  • 12 replies
  • 476 views
P
Badge +3

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

icon

Best answer by borrajaya 28 September 2017, 17:34

View original

12 replies

bamaeric
Userlevel 5
Badge +13

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. 

I
Badge +4

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.

A
Badge +5

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.

P
Badge +3

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?

B
Badge +5

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!

P
Badge +3

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?

B
Badge +5

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?commentID=54467#comm… 

A
Badge +5

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

TomaszPoszytek
Userlevel 7
Badge +17

Paul Gemme‌ if you find any of the above answers as correct please mark it so that other users will know there is a solution here.

Regards,

Tomasz

Regards, Tomasz Poszytek
P
Badge +3

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 

B
Badge +5

Paul,

Apologies for the delayed response, caught up with too many things going around.

It is supposed to be a site workflow not an item level workflow, remember you are looping through all the items from your list ? , create a site workflow with the steps as explained in my previous reply and schedule it to run everyday at your desired time window.

Happy Nintexing

P
Badge +3

Jaya,

I was able to get this to work based on the steps you provided. Initially in the query List action, I was filtering by " When Date Column  equals "WV30days" after"  and it was not capturing the id's.  I changed it to  'is Less than or equal to" and now it works.  

Thanks for your help,

Paul

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings