Querying Multiple Tables in the Power Apps Component Framework-CodePudding

I am currently working on a component for Dynamics 365 Sales using the Power App Component Framework. I am at the stage where I have my design completed, but now need a way to display the data. I have two tables as follows (one-to-many relationship):

Lead (Default Entity)

Visit (Custom Entity)

Lookup called "new_new_parent_leadid" (relationship)
DateCreated
Duration
Value

What Id like to do is query data from Visits table from a component on the Lead form. This component would display the following:

Count: Count of all related visits
Latest Visit: DateCreated of the latest related record
Total Duration: Sum of all Durations of related visits
Value: Average of value of related visits

How might I retrieve this data?

CodePudding user response：

to retrieve data you should be using Xrm.WebApi functions.

There is an managed Solution/Tool called Dataverse REST Builder

Take a look at this tool and you will get all the answers to your questions.

Example--> Count: Count of all related visits (I changed it to get all contacts for a Parent Account)

You will get an array of records (Tool will display you result as well)

// NOTE: retrieveMultipleRecords is available in offline mode, if you need this functionality change the call to Xrm.WebApi.offline.retrieveMultipleRecords
// https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/xrm-webapi/offline
Xrm.WebApi.online.retrieveMultipleRecords("contact", "?$select=contactid,_parentcustomerid_value,fullname&$filter=_parentcustomerid_value eq 7b649f5c-c986-e411-a2ea-00505693000c").then(
    function success(results) {
        console.log(results);
        for (var i = 0; i < results.entities.length; i  ) {
            var result = results.entities[i];
            // Columns
            var contactid = result["contactid"]; // Guid
            var parentcustomerid = result["_parentcustomerid_value"]; // Customer
            var parentcustomerid_formatted = result["_parentcustomerid_value@OData.Community.Display.V1.FormattedValue"];
            var parentcustomerid_lookuplogicalname = result["[email protected]"];
            var fullname = result["fullname"]; // Text
        }
    },
    function(error) {
        console.log(error.message);
    }
);