Comparison of two dates in SharePoint bibliothec

[susistgl]

Hello together,

I have created two columns in a SharePoint library. One displays today's date. The other column called "Next check" subtracts 30 days from today's date and displays the result as the date. As soon as today's date matches the "Next check" date, a mail should be sent to the responsible person (I created a planned Flow in Power Automate). This is to check certificates one month before they expire. However, not every certificate has an expiration date, so there are also empty fields in the "Next check" column. During a test, my workflow is shown as "executed successfully", although no mail was sent.

Here is the build Flow:

Thank you!

[RobotRising]

Instead of using calculated columns in your SharePoint list for this (I am assuming that is what you are doing) I would set a static certification date column and then run the flow in recurrence to only get items that are 30 days from expiration and run your actions on those:

1. Trigger: Start your flow with an appropriate trigger, such as "Recurrence" if you want the flow to run at regular intervals, or "When an item is created or modified" if you want it to trigger when a new item is added or an existing one is updated in your SharePoint list.

2. Action: Add the "Get items" action to retrieve all items from your SharePoint list that have a certification date.

3. Filter Query: after getting the items, add a "Filter array" action to filter the items based on the certification date. Use an expression like `addDays(items('Get_items')?['body/CertificationDate'], 30)

to calculate a date that is 30 days from the current date. (You could also use the filter query in the get items which would be more performant as it would only get the items you need from the list) 

4. Loop: Add a "Apply to each" control and set it to loop through the filtered items.

5. Condition: Inside the loop, add a "Condition" action to check if the certification expiration date of the current item is equal to the calculated date from step 3.

6. If true, take appropriate actions (e.g., send an email, create a task, etc.) for the items that meet the criteria.

[susistgl]

Dear RobotRising,

thank you for your answer.

I tried to follow your instructions, but I don't get it... This is how far I've come:

I added the filter in the "Get File" Filter column as you proposed:

addDays(items('Get_items')?['body/G_x00fc_ltig_x0020_bis'],-30)

Could you maybe help me out? Maybe send an example so I get exactly what you mean.

BR, Susi!

[RobotRising · Accepted solution]

For the filter query it will be a different query. 

You could get all items and then filter the array with that query but for filter query in getting the items you would want: 

To filter SharePoint items that are 30 days away from a certification expiration date field, you can use the following OData filter query:

```

CertificationExpirationDate ge 'addDays(utcNow(),30)'

```

Make sure to replace "CertificationExpirationDate" with the actual name of the field in your SharePoint list that stores the certification expiration date.

This filter query will retrieve items where the "CertificationExpirationDate" is greater than or equal to the current date plus 30 days, effectively giving you items that are 30 days away from expiration.

[susistgl]

Thanks again for you help!

The filter is now working really well & also a mail is sent, when the date is 30 days away from the date when the certificate is not valid anymore. But only, when I'm not using the dynamic tools.. I wanted to fill the mail with informations about the certificate (the column contains a link to the certificate) and the date when it's not valid anymore. When I do that, no mail is not sent anymore.. Can you maybe explain why so?

[RobotRising]

Does it give you any error messages in the flow history when the email does not send? I don't see any conditions in there that would be stopping it 

[susistgl]

No, it sais that the flow was successfull..

[RobotRising]

It looks like it isn't pulling any items from that get items action which means the apply to each with the email action wouldn't run as there are no items to process. 

Are you sure your SharePoint list has expiring items in it within the range you set in the filter? 

[RobotRising]

Hi @susistgl just checking in, was this any help in resolving the issue? 

Thank you

[susistgl]

Hi RobotRising!

Thank you for your message. Unfortunately, I could not use the comparison in the list. I kept getting an error message. So I created a library after all & used your code for this! Now it works. Thank you very much for your help!

BR, Susanne