The new Column comparison in queries in Dynamics 365 / PowerApps

Nishant Rana's Weblog

Column Comparison is one of the best features that has been recently added to the product.

Column comparison allows us to compare values of 2 different columns in our query be it Fetch XML, Web API, or through SDK API.

Sharing some of the basic examples that we tried using our favorite  FetchXML Builder

  • If we run the following query “Find all contact which has the first name equal to the last name”

The result –

  • Find all contact where the first name is not equal to last name

The result –

The operators supported are –

EqualLessEqualGreaterEqual
Not EqualLessThanGreaterThan
  • Only 2 columns can be compared.
  • Only compatible attribute types can be compared.
  • E.g. “Find all contact where the first name is equal to address 1 city”

The result

It works as both are string attributes.

  • If we try comparing text field with options set, we…

View original post 111 more words

Every PowerApps Formula You Ever Wanted To Know As A Functional Consultant

DIY D365

Hello Readers

This blog is to help fellow consultants to start their journey on PowerApps. We all know how easy it is to put together a screen on Canvas Apps but as and when we need a specific action on it or from it; we fall short of formulas/functions. That’s because we never required these formulas in Model Driven Apps.

But as Business/Functional Consultant, you want to deliver value to your client, make their life easier, Don’t you?

I am sure you do, so here I am providing you with 20 essential functions you must know as a functional consultant. This can be a starting point for you; well, Microsoft is trying hard for Business Users and Functional Consultants to get in the rink of Power Platform. Their learning path is specially designed for Business Users and Functional Consultants, check it out here Learn PowerApps.

All formulas below are…

View original post 504 more words

Use Azure AD Conditional Access to block access by country (Dynamics 365)

Nishant Rana's Weblog

In the previous post, we covered conditional access based on the device platform, here we’d look into how we can use the network location to block the access.

We can either use IP ranges or Countries / Regions for defining the location.

Login into the Azure Portal

https://portal.azure.com/

Navigate to Azure Active Directory – Security – Named locations to define the location.


Here we are adding a new countries location record.


For the new location, we have selected India and UAE.


Next click on Conditional Access to define a new policy.


For Users and groups, we have selected a user named testuser1.


For Cloud Apps or actions, we have selected Common Data Service.


For Conditions, we have specified Locations condition with the Restricted Locations record that we had created earlier.


For Access Controls, we have selected Block access.


Enable and create the policy.


Before…

View original post 57 more words

Status Reason Transitions in MS CRM

Today while exploring the CRM, I found “status reason transitions” option of status reason field. This option is introduced many years back but I haven’t noticed. Status reason transitions are an optional additional level of filtering to define what the status reason value can be changed to for each status reason. That means it will restrict the users to select the status reason which is not valid based on current status reason. Follow the below steps to configure this in your environment.

  1. Open the Default solution.
  2. Navigate to the entity (Case for example).
  3. Go to Fields and open “statuscode” field.
  4. Click on the “Edit status reason transition” button on the top.
  1. A window will open to configure the status reason transition . The Current Status Reasons are available on the left and for each of the Current Status Reason you can specify the available New Status Reasons.
  2. Select “Enable Status Reason Transitions” check box and add the new status reasons based on your business need.
  1. Save and publish the changes.

In the above example, one of the business scenario is, I will not allow the users to select “Problem Solved” status reason from “On Hold”. User has to change it to “In Progress” in order to move to any other status reason.

Like this you can configure all your scenarios based on your business needs. This can be configured on both System and Custom entities.

MSDN Link: https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/define-status-reason-transitions

Solved: Navigation property naming issue when working with Lookup fields using the WebAPI endpoint in CDS/CRM Connection Manager

In one of my previous post, I explained about the navigation property naming issue when working with Lookup fields using the WebAPI endpoint in “CDS/CRM Connection Manager”. Recently, I got a mail from Kingsway Soft support team with a Hotfix to resolve this issue. I installed this Hotfix in my machine and ran a simple package successfully to update custom lookup (account) on Contact entity.

This Hotfix is part of the most recent official release (20.1.0.1564). You can download this from the below link.

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/download

So, I would recommend everyone to use WebAPI endpoint instead of SOAP in “CDS/CRM Connection Manager” due to the fact that SOAP endpoint is deprecated and no longer supported by Microsoft.

You can find all enhancements of the recent official release (20.1.0.1564) in the below link

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/change-log

Happy migration….

Unit Test Case for Typescript Webresources

In our project, we got a requirement to write unit test cases for typescript webresources. In this blog, I am going to explain step by step process to write unit test case for one scenario.

Scenario: Set the Country field on Account Form. The data should be copied from logged in user profile.

I used the following frameworks to write unit test case.

Process: Follow the below steps

  1. Create a “Blank Node.js Web Application” project.
  2. Install the following node packages.
    • mocha@7.2.0
    • sinon@9.0.2
    • xrm-mock@3.4.18
    • @types/xrm@9.0.18
  3. Add “src” and “test” folders in the project.
  4. Add “Account.ts” file under “src” folder and “AccountTest.ts” file under “test” folder.
  5. Add the below code in Account.ts
export default class Account {
    
    public static setCountryField(): Promise<void> {
        let loggedInUserId = Xrm.Page.context.getUserId().replace("{", "").replace("}", "");
        if (loggedInUserId != null) {
            return new Promise((resolve, reject) => {
                Xrm.WebApi.retrieveRecord("systemuser", loggedInUserId, "?$select=_new_countryid_value").then((result) => {
                    resolve(
                        Xrm.Page.getAttribute("new_defaultcountryid").setValue([{
                            entityType: result["_new_countryid_value@Microsoft.Dynamics.CRM.lookuplogicalname"],
                            id: result["_new_countryid_value"],
                            name: result["_new_countryid_value@OData.Community.Display.V1.FormattedValue"]
                        }]));
                }).catch((error) => {
                    reject(error);
                });
            });
        }
    }

  1. Add the below code in AccountTest.ts
import { beforeEach, describe, it } from "mocha";
import Account from "../src/Account";
import assert = require('assert');
import { XrmMockGenerator } from "xrm-mock";
import * as sinon from "sinon"

describe("SetCountryTest", () => {
    beforeEach(() => {
        XrmMockGenerator.initialise();
        XrmMockGenerator.Attribute.createLookup("new_defaultcountryid", null);
    });

    it("Get logged in user country and set it on Account Form", () => {
        sinon.stub(Xrm.WebApi, "retrieveRecord").resolves({
            "_new_countryid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "new_country",
            "_new_countryid_value": "{00000000-0000-0000-0000-000000000002}",
            "_new_countryid_value@OData.Community.Display.V1.FormattedValue": "Germany"
        });
        return Account.setCountryField().then(() => {
            let countryId = Xrm.Page.getAttribute("new_defaultcountryid").getValue();
            assert.equal(countryId[0].id, "{00000000-0000-0000-0000-000000000002}");
        });
    });
});
  1. Build Solution (You have to build project/solution before Run Tests).
  2. Open the Test Explorer (Test –> Test Explorer).
  3. Run the tests by clicking the Run All link in Test Explorer. Or, you can run tests by selecting one or more tests or groups, right-clicking, and selecting Run Selected Tests from the shortcut menu.
  4. Here is the result from Test Explorer.
  1. You can also debug selected tests by selecting Debug Selected Tests.
  2. Final folder structure looks like below

Hope it helps…

Using Data Spawner component (SSIS) to generate sample data in Dynamics 365

Nishant Rana's Weblog

At times we need to generate sample data for our entities in Dynamics 365 for various reasons, performance testing is one of them.

Data Spawner component which is part of KingswaySoft’s
SSIS Productivity Pack provides us the most efficient way of doing so.

Download the component here –

https://www.kingswaysoft.com/solutions/ssis-data-generation-anonymization-components/data-spawner-component

Let us generate the sample data for Contact Entity.

Add the Data Spawner component to the Data Flow along with the CDS Destination component in the integration service project.

Double click the Data Spawner to open the editor.

Click on Add + button to specify the columns, here we have specified four different columns.

We have kept the name for each of the columns, same as the schema name so that it is easy to map them in CDS Destination.

For the First Name column, we have specified Data Type as nvarchar and Spawn Type as the First Name, which will…

View original post 180 more words

PluginType not found in PluginAssembly which has a total of [0] plugin/workflow activity types.

Today while updating one of my plugin assembly in plugin registration tool, I got the below error.

Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: PluginType  not found in PluginAssembly which has a total of [0] plugin/workflow activity types.
Detail: <OrganizationServiceFault xmlns="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <ActivityId>2b06487c-19ac-4d04-9b0f-0d8c95aa2eb3</ActivityId>
  <ErrorCode>-2147204725</ErrorCode>
  <ErrorDetails xmlns:a="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
    <KeyValuePairOfstringanyType>
      <a:key>ApiExceptionSourceKey</a:key>
      <a:value i:type="b:string" xmlns:b="http://www.w3.org/2001/XMLSchema">Plugin/Microsoft.Crm.ObjectModel.PluginAssemblyService</a:value>
    </KeyValuePairOfstringanyType>
    <KeyValuePairOfstringanyType>
      <a:key>ApiOriginalExceptionKey</a:key>
      <a:value i:type="b:string" xmlns:b="http://www.w3.org/2001/XMLSchema">Microsoft.Crm.CrmException: PluginType not found in PluginAssembly  which has a total of [0] plugin/workflow activity types. ---&gt; Microsoft.Crm.CrmException: PluginType not found in PluginAssembly which has a total of [0] plugin/workflow activity types.

After a bit of research, I found that I forgot to change the “Copy Local” property to false of “Microsoft.Xrm.Sdk” and “Microsoft.Crm.Sdk.Proxy” dlls. In our case, we are using ILMerge to merge different dlls but these Microsoft dlls should not be part of the Merged dll. After setting the “Copy Local” property to “false”, Plugin assembly updated sucessfully.

Hope it helps…..

Get Transaction currency name for logged in user in Dynamics 365

Debajit's Dynamic CRM Blog

Recently Microsoft have released quite a few updates to it’s client API and one such is the update to API for getting the currency name of the logged in user.

All this time, Microsoft had an API to get the transaction currency id of the logged in user using the API – . You needed to run a separate query to fetch the currency name based on currency id. However it is being deprecated now and now the replacement API is

This wonderful api returns the name of the currency as well as the id and entityType.

Below is the sample output

Wonderful isn’t it?

Hope this helps!

Debajit Dutta

(Dynamics MVP)

For consultation/ corporate training visit www.xrmforyou.com or reach out to us at info@xrmforyou.com

Our product offerings:

Role based views for Dynamics 365 (http://www.xrmforyou.com/role-based-views.html)

CRM-Sharepoint Attachment uploader and metadata manager (http://www.xrmforyou.com/sharepoint-integrator.html)

Record Cloner for Dynamics…

View original post 4 more words

Nested Editable Grids in D365 CE v9 UCI

D365 Demystified

Nested Grids – as the name suggests is a grid-within-a-grid (or rather, Grid-ception!). Nested Grids will let you expand a sub-grid entry to look at another grid of the expanded record. This depends on how you configure it.

Be aware, Nested Grids work with Editable Grids and only for Tablet, Phones and Unified Interface. This is not available for the classic Web UI.

Configuring Nested Editable Grid

Here’s my entity structure – I have Account, having multiple Contacts and each Contact, having Opportunities under them. Like in the below diagram –

relationships

Now, I have a Contact grid on my Account form (just like we usually do).

  1. I now will have to choose the grid to be a Editable Grid Control as follows. Also, click on the Nested grid view control as pointed
    editableGridControl
  2. On clicking the Nested grid view pencil, the next dialog box will let you select what entity…

View original post 186 more words