cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KarlinOz
Helper I
Helper I

http request failing for one field

I've been trying to get the http request working for about 5 hours now so it's time to ask for help again.

I get a basic query working fine:

api/web/lists/getByTitle('LST Training Register')/items?$select=Employment_x0020_Status/Value,Id

but when I add the field I'm really interested in grabbing (Employee):

api/web/lists/getByTitle('LST Training Register')/items?$select=Employment_x0020_Status/Value,Id,Employee/Value

my query fails with the error:

 

{"odata.error":{"code":"-1, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The query to field 'Employee' is not valid. The $select query string must specify the target fields and the $expand query string must contains Employee."}}}

 

I have been trying to follow the guidance here:

https://www.chakkaradeep.com/2018/05/14/select-and-filter-sharepoint-list-items-by-lookup-fields-usi...

and it works for the Employment Status field but not the Employee field.

This is what a subset of my data looks like:

array (
0 => 
array (
'@odata.etag' => '"14"',
'ItemInternalId' => '1',
'ID' => 1,
'Title' => 'Test McPerson',
'Employee' => 
array (
'@odata.type' => '#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference',
'Id' => 2,
'Value' => 'Test McPerson',
),
'Employee#Id' => 2,
'Employment_x0020_Status' => 
array (
'@odata.type' => '#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference',
'Id' => 0,
'Value' => 'Current',
),
'Employment_x0020_Status#Id' => 0,
),

 

I don't understand why I can return data from the Employment_x0020_Status/Value field but when I try to include the Employee/Value field I get the error. They appear, to me, to be structured the same.

I realise my data sample shows Title as having the same data, in this instance, as Employee/Value but that is not always the case. I need to grab Employee/Value.

 

I have checked the name of the Employee field by browsing to its settings and it appears to be called Employee:

From the browser url:

2020_12_16_22_22_28_Edit_your_flow_Power_Automate_Brave.png

 

This is a picture of my flow element:

2020_12_16_23_23_57_Edit_your_flow_Power_Automate_Brave.png

I'm hoping someone may be able to give me some guidance as to why it might be failing. Once I have data from that field coming in I will be wanting to filter by it.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hello @KarlinOz , please try any one below URLs and see if it works or not

_api/web/lists/getByTitle('LST Training Register')/items?$select=Employment_x0020_Status/Value,Id,Employee/Title&$expand=Employment_x0020_Status,Employee


_api/web/lists/getByTitle('LST Training Register')/items?$select=Employment_x0020_Status/Value,Id,Employee/Value&$expand=Employment_x0020_Status,Employee

 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

@annajhaveri you gave me a good lead so I am going to mark yours as the solution.

What ended up working was:

_api/web/lists/getByTitle('LST Training Register')/items?$select=Id,Employment_x0020_Status/Value,Employee/Full_x0020_Name&$expand=Employee

because what I didn't realise is the field Employee in my list is a lookup field to a parent list. The parent field is called 

Full Name

so I had to reference that. And with your syntax it all finally came together. Thank you for your guidance.

View solution in original post

5 REPLIES 5
annajhaveri
Super User
Super User

Hello,

 

What is the data type of the Employee Field, is it a lookup field?

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.
KarlinOz
Helper I
Helper I

Hi @annajhaveri , yes it is a lookup field

Hello @KarlinOz , please try any one below URLs and see if it works or not

_api/web/lists/getByTitle('LST Training Register')/items?$select=Employment_x0020_Status/Value,Id,Employee/Title&$expand=Employment_x0020_Status,Employee


_api/web/lists/getByTitle('LST Training Register')/items?$select=Employment_x0020_Status/Value,Id,Employee/Value&$expand=Employment_x0020_Status,Employee

 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

Hi @annajhaveri , thanks for the guidance.

Your proposed solution 

_api/web/lists/getByTitle('LST Training Register')/items?$select=Employment_x0020_Status/Value,Id,Employee/Title&$expand=Employment_x0020_Status,Employee

 gets a resonse:

The field or property 'Employment_x0020_Status' does not exist.

and the other one;

_api/web/lists/getByTitle('LST Training Register')/items?$select=Employment_x0020_Status/Value,Id,Employee/Value&$expand=Employment_x0020_Status,Employee

gets

The query to field 'Employee/Value' is not valid.

@annajhaveri you gave me a good lead so I am going to mark yours as the solution.

What ended up working was:

_api/web/lists/getByTitle('LST Training Register')/items?$select=Id,Employment_x0020_Status/Value,Employee/Full_x0020_Name&$expand=Employee

because what I didn't realise is the field Employee in my list is a lookup field to a parent list. The parent field is called 

Full Name

so I had to reference that. And with your syntax it all finally came together. Thank you for your guidance.

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,986)