PowerApps – 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

Finding Dirty / Unsaved Fields on the Form Using JavaScript / Browser Console (Dynamics 365 / Dataverse)

Sometimes while debugging forms in Dynamics 365, we need to know which fields have been modified but not yet saved. These are called dirty fields, and they can be quickly identified by running a small JavaScript snippet directly from the browser console.

We can open the form, press F12 to bring up the developer tools, go to the Console tab, and paste the following code

(function () {
    var dirtyFields = [];
    var attributes = Xrm.Page.data.entity.attributes.get();

    attributes.forEach(function (attribute) {
        if (attribute.getIsDirty()) {
            dirtyFields.push(attribute.getName());
        }
    });

    if (dirtyFields.length) {
        alert("Dirty fields: " + dirtyFields.join(", "));
    } else {
        alert("No dirty fields found.");
    }
})();

This script loops through all the attributes on the form and checks if they are dirty using getIsDirty(). If it finds any, it shows their names in an alert, otherwise it shows a message saying no dirty fields are found.

For example, if we modify First Name and Email on the Contact form without saving, it will pop up an alert showing:

A screenshot of a computer

AI-generated content may be incorrect.

Here we are using Xrm.Page even though it is deprecated, because it is still the quickest way to test such snippets directly from the console for debugging purposes. In actual form scripts, we should always use formContext.

Hope it helps..

Advertisements

Use Security Roles to manage access to views (preview)– Dataverse / Dynamics 365

Sometimes, we might create a new view for a table, and not everyone in our organization needs to see it — or maybe, only a specific team should have access to the existing views, now we can achieve it through security roles.

This feature is governed by the EnableRoleBasedSystemViews property, which we can manage through the OrganizationSettingsEditor tool.

Download and install the managed solution.

https://github.com/seanmcne/OrgDbOrgSettings/releases

A black background with white lines
AI-generated content may be incorrect.

Before we set the property as true, let us apply security roles to some of our views.

To apply it, select the view in the PowerApps Maker Portal and click on View Settings.

From the View Settings window, we can choose Specific security roles to apply to the views. Here we have selected the Vice President of Sales role for the All Leads Views. Save and publish the change.

Here we have repeated the same steps for the below remaining views.

Now if we open the leads view as System Admin, we can see all the views. This is because we have not yet set the EnableRoleBasedSystemViews to true.

Now let us set the property as true.

As soon as we set the property as true, we can see the views getting filtered for the System Admin user.

Only the below public view is visible. (along with the 2 private views at the top)

Now if we assign the “Vice President of Sales” role to the same user, he can then see the views on which security roles were applied.

The user can still see the remaining views through the “Manage and share views” option.

The records that the user can see in the views are still governed by security privileges.

Although the documentation mentions, that this feature only filters views in the table list view selector and not in associated grids or subgrids, we can see the same filtering applied.

Below is the Lead Associated view in the Competitor table.

When we enable the EnableRoleBasedSystemViews setting using the OrganizationSettingsEditor tool, it takes effect immediately. All table views, except the default one, start getting filtered based on assigned security roles right away. Assigning security roles to a view is also effective immediately after we save and publish the view. However, if we change a view’s access from ‘Specify security roles’ to ‘Everyone’, it might take up to 24 hours for the change to fully apply across the system.

Get all the details here – Manage access to public system views (preview)

Hope it helps..

Advertisements

Unable to save. This form can’t be saved due to a custom setting error in Dynamics 365 / Dataverse.

Recently we got the below error while trying to assign the record.

Unable to save. This form can’t be saved due to a custom setting.

A screenshot of a computer error

AI-generated content may be incorrect.

Turned out we had certain conditions in the OnSave event for the form, and if the record satisfies those conditions we were canceling the save event using

executionContext.getEventArgs().preventDefault();

A screen shot of a computer program

AI-generated content may be incorrect.

So it was the expected behaviour, just that the error message was a bit generic.

Hope it helps..

Advertisements

Change Choice / OptionSet value’s text/label using JavaScript – Dataverse / Dynamics 365

In Dynamics 365, there are instances when we need to dynamically change the labels of option set fields based on specific conditions. For example, we might want to update the label of the “Priority” field option from High to Critical when a case is marked as escalated (Is Escalated = True).

Below is the sample code we can use for it. The code will be registered on the OnLoad for Form and OnChange of Is Escalated field.

	function SetLabel(executionContext)
	{
		 var formContext = executionContext.getFormContext();
		 var isEscalated = formContext.getAttribute("isescalated"); 
		 var optionSetControl = formContext.getControl("prioritycode"); // field is in header and not in form
		 var optionHigh = 1; // high
		 var newLabel = "Critical";
		 
		 if(optionSetControl && isEscalated.getValue() == true)
		 {
			var options = optionSetControl.getOptions();
            for (var i = 0; i < options.length; i++) {
                if (options[i].value === optionHigh) {                   
                    optionSetControl.removeOption(optionHigh);
                    optionSetControl.addOption({
                        text: newLabel,
                        value: optionHigh
                    });
                    break;
                }
            }
		 }		 
	}

On opening the form, we can see the Priority’s value High changed to Critical in case Is Escalated = Yes.

A screenshot of a computer

Description automatically generated

var formContext = executionContext.getFormContext(); = This retrieves the form context from the execution context, which is essential to interact with the form’s attributes and controls.

var isEscalated = formContext.getAttribute(“isescalated”) = The isescalated attribute is used to determine whether the case is escalated.

var optionSetControl = formContext.getControl(“prioritycode”);

The current options are retrieved using getOptions().
The option with the value 1 (High priority) is removed.
A new option with the updated label “Critical” and the same value is added.

However, one interesting thing to note is the Header still shows the old value High, it seems there is no supported way to change the label in case if the field is used in the Header.

Hope it helps..

Advertisements

How to use the setIsValid Method in Dataverse / Dynamics 365

We can use the setIsValid method for validating field values in Model-driven apps. This method helps ensure that the data entered by users meets the required criteria before it’s processed or saved.

The setIsValid method is used to set the validity of a column’s value. It can mark a field as valid or invalid based on custom validation logic.

formContext.getAttribute(arg).setIsValid(bool, message);

bool: A Boolean value. Set to false to mark the column value as invalid, and true to mark it as valid.

message: (Optional) A string containing the message to display when the value is invalid.

Below we are using the setIsValid method in the function that ensures that the “End Date” is earlier than or equal to the “Start Date”, else it will mark the “End Date” as invalid.

function validateDates(executionContext) {
    var formContext = executionContext.getFormContext();
    var startDate = formContext.getAttribute("custom_startdate");
    var endDate = formContext.getAttribute("custom_enddate");
  
    if (startDate && endDate && endDate.getValue() <= startDate.getValue()) {
        endDate.setIsValid(false, "End Date must be after Start Date.");
    } else {
        endDate.setIsValid(true);
    }
}

We have it registered in the On Change of the ‘Start Date’ and ‘End Date’ fields.

Here if we try saving the record, if the End Date is smaller than the Start Date, we will get the message specified.