Send an HTTP request to SharePoint returns bad gateway

[Aimthedame]

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

[Expiscornovus]

Hi @Aimthedame,

Normally these type of queries work with the internal names of the columns.

Have you checked if HireDate column has an internal name of HireDate? The internal name of this column might be different, that could explain why your query is not working.

Here is an article from @ekarim2020 on how to double check/find the internal name of a column:

https://elliskarim.com/2021/12/20/finding-the-internal-name-of-a-sharepoint-column/

[Aimthedame]

That was the first thing I checked. I should have included that in my post. Sorry!

[Expiscornovus]

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).

[Aimthedame]

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...

[Expiscornovus]

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? 

[Aimthedame]

I tried adding new columns to the list and it does the same thing but when I used Birthday, Modified, or Created it works.

[Expiscornovus]

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?

[Aimthedame]

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?

[Expiscornovus]

Hi @Aimthedame,

Yes, you could use a ? character for that as a wildcard in your expression.

Try:

item()?['HireDate']

[Expiscornovus · Accepted solution]

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'))}

[Aimthedame]

That's exactly what I did and it worked. Thanks so much for your time on this!