Retrieve Dataverse records with JavaScript

Ben Prins Power Platform

When validations or manipulations in a model-driven app are too complex for a business rule you can use JavaScript instead. With JavaScript you can use the Dynamics API to gather information and/or update records. JavaScript only runs on the interface; this means that the validation or manipulation only happen when a user is interacting with the model-driven app.

retrieveRecord

With retrieveRecord you can retrieve a records form a table if you know the ID. 

Xrm.WebApi.retrieveRecord("account", "a8a19cdd-88df-e311-b8e5-6c3be5a8b200", "?$select=name,revenue")

Xrm.WebApi.retrieveRecord("TABLE", "ID", "?$select=COLUMN,COLUMN")

In this example a record from the table accounts is retrieved and the columns name and revenue are returned. If it was successful the results are displayed in the console, if an error occurred then the error message is displayed in the console.

Xrm.WebApi.retrieveRecord("account", "a8a19cdd-88df-e311-b8e5-6c3be5a8b200", "?$select=name,revenue").then(
    function success(result) {
        console.log("Retrieved values: Name: " + result.name + ", Revenue: " + result.revenue);
        // perform operations on record retrieval
    },
    function (error) {
        console.log(error.message);
        // handle error conditions
    }
);

retrieveMultipleRecords

With retrieveMultipleRecords you can retrieve multiple records from a table based on a filtering.

Xrm.WebApi.retrieveMultipleRecords("account", "?$select=name,primarycontactid&$filter=primarycontactid eq a0dbf27c-8efb-e511-80d2-00155db07c77")

Xrm.WebApi.retrieveMultipleRecords("[TABLE]", "?$select=[COLUMN],[COLUMN]&$filter=[COLUMN] eq ID")

In this example three records from the table accounts are retrieved and the columns name is returned. If it was successful the results are displayed in the console, if an error occurred then the error message is displayed in the console.

Xrm.WebApi.retrieveMultipleRecords("account", "?$select=name", 3).then(
    function success(result) {
        for (var i = 0; i < result.entities.length; i++) {
            console.log(result.entities[i]);
        }
        console.log("Next page link: " + result.nextLink);
        // perform additional operations on retrieved records
    },
    function (error) {
        console.log(error.message);
        // handle error conditions
    }
);

Expand query to get related records

With the $expand options we can retrieve related records of the record that was returned, this works for both retrieveRecord and retrieveMultipleRecords. Expand uses navigation columns (relationship/lookup) to retrieve the related records.

Xrm.WebApi.retrieveRecord("account", "a8a19cdd-88df-e311-b8e5-6c3be5a8b200", "?$select=name&$expand=primarycontactid($select=contactid,fullname)")
Xrm.WebApi.retrieveRecord("[TABLE]", "ID", "?$select=[COLUMN]&$expand=[NAVIGATION COLUMN]($select=[COLUMN],[COLUMN])")

Xrm.WebApi.retrieveMultipleRecords("account", "?$select=name&$top=3&$expand=primarycontactid($select=contactid,fullname)", 3)
Xrm.WebApi.retrieveMultipleRecords("[TABLE]", "?$select=[COLUMN]&$top=3&$expand=[NAVIGATION COLUMN]($select=[COLUMN],[COLUMN])", 3)

Asynchronous function to wait on the return

When using retrieveMultipleRecords you might need to use an asynchronous function. The function needs to wait on retrieveMultipleRecords to return the values before continuing with the function. You do this by making two async functions, one with the main logic and the second one which retrieves the records.

async function xseption(formContext) {
    var xseptions = await getXseptions(companyProfileId);
    //Do something with the return
}

async function getXseptions(guid) {
    var query = "?$select=rc_categorytypeid,rc_xseptionsid&$filter=_rc_related_companyprofile_value eq " + guid + "&$expand=rc_categorytypeid($select=rc_value)";
    var result = await Xrm.WebApi.retrieveMultipleRecords("rc_xseptions", query);

    return result;
}

Embed a canvas app in a model-driven app

Ben Prins Power Platform

Did you know that you can embed (add) a canvas app in a model-driven app? With the embedded canvas app, you can fully use the power of the canvas app inside a model-driven app. In my project I used it to provide the user with the capability to search an Oracle database and select a specific company.

It is very easy to add a canvas app, but I recommend to use it only when no other options are viable. The reason for this is that the embedded canvas app needs to be reconnected every time you transfer the solution form one environment to another.

Embed the canvas app

  • Create / add a canvas app in the same environment as the model-driven app.
  • Open the form of the entity where the canvas app needs to be embedded.
  • Click on +Component and select the Canvas app.
  • Fill in the App ID Static value with the unique ID of the canvas app and click on Done.
  • You can find the App ID by right clicking on an app and clicking on Details.

Solution deployments

The canvas app is now part of the model-driven app and needs to be in the same solution. When you transfer the solution from the development environment to the test environment, you will need to update the model-driven form manually. The reason for this is that the model-driven app is still connected to the canvas app on development. You will need to change the reference / GUID to the canvass app on production. And do not forget to share the canvas app with the users.

Power Automate: Advanced Flow building

Ben Prins Power Platform

Power Automate is one of my favorite tools from the Power Platform. It is extremely versatile and can be used to automate tasks between online services and automate processes ranging from simple to highly complex. In this post, I will share with you 3 advanced expressions I have used recently on my project. One part of the project is to convert XML data to data we can store in the dataverse.

Convert XML to JSON for easy access

For a project I needed to read multiple XML files with millions of rows and store data from the files into the dataverse. XML is harder to use in a Flow then JSON, so with a simple expression I transformed the XML to JSON.

  • Add a compose action with the name XML to JSON with the following code.
json(xml(variables('XML')))
  • Change the variable(‘XML’) to your XML content or store your XML content in that variable.
  • Add a parse json action set the Content to the output of the XML to JSON compose.
  • Add/create the JSON schema.

Using path in JSON

In most cases when you need to save data from JSON you can use the dynamic content to find it.
But sometimes you are looking for a field name that is not unique. In my case I needed a field called country related to the company. But the country field was used multiple times for various blocks. You can select the correct country by using the path (location) of the field in an expression.

  • Select the JSON through the dynamic content.
  • Copy the code from the dynamic content to the Expression.
  • Add the path add the end of the code.
  • I added .company.country to select the country of the company.
body('Parse_JSON').company.country

Dataverse lookup field

Lookup fields in Dataverse are really useful, but when you select them through the dynamic content the value will be the id not the display value. If the data must be readable for users, you can use the following steps to select the display value.

  • Add a compose to the flow.
  • Select the lookup field in the Inputs through the dynamic content.
  • Copy the code from the dynamic content to the Expression.
  • Your output looks something like this.
outputs('company')?['body/rc_countrycode']}
  • Add the @OData.Community.Display.V1.FormattedValue’ after rc_countrycode (your field name will be different).
  • The end results looks like this.
outputs('company')?['body/rc_countrycode@OData.Community.Display.V1.FormattedValue']

Power Automate: Find the current environment

Ben Prins Power Platform

When working with an ATOP setup you might need to know in which environment the Power Automate flow is running. For my solution I needed to know the environment because each environment uses a different Gateway and database credentials. In this post, I will share with you how to find the environment GUID and name.

Creating the flow

  • Create a flow and use the trigger Manually trigger a flow.
  • Add the action Get Environments under Power Apps for Makers
  • Add the Compose action and use the Workflow() expression, to get the current instance of the flow.
  • Parse the Output in a Parse JSON action.
  • Initialize a variable with the name environment as a string.
  • The Value is the EnvironmentName form the Parse JSON output.
  • Now you have the GUID of the current Environment in the variable.
  • To find the name of the current environment we need to go through the results of the Get Environments action.
  • Add a Condition control action and check if the environment variable is equal to the name from the Get Environments actions.
  • This will automatically add an Apply to each, this is because the Get Environments action might return more than one environment.
  • Add a Set variable action in the If yes section and set the variable environment to displayName.
  • Now you have the Name of the current Environment in the variable.
  • The final step is to add a Switch control and switch based on the name of the current environment.
  • The flow will now look like this.