Reminder Flow for Microsoft List to include Blank Date Field as Blank/Unavailable

[jesj]

Please could somebody in this community help me with a reminder flow that I am trying to create as I'm quite new to PA?

I want to send a reminder email 30 days prior to expiry of a certificate and CV to individual users. I also want to send emails to those users that I do not have a date of issue for their previous CVs and certificates (as I'm picking from an existing excel list).

When I Get items with a Filter Query, the flow is successful when there are dates but runs into errors for when there are no dates. I tried using null but get the error "not expected string Date/Time format".

The most recent error is -

The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. Creating query failed.
clientRequestId: 726d26fc-5a4d-4ae7-b4bd-cb77a1ab8deb
serviceRequestId: 726d26fc-5a4d-4ae7-b4bd-cb77a1ab8deb

The following is my flow -

And, following is the Filter Query being used -

I also tried @abm's advise for a similar issue in one of his posts and the flow ran partially with the following error -

One or more fields provided is of type 'Null', a different type is expected.

* I removed the unnecessary parameters ()

I think I'm struggling with getting the flow to pick up fields that have no value. The following is my test data set -

* The Expiry and Status columns in the above image are calculated columns.

On further investigation, I then removed the formatDateTime expression from inside the Send an email (v2) as that was causing the error for null fields -

formatDateTime(items('Apply_to_each_-_CVReminder')?['field_4'],'dd-MMM-yyyy')

* field_4 is the CV Updated date

Now the flow runs and as I have used the following expression -

items('Apply_to_each_-_CVReminder')?['field_4']

But the issue with this now is that the date appears in the email in the format yyyy-MM-dd where available and blank where not present. However, I would like it to appear as dd-MMM-yyyy (remember I had used the formatDateTime expression earlier and that caused the issue) where the date is present, and as "Unavailable" where not present.

As for the 30 day advance, I initially used the adddays expression, but I was unable to use the CV Expiry date as a reference because it was a calculated column (which PA does not support I believe). So, instead, I am using the subtractFromTime expre@@ssion to count the required period backward (from each day using the utcnow expression) minus the 30 days/1 month and comparing that against the CV Updated/Cert Completed dates.

Any advise and help would be appreciated. Thank you!

[abm · Accepted solution]

Hi @jesj 

Looks like your expression for date is not quite right.   For 30 days before you need something like this.   formatDateTime(addDays(-30), 'yyyy-MM-dd')  

If the date is empty then do the following check.

if(empty(<DynamicDate>), null, formatDateTime('<DynamicDate>','dd-MM-yyyy'))