Customer Insight Journeys Real Time Dynamic Forms Loader

Customer Insight Journeys Real Time Dynamic Forms Loader

In a previous post I blogged about how to break down the Form script that was exported from CI-J (Real Time). As some customers asked me about making this into a script that was the same and was dynamic based on query string parameters (parameters in the URL), I worked a bit on that and thought I’d share it here;

<div id="formdiv">
</div>

<script>
function getQueryParam(name) {
         const queryString = window.location.search;
         const urlParams = new URLSearchParams(queryString);
         const queryparam = urlParams.get(name);
         return queryparam;
        }

        const formobj = document.querySelector('#formdiv');
        const formid = getQueryParam('id');
        const formapiurl = 'https://public-eur.mkt.dynamics.com/api/v1.0/orgs/' + getQueryParam("orgid") + '/landingpageforms'; 
        const formcachedurl = 'https://assets-eur.mkt.dynamics.com/' + getQueryParam("orgid") + '/digitalassets/forms/' + formid;

        formobj.setAttribute('data-form-id', formid);
        formobj.setAttribute('data-form-api-url', formapiurl);
        formobj.setAttribute('data-cached-form-url', formcachedurl);
</script>
    
    <script src='https://cxppusa1formui01cdnsa01-endpoint.azureedge.net/eur/FormLoader/FormLoader.bundle.js'></script>

A few things that can be mentioned. This script expects that there will be two query string parameters:
id – the id of the form. Click on the form and copy it from the URL after the “id=”
orgid = the orgid which you can find in the PPAC or in the export of the script as I described in the previous article. If it is placed on the url: https://contoso.com/form.html then an example of the url would be:

https://contoso.com/form.html?id=6d10214b-cda1-41d8-9bf7-2c7262c527aa&orgid=b1616e23-557d-4a3f-8112-e8fac97f8ebe

Finally some organizations also have problems with loading a script from external sources. I will look at that too. Mainly there are two option. Copy-paste the entire script inline into this script or copy the script file to an “internal” or recognized store with a public URL and change the src-attribute. If you move this away from referencing Microsoft, I would recommend checking their website on a regular basis to make sure it hasn’t changed.

I hope this helps out!

Breaking down form script in Customer Insight Journeys

Breaking down form script in Customer Insight Journeys

When creating a form in Customer Insight Journeys (real time) one option is to embedd it in an existing page. A customer asked me if it was possible to get this script from the API some how. Turns out that you don’t have to. It has a logical build-up and you can generate it yourself with a script.

First of all, let’s have a look at the generated script;

<div
        data-form-id='2e2b50a9-0000-0000-9079-0022489ca998'
        data-form-api-url='https://public-eur.mkt.dynamics.com/api/v1.0/orgs/4e5c8ea2-0000-0000-ac66-a30471bdf4fa/landingpageforms'
        data-cached-form-url='https://assets-eur.mkt.dynamics.com/4e5c8ea2-0000-0000-ac66-a30471bdf4fa/digitalassets/forms/2e2b50a9-0000-0000-9079-0022489ca998' >
</div>
<script src = 'https://cxppusa1formui01cdnsa01-endpoint.azureedge.net/eur/FormLoader/FormLoader.bundle.js' ></script>

I have highlighted two different guids (I have changed them so they are not the actual guids).

The first one; 2e2b50a9-0000-0000-9079-0022489ca998 this is the Form id. It is the exact Id of the form that you have created. Easy to confirm by checking the id-parameter in the querystring for the form. This can easily be queried from the WebAPI from the table msdynmkt_marketingform.

Second guid; 4e5c8ea2-0000-0000-ac66-a30471bdf4fa is the instance id of the dataverse instance. This can be found in the Power Platform Admin Center under “Organization Id”. This is hence the same for all forms that are from the same instance.

The rest of the script is the same. Hence you can generate this quite simply with a script if you have these two values. I don’t know if the library referenced might risk being changed during updates or similar. Hence I would recommend using it with the same reference as seen above, but it might also be possible to download it and host it yourself. This is not something I have tried.

A colleague of mine, Thomas Passad, also mentioned that some CMS:s, like Optimizly, cannot handle the script being reference directly and that it had to be placed in some general footer or similar.

With this knowledge I think it is possible for you to handle this script in a more dynamic fashion but make sure to check that it hasn’t changed every month or so, as it might cause issues if there is a change and you havn’t taken that into consideration.

Unable to delete table due to entityanalyticsconfig

Unable to delete table due to entityanalyticsconfig

Working on a new environment recently I had to remove a few tables. However, after removing all normal dependencies, it complained finally about a dependency to the table: entityanalyticsconfig. Never heard about it. After some googling on Microsoft Learn I found that it is about the sync to datalake, which was funny as we hadn’t set up any sync. I think it might be the new Microsoft Fabric sync from Dataverse that might be causing this. It might be switched on by default in the case that you have change tracking switched on.

Also I couldn’t find it in advanced find (the old or new one) but with https://fetchxmlbuilder.com/ in XrmToolBox I was able to find it and also the rows that were associated with it. So, I created FetchXml for these specific rows, used the tool Bulk Delete Tool (no not Bulk Delete Manager, which I made) by Andy Popkin and simply ran the delete for these specific rows. This allowed me to remove the dependency and then remove the tables.

I hope this will help you too!

Bulk Delete failing

Bulk Delete failing

I have recently been working with a customer with a large (500+ GB dataverse db) production instance and we are attempting to cut this down quite a lot. The natural way of doing this is bulk delete but it seems that if the underlying SQL isn’t up to speed, your jobs might end without actually being done.

My customer has a production instance of over 500GB which is costing them some substantial money and hence we started looking at removing some of the less necessary data. In this case it was the order products (salesorderdetail) which we have perceived that we do not need more than one year after delivery. Hence we set up an advanced find, agreed on the exact filters, saved the view and then tried to remove the records (well over 5M) using bulk delete. I restarted it several times. You can see the chronological results in the screenshot below:

As you can see, sometimes it actually deleted a few records, but most times, it didn’t. My professional analysis of this (=guess) is that this is caused by bulk delete not handling exceptions like SQL Timeout properly or that it has a limit on the number of times it will retry.

I also, in parallell tried to remove the same records with the same FetchXML using SSIS/Kingswaysoft and here I have several times gotten this error. I have had to turn down the knobs to a very low setting to get it to work, but the error message I did get was:

{“error”:{“code”:”0x80044151″,”message”:” Sql error: SQL timeout expired. CRM ErrorCode: -2147204783 Sql ErrorCode: -2146232060 Sql Number: -2″,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionSourceKey”:”Plugin/Microsoft.Crm.ObjectModel.CustomBusinessEntityService”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiStepKey”:”60ccbb1b-ea3e-db11-86a7-000a3a5473e8″,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiDepthKey”:”1″,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiActivityIdKey”:”4edd4e59-4b1e-4d0b-8d13-79ea53ab10ff”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiPluginSolutionNameKey”:”System”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiStepSolutionNameKey”:”System”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionCategory”:”SystemFailure”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionMessageName”:”unManagedidssqltimeouterror”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionHttpStatusCode”:”503″,”@Microsoft.PowerApps.CDS.HelpLink”:”http://go.microsoft.com/fwlink/?LinkID=398563&error=Microsoft.Crm.CrmException%3a80044151&client=platform”,”@Microsoft.PowerApps.CDS.InnerError.Message”:” Sql error: SQL timeout expired. CRM ErrorCode: -2147204783 Sql ErrorCode: -2146232060 Sql Number: -2″}}
–batchresponse_5165dc8d-efdb-4237-b62f-267ed01f73b8–
) (SSIS Integration Toolkit for Microsoft Dynamics 365, v23.2.0.25101 – DtsDebugHost, v16.0.1000.6)System.Net.WebException

(Status Reason: ServiceUnavailable): The remote server returned an error: (503) Server Unavailable.”.

As you can see, there is a lot of noise but it clearly, in the highlighted part, say that there is a SQL Timeout.

Hence my takeaway from this is that you need to be a bit wary of bulk delete in large instances or in general as it might indicate that it has completed successfully but in fact it stopped due to SQL Timeout (or some other platform related issue).

On a personal note, I really hope the bulk delete functionality gets a modernized revamp soon. It is really old and is becoming more and more relevant.

Calculated columns + Azure Synapse Link != true

Calculated columns + Azure Synapse Link != true

I was recently helping my colleague Ebba Linnea Nilsson with a support ticket with data not being propagated correctly from dataverse to a datalake via Azure Synapse Link. It turned out that this was all by design. A design that might not be what normal users would expect.

Calculated columns and now recently the formula columns are both very useful way of being able to calculate data in a field that is based on other fields. Common scenarios are calculations like “Weighted revenue” which is the probability multiplied by the estimated revenue for an opportunity. However, there are scenarios where you need to be aware of how these fields actually work or you might get an unwanted or unexpected behaviour.

The first thing that needs to be understood is that these column types are calculated “on-the-fly” everytime dataverse attemts to access these columns. It might seem like the data is “in the columns” but it really isn’t, it is calculated. This is a big difference from for instance rollup-columns is that those columns are calculated on a regular interval by the system, and the result is stored in the record.

What does this mean for Azure Synapse Link? Well, let’s say we have a simple calculation, that sets the value “A” into all records for this calculated column. We then enable the Azure Synapse Link which will make an initial sync and set the column in the datalake to “A”. Now we change the calculation of the rule to output “B” instead. As no records are actually changed, this will not cause any records in the datalake to be updated, hence they will all still have the value “A”. From a user perspective comparing Dynamics 365 to the datalake without any underlying understanding of how this functions, it will look like an error. Same column has different values comparing what is in dataverse with what is in the datalake.

As soon as a record is actually changed, all columns for that record will then be sent to the datalake, and hence the calculated column will be set to “B” at that time. It is hence possible, to manually or semimanually force a resync, but it would require some bulk like for instance SSIS with Kingswaysoft especially for implementations with large amounts of records.

An important question to ask, is why would you want to calculate the data in dataverse and then use it in in the datalake. If you have a propper datalake architecture it should be easier to make calculated columns/fields in the datalake/datalakehouse. If the data is calculated only for use in the datalake, I would suggest moving the calculation to the datalake.

There are, of course, scenarios when it is preferrable to have calculations in one place and reuse the output in many places. However, this understanding of what can reasonably be expected is then essential.

As for product improvements, I have added an idea on the subject, if you agree with me, please vote! Microsoft Idea (dynamics.com)

A final note is that this type of unexpected behaviour is not limited to just Azure Synapse Link but really to any integrations based on either “modified on” or change tracking without doing periodic synchronizations. Hence I would also like to give a general warning about this.