http request failing for one field

[KarlinOz]

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:

This is a picture of my flow element:

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.

[annajhaveri]

Hello,

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

[KarlinOz]

Hi @annajhaveri , yes it is a lookup field

[annajhaveri · Accepted solution]

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

[KarlinOz]

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.

[KarlinOz · Accepted solution]

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