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:
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.
Solved! Go to 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
@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.
Hello,
What is the data type of the Employee Field, is it 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
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.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
User | Count |
---|---|
32 | |
28 | |
27 | |
21 | |
10 |