Answered

Send an HTTP request to SharePoint returns bad gateway

(0) Share

Report

Posted on by Aimthedame

220

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

Categories:

Building flows

I have the same question (0)

All responses (11)

Answers (1)

Sort by

Expiscornovus 33,830 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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/

Was this reply helpful? Yes No
Aimthedame 220 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Expiscornovus 33,830 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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).

Was this reply helpful? Yes No
Aimthedame 220 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Expiscornovus 33,830 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Aimthedame 220 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Expiscornovus 33,830 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Aimthedame 220 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Expiscornovus 33,830 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Aimthedame,

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

Try:

item()?['HireDate']

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,830 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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'))}

Was this reply helpful? Yes No