I am querying our MS Dynamics HR Talent installation using the OData WebApi.
I am running this query:
https://nnnnnnnnnnn.crm.dynamics.com/api/data/v9.1/cdm_workers?$select=cdm_fullname,cdm_primaryemailaddress&$expand=cdm_worker_employment($select=cdm_employmentnumber,cdm_ompayrollid_custom,_cdm_companyid_value;$expand=cdm_CompanyId)
using OData version 4.0.
The entities are not custom ones, but some have one or two custom fields.
When the results come back, the values for cdm_workers and for cdm_worker_employment are just what I would expect.
However the values for cdm_CompanyId are not. The values for all the fields are null, except for cdm_companyid which is what I would expect, and an extra, unexpected field named '__DisplayName__' which has the value I would expect to see in cdm_companycode.
Can anyone explain what is going on, and why I am seeing this behaviour?
Here is an example of the results I'm getting from this query:
"value" : [ {
"@odata.etag" : "W/\"3017162\"",
"cdm_fullname" : "Mmmmmm Rrrrrr Tttttttt",
"cdm_primaryemailaddress" : "mmmm.ttttttt@xyz.org",
"cdm_workerid" : "95f1fee2-2dcb-e911-a81d-000d3a1cae2a",
"cdm_worker_employment" : [ {
"@odata.etag" : "W/\"3767381\"",
"cdm_employmentnumber" : "EMPL0001029",
"cdm_ompayrollid_custom" : "444",
"_cdm_companyid_value" : "38d41deb-6888-e911-a81a-000d3a1cae2a",
"_cdm_workerid_value" : "95f1fee2-2dcb-e911-a81d-000d3a1cae2a",
"cdm_employmentid" : "bcb1f5df-2dcb-e911-a821-000d3a1ca610",
"cdm_CompanyId" : {
"_createdby_value" : null,
"overriddencreatedon" : null,
"_ownerid_value" : null,
"timezoneruleversionnumber" : null,
"_modifiedonbehalfby_value" : null,
"utcconversiontimezonecode" : null,
"_modifiedby_value" : null,
"createdon" : null,
"_owningbusinessunit_value" : null,
"_owningteam_value" : null,
"statuscode" : null,
"cdm_name" : null,
"versionnumber" : null,
"_createdonbehalfby_value" : null,
"cdm_companyid" : "38d41deb-6888-e911-a81a-000d3a1cae2a",
"_owninguser_value" : null,
"statecode" : null,
"cdm_companycode" : null,
"modifiedon" : null,
"importsequencenumber" : null,
"__DisplayName__" : "100",
"IsReferencedQueryCall" : true
}
} ]
By contrast, here's the result I get for this query: https://nnnnnnnnnnn.crm.dynamics.com/api/data/v9.1/cdm_companies?$filter=cdm_companyid+eq+'38d41deb-6888-e911-a81a-000d3a1cae2a'
"value" : [ {
"@odata.etag" : "W/\"3497456\"",
"_owningbusinessunit_value" : "f1ed9fcc-496e-e911-a9b7-000d3a152d6d",
"cdm_companyid" : "38d41deb-6888-e911-a81a-000d3a1cae2a",
"cdm_name" : "International",
"statecode" : 0,
"statuscode" : 1,
"_createdby_value" : "fe5e4d27-0b70-e911-a817-000d3a1ca508",
"cdm_companycode" : "100",
"_ownerid_value" : "fe5e4d27-0b70-e911-a817-000d3a1ca508",
"_modifiedby_value" : "fe5e4d27-0b70-e911-a817-000d3a1ca508",
"_owninguser_value" : "fe5e4d27-0b70-e911-a817-000d3a1ca508",
"createdon" : "2019-06-06T14:39:47Z",
"versionnumber" : 3497456,
"modifiedon" : "2019-10-24T07:59:37Z",
"overriddencreatedon" : null,
"timezoneruleversionnumber" : null,
"_modifiedonbehalfby_value" : null,
"utcconversiontimezonecode" : null,
"_owningteam_value" : null,
"_createdonbehalfby_value" : null,
"importsequencenumber" : null
} ]
I was expecting to see this data in the cdm_CompanyId object of the previous results.
The answer we have received from our support ticket is that multi-level expands are not supported.
There is a 'documentation mismatch' in the Microsoft documentation.
This page: https://learn.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/retrieve-entity-using-web-api#options-to-apply-to-expanded-entities says:
"You can apply certain system query options on the entities returned for a collection-valued navigation property. Use a semicolon-separated list of system query options enclosed in parentheses after the name of the collection-valued navigation property. You can use $select, $filter, $orderby, $top, and $expand.", which implies you can expand an expand, i.e. go to the third level (and further?).
However this page: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/odata#odata-services which relates to using OData with Dynamics 365 for Finance and Operations, says: "Available query options are: . . . $expand (only first-level expansion is supported)".
So the short answer is, expanding to the third level is not supported when using OData to query Talent.