Check if a date is blank in SharePoint list

GraemeNZ · ‎11-22-2021

Hi. I have a SharePoint list that has two types of certificates (Halal and Kosher) and their expiry dates. In the SP List I created a calculated column that takes the end date of the certificate and subtracts 30 days to give me an Alert 30 days before the certificate expires.

I created a Flow to run daily for the Halal certificates - it checks the Alert Date and if it is greater than Now() the Status updates to Good. If the Alert is not greater than Now(), it updates the Status to Expiring. Then the next action checks the End Date and if that is greater than Now() it changes the status to Expired. This works fine.

Now I want to add an extra check to the start. Sometimes there is no certificate for one type, so the End Date will be empty. I copied the first (working) Flow and updated it for the Kosher parameters. Then I added a check at the beginning which checks KosherEnd date is equal to null. If Yes, update KosherStatus to N/A. If No (not null) then follow the same description as above... But it's not working consistently.

This time it mostly got it right, but there are some blank end dates that should be N/A, and the test status is Failed.

Error says 'no dependent actions succeeded'

When I step through the actions, some failed and some succeeded. When I click through a failed one I find

I don't understand why the logic for this doesn't work for Kosher certs, when the original for Halal certs works just fine but doesn't have the blank check at the start. Have I got something wrong with the blank check?

Thanks in advance.

Anonymous · ‎11-22-2021

Hi @GraemeNZ ,

Check out this post by Tim Riha:

What value to use in condition 'if field is empty' in Power Automate (tomriha.com).

Please let me know if this helps you.

Regards,

Daniel

GraemeNZ · ‎11-23-2021

Hi @Anonymous - thanks for your reply.

In my Flow, for the Get Items, I have this:

When I click the download, I get this... (a small snip - you get the idea)

I copied this and pasted into MS Word so I could do a search, and looked for the date field. There were only 5 references to this field and they are the ones that had a date in them

Therefore I conclude that I should use null, as per Tim's post

...which is what I've tried to do.

Can you think of any other reason why my Flow would work on some, but not all, of the blank dates?

Cheers, GraemeNZ

GraemeNZ · ‎11-23-2021

I set up some regular test data to make it easy to see what results I get from the Flow.

I then deleted the ones that should be NA and ran the flow again, and got the same result.

It appears the flow first checks the blanks and changes them to N/A (so this bit works). Then it checks the alert dates and changes them to Expiring based on that logic (which also works). But what I'm trying to do is say if the blank check applies then don't check any further on those ones.

Here's a tree view of my Flow, in case my logic approach is incorrect. (It annoys me that I can't get Flow to display a simple tree view on a page - if anyone can tell me how to do this, please let me know.)

vwyankee · ‎11-24-2021

If I recall correctly, "null" isn't going to work with a date field. See this thread where they check to see if the empty value is true - that should work for you.

Here is another reference that essentially is the same, but breaks it out into another step - Check if a date field is blank in Power Automate.

Anonymous · ‎11-24-2021

Hi @GraemeNZ ,

From the fact that this works in some cases and not in others, it means that you did it correctly but there is something variable that alters the result between it working and not working as desired.

I think the best is to compare the successful flow run with the flow run that did not produce the result you want in detail and note the differences.

Does your blank check work in come cases and not in others or not at all?

It sounds like it's just this part that you're experiencing a problem with?

GraemeNZ · ‎11-25-2021

Hi. I spent many hours today doing exactly what you've suggested. I came to the conclusion fairly early on that it wasn't the date check that was the problem, but rather my logic tree. I tried moving the check for a blank date to the end; I tried adding it as the outcome of one of my yes/no; I tried using a different column in my SP List that states whether or not there is a Halal certificate or a Kosher certificate (Yes/No). No matter what I did I couldn't get this to work.

In the end I changed the default end date to 2030, which then satisfied the requirement that if the end date is greater than Now(), change the Status to Good.

I then changed the Power App to only display items where the Halal or Kosher certificate was Yes, so it ignored all the default dates. Since most people will access the data through the app, this is the required outcome. It just looks wrong in the SP List...

I've been really frustrated and disappointed with Power Automate - despite all the hype that it is a fantastic easy to use product that you don't need to be a programmer for, it is incredibly pernickety and the facts that

you can't test a flow without saving it, and
if it doesn't work you can't undo the changes because you have saved it, and
you can't restore from a previous version

...all compounds to many hours of wasted effort trying to get this to work. It's made Power Automate something I will try very hard to avoid in the future... which seems a poor indictment on Microsoft. Granted Power Apps has its own issues, but this is now the second thing I've been able to solve in Power Apps that I haven't been able to solve in Power Automate.

Thanks for trying to help though 🙂