Featured

Recent Posts


Something went wrong. Please refresh the page and/or try again.

Advertisements

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 MethodTime Zone UsedResult
Advanced Find (Auckland User)Auckland (NZDT)5,755
SQL4CDSUTC Mode3,027
SQL4CDSLocal 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 UserStored UTC Value
01-Jan-202631-Dec-2025 11:00 UTC
02-Jan-202601-Jan-2026 11:00 UTC
03-Jan-202602-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 BoundaryIST Boundary
31-Dec-2025 11:00 UTC31-Dec-2025 16:30 IST
02-Jan-2026 11:00 UTC02-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 MethodQuery BoundaryResult
Advanced Find (Auckland User)User Time Zone5,755
SQL4CDS UTC Mode31-Dec-2025 11:00 UTC → 02-Jan-2026 11:00 UTC5,755
SQL4CDS Local Mode (India)31-Dec-2025 16:30 IST → 02-Jan-2026 16:30 IST5,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:

  1. Identify the time zone of the user who ran Advanced Find.
  2. Convert the date boundaries to UTC.
  3. Run SQL4CDS in UTC mode.
  4. 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

How We Successfully Removed the Resco Woodford Managed Solution from Dynamics 365 Field Service


Recently, we needed to remove the legacy Resco MobileCRM (Woodford) managed solution from a Dynamics 365 Field Service environment.

Although the uninstall initially appeared straightforward, when we attempted to uninstall the Woodford solution, Dynamics 365 displayed a list of dependencies that needed to be addressed before.

The dependencies included references from model-driven apps, site maps, security roles, workflows, plug-in steps and managed solutions.

One of the first dependencies we identified was a set of Resco tables that were still included in a model-driven application. These included Mobile Project, Mobile Audit, Questionnaire, and Mobile Report. Removing these tables from the application eliminated the app-related dependencies.

The next set of dependencies came from site maps. The Mobile CRM navigation area was still present in multiple site maps and needed to be removed. Once the references were removed and the changes published, the related site map dependencies disappeared.

The dependency report also showed a dependency on the Resco MobileCRM Administrator security role. The role was being referenced from a form, and removing that reference resolved the dependency.

Another dependency involved the following plug-in step:

MobileCrm.Server.Plugins.Tracking.DisassociateTracking

Deleting the plug-in step removed the dependency.

We also found a legacy workflow named:

Push Notification - Schedule Change

After stopping and deleting the workflow, the dependency was removed.

At this point, most of the dependencies had been addressed, but the uninstall was still blocked by process and flow dependencies associated with Resco entities.

After further investigation, we found that these dependencies were originating from legacy geofencing functionality. Uninstalling the Geofence Alerts managed solution removed the remaining process and flow dependencies.

At this point, the dependency report showed no remaining blockers.

Despite the dependency report being clean, the uninstall continued to fail with the following error:

The uninstall operation will delete the base layer for the component ‘SdkMessageProcessingStep’. The operation cannot continue because there are other managed layers over the base layer.

The error referenced a plug-in step associated with a Resco entity. Since the dependency report was now clean, we investigated the component using the Solution Layers feature.

The layer information showed that Woodford was providing the base layer, while another managed solution named msdyn_FSMNotifications had installed a managed layer on top of that component.

This explained why Dataverse would not allow the Woodford base layer to be removed.

After uninstalling the dependent managed solution, the layer dependency was removed.

We then re-tried the Woodford uninstall, and this time it completed successfully.

One interesting observation was that the uninstall itself took approximately two hours to complete after it was started.

The key takeaway from this exercise is that a clean dependency report does not always mean a managed solution can be removed. If the uninstall fails with a managed layer error, reviewing the Solution Layers for the component referenced in the error message can quickly identify the actual blocking solution.

Hope it helps..

Advertisements

Unable to Delete Work Order Due to “The Time To Promised Must Be Later Than The Time From Promised” exception – Dynamics 365 Field Service


While attempting to delete a historical Dynamics 365 Field Service Work Order, we encountered the following error:

Exception Message: The time to promised must be later than the time from promised.

ErrorCode: -2147220891

HexErrorCode: 0x80040265

Error Details: {“errorCode”:2147746405,”message”:”The time to promised must be later than the time from promised.”,”code”:2147746405,”raw”:”{\”_errorCode\”:2147746405,\”_errorFault\”:{\”_responseXml\”:null,\”_errorCode\”:2147746405,\”_innerFault\”:{\”_responseXml\”:null,\”_errorCode\”:0,\”_innerFault\”:null,\”_callStack\”:null,\”_responseText\”:null,\”_annotations\”:null,\”_hasCustomerInfo\”:false,\”_messages\”:[\”The time to promised must be later than the time from promised.\”]},\”_callStack\”:null,\”_responseText\”:\”{\\\”error\\\”:{\\\”code\\\”:\\\”0x80040265\\\”,\\\”message\\\”:\\\”The time to promised must be later than the time from promised.\\\”,\\\”@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionSourceKey\\\”:\\\”Plugin/Microsoft.Dynamics.FieldService.FieldServicePlugin\\\”,\\\”@Microsoft.PowerApps.CDS.ErrorDetails.ApiStepKey\\\”:\\\”919f17c2-2931-4b27-b6b2-daaf91aaaaf8\\\”,\\\”@Microsoft.PowerApps.CDS.ErrorDetails.ApiDepthKey\\\”:\\\”1\\\”,\\\”@Microsoft.PowerApps.CDS.ErrorDetails.ApiActivityIdKey\\\”:\\\”1642f2af-356e-45c3-b971-42b11e9e91d9\\\”,\\\”@Microsoft.PowerApps.CDS.ErrorDetails.ApiPluginSolutionNameKey\\\”:\\\”

At first, the error suggested that the Work Order contained invalid promise dates. We reviewed the values stored on the record and found that Time To Promised was already later than Time From Promised.

Since the values appeared valid, we attempted to clear both fields using SQL 4 CDS:

The update completed successfully and both fields were set to NULL.

However, deleting the Work Order still resulted in the same error.

Changing the Work Order Status to Cancelled also didn’t help.

After further testing, we changed the Record Status from Active to Inactive. Once the record was inactive, the Work Order could be deleted successfully.

Based on the plugin trace, deleting the Work Order triggered an internal update before the delete operation was executed. It appears that when the Work Order was Active, additional Field Service validations were performed, resulting in the promised date error even after the fields were cleared.

After changing the record to Inactive, the delete operation likely followed a different validation path, allowing the Work Order to be deleted successfully.

Hope it helps..

Advertisements

Open a Power Automate Flow for Edit Without Fixing Broken Connections First


While reviewing Power Automate flows recently, we ran into an issue where we could not open a flow in edit mode

When opening the flow, Power Automate displayed the “This flow will connect to” screen and requested that we fix one or more connections before proceeding.

In our case, the flow contained an Office 365 Outlook connection reference that required attention. The problem was that we did not have access to create or repair that connection. Because of this, the Continue button remained disabled and we were unable to open the flow designer to understand what the flow actually did.

At first glance, it appeared that fixing the connection was the only option. However, in our scenario, we were simply analyzing existing flows to understand their purpose and determine whether they were still required.Repairing the Outlook connection would have required additional investigation, coordination with the appropriate users, and potentially creating or reassigning connection references. Since our immediate goal was only to review the flow logic, spending time resolving the connection issue first did not make sense.

Fortunately, we found another approach that allowed us to access the flow designer without fixing the connection immediately.

Instead of trying to fix the connection, open the flow’s Details page. From the details page, locate the Connections section and select Edit.

Next, add your user account as a Co-owner of the flow.

Once we added our user account as a co-owner, we refreshed the page and tried opening the flow again.This time, we were able to open the flow designer and review the flow logic even though the Outlook connection issue was not resolved.

Seems that adding our account as a co-owner provides the permissions required to access and inspect the flow. This simple workaround can save considerable time when reviewing large numbers of flows across an environment.

Hope it helps..

Advertisements

Dataverse Web API: Returning Record Data During Create Using Prefer: return=representation


When creating records using the Dataverse Web API, the default behavior is to return a successful response without including the record data in the response body.

A standard create request does provide access to the newly created record’s GUID through the OData-EntityId response header. However, if we need additional column values from the newly created record, we would typically need to perform a separate retrieve request.

Dataverse provides a convenient way to return record data as part of the create operation itself by using the Prefer: return=representation request header.

Let’s see how it works.

Creating a Record and Returning Data

Suppose we want to create a Contact record and immediately retrieve some of its values.

POST https://.crm.dynamics.com/api/data/v9.2/contacts?$select=contactid,fullname,emailaddress1

Content-Type: application/json

Prefer: return=representation

Request Body:

{
    "firstname": "Nishant",
    "lastname": "Rana",
    "emailaddress1": "nishant@example.com"
}

The key part of the request is the following header:

Prefer: return=representation

This tells Dataverse to return the created record in the response.

Using $select to Limit Returned Columns

In the example above, the request URL includes a $select clause:

?$select=contactid,fullname,emailaddress1

This allows us to control which columns are returned in the response.

Returning only the required columns helps reduce the response size and makes the response easier to process.

Response

When the request succeeds, Dataverse returns an HTTP 201 Created response along with the requested column values.

Example response:

{
    "contactid": "4e7d5c6a-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
    "fullname": "Nishant Rana",
    "emailaddress1": "nishant@example.com"
}

Because the record data is already available in the response, there is no need to perform an additional retrieve request to obtain these values.

Standard Create vs Return Representation

Let’s compare the behavior with a standard create request.

A normal create request returns an HTTP 204 No Content response.

HTTP/1.1 204 No Content

The response also includes an OData-EntityId header containing the URI of the newly created record.

OData-EntityId: https://.crm.dynamics.com/api/data/v9.2/contacts(4e7d5c6a-xxxx-xxxx-xxxx-xxxxxxxxxxxx)

This means we can still obtain the GUID of the created record without using Prefer: return=representation.

However, if we need additional values such as calculated fields, default values applied by Dataverse, or other column data, a separate retrieve request would typically be required.

Feature


Basic Create


Create with Data Returned


Status Code


204


201


Response Body


No


Yes


Extra Retrieve Needed


Usually


No


Performance


Faster


Slightly More Expensive


Best For


Bulk Operations


Integrations & UI Scenarios


When Should We Use It?

Using Prefer: return=representation can be useful when:

  • An integration needs values from the newly created record immediately.
  • We want to retrieve calculated or system-generated values.
  • A client application needs to display record information immediately after creation.
  • We want to avoid an additional retrieve request and simplify the integration logic.

If only the GUID of the newly created record is required, the standard create operation is usually sufficient.

Get more details – Create with data returned

Hope it helps..

Advertisements

Testing the New RunJobForSandbox Option in Bulk Delete Jobs (Preview) – Dataverse / Dynamics 365


Microsoft recently introduced a preview feature for Dataverse Bulk Delete Jobs that provides additional control over bulk delete processing. One of the new options available when creating a bulk delete job through the API is RunJobForSandbox.

According to the documentation, this option is intended to control sandbox processing during bulk delete operations, which could be particularly useful in environments where delete plugins or custom workflows impact large-scale data cleanup activities.

For our testing, we created a Bulk Delete Job using Postman and included the following option in the request payload:

{
  "QuerySet": [
    {
      "EntityName": "contact",
      "Criteria": {
        "FilterOperator": "And",
        "Conditions": [
                   {
            "AttributeName": "createdon",
            "Operator": "OnOrBefore",
            "Values": [
              {
                "Value": "2026-09-07T23:59:59Z",
                "Type": "System.DateTime"
              }              
            ]
          }
        ]
      }
    }
  ],
  "JobName": "Sample Bulk Delete Job with Run Job For Sandbox True",
  "SendEmailNotification": false,
  "RecurrencePattern": "",
  "StartDateTime": "2026-05-18T00:00:00Z",
  "ToRecipients": [],
  "CCRecipients": [],
  "Options": {
    "CanRecoverDeletedRecords": false,
    "RunJobForSandbox": true
  }
}

The complete job targeted Contact records based on their Created On date and was created successfully.

To understand how this option behaves, we registered a simple plugin on the Delete message of the Contact table. The plugin was intentionally designed to throw an InvalidPluginExecutionException whenever a record deletion was attempted.

Our expectation was that enabling RunJobForSandbox would prevent the sandbox plugin from executing during the bulk delete process, allowing the records to be deleted successfully.

However, the results were different from what we anticipated.

When the bulk delete job was executed, the delete plugin was still triggered. Because the plugin threw an exception, all targeted records failed to delete. The Bulk Delete Job completed with failures and reported errors indicating that the deletion operation had been aborted by a plugin or custom workflow.

Since this capability is currently in Preview, it is possible that the feature is still evolving, has limitations that are not yet documented, or requires additional configuration. To better understand the observed behavior, we have raised a Microsoft Support ticket and are awaiting clarification from the team.

Even though our initial test did not produce the expected result, this is still a very promising feature. Once fully implemented and generally available, the ability to control sandbox processing during bulk delete operations could make large-scale data cleanup significantly easier, especially in environments where plugins and custom workflows frequently interfere with bulk deletion activities.

We’d update this post once we receive additional information from Microsoft regarding the current behavior and intended functionality of RunJobForSandbox.

The feature is documented here:

Control Bulk Delete Processing (Preview)

Hope it helps..

Advertisements

Nishant Rana's Weblog

Everything related to Microsoft .NET Technology

Skip to content ↓