The best way to implement what you want is with a site workflow; that fixes your not-checked in problem, fixes your possible date change scenario, and is a better practice so that you don't have suspended/runnign workflows waiting for months.

I would have a site workflow run every morning that queries your list for items where renewal date == today + 30 days and then sends an email for each one (you could do that within the site workflow or kick off a list level workflow to handle that piece, not much difference).

Thanks Courtney, Is this possible when using Nintex for SharePoint online? I thought you couldn't use site workflows.

Yep! Go to Site Contents and click on the Nintex Workflow icon, this will take you to a Site Workflow creator. As for scheduling, currently Nintex does not support that, but there are third party applications, such as Plumsail, as well as ways to do this with just Nintex or while leveraging Azure.

Thanks. I have tried to create a site workflow with no luck. Struggling with the Query List action to return items. I have been following this post here https://community.nintex.com/community/build-your-own/blog/2016/05/23/site-workflow-document-review-date-approaching-reminders

The steps I have taken are:

1. Set todays date in a variable
2. Calculated the todays date +30 days to get the date I want to query against the list. I stored this in a variable varRenewalDate
3. In the query action item I have filtered for records where Renewal date is equal to the varRenewalDate and set the format to short date.

That is where I get stuck. It returns no values. I think it is the time portion causing the issue.  I have set the Renewal Date column in the list to date only.

Yep, you're right, it's probably the time. So, what I do in this scenario, is I'm assuming you're setting your variable and checking the "use date action was executed" thing, right? So what I would do, is have your first action be a "set time portion of Date/Time" and set the minutes and hours to 0 and check the "date action was executed" box and output that to a variable.

Then, have a "add time to date" action and add your 30 days to that. It will basically nullify the time portion.

If you're already not using time in your list column, that will be, by default, typically 00:00:00 or 12:00:00 which this will now match. IF you're ever unsure on something like this, you should use the "log" action to print your variables/values at different times to make sure they're matching what you think they are.

Thanks Courtney. I have done the above and logged both dates and they match however when it runs the query it still returns no match. Seems odd! Am I missing something? Is it date formatting? Maybe an issue with Online version of Nintex?

Ah I finally got it to match the dates and return the IDs in the output. Now I need to figure out how to query the list and get the fields I need to include in the email.  The post I was referring to doesn't seem to work for me. It mentioned a variable type of List Item ID. This doesn't seem to be available in O365 version. Is it a collection variable?

I finally got this all to work. Thank you so much for all your help and patience!!

This only issue I have now is the date that is getting queried from the list and stored in the collection variable. It is US format and time zone. How can I get it to show based on our regional settings?

See example:

Renewal Date - 6/2/2017 2:00:00 PM

Date in the list is actually 03/06/2017 12:00:00AM as per the log

Wow, glad you were able to get a solution! As for the regional question, I'm afraid I'm not much help. We are in one location and so don't really have issues there. You could try to post a new question to get more views/traction! Perhaps ‌ could help, he's quite sharp.

Katie, for the first, and the very early question - how can you make the workflow to wait until the file is checked in - you shouldn't. You should however add the "Filter" action as the first in your workflow that will be configured to "Checked out" is empty and set it to be triggered if the file is modified. Moreover - if the workflow should be triggered once the file is checked in for the first time (so published?) then you can make a filter rule to: Version equals 1.0

I saw from the discussion that went for a different solution, so that you involved Site Workflows as Courtney suggested - I think it is a good idea, and one of the best. Unfortunately ‌ in nintex for office365‌ do not have "Scheduling" functionality available yet. That would be the best solution for you. It is already planned to be delivered later this year (maybe Q3?).

Regarding your "date" question - there is now solution for that. Or at least I am not aware of any. To be sure you did whatever possible, go to the "Site Settings" and check "Regional Settings" page to verify what is the date format set there. I think Nintex may be taking these settings into consideration. If not however, then the only way I see to make it different is to get the date in "Text" format, then use the "Regular expression" action to split it using the "/" character and then to get such created collection elements to create your new date string (but be aware, that such created string will not be treated as a valid "date" string...)

Regards,

Tomasz

Thanks for all your help guys. Really appreciate it. I went with a site workflow and have scheduled it using the example below. Works well.

https://community.nintex.com/community/tech-blog/blog/2014/09/23/scheduled-workflows-in-office-365

I will have a look at your suggestion for the date.

Thanks again!

Already have as the main question was answered and solved  I am looking into the date issue now.

Yes I think I will once I have confirmed all my regional settings are correct. I think they are but I will double check I haven't missed anything before posting.

I think this is the issue here that I am experiencing.

https://community.nintex.com/thread/4029#comment-13348

I have managed to resolve the date issue appearing in my email incorrectly by doing the following:

1. Created a calculated column based on renewal date to appear in the format that I wanted in the email. Date only, no time and formatted as DD/MM/YYY
2. In the query returned the calc date in a collection variable then got it from the collection variable and stored in a text variable.
3. Used the text variable in my email

Thanks again everyone. Been pulling my hair out over this!