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

[loeakaodas]

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"
    }
}

[dpoggemann]

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 

[dpoggemann]

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

[ChrisPiasecki]

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 · Accepted solution]

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! 

[loeakaodas · Accepted solution]

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 !