Author: Sudheer Reddy Nelakurthi

Power Platform developer for 8 years

Retrieve Records by comparing two Columns in Web API Query

Hi learners,

Till now, $filter can be applied for a field against a constant value. With this new feature $filter can be applied for a field against another field in scenarios like below

  • Get all contacts where First Name = Last Name
  • Get all Opportunities where Estimated Revenue >= Actual Revenue
  • Get all Cases Resolved post due date.
    etc..

Supported Filters:

  1. Equal
  2. NotEqual
  3. GreaterThan
  4. GreaterEqual
  5. LessThan
  6. LessEqual

Example: To retrieve all contacts whose First Name = Last Name

Web API Query:

<Instance Web API>/contacts?$filter=firstname eq lastname
[ Note: This Web API expression works only for version 9.1.0000.19562 or later ]

fetch XML Query:


References:

1) https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/column-comparison
2) https://powerapps.microsoft.com/en-us/blog/announcing-column-comparison-through-fetchxml-sdk-and-odata/

Update records without changing ‘Modified-By’ & ‘Modified-On’ in Dynamics 365

Hi Learners,

Using this approach we can update any field from a Record without actually impacting Modified By & Modified On values.

Please Note: It is not best practice to suppress Modified By & Modified On auto calculation by Dynamics, but this approach helps in scenarios like below

  1. Scenario -1 : We introduced a new filed and want to set a default value for all the existing records, but don’t want to touch Modified By for reporting / downstream integration purposes.
  2. Scenario – 2 : We missed updating some information during data migration and want to update the information later without Modified On/Modified By being updated.

Approach:

We will be using Pre-Operation plugin to set “modifiedon” & “modifiedby” to the existing values (fetched from Pre-Image). Same Approach can be for overriding Modified By / Modified On / Created By / Created On with some custom values.

Step-By-Step process:

1) For the Entity you want to use (Account for this example), Create a new boolean field called “Override Modified On” (new_overridemodifiedon), set default value as NO.

2) Create a new Plugin with below code.

public void Execute(IServiceProvider serviceProvider)
{
IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
Entity targetEntity = (Entity)context.InputParameters["Target"];
Entity preImage = context.PreEntityImages.Contains("PreImage") ? (Entity)context.PreEntityImages["PreImage"] : null;
bool _override = targetEntity.Contains("new_overridemodifiedon") ? (bool)targetEntity.Contains("new_overridemodifiedon") : false;    
if (preImage != null && _override)
    {
        targetEntity["modifiedon"] = preImage["modifiedon"];
        targetEntity["modifiedby"] = preImage["modifiedby"];
        //Set Override Modified On to false for future use.
        targetEntity["new_overridemodifiedon"] = false;
    }
}

3) Register the plugin and add step as shown below.

Message = Update;
Primary Entity = <<Entity you want to use >>
Filtering Attribute = new_overridemodifiedon (created in 1st step)
Pipeline Stage = Pre-Operation.

4) Register an Image under the step as below

Image Type = Pre Image
Name = PreImage
Parameters = modifiedby, modifedon

5) Now whenever you want to Update something without “Modified On” being touched, Check the checkbox “Override Modified On” along with your other changes.


The changes are tracked under Audit History.



But you can observe that, even after the fields are updated, Modified On doesn’t change and retains its old value.


6)It works even if you are making updates through SDK / any other integration platform. You just need to set “Override Modified On” = true, along with the update that you perform.

Example:


References:

1) https://community.dynamics.com/365/b/learncrminfingertips/posts/preserve-or-ovverride-createdon-createdby-modifiedon-modifiedby-fields

Add Formatting & Country Code Prefix to Phone Number based on Country (Using OOB Input Mask Control)

Hi Learners,

Most of the projects have this requirement to apply validations OR add Country Code as prefix to Phone Number based on the Country. This approach makes use of most of the OOB methods to achieve this.

  1. On Account Entity create a field to capture the formatting “Phone Number Format”
  2. Configure the Input Mask control for Phone Number field as below.

    From form editor -> field properties -> Controls, Select Input Mask control


In the Mask Properties, Select “Bind to a value on a field” and select the field created in Step-1 (Phone Number Format)

3. Create a new Entity for Country and create a field “Phone Number Format” to store Phone Number formats for each country.

Guideline for formatting:

0 – Digit
9 – Digit or space
#– Digit, sign, or space
L – Letter
I – Letter or space
A – Alphanumeric
A – Alphanumeric or space
< – Converts characters that follow to lower case
– Converts characters that follow to upper case
| – Disables case conversion
\ – Escapes any character, turning it into a literal
All others – Literals


Meaning the format shown in the picture [+\910000000000] –>

+\91 –> Country Code literal “+91” . (have to use “\” before “9” because “9” is a special character in Input masking and needs to escaped to convert it to literal)

rest 10 zeroes “0000000000” represents place holder for 10 digits.

4. On Account Entity Create a lookup For Country and write a JavaScript function to populate “Phone Number Format” from selected country to the Account’s field. This will apply the mask on Phone Number field automatically.

(This field can be hidden if user’s don’t want to see it)


5. When user tries to enter value other than specified in the mask, Error message is shown like below. Also, it allows you to type characters only as specified in the Mask format. (In our example, you can only type in digits but cannot type in alphabet / special characters in the remaining 10 places.)


References :

1) https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/additional-controls-for-dynamics-365-for-phones-and-tablets#input-mask

Multimedia control (OOB) to embed and stream Audio / Video on Entity Form. (Without IFRAME)

Hi learners,

You can embed videos to provide a richer customer experience for sales and field people on the go. Use this control to map to a URL field that contains the audio or video link to play in the control.

Supported media types:

1) Streaming MP4 files
2) YouTube videos
3) Azure media
4) Audio streams

Configuration:

Open field properties on the form -> Go to Controls -> Click “Add Control” -> Select “Multimedia Control”.

Select the client. Please note that it works only with UCI.


2) Using the Control:

Just enter the Link to the multimedia file in the field and tab out. Media stream appears below the field automatically.

References:

1) https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/additional-controls-for-dynamics-365-for-phones-and-tablets#multimedia


Custom Lookup Control with multi select and with multiple entities

Hi Learners,

Here is the supported / documented way of creating a custom Lookup input control using Client Side Scripting (JavaScript). We can use this to capture multiple records across multiple entities (Just like Party List control in Email Activities). It can be called from form events / HTML web resource to prompt input from users using Lookup control.

JavaScript Sample Code:



When invoked, this function prompts a lookup control as a model dialog (aligned to the right, like Quick Create Form)

Search will be performed across the entities specified in entityTypes property (Accounts, Leads and Contacts in this Sample)

We can select multiple records from the results

When we click on “Add“, the selected entity reference objects are passed to the Success Call back function as below. We can use this information further in our JavaScript Code based on the requirement.


lookupOptions object defines the look and behavior of the lookup control. It has got all the properties that an OOB lookup control on the form would have, like view selection control, disabling most recently used items etc.

Below is the list of properties available. More information available HERE

Property NameTypeRequiredDescription
allowMultiSelectBooleanNoIndicates whether the lookup allows more than one item to be selected.
defaultEntityTypeStringNoThe default entity type to use.
defaultViewIdStringNoThe default view to use.
disableMruBooleanNoDecides whether to display the most recently used(MRU) item.
entityTypesArrayYesThe entity types to display.
filtersArray of objectsNoUsed to filter the results. Each object in the array contains the following attributes:
searchTextStringNoIndicates the default search term for the lookup control.
showBarcodeScannerBooleanNoIndicates whether the lookup control should show the barcode scanner in mobile clients.
viewIdsArrayNoThe views to be available in the view picker. Only system views are supported.

References:

1) https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/xrm-utility/lookupobjects

Use SQL Connection to Query CDS / Dynamics 365 Data (Works with SSMS and Power BI)

Hello Learners, here is the key take-away from Microsoft Business Applications Summit (MBAS)-2020.

Power Platform provides Tabular Data Stream (TDS) endpoint for CDS, using which we can access environment’s data using SSMS just like how we access the data in on-premise environment (*Only Read is permitted, cannot perform Updates)

**Image Source – Microsoft Docs


Below are the key points to remember.

  1. Its in Preview and not available for all regions. It works only with version 9.1.0.17437 and above. Check if the latest version is available for your region here. Alternatively you can check the version of your Environment from Settings –>About.

2. To use the SQL connection, you need to enable the Tabular Data Stream (TDS) endpoint using below options

a) Using OrgDBOrgSettingsTool. Step-By-Step guidance for the same is available here.
b) Go to https://admin.powerplatform.microsoft.com -> Environments ->(select environment)-> Settings -> Features , and turn Enable TDS EndPoint on.



3. SQL Connection’s Server Name would be <organization url>,5558 (5558 is the port used for TDS endpoint. it is same for all organizations). For authentication, we can use the same Active Directory Credentials that we use to access the Organization. Application Users with Azure Client ID are not supported for now. Step-By-Step guidance is available here

4. The same SQL Connection Can be used in Power BI to analyse data. Step-By-Step guidance HERE

References for more details:

1) https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query
2) https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/view-entity-data-power-bi
3) https://mymbas.microsoft.com/sessions/5b645f84-8689-4545-9b8a-af94fa2fad7c