I'm using Paul Murana's YouTube video How to send Automatic Birthday / Anniversary Email using a Power Automate Cloud Flow. Strangely, it works perfectly when I use the Birthday column of my SharePoint list but not the HireDate column. Both are date columns. Any ideas why the HireDate column doesn't work?
This works
This doesn't work
That's exactly what I did and it worked. Thanks so much for your time on this!
Hi @Aimthedame,
Just realised you want this check in the HTTP request, my bad 😂
Try and add a HireDate ne null check to the $filter, try this instead:
/_vti_bin/listdata.svc/EmployeeDirectory?$filter=HireDate ne null and month(HireDate) eq @{int(formatdatetime(utcNow(), 'MM'))} and day(HireDate) eq @{int(formatdatetime(utcNow(), 'dd'))}
Hi @Aimthedame,
Yes, you could use a ? character for that as a wildcard in your expression.
Try:
item()?['HireDate']
I finally figured it out! The only difference between the two columns is that two of the list items do not contain hire dates. When I entered dates it worked. Is there a way to leave those blank?
Hi @Aimthedame,
That is the bad gateway error, the first error.
In your previous reply you mentioned that in one of your checks you got a different error, the one below:
'The template language expression 'item()['HireDate']' cannot be evaluated because property 'HireDate' doesn't exist, available properties are '@odata.etag, ItemInternalId, ID, Title..
I am interested in that error, that might give me some clues. Can you share a screenshot of that specific error and where you used that item()['HireDate'] expression in your check?
I tried adding new columns to the list and it does the same thing but when I used Birthday, Modified, or Created it works.
Hi @Aimthedame,
Can you share a screenshot of your latest setup, that will help troubleshooting. Where did you add that expression with the item reference?
I decided to start clean just in case there was corruption somewhere along the line. I performed a Get items and then performed a compose to see the output. HireDate was listed and contained dates for 22 items. Then I checked the HireDate specifically (item()['HireDate']) and received this error.
'The template language expression 'item()['HireDate']' cannot be evaluated because property 'HireDate' doesn't exist, available properties are '@odata.etag, ItemInternalId, ID, Title...
Hi @Aimthedame,
Thanks for checking. What is your HireDate column setup?
Below is an example of the setup I used for testing and a couple of records with a HireDate value. In test only Jane should be returned today.
What is your exact URI value, is it like the below?
/_vti_bin/listdata.svc/EmployeeDirectory?$filter=month(HireDate) eq @{int(formatdatetime(utcNow(), 'MM'))} and day(HireDate) eq @{int(formatdatetime(utcNow(), 'dd'))}
I have tried to reproduce your issue in my development environment. It is only returning the Jane list item in my setup without any error and that URI you shared works like expected.
You might want to double check your inputs as well of the HTTP request action. Just to make sure it is compare month with 1 and day with 31 (in case of today's date).
That was the first thing I checked. I should have included that in my post. Sorry!