CRM – Nishant Rana's Weblog

Why SQL4CDS Record Counts May Not Match Advanced Find for Date Filters (Dataverse / Dynamics 365)

While validating some Dynamics 365 Field Service data recently, we came across an interesting scenario where SQL4CDS and Advanced Find returned different record counts even though the date filters appeared to be identical.

At first glance it was surprising to see different record counts being returned despite using what appeared to be the same date range. After investigating further, we found that the difference was related to time zone handling and the behavior of User Local date fields.

In this post, we’ll walk through the issue, explain why it happens, and show how to get matching results between Advanced Find and SQL4CDS.

The Scenario

We had a user in Auckland, New Zealand running the following Advanced Find query against Work Orders.

Date Window Start

On or After 01/01/2026
On or Before 02/01/2026

Advanced Find returned:

5,755 records

The generated FetchXML looked like this:

To validate the result, we ran the following query in SQL4CDS:

SELECT COUNT(*)
FROM msdyn_workorder
WHERE msdyn_datewindowstart >= ‘2026-01-01 00:00:00’
AND msdyn_datewindowstart <= ‘2026-01-02 00:00:00’;

The results were unexpected.

Query Method	Time Zone Used	Result
Advanced Find (Auckland User)	Auckland (NZDT)	5,755
SQL4CDS	UTC Mode	3,027
SQL4CDS	Local Mode (India)	3,026

At this point, it was clear that Advanced Find and SQL4CDS were evaluating different date boundaries, even though the filters appeared very similar. The next step was to understand why.

Understanding the Date Window Start Field

The key detail was the configuration of the Date Window Start field.

The Date Window Start field is configured as a Date Only field with User Local behavior.

Although users only see a date value, Dataverse stores an underlying UTC datetime value and performs time zone conversion based on the user’s personal settings.

To better understand what was happening, we queried some of the underlying values directly.

SELECT msdyn_workorderid,
msdyn_datewindowstart
FROM msdyn_workorder
WHERE msdyn_datewindowstart >= ‘2026-01-01 00:00:00’
AND msdyn_datewindowstart <= ‘2026-01-02 00:00:00’;

When running SQL4CDS in UTC mode, many records had values such as:

2026-01-01 11:00:00

This initially looked unusual because users only see a date value in the application.

However, the explanation becomes clear when we consider the Auckland user’s time zone.

In January, Auckland operates on New Zealand Daylight Time (NZDT), which is UTC+13.

For a User Local Date Only field, Dataverse converts the user’s local date into UTC before storing it.

Date Seen by Auckland User	Stored UTC Value
01-Jan-2026	31-Dec-2025 11:00 UTC
02-Jan-2026	01-Jan-2026 11:00 UTC
03-Jan-2026	02-Jan-2026 11:00 UTC

This explains why so many records appear with a value of 11:00 UTC when viewed in SQL4CDS running in UTC mode.

Why Advanced Find Returned More Records

When the Auckland user enters:

01/01/2026
to
02/01/2026

Advanced Find interprets those dates using the user’s personal time zone.

The actual UTC boundaries become:

>= 2025-12-31 11:00:00 UTC
< 2026-01-02 11:00:00 UTC

This represents two complete calendar days for the Auckland user.

Our original SQL4CDS query was searching a different range entirely:

>= 2026-01-01 00:00:00 UTC
<= 2026-01-02 00:00:00 UTC

Although the dates appear similar, the actual UTC boundaries are very different.

Finding the Correct SQL4CDS Query in UTC Mode

To reproduce the Advanced Find results, we converted the Auckland user’s date range into UTC and updated the SQL4CDS query accordingly.

SELECT COUNT(*)
FROM msdyn_workorder
WHERE msdyn_datewindowstart >= ‘2025-12-31 11:00:00’
AND msdyn_datewindowstart < ‘2026-01-02 11:00:00’;

This returned:

5,755 records

which matched Advanced Find exactly.

What If SQL4CDS Is Running in Local Mode?

The example above used SQL4CDS running in UTC mode. However, SQL4CDS can also be configured to use Local Time mode.

In our scenario, SQL4CDS was running on a machine configured for India Standard Time (IST), which is UTC+5:30.

To match the Advanced Find results in Local Mode, we need to convert the Auckland UTC boundaries into the local time zone used by SQL4CDS.

Earlier we determined that the Auckland user’s date range:

01-Jan-2026 to 02-Jan-2026

corresponds to the following UTC boundaries:

31-Dec-2025 11:00 UTC
to
02-Jan-2026 11:00 UTC

When SQL4CDS is running in Local Mode on an India machine, those UTC values need to be converted to IST.

UTC Boundary	IST Boundary
31-Dec-2025 11:00 UTC	31-Dec-2025 16:30 IST
02-Jan-2026 11:00 UTC	02-Jan-2026 16:30 IST

The equivalent SQL4CDS query becomes:

SELECT COUNT(*)
FROM msdyn_workorder
WHERE msdyn_datewindowstart >= ‘2025-12-31 16:30:00’
AND msdyn_datewindowstart < ‘2026-01-02 16:30:00’;

This query also returned:

5,755 records

matching Advanced Find exactly.

The results can now be summarized as follows:

Validation Method	Query Boundary	Result
Advanced Find (Auckland User)	User Time Zone	5,755
SQL4CDS UTC Mode	31-Dec-2025 11:00 UTC → 02-Jan-2026 11:00 UTC	5,755
SQL4CDS Local Mode (India)	31-Dec-2025 16:30 IST → 02-Jan-2026 16:30 IST	5,755

References

For a deeper understanding of how SQL4CDS handles date and time values, I highly recommend Mark Carrington’s article:

https://markcarrington.dev/2021/05/21/date-time-handling-in-sql-4-cds

This article explains how SQL4CDS interprets date and time values in both UTC and Local Time modes and was a useful reference while investigating this scenario.

Key Takeaways

The investigation highlighted that there may be three different time zones involved when validating results:

The Dataverse user’s personal time zone used by Advanced Find.
The SQL4CDS Local Time setting.
UTC when SQL4CDS is configured to use UTC mode.

Even when the same date values are entered, the actual UTC range being queried may be different.

For the most reliable comparison:

Identify the time zone of the user who ran Advanced Find.
Convert the date boundaries to UTC.
Run SQL4CDS in UTC mode.
Use explicit UTC values in your query.

We also recommend using an exclusive upper boundary:

WHERE Field >= StartBoundaryUTC
AND Field < EndBoundaryUTC

instead of:

WHERE Field <= EndOfDay

This avoids potential issues with milliseconds and provides more predictable results.

SQL4CDS can match Advanced Find in either UTC Mode or Local Mode. The important requirement is that the date boundaries represent the same moment in time. We generally prefer UTC Mode because the query behaves consistently regardless of the machine or user executing it.

Hope it helps..

Advertisements

Multiple Active Business Process Flow Instances for a record in Dynamics 365 / Dataverse

We recently worked on a requirement where we had to sync Business Process Flow (BPF) data between two different Dataverse environments for the Case (Incident) table. At first glance, the requirement looked straightforward — pick the active BPF instance and replicate it. However, while analyzing the source environment, we encountered an interesting and unexpected scenario.

For certain Case records, we found that there were multiple active Business Process Flow instances (of the same BPF) present for the same record.

Below is an example where we observed two active BPF instances for the same Case record:

Below is our Case record with Research as the active stage.

As we know, Dataverse is designed to maintain only one active BPF instance per record. So naturally, this raised a question — which one should we consider during synchronization?

On further analysis, we observed a consistent pattern. One BPF instance was typically created at the time when the Case record itself was created. The second instance — usually the one with the most recent Modified On value — corresponded to the latest process applied.

Based on this observation, we decided to use the following approach during synchronization:

For records with multiple active BPF instances, we pick the instance with the most recent Modified On value and ignore the older ones. This ensures that we are syncing the most relevant and currently active business process state.

Naturally, we wanted to understand how such a scenario could even exist, so we tried to replicate it. We attempted to create another BPF instance programmatically using the SDK. The code executed successfully and even returned a GUID; however, interestingly, the GUID was always the same as the already existing active instance.

This behavior clearly indicates that the platform prevents creating duplicate active instances through standard SDK operations.

After further experimentation, we discovered that the only way we were able to create multiple active BPF instances was by directly updating the Incident lookup (incidentid) on an existing BPF instance record. By reassigning the BPF instance from one Case record to another, we effectively bypassed the normal BPF lifecycle validations. This resulted in multiple active BPF instances being associated with the same Case record.

During data migration or synchronization scenarios, it is important to handle such anomalies carefully. In our case, choosing the BPF instance with the latest Modified On value ensured that we always picked the most relevant process state.

Hope it helps..

Advertisements

Using addNotification to Simulate Dynamic Tooltips (Dataverse / Dynamics 365)

When working with forms in Dynamics 365 / Power Apps model-driven apps, we often customize field labels based on context, using the setLabel method. At times, we would also like to change the tool tip to go with the changed label of the field. The tooltip is defined as a Description of the field.

Below is the Topic (subject) field of the lead.

A screenshot of a computer

AI-generated content may be incorrect.

However, we cannot set the tool tip (description) of the field dynamically in the form using the Client API. So, what do we do when the meaning of a field changes depending on another value on the form? That’s where addNotification comes in as a handy workaround.

Let us take a simple example to see how we can use it. On the Lead form, the Topic(subject) field means different things depending on the Lead Source. So here we will be changing the label of the Topic (subject) field, along with setting a different notification message.

For e.g., if Lead Source – Advertisement, we are changing the label to Campaign Name. We can also notice the bulb icon next to the field.

Clicking on it, we can see our message –

A screenshot of a web page

AI-generated content may be incorrect.

Similarly, on changing the Lead Source to Web, we are changing the label to Landing Page, and clicking on the icon, we can see a different message.

Sample Code –

function updateSubjectField(executionContext) {
	
    var formContext = executionContext.getFormContext();
    var leadSourceAttr = formContext.getAttribute("leadsourcecode");
    var subjectControl = formContext.getControl("subject");
   
    subjectControl.clearNotification("subjectTooltip");

    var leadSource = leadSourceAttr ? leadSourceAttr.getValue() : null;

    if (leadSource === 1) { 
        // 1 = Advertisement
        subjectControl.setLabel("Campaign Name");
        subjectControl.addNotification({
            messages: ["Enter the name of the ad campaign"],
            notificationLevel: "RECOMMENDATION",
            uniqueId: "subjectTooltip"
        });
    }
    else if (leadSource === 2) { 
        // 2 = Referral
        subjectControl.setLabel("Referrer Notes");
        subjectControl.addNotification({
            messages: ["Mention details about the referrer"],
            notificationLevel: "RECOMMENDATION",
            uniqueId: "subjectTooltip"
        });
    }
    else if (leadSource === 8) { 
        // 3 = Web
        subjectControl.setLabel("Landing Page");
        subjectControl.addNotification({
            messages: ["Provide the landing page URL"],
            notificationLevel: "RECOMMENDATION",
            uniqueId: "subjectTooltip"
        });
    }
    else {
        // Default
        subjectControl.setLabel("Subject");
    }
}

While addNotification isn’t a perfect replacement for a native tooltip, it’s a practical workaround when we need dynamic, context-aware user guidance.

Hope it helps..

Advertisements

Using gridContext.refreshRibbon() to Dynamically Show/Hide a Subgrid Ribbon Button – Dynamics 365 / Dataverse

In Dynamics 365 / Dataverse, sometimes we want to show or hide a ribbon button based on a form field value. But when the button is on a subgrid, it does not refresh automatically when a field changes on the form. We can handle this requirement using gridContext.refreshRibbon(). It is a small but very useful method that helps to refresh the subgrid ribbon without saving or reloading the form.

Here we are taking a simple scenario to understand the usage.

We will only show the New Case button on the Case Subgrid if the Preferred Method of Contact = Any else we will hide it.

Below is our JavaScript function to check the field value and return true or false. This function will be used as CustomRule for our Add New button command’s EnableRule on the subgrid. This function checks if the Preferred Method of Contact is ‘Any’. If yes, it returns true. Otherwise, it returns false.

A screen shot of a computer code

AI-generated content may be incorrect.

We are passing CRM Parameter = PrimaryControl here.

Depending on where the button lives (Form ribbon or Subgrid ribbon), the correct context is passed.

On a form: PrimaryControl is the formContext.
On a Subgrid: PrimaryControl gives the context of the parent form hosting the subgrid.

Below we have customized the Add New Subgrid button for Case and added a new Enable Rule for its command.

Now on the form load, the Add New button on the subgrid will be hidden on the form load event.

But when we change the value for the Preferred Method of Contact we will not see any effect on the Add New button. For it to work we need to use the refershRibbon method of the grid’s context as shown below.

We added it on the onChange event for the Preferred Method of Contact field so that when a user changes it, the subgrid ribbon refreshes.

Now, when a user changes the Preferred Method of Contact, the subgrid ribbon will refresh and check again if the button should be visible.

As a result, now the Add New button appears on the Case subgrid when the Preferred Method of Contact is ‘Any’.

The ribbon refreshes immediately when the field changes to Email or any other value except Any.No need to save or reload the form.

JavaScript –

function showAddNewButtonOnCaseSubgrid(primaryControl) {
    var formContext = primaryControl;
    var preferredMethod = formContext.getAttribute("preferredcontactmethodcode");    
    var preferredMethodValue = preferredMethod.getValue();
    // Check if Preferred Method is 'Any' 
    if (preferredMethodValue === 1) {
        return true;
    }
    else {
        return false;
    }
}
function refreshCaseSubgridRibbon(executionContext) {
    var formContext = executionContext.getFormContext();
    var gridContext = formContext.getControl("Subgrid_Cases");  
    if (gridContext) {
        gridContext.refreshRibbon();  
    }
}

The helpful post – https://butenko.pro/2019/04/16/refreshing-the-ribbon-form-vs-grid/

Hope it helps..

Advertisements

Using External Value property of Choice / Option Set Field for Integration – Dynamics 365 / Dataverse

When working with Choice fields (Option Sets) in Dataverse, we mostly use the label and internal numeric value. But there’s also a lesser-known property — External Value — which can be quite handy, especially in integration scenarios.

Below is the Priority column of the case table.

We have Severe, Moderate, and Minor external values specified for High, Normal, and Low choices respectively. These external values could be how the external third-party system is tracking the priority values to which we are integrating.

The external system sends us “priority”:” high” and we map that to the internal value 1 in Dataverse.

While sending data to the external system we convert the internal value to the external code i.e. 1 to. High

Below is how we can read the external values.

     var myServiceClient = new CrmServiceClient(connectionString);     

        if (myServiceClient.IsReady)
        {
            var request = new RetrieveAttributeRequest
            {
                EntityLogicalName = "incident",
                LogicalName = "prioritycode",
                RetrieveAsIfPublished = true
            };

            var response = (RetrieveAttributeResponse)myServiceClient.Execute(request);
            var picklistMetadata = (PicklistAttributeMetadata)response.AttributeMetadata;

            foreach (var option in picklistMetadata.OptionSet.Options)
            {
                Console.WriteLine($"Value: {option.Value}, Label: {option.Label?.UserLocalizedLabel?.Label}, External: {option.ExternalValue}");
            }

            string ext = "Severe";
            var match = picklistMetadata.OptionSet.Options.FirstOrDefault(o => o.ExternalValue == ext);
            if (match != null)
            {
                Console.WriteLine($"\nExternal '{ext}' maps to Value {match.Value}");
            }

        }

Using External values, we can decouple integrations from the internal values that we use for our choice column. By using them, we can speak the language of the external system while still maintaining proper structure and metadata within Dataverse.

Hope it helps..

Advertisements

Querying / Filtering MultiSelect Choice / OptionSet Fields in Dataverse / Dynamics 365

MultiSelect OptionSet (Choices) fields in Dataverse provide a flexible way to store multiple values within a single field. However, querying and filtering these fields require different techniques depending on the approach used.

In this blog post, we will explore various ways to filter records based on the Skills field (cr1a7_skills), which has the following values:

Name	Value
C#	255780000
Java	255780001
Python	255780002

We have the Skills (choices) field in our Contact table.

The query is to fetch all the contact records where skills includes C# or Java.

Filtering Using FetchXML

FetchXML allows filtering MultiSelect Option Set fields using the contain-values operator.

<fetch>
  <entity name="contact">
    <attribute name="fullname" />    
    <filter>
      <condition attribute="cr1a7_skills" operator="contain-values">
        <value>255780000</value>
        <value>255780001</value>
      </condition>
    </filter>
  </entity>
</fetch>

Filtering Using QueryExpression (C#)

Using QueryExpression, we can apply the ContainValues condition to filter Multi

var query = new QueryExpression("contact");
query.ColumnSet.AddColumns("fullname");
query.Criteria.AddCondition("cr1a7_skills", ConditionOperator.ContainValues, 255780000, 255780001);

Filtering Using OData (Web API)

OData allows filtering MultiSelect Option Set fields using the ContainValues function.

https://orgname.crm.dynamics.com/api/data/v9.2/contacts?$select=fullname,createdon,modifiedon,statecode&$filter=(Microsoft.Dynamics.CRM.ContainValues(PropertyName='cr1a7_skills',PropertyValues=['255780000','255780001']))

Filtering Using SQL4CDS (XrmToolBox)

If you’re using the SQL4CDS tool in XrmToolBox, you can filter MultiSelect Option Set fields using LIKE conditions.

SELECT contactid,
       fullname
FROM   contact
WHERE  statecode = 0
       AND (cr1a7_skills = '255780000'
            OR cr1a7_skills LIKE '255780000,%'
            OR cr1a7_skills LIKE '%,255780000,%'
            OR cr1a7_skills LIKE '%,255780000'
            OR cr1a7_skills = '255780001'
            OR cr1a7_skills LIKE '255780001,%'
            OR cr1a7_skills LIKE '%,255780001,%'
            OR cr1a7_skills LIKE '%,255780001'            
            );

Filtering Using LINQ (C#)

For LINQ queries, MultiSelect Option Sets must be processed as OptionSetValueCollection.


        if (myServiceClient.IsReady)
        {
            using (var context = new OrganizationServiceContext(myServiceClient))
            {

                var skillValues = new List<int> { 255780000, 255780001 };              
                              
                var allContacts = context.CreateQuery("contact")
                .Where(c => c["cr1a7_skills"] != null &&
                            (int)c["statecode"] == 0)
                .ToList();                
              
                var filteredContacts = allContacts
                    .Where(c => ((OptionSetValueCollection)c["cr1a7_skills"])
                                .Select(osv => osv.Value)
                                .Any(skill => skillValues.Contains(skill)))
                    .Select(c => new
                    {
                        ContactId = c["contactid"],
                        FullName = c.Contains("fullname") ? c["fullname"].ToString() : string.Empty
                    })
                    .ToList();

                var result = filteredContacts;

            }
        }

Get more detailed information

Hope it helps..