Add Unlicensed Users to Activity Recipient Fields in Dynamics 365 (Dataverse)


In Dynamics 365 and Dataverse, users generally need a Dataverse or Power Apps license to be added to activity recipient fields.

However, there are scenarios where we only want to include someone in an activity without giving them access to the Dynamics 365 application. For example, adding an employee to an email’s To or Cc field, including someone as an appointment attendee, or associating a person with a phone call activity.

Microsoft has introduced a feature (currently in preview) that addresses this requirement: Activities for users without licenses.

With this feature enabled, administrators can allow users who have an active Microsoft Entra ID account in their tenant to be added to activity recipient fields without requiring a Dataverse or Power Apps license.

Let’s look at how it works and how to enable it.

What does this feature do?

By default, Dynamics 365 only allows users with an appropriate Dataverse or Power Apps license to be added to activity recipient fields.

After enabling this feature, users with an active Microsoft Entra ID account can be selected in activity recipient fields, even if they don’t have a Dataverse or Power Apps license.

The feature supports the following activity fields:

  • Email: To, Cc, and Bcc.
  • Appointment: Required Attendees and Optional Attendees.
  • Phone Call: Call From and Call To.

One important distinction: these users can be added as activity recipients, but they don’t get access to Dynamics 365 applications.

Prerequisites

Before enabling this feature, we need to make sure we have the following permissions:

  1. Microsoft Entra ID: A Privileged Role Administrator is required to grant users read access to the Microsoft Entra ID table.
  2. Power Platform: A Power Platform Administrator is required to enable the feature in the Power Platform admin center.

Additionally, all users in the tenant need read permission on the Microsoft Entra ID table.

We can provide this permission in either of the following ways:

  • Update the existing security roles assigned to users and grant Read permission on the Microsoft Entra ID table.
  • Create a custom security role with Read permission on the Microsoft Entra ID table and assign it to all users.

Here we can see that System Administrator role has the Organization level access to the Microsoft Entra ID table.

Enable Activities for users without licenses.

Follow these steps to enable the feature:

Log in to Power Platform Admin Center > Environment > Settings > Product > Features – Locate “Activities for users without licenses” and enable the checkbox – “Add anyone with an Entra ID to an activity’s recipients fields”

Once enabled, users with an active Microsoft Entra ID account can be selected in the supported activity recipient fields.

How does it work?

The feature uses the Microsoft Entra ID table, which is a virtual Dataverse table that displays the current list of active Microsoft Entra ID users.

When we search for and select an unlicensed user in an activity recipient field, Dataverse creates a corresponding unlicensed-user record in the environment.

This record is created with a Disabled status.

This is important because the user can now be associated with activities without being provisioned as an active Dynamics 365 application user.

The user can be included in activity recipient fields, but cannot sign in to or run Dynamics 365 applications.

E.g., we have an unlicensed user – Adele Vance – if we search for the user in the Phone Call’s Call To field, it will return “No records found.”

After enabling this feature and providing the user with Read access to the Microsoft Entra ID table (or if the user is a System Administrator, who already has that access), we can see the user listed there.

On creating the Phone call record, we can see the user record created in the Environment.

What happens when the user gets a license later?

Another interesting aspect of this feature is what happens when an unlicensed user is subsequently assigned a license.

Microsoft automatically updates the user to a licensed user.

The user can then access the application based on their assigned security roles and permissions.

More importantly, existing activities associated with that user remain linked to the same user record. There is no need to migrate or convert the user record or update the associated activities.

Conclusion

This feature provides a useful option for organizations that want to include employees in Dynamics 365 activities without assigning them a Dataverse or Power Apps license solely for that purpose.

It is particularly useful for email recipients, appointment attendees, and phone call activities where the person needs to be associated with an activity but doesn’t need access to the Dynamics 365 application.

Reference:

Add users without licenses to activity recipient fields (preview) – Microsoft Learn

Hope it helps..

Filtered View Security: Adding Filter-Based Access to Existing User/ team owned Tables (Dataverse / Dynamics 365)


In the previous post, we looked at Filtered Record Ownership, where filters become the primary mechanism for record access. In this post, we will look at a related capability: adding filter-based security to an existing User/Team-owned table.

Filtered Record Ownership: A New Approach to Row-Level Security (Dataverse / Dynamics 365)

What is Filtered View Security?

Suppose we already have a User/Team-owned Store table and want to give a Test User 1 access to additional records based on a business condition, such as City = Christchurch.

Instead of changing ownership, sharing individual records, or creating another team, we can add a Record Filter and grant access to that filter through a security role.

The important difference: the existing ownership and security model remains in place. Filter-based security adds another way to grant access.

Our Store Example

We will use a custom table called Store. Unlike the previous example, this table is created with Record ownership = User or team.

We have the following Stores record.

Currently, Test User 1 has access to only the records owned by him, as he has user-level access to the Store table.

The Requirement

Test User 1 should be able to access all stores in Christchurch, even when those records are owned by another user.

For example, Store D is owned by User B, but because its City is Christchurch, Test User 1 should receive additional Read access to it through the filter.

Before the Filter

StoreCityOwnerTest User 1
Store AChristchurchTest User 1✓
Store BWellingtonUser B✗
Store CAucklandUser B✗
Store DChristchurchUser B✗
Store EAucklandTest User 1✓

Configuration Steps

1. Create the Store table

Create the Demo Store table and select User or team for Record ownership. Save the table and add the required City column.

2. Add sample records

Create a few Store records with different City values and assign them to different users.

3. Create the Record Filter

Create a Record Filter that identifies the records that should receive additional access. Example filter: City = Christchurch. The filter can be created using the appropriate FetchXML condition. Advanced Find can also be used to build the condition and download the FetchXML.

4. Create the Entity Record Filter

Create an EntityRecordFilter and associate the Christchurch Store filter with the Store table.

5. Configure the Security Role

Create or update a security role and grant the required filter privilege. For our first test, grant Read access to the Christchurch Store filter.

6. Assign the role

Assign the filter security role to Test User 1 and test the Store table using that user’s account.

The Result

After assigning the filter role, Test User 1 keeps the access they already had through the normal ownership model and receives additional access to records where City = Christchurch.

StoreCityOwnerTest User 1 after filter
Store AChristchurchTest User 1✓ Existing access
Store BWellingtonUser B✗
Store CAucklandUser B✗
Store DChristchurchUser B✓ Filter access
Store EAucklandTest User 1✓ Existing access

This is the key point of the experiment: the filter did not replace ownership. It added access to matching records.

Filtered View Security vs a View

A normal Dataverse view controls which records are displayed. Filter-based security controls which records the user is allowed to access.

Normal ViewFiltered View Security
Controls what is displayedControls record access
Does not grant security accessCan grant additional access
Used for presentation/navigationUsed for row-level security

A Few Important Points

  • The existing User/Team ownership model remains in place.
  • Filter-based security can add access to matching records.
  • Multiple authorized filters can contribute to a user’s access.
  • Filter privileges can be configured for operations such as Read, Write, Create, and Delete.
  • Filter design can affect query performance because filter permissions are evaluated with queries.
  • The feature is currently a Preview capability.

Conclusion

Filtered View Security gives us another option for row-level access in Dataverse. Instead of changing ownership or sharing records individually, we can use a business condition to provide additional access.

In our Store example, Test User 1 continued to use the normal ownership model while gaining access to all Christchurch stores through a filter. This makes the capability particularly interesting for scenarios where access needs to follow attributes stored in the data.

As this is a Preview capability, it is best evaluated in a non-production environment before considering it for a production solution.

Microsoft Reference

Add filtered view security to record ownership tables (preview)

Hope it helps..

Advertisements

Export Bookable Resource Working Hours using ExpandCalendar (Dynamics 365 Field Service)


Recently we had a requirement to export the working hours of Bookable Resources from Dynamics 365 for a specified date range and make that information available to a third-party system.

Rather than reading the underlying calendar rules and building our own logic to calculate the working hours, we looked at using the platform capabilities provided by Dynamics 365 and used the Dataverse ExpandCalendar function

How it works

We first retrieve the Bookable Resources from Dynamics 365 and identify the calendar associated with each resource.

For each calendar, we call the Dataverse ExpandCalendar function for the required date range. Dynamics 365 expands the calendar rules and returns the resulting calendar blocks, including the start and end times along with information such as TimeCode and SubCode.

We then process these results and export the required information to a CSV file.

This approach means we do not need to recreate the calendar rules and recurrence logic ourselves. Dynamics 365 does the calendar expansion for us, and we simply consume the resulting calendar blocks.

Why use ExpandCalendar?

A Bookable Resource calendar is not simply a list of start and end times. Calendar rules can repeat over time and can include changes or exceptions. Building a separate calendar engine to interpret those rules would duplicate logic that Dynamics 365 already understands.

With ExpandCalendar, we provide the calendar and the date range, and Dynamics 365 returns the expanded calendar information for that period.

This makes the solution smaller and easier to maintain, particularly when the calendar configuration changes in Dynamics 365.

Getting the Bookable Resources

The first step is to retrieve the Bookable Resources that we want to process. The important fields for this example are the resource ID, resource name, and calendar ID.

private static List<Entity> GetAllBookableResources(
    IOrganizationService service)
{
    QueryExpression query = new QueryExpression
    {
        EntityName = "bookableresource",
        ColumnSet = new ColumnSet(
            "bookableresourceid",
            "name",
            "calendarid")
    };

    return service.RetrieveMultiple(query).Entities.ToList();
}

The calendar ID is important because it is the calendar that we pass to ExpandCalendar.

Calling ExpandCalendar

The ExpandCalendar function is exposed through the Dataverse Web API. In a .NET console application using Microsoft.PowerPlatform.Dataverse.Client, we can execute the Web API request through the ServiceClient.

private static async Task<List<CalendarBlock>> ExpandCalendarAsync(
    ServiceClient serviceClient,
    Guid calendarId,
    DateTime startUtc,
    DateTime endUtc)
{
    string requestUrl =
        serviceClient.ConnectedOrgUriActual.ToString().TrimEnd('/') +
        "/api/data/v9.2/ExpandCalendar";

    var request = new HttpRequestMessage(
        HttpMethod.Post,
        requestUrl);

    request.Headers.Add(
        "OData-Version",
        "4.0");

    request.Headers.Add(
        "OData-MaxVersion",
        "4.0");

    var body = new
    {
        CalendarId = calendarId,
        Start = startUtc.ToString("yyyy-MM-ddTHH:mm:ssZ"),
        End = endUtc.ToString("yyyy-MM-ddTHH:mm:ssZ")
    };

    string json = JsonConvert.SerializeObject(body);

    request.Content = new StringContent(
        json,
        Encoding.UTF8,
        "application/json");

    HttpResponseMessage response =
        await serviceClient.ExecuteWebRequestAsync(request);

    response.EnsureSuccessStatusCode();

    string responseJson =
        await response.Content.ReadAsStringAsync();

    JObject responseObject =
        JObject.Parse(responseJson);

    List<CalendarBlock> result =
        responseObject["value"]
            .ToObject<List<CalendarBlock>>();

    return result;
}

The exact request shape can vary depending on the Dataverse API version and implementation, so the ExpandCalendar Web API documentation should be used as the authoritative reference for the request parameters.

Working with the response

The response contains the expanded calendar blocks for the requested period. A simplified response can look like this:

{
  "Start": "2026-10-07T19:00:00Z",
  "End": "2026-10-08T04:00:00Z",
  "TimeCode": "Available",
  "SubCode": "Schedulable"
}

For each block, we can capture the start and end time together with TimeCode and SubCode. The application can then transform these results into the format required by the consuming system.

Working hours vs actual availability

One important distinction is that expanded calendar information should not automatically be treated as the complete scheduling availability of a resource.

ExpandCalendar gives us the expanded calendar information for the requested period. Actual scheduling availability can also be affected by existing bookings, requirements, constraints, and other scheduling factors.

If the requirement is to determine when a resource can actually be booked, the Field Service resource availability APIs should be considered instead. For a requirement focused on exporting configured calendar or working-hour information, ExpandCalendar is a good fit.

Refereces

Hope it helps..

Advertisements

Column Visualizations for Grids / Views (Preview) –Dataverse / Dynamics 365


Power Apps now lets us render a column’s data as a small graphic in the grid – a gauge, a sparkline, a colored bar, or stars – instead of plain text. We tried it out on a couple of our own columns, and here’s a quick step-by-step on how to set it up, along with the data format each visualization expects.

This is a preview feature, so it’s best tried out in Dev/Sandbox for now rather than rolled out to production grids.

Pick a Visualization for the Column

  1. Sign in to Power Apps (make.powerapps.com) → Solutions → open the table.
  2. Open the column to edit (or create a new one).
  3. Expand Advanced options → “Visualization”.
  4. Select one of: Heat Map, Line Chart, Radial Dial, Star Rating.
  5. Save. Every grid or view showing that column now shows the graphic automatically.

That’s it – it’s a one-time setting on the column, not something you configure per view.

Know What Data Each One Expects

This is the part that actually matters – each visualization is strict about the shape of the data behind it.

Radial Dial: expects a single value, 0–100. Example: 60 → ring 60% full.

Heat Map: expects a single value, 0–100 by default. Example: 90 → bar colored red (high).

Star Rating: expects a value from 0 to the star count, 5 by default. Example: 3 → three stars filled.

Line Chart: expects comma-separated numbers, 100 characters maximum length. Example: 10,20,30,45 → a trend line.

For .e.g the data for these columns –

Below is how they are rendered in a view.

And this is as a subgrid.

Key Takeaway

Set the visualization once on the column, keep the underlying data clean (0–100 for Radial Dial/Heat Map, comma-separated numbers for Line Chart, 0 to 5 for Star Rating), and every grid using that column just works. Most issues we ran into came down to one thing – the value wasn’t stored the way the visualization expected.

Get all the details here: Column visualizations for grids in Power Apps

Hope it helps..

Advertisements

Implementing Cloudflare Turnstile for Dynamics 365 Customer Insights – Journeys Real-Time Marketing Forms


Check the previous posts for more details –

In one of our recent projects, we implemented Cloudflare Turnstile (Invisible) for a Dynamics 365 Customer Insights – Journeys Real-Time Marketing form. In this post, we’ll look at how to configure Cloudflare Turnstile, integrate it with the marketing form using JavaScript, and validate submissions on the server using the msdynmkt_validateformsubmission plugin.

Check the following post for more details –

https://www.ameyholden.com/articles/recaptcha-v3-cloudflare-turnstile-for-customer-insights-journeys-forms

Cloudflare Turnstile provides an invisible verification mechanism that evaluates the request in the background and only requires additional verification when necessary.

For marketing forms, this means:
• Better user experience
• Reduced spam and bot submissions
• Server-side verification

Solution Overview

The implementation consists of two major components.

The first component is a JavaScript that runs inside the Marketing form. It loads the Cloudflare Turnstile library, renders an invisible widget, requests a token when the user submits the form, writes that token into a hidden form field, and then allows the submission to continue.

The second component is a Dataverse plugin registered on the msdynmkt_validateformsubmission message. The plugin retrieves the Turnstile token, reads the Cloudflare secret key from a Dataverse Environment Variable, validates the token against the Cloudflare SiteVerify API, and finally tells Customer Insights whether the submission should be accepted.

Solution Flow

Visitor
   │
   ▼
Customer Insights – Journeys Form
   │
   ▼
JavaScript
   │
   ├── Loads Cloudflare Turnstile
   ├── Executes Invisible Challenge
   └── Stores Token in Hidden Field
   │
   ▼
Form Submission
   │
   ▼
msdynmkt_validateformsubmission Plugin
   │
   ├── Reads Token
   ├── Retrieves Secret Key from Environment Variable
   ├── Calls Cloudflare SiteVerify API
   └── Returns Validation Result
   │
   ▼
Lead / Contact Created

Step 1 – Create a Cloudflare Account

Create a free Cloudflare account and navigate to Application Security > Turnstile. Turnstile widgets are managed from this area.

Step 2 – Create an Invisible Turnstile Widget

Create a new widget, select Invisible mode, and configure all hostnames that will serve the Customer Insights form.

e.g. hostname –  assets-oce.mkt.dynamics.com

Cloudflare generates a Site Key and Secret Key. The Site Key is used by JavaScript while the Secret Key is kept securely on the server and used by the plugin.

Step 3 – Create a Dataverse Environment Variable

Create an environment variable to hold the Secret Key that will be used by the plugin for server-side validation of the token.

Step 4 – Configure the Marketing Form

Add an unmapped hidden Short Text field named cf_token to the form. This field temporarily stores the token generated by Cloudflare.

Enable data-validate-submission=”true” and add the JavaScript to the form between the closing form and body tag : </form> <Script>JavaScript </Script></body> (added in the next section)

JavaScript Implementation

The JavaScript is responsible for integrating the Customer Insights form with Cloudflare Turnstile.

At a high level, it performs the following tasks:

• Defines configuration such as the Site Key and token field name.
• Dynamically loads the Cloudflare Turnstile JavaScript library.
• Renders an invisible Turnstile widget inside the marketing form.
• Waits until the user clicks Submit.
• Executes Turnstile to obtain a fresh token.
• Stores the token in the hidden cf_token field.
• Resubmits the form after the token has been written.

The implementation also caches tokens for a short period and automatically refreshes expired tokens, ensuring that only valid tokens are submitted.

(function () {
   'use strict';

   // ---------------------------------------------------------------------
   // Configuration
   // ---------------------------------------------------------------------

   var TURNSTILE_SITE_KEY = 'SITEKEY';
   var TURNSTILE_SCRIPT_URL = 'https://challenges.cloudflare.com/turnstile/v0/api.js?render=explicit';

   var FORM_SELECTOR = 'form.marketingForm';
   var SUBMIT_EVENT = 'd365mkt-formsubmit';
   var AFTER_LOAD_EVENT = 'd365mkt-afterformload';

   // Refresh tokens after four minutes.
   // Cloudflare tokens expire after approximately five minutes.
   var TOKEN_STALE_MS = 4 * 60 * 1000;

   // Hidden unmapped field added through the Customer Insights – Journeys
   // form designer. The generated Turnstile token is copied into this field
   // before the form is submitted.
   var TOKEN_FIELD_NAME = 'cf_token';

   var scriptPromise = null;

   // Stores the Turnstile widget state for each form instance without
   // preventing the form from being garbage collected.
   var widgetState = new WeakMap();

   function loadTurnstileScript() {
      if (scriptPromise) return scriptPromise;

      scriptPromise = new Promise(function (resolve, reject) {

         if (window.turnstile) {
            resolve();
            return;
         }

         var script = document.createElement('script');
         script.src = TURNSTILE_SCRIPT_URL;
         script.async = true;
         script.defer = true;

         script.onload = function () {
            resolve();
         };

         script.onerror = function () {
            scriptPromise = null;
            reject(new Error('[CIJ Turnstile] Failed to load Turnstile script.'));
         };

         document.head.appendChild(script);
      });

      return scriptPromise;
   }

   function renderWidget(formEl) {

      if (widgetState.has(formEl)) {
         return widgetState.get(formEl);
      }

      // The widget container must exist inside the form so that Cloudflare's
      // internally managed hidden field is created within the form element.
      var container = document.createElement('div');
      container.style.display = 'none';
      formEl.appendChild(container);

      var entry = {
         widgetId: null,
         token: null,
         tokenAt: 0,
         resolve: null,
         reject: null
      };

      entry.widgetId = window.turnstile.render(container, {
         sitekey: TURNSTILE_SITE_KEY,

         // Execute Turnstile only when the user submits the form.
         execution: 'execute',

         // Keep the widget completely invisible.
         appearance: 'execute',

         callback: function (token) {
            entry.token = token;
            entry.tokenAt = Date.now();

            if (entry.resolve) {
               entry.resolve(token);
               entry.resolve = null;
               entry.reject = null;
            }
         },

         'error-callback': function (code) {
            console.error('[CIJ Turnstile] Widget error:', code);

            if (entry.reject) {
               entry.reject(new Error('Turnstile error: ' + code));
               entry.resolve = null;
               entry.reject = null;
            }
         },

         'expired-callback': function () {
            entry.token = null;
            entry.tokenAt = 0;
         }
      });

      widgetState.set(formEl, entry);

      return entry;
   }

   function getToken(formEl) {

      return loadTurnstileScript().then(function () {

         var entry = renderWidget(formEl);

         var isFresh =
            entry.token &&
            entry.tokenAt &&
            (Date.now() - entry.tokenAt) < TOKEN_STALE_MS;

         if (isFresh) {
            return Promise.resolve(entry.token);
         }

         return new Promise(function (resolve, reject) {

            entry.resolve = resolve;
            entry.reject = reject;

            window.turnstile.reset(entry.widgetId);
            window.turnstile.execute(entry.widgetId);
         });
      });
   }

   function getFormFromEvent(evt) {

      var target = evt && evt.target;

      if (!target) {
         return null;
      }

      if (target.tagName === 'FORM') {
         return target;
      }

      if (target.querySelector) {
         var nested = target.querySelector('form');

         if (nested) {
            return nested;
         }
      }

      return target.closest ? target.closest('form') : null;
   }

   function setTokenField(formEl, token) {

      var input = formEl.querySelector('[name="' + TOKEN_FIELD_NAME + '"]');

      if (!input) {
         console.error(
            '[CIJ Turnstile] Could not find field "' +
            TOKEN_FIELD_NAME +
            '". Add an unmapped field with this name in the Customer Insights – Journeys form designer.'
         );
         return false;
      }

      input.value = token;

      return true;
   }

   function getSubmitButton(formEl) {
      return formEl.querySelector('button[type="submit"], input[type="submit"]');
   }

   function showWaiting(formEl) {

      var btn = getSubmitButton(formEl);

      if (btn) {
         btn.disabled = true;
         btn.style.opacity = '0.6';
         btn.style.cursor = 'wait';
      }
   }

   function hideWaiting(formEl) {

      var btn = getSubmitButton(formEl);

      if (btn) {
         btn.disabled = false;
         btn.style.opacity = '';
         btn.style.cursor = '';
      }
   }

   function onFormSubmit(evt) {

      var formEl = getFormFromEvent(evt);

      if (!formEl || formEl._cijResubmitting) {
         return;
      }

      // Allow the re-submitted request to continue once a valid token
      // has already been written to the hidden field.
      if (formEl._cijTurnstileReady) {
         formEl._cijTurnstileReady = false;
         return;
      }

      evt.preventDefault();

      if (evt.stopImmediatePropagation) {
         evt.stopImmediatePropagation();
      }

      showWaiting(formEl);

      getToken(formEl)

         .then(function (token) {

            setTokenField(formEl, token);

            formEl._cijTurnstileReady = true;
            formEl._cijResubmitting = true;

            // Re-submit the form after a valid Turnstile token has been obtained.
            if (formEl.requestSubmit) {
               formEl.requestSubmit();
            } else {
               formEl.submit();
            }

            setTimeout(function () {
               formEl._cijResubmitting = false;
            }, 0);
         })

         .catch(function (err) {

            // Do not allow the form to continue without a valid token.
            // The server-side plugin will also reject missing or invalid tokens.
            console.error('[CIJ Turnstile] Could not obtain token. Submission blocked.', err);

            hideWaiting(formEl);
         });
   }

   function wireForm(formEl) {

      if (formEl._cijTurnstileWired) {
         return;
      }

      formEl._cijTurnstileWired = true;

      loadTurnstileScript().then(function () {
         renderWidget(formEl);
      });
   }

   function findAndWireForms(root) {
      (root || document)
      .querySelectorAll(FORM_SELECTOR)
         .forEach(wireForm);
   }

   document.addEventListener(AFTER_LOAD_EVENT, function (evt) {

      var formEl = getFormFromEvent(evt);

      if (formEl) {
         wireForm(formEl);
      } else {
         findAndWireForms(document);
      }
   });

   document.addEventListener(SUBMIT_EVENT, onFormSubmit);

   // Handle forms that were rendered before this script was loaded.
   if (document.readyState !== 'loading') {
      findAndWireForms(document);
   } else {
      document.addEventListener('DOMContentLoaded', function () {
         findAndWireForms(document);
      });
   }

})();

Plugin Implementation

The plugin performs the server-side verification. The Execute method first retrieves the form submission payload supplied by Customer Insights. From that payload, it reads the cf_token field. The plugin then retrieves the Cloudflare Secret Key from the Dataverse Environment Variable. If the Environment Variable has not been configured, the plugin throws an exception because verification cannot continue.

Once both values are available, the plugin calls the Cloudflare SiteVerify endpoint using HttpClient. Cloudflare returns a JSON payload indicating whether the token is valid. Only successful responses allow the submission to continue. Finally, the plugin creates a ValidateFormSubmissionResponse object and returns it to Customer Insights.  The cf_token field is included in ValidationOnlyFields, so it is ignored during entity creation.

using Microsoft.Xrm.Sdk;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using System.Text;

namespace SamplePlugin
{
    /// <summary>
    /// Validates Customer Insights - Journeys form submissions by verifying
    /// the Cloudflare Turnstile token before allowing record creation.
    /// Registered on:
    /// Message: msdynmkt_validateformsubmission
    /// Stage: PostOperation
    /// Mode: Synchronous   
    /// </summary>
    public class ValidateTurnstilePlugin : IPlugin
    {
        private const string TurnstileVerifyUrl =
            "https://challenges.cloudflare.com/turnstile/v0/siteverify";

        private const string TokenFieldName = "tokenfieldname";

        private const string FailureMessage =
            "Captcha validation failed. Please refresh the page and try again.";

        private const string SecretKeyEnvironmentVariableSchemaName =
            "environmentvariablename";

        public void Execute(IServiceProvider serviceProvider)
        {
            var tracing =
                (ITracingService)serviceProvider.GetService(typeof(ITracingService));

            var context =
                (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            if (!context.InputParameters.Contains("msdynmkt_formsubmissionrequest"))
            {
                tracing.Trace(
                    "[Turnstile] Input parameter 'msdynmkt_formsubmissionrequest' not found.");
                return;
            }

            var serviceFactory =
                (IOrganizationServiceFactory)serviceProvider.GetService(
                    typeof(IOrganizationServiceFactory));

            var service = serviceFactory.CreateOrganizationService(null);

            string secretKey = GetEnvironmentVariableValue(
                service,
                tracing,
                SecretKeyEnvironmentVariableSchemaName);

            if (string.IsNullOrWhiteSpace(secretKey))
            {
                throw new InvalidPluginExecutionException(
                    $"Environment Variable '{SecretKeyEnvironmentVariableSchemaName}' does not contain a value.");
            }

            var request = Deserialize<FormSubmissionRequest>(
                (string)context.InputParameters["msdynmkt_formsubmissionrequest"]);

            var fields = request?.Fields ?? new List<FormField>();

            string token = fields
                .FirstOrDefault(f => f.Key == TokenFieldName)?
                .Value;

            if (string.IsNullOrWhiteSpace(token))
            {
                tracing.Trace(
                    $"[Turnstile] Token field '{TokenFieldName}' is empty.");

                SetValidationResponse(
                    context,
                    false,
                    FailureMessage);

                return;
            }

            bool isValid = VerifyTurnstile(
                secretKey,
                token,
                tracing);

            tracing.Trace(
                $"[Turnstile] Validation Result = {isValid}");

            SetValidationResponse(
                context,
                isValid,
                isValid ? null : FailureMessage);
        }

        private bool VerifyTurnstile(
            string secretKey,
            string token,
            ITracingService tracing)
        {
            var formContent =
                new FormUrlEncodedContent(
                    new Dictionary<string, string>
                    {
                        { "secret", secretKey },
                        { "response", token }
                    });

            using (var httpClient = new HttpClient())
            {
                string body;

                try
                {
                    var response =
                        httpClient.PostAsync(
                            TurnstileVerifyUrl,
                            formContent).Result;

                    body =
                        response.Content.ReadAsStringAsync().Result;

                    if (!response.IsSuccessStatusCode)
                    {
                        tracing.Trace(
                            $"[Turnstile] siteverify returned HTTP {(int)response.StatusCode}. " +
                            $"Body: {(string.IsNullOrWhiteSpace(body) ? "<empty>" : body)}");

                        return false;
                    }
                }
                catch (Exception ex)
                {
                    tracing.Trace(
                        "[Turnstile] Exception calling siteverify: " +
                        ex);

                    return false;
                }

                var verifyResponse =
                    Deserialize<TurnstileVerifyResponse>(body);

                tracing.Trace(
                    $"[Turnstile] success={verifyResponse.Success}, " +
                    $"hostname={verifyResponse.Hostname}, " +
                    $"errors={(verifyResponse.ErrorCodes == null ? "none" : string.Join(", ", verifyResponse.ErrorCodes))}");

                return verifyResponse.Success;
            }
        }

        private void SetValidationResponse(
    IPluginExecutionContext context,
    bool isValid,
    string error)
        {
            var response = new ValidateFormSubmissionResponse
            {
                IsValid = isValid,
                ValidationOnlyFields = new List<string>
                {
                    TokenFieldName
                },
                Error = error
            };

            context.OutputParameters["msdynmkt_validationresponse"] =
                Serialize(response);
        }

        private string GetEnvironmentVariableValue(
            IOrganizationService service,
            ITracingService tracing,
            string schemaName)
        {
            tracing.Trace(
                "[Turnstile] Retrieving Environment Variable: {0}",
                schemaName);

            var request = new OrganizationRequest(
                "RetrieveEnvironmentVariableValue")
            {
                Parameters =
                {
                    ["DefinitionSchemaName"] = schemaName
                }
            };

            var response = service.Execute(request);

            if (response.Results.Count == 0)
            {
                tracing.Trace(
                    "[Turnstile] Environment Variable '{0}' not found.",
                    schemaName);

                return null;
            }

            var value = response.Results
                .Values
                .FirstOrDefault()
                ?.ToString();

            tracing.Trace(
                "[Turnstile] Environment Variable '{0}' retrieved successfully.",
                schemaName);

            return value;
        }

        private static T Deserialize<T>(string json)
        {
            using (var stream =
                new MemoryStream(Encoding.UTF8.GetBytes(json)))
            {
                return (T)new DataContractJsonSerializer(typeof(T))
                    .ReadObject(stream);
            }
        }

        private static string Serialize<T>(T value)
        {
            using (var stream = new MemoryStream())
            {
                new DataContractJsonSerializer(typeof(T))
                    .WriteObject(stream, value);

                return Encoding.UTF8.GetString(stream.ToArray());
            }
        }

        #region Models

        [DataContract]
        private class FormField
        {
            [DataMember(Name = "Key")]
            public string Key { get; set; }

            [DataMember(Name = "Value")]
            public string Value { get; set; }
        }

        [DataContract]
        private class FormSubmissionRequest
        {
            [DataMember(Name = "Fields")]
            public List<FormField> Fields { get; set; }
        }

        [DataContract]
        private class ValidateFormSubmissionResponse
        {
            [DataMember(Name = "IsValid")]
            public bool IsValid { get; set; }

            [DataMember(Name = "ValidationOnlyFields")]
            public List<string> ValidationOnlyFields { get; set; }

            [DataMember(Name = "Error")]
            public string Error { get; set; }
        }

        [DataContract]
        private class TurnstileVerifyResponse
        {
            [DataMember(Name = "success")]
            public bool Success { get; set; }

            [DataMember(Name = "hostname")]
            public string Hostname { get; set; }

            [DataMember(Name = "error-codes")]
            public string[] ErrorCodes { get; set; }

            [DataMember(Name = "action")]
            public string Action { get; set; }

            [DataMember(Name = "challenge_ts")]
            public string ChallengeTimestamp { get; set; }
        }

        #endregion
    }
}

Register a synchronous plugin step on the msdynmkt_validateformsubmission message.


On failure – we get the message specified in the plugin

On the successful submission, we can see the marketing form submitted successfully and the details in our plugin trace log.

References

Amey Holden – https://www.ameyholden.com/articles/recaptcha-v3-cloudflare-turnstile-for-customer-insights-journeys-forms

MS Learn: Customer Insights – Journeys Customize form submission validation:

MS Learn: Customer Insights client-side extensibility

Cloudflare Turnstile: https://developers.cloudflare.com/turnstile/

Client-side rendering: https://developers.cloudflare.com/turnstile/get-started/client-side-rendering/

Server-side validation: https://developers.cloudflare.com/turnstile/get-started/server-side-validation/

Megan  Walker – https://meganvwalker.com/setting-up-recaptcha-v2-for-realtime-forms/

Hope it helps..

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