Query List action: how to filter dates for a current month?

[igorsp]

My users are required to complete a task every month and a list item is updated with a date when the task was last completed. I would like to send an email reminder to all users who did not complete the task for the current month. I want to use the Query List action to filter the list but I am not sure what would be the best approach?

[SimonMuntz]

Hi,

There are many ways to skin a cat so I cant say ths is the best approach but this is what i would do.

1. Start with a workflow Variable that sets todays date.

2. Use a "Extract Substring of String from Index with Length" action to extract the Month from the date in the first variable.
For US dates you could just use the "Extract Substring from Start of String" action instead as the month is at the start.

2.1 Use a convert Value action to convert the extracted month into a number.

3. Query the list and get all the item ID's

4. Use a For Each action to go through the collection variable.
5. In the For each the first action would grab the date from the first item ID and then trim it as per step 2.

6. Next action in the for each would be to convert the output of step 5 into a number as per step 2.1.

7. In the for each use a conditional branch and set the condition that if the extracted month is greater than the current month send an email. If not do nothing.

This will loop through all items on the list and send emails when appropriate.

[igorsp]

Thanks for your suggestions. I am actually converting to ISO format because I also need a year. I will report back once I have the workflow ready.

[raghavat]

Hi @igorsp 

I suggest you to create a site workflow. This site workflow can be scheduled to run once a month.

Use a "Query list" action to get all the IDs from the items of your list. Store the IDs inside a collection variable and use a "For each" action to loop through your IDs. Inside the loop you can then use another "Query list" and configure its filter to only get the values for the item where the item ID is your current ID in the loop.

To determine whether the entry in the items date column is in the current month, Use a "Calculate date" action. Get the the current date, substract 30 or 31 days and compare it to the fields value.

Get the user field who assigned to the task and send a notification .