I am trying to aggregate using "group by" on a lookup field in the Dataverse API.
Example:
$apply=groupby((<lookupfield>/<fieldname>), aggregate($count as total))
The error message I'm getting is:
code=0x80041103 {table} entity doesn't contain attribute with Name = 'ownerid' and NameMapping = 'Platform'.
{"error":{"code":"0x80041103","message":"'SystemUser' entity doesn't contain attribute with Name = 'ownerid' and NameMapping = 'Platform'. MetadataCacheDetails: ProviderType=Dynamic, StandardCache=True, IsLoadedInStagedContext = False}}
Here are very simple tests to use in a browser. (PrimaryContactId and PreferredSystemUserId are both lookup fields in the default accounts table.)
This works:
<organizationURL>/api/data/v9.2/accounts?$apply=groupby((primarycontactid/fullname))
This errors:
<organizationURL>/api/data/v9.2/accounts?$apply=groupby((preferredsystemuserid/fullname))
It also fails on some tables that have an Owner ID. The error message is the same, but it mentions the root entities instead of the related entities.
This errors:
<organizationURL>/api/data/v9.2/cr203_newtables?$apply=groupby((cr203_Lookup/cr203_code))
'cr203_newtable' entity doesn't contain attribute with Name = 'cr203_Lookup' and NameMapping = 'Platform'
(Different error "Property not found" if I use Logical Name instead - cr203_lookup/cr203_code)