cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
loeakaodas
New Member

Determine which fields will be present for an entity using the Dataverse Web API??

I'm trying to build an ETL/ELT application using the Dataverse Web API and I'm having a problem with figuring out what fields/attributes/columns will be returned for a given entity/table. I've used the `EntityDefinitions` metadata endpoint with the $expand query param for the Attributes to try and determine this, but Im unable to determine what combination of `IsValidForRead` and `IsRetrievable` (or other) properties would get me the attributes/columns that I would get when calling a specific entity endpoint.
 
As an example in the "contact" entity/table the `parentcustomerid` attribute has the value of "true" for both the `IsValidForRead` and `IsRetrievable` properties, but doesn't show up when you call the contact endpoint and causes an error if included in the $select query param.  Is there a way to figure out what fields/columns will be returned for a given entity endpoint using the metadata for that endpoint? I'm not very familiar with the Dynamics data model or product and any help would be greatly appreciated!
 
I'm using the Microsoft sample dataset (if that matters). Here's the full metadata for attribute:
{
    "@odata.type": "#Microsoft.Dynamics.CRM.LookupAttributeMetadata",
    "MetadataId": "c7a58b13-df19-491c-a918-1bc26eaf6eb3",
    "HasChanged": null,
    "AttributeOf": null,
    "AttributeType": "Customer",
    "ColumnNumber": 179,
    "DeprecatedVersion": null,
    "IntroducedVersion": "5.0.0.0",
    "EntityLogicalName": "contact",
    "IsCustomAttribute": false,
    "IsPrimaryId": false,
    "IsValidODataAttribute": true,
    "IsPrimaryName": false,
    "IsValidForCreate": true,
    "IsValidForRead": true,
    "IsValidForUpdate": true,
    "CanBeSecuredForRead": false,
    "CanBeSecuredForCreate": false,
    "CanBeSecuredForUpdate": false,
    "IsSecured": false,
    "IsRetrievable": true,
    "IsFilterable": false,
    "IsSearchable": true,
    "IsManaged": true,
    "LinkedAttributeId": null,
    "LogicalName": "parentcustomerid",
    "IsValidForForm": true,
    "IsRequiredForForm": false,
    "IsValidForGrid": true,
    "SchemaName": "ParentCustomerId",
    "ExternalName": null,
    "IsLogical": false,
    "IsDataSourceSecret": false,
    "InheritsFrom": null,
    "CreatedOn": "1900-01-01T00:00:00Z",
    "ModifiedOn": "1900-01-01T00:00:00Z",
    "SourceType": null,
    "AutoNumberFormat": "",
    "Targets": [
        "account",
        "contact"
    ],
    "Format": "None",
    "AttributeTypeName": {
        "Value": "CustomerType"
    },
    "Description": {
        "LocalizedLabels": [
            {
                "Label": "Select the parent account or parent contact for the contact to provide a quick link to additional details, such as financial information, activities, and opportunities.",
                "LanguageCode": 1033,
                "IsManaged": true,
                "MetadataId": "6391aa12-2341-db11-898a-0007e9e17ebd",
                "HasChanged": null
            }
        ],
        "UserLocalizedLabel": {
            "Label": "Select the parent account or parent contact for the contact to provide a quick link to additional details, such as financial information, activities, and opportunities.",
            "LanguageCode": 1033,
            "IsManaged": true,
            "MetadataId": "6391aa12-2341-db11-898a-0007e9e17ebd",
            "HasChanged": null
        }
    },
    "DisplayName": {
        "LocalizedLabels": [
            {
                "Label": "Company Name",
                "LanguageCode": 1033,
                "IsManaged": true,
                "MetadataId": "6291aa12-2341-db11-898a-0007e9e17ebd",
                "HasChanged": null
            }
        ],
        "UserLocalizedLabel": {
            "Label": "Company Name",
            "LanguageCode": 1033,
            "IsManaged": true,
            "MetadataId": "6291aa12-2341-db11-898a-0007e9e17ebd",
            "HasChanged": null
        }
    },
    "IsAuditEnabled": {
        "Value": true,
        "CanBeChanged": true,
        "ManagedPropertyLogicalName": "canmodifyauditsettings"
    },
    "IsGlobalFilterEnabled": {
        "Value": true,
        "CanBeChanged": true,
        "ManagedPropertyLogicalName": "canmodifyglobalfiltersettings"
    },
    "IsSortableEnabled": {
        "Value": false,
        "CanBeChanged": true,
        "ManagedPropertyLogicalName": "canmodifyissortablesettings"
    },
    "IsCustomizable": {
        "Value": true,
        "CanBeChanged": false,
        "ManagedPropertyLogicalName": "iscustomizable"
    },
    "IsRenameable": {
        "Value": true,
        "CanBeChanged": false,
        "ManagedPropertyLogicalName": "isrenameable"
    },
    "IsValidForAdvancedFind": {
        "Value": true,
        "CanBeChanged": true,
        "ManagedPropertyLogicalName": "canmodifysearchsettings"
    },
    "RequiredLevel": {
        "Value": "None",
        "CanBeChanged": true,
        "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
    },
    "CanModifyAdditionalSettings": {
        "Value": true,
        "CanBeChanged": false,
        "ManagedPropertyLogicalName": "canmodifyadditionalsettings"
    }
}

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
EricRegnier
Super User
Super User

Hi @loeakaodas,

It will be hard (but possible) to automatically detect the columns within a table.  parentcustomerid will not be the only column type that you need to special logic but File type and eventually 1-M (multi-table) lookups also (e.g. parentcustomerid into _parentcontactid_value or _parentaccountid_value). I suggest if you can to keep it simple. It will be a lot of continuous maintenance to always refine as the metadata changes. Maybe you include in your ALM process to automatically detect metadata changes and then manually check with the team what those changes are and if it requires an update to the ETL? You can detect metadata changes with RetrieveMetadataChangesRequest message: https://docs.microsoft.com/powerapps/developer/data-platform/org-service/metadata-retrieve-detect-c... 

Hope this helps! 

 

View solution in original post

loeakaodas
New Member

Thanks to everyone for the replies!

 

I believe I've solved my own problem by retrieving and parsing the $metadata document for the Dynamics instance. The `Property` elements for each `EntityType` match up exactly to the fields returned by calling that specific entity/table endpoint; at least for the few entities I've checked so far like the "contact" entity I was having with issues in the OP.

 

I'm not concerned with the complex types like lookup, file, and relationships at this time but thanks for the heads up @EricRegnier and @ChrisPiasecki !

View solution in original post

5 REPLIES 5
dpoggemann
Super User
Super User

Hi @loeakaodas,

 

Can you utilize the metadata service api to get what you are looking for?  https://docs.microsoft.com/en-us/powerapps/developer/data-platform/webapi/query-metadata-web-api 

 

Hope this helps.  Please accept if answers your question or Like if helps in any way.

 

Thanks,

 

Drew 

Hope this helps. Please accept if answers your question or Like if helps in any way.
dpoggemann
Super User
Super User

Sorry, looked more and that is what you tried...

Hope this helps. Please accept if answers your question or Like if helps in any way.
ChrisPiasecki
Super User
Super User

Hi @loeakaodas,

 

In your example, the parentcustomerid is a Lookup, and actually a special polymorphic Lookup (can be contact or account). Data from related tables are not returned by default and you'll need to use the Expand query operator to get related data.

 

Have a review of the querying the Web API documentation to get familiar with querying it using OData.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

EricRegnier
Super User
Super User

Hi @loeakaodas,

It will be hard (but possible) to automatically detect the columns within a table.  parentcustomerid will not be the only column type that you need to special logic but File type and eventually 1-M (multi-table) lookups also (e.g. parentcustomerid into _parentcontactid_value or _parentaccountid_value). I suggest if you can to keep it simple. It will be a lot of continuous maintenance to always refine as the metadata changes. Maybe you include in your ALM process to automatically detect metadata changes and then manually check with the team what those changes are and if it requires an update to the ETL? You can detect metadata changes with RetrieveMetadataChangesRequest message: https://docs.microsoft.com/powerapps/developer/data-platform/org-service/metadata-retrieve-detect-c... 

Hope this helps! 

 

View solution in original post

loeakaodas
New Member

Thanks to everyone for the replies!

 

I believe I've solved my own problem by retrieving and parsing the $metadata document for the Dynamics instance. The `Property` elements for each `EntityType` match up exactly to the fields returned by calling that specific entity/table endpoint; at least for the few entities I've checked so far like the "contact" entity I was having with issues in the OP.

 

I'm not concerned with the complex types like lookup, file, and relationships at this time but thanks for the heads up @EricRegnier and @ChrisPiasecki !

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,197)