When working with form-level JScript functionality on Dynamics CRM/Dynamics 365 for Enterprise (D365E), you often uncover some interesting pieces of exposed functionality that can be utilised neatly for a specific business scenario. I did a blog post last year on arguably one of the best of these functions when working with Lookup field controls – the Xrm.Page.getControl().addPreSearch method. Similar to other methods exposed via the SDK, its prudent and effective implementation can greatly reduce the amount of steps/clicks that are involved when populating Entity forms.

I’ve already covered as part of last years post just what this method does, its sister method, addCustomFilter, and also some of the interesting problems that are encountered when working with the Customer lookup field type; a special, recently introduced field type that allows you to create a multi-entity lookup/relationship onto the Account and Contact entities on one field. I was doing some work again recently using these method(s) in the exact same conditions, and again came across some interesting quirks that are useful to know when determining whether the utilisation of these SDK methods is a journey worth starting in the first place. Without much further ado, here are two additional scenarios that involve utilising these methods and the “lessons learned” from each:

Pre-Filtering the Customer Lookup to return Account or Contact Records Only

Now, your first assumption with this may be that, if you wanted your lookup control to only return one of the above entity types, then surely it would be more straightforward to just setup a dedicated 1:N relationship between your corresponding entity types to achieve this? The benefits of this seem to be pretty clear – this is a no-code solution that, with a bit of ingenious use of Business Rules/Workflows, could be implemented in a way that the user never even suspects what is taking place (e.g. Business Rule to hide the corresponding Account/Contact lookup field if the other one contains a value). However, assume one (or all) of the following:

  • You are working with an existing System entity (e.g. Quote, Opportunity) that already has the Customer lookup field defined. This would, therefore, mean you would have to implement duplicate schema changes to your Entity to accommodate your scenario, a potential no-no from a best practice point of view.
  • Your entity in question already has a significant amount of custom fields, totalling more than 200-300 in total. Additional performance overheads may occur if you were to then choose to create two separate lookup fields as opposed to one.
  • The entity you are customising already has a Customer lookup field built in, which is populated with data across hundreds, maybe thousands, of records within the application. Attempting to implement two separate lookups and then going through the exercise of updating every record to populate the correct lookup field could take many hours to complete and also have unexpected knock-on effects across the application.

In these instances, it may make more practical sense to implement a small JScript function to conditionally alter how the Customer Lookup field allows the user to populate records when working on the form. The benefit of this being is that you can take advantage of the multi-entity capablities that this field type was designed for, and also enforce the integrity of your business logic/requirements on the applications form layer.

To that end, what you can look at doing is applying a custom FetchXML snippet that prevents either Account or Contact records from returning when a user clicks on the control. Paradoxically, this is not done by, as I first assumed, using the following snippet:

var filter = "<filter type='and'><condition attribute='accountid' operator='not-null' /></filter>";
Xrm.Page.getControl("mycustomerlookupfield").addCustomFilter(filter, "account");

This will lead to no records returning on your lookup control. Rather, you will need to filter the opposite way – only return Contact records where the contactid equals Null i.e. the record does not exist:

var filter = "<filter type='and'><condition attribute='contactid' operator='null' /></filter>";
Xrm.Page.getControl("mycustomerlookupfield").addCustomFilter(filter, "contact");

Don’t Try and Pass Parameters to your addCustomerFilter Function (CRM 2016 Update 1)

If your organisation is currently on Dynamics CRM 2016 Update 1, then you may encounter a strange – and from what I can gather, unresolvable – issue if you are working with multiple, parameterised functions in this scenario. To explain further, lets assume you have a Customer Lookup and a Contact Lookup field on your form. You want to filter the Contact Lookup field to only return Contacts that are associated with the Account populated on the Customer Lookup. Assume that there is already a mechanism in place to ensure that the Customer lookup will always have an Account record populated within it, and your functions to use in this specific scenario may look something like this:

function main() {

    //Filter Contact lookup field if Customer lookup contains a value

    var customerID = Xrm.Page.getAttribute('mycustomerlookupfield').getValue();

    if (customerID != null) {
   
        Xrm.Page.getControl("mycontactfield").addPreSearch(filterContactNameLookup(customerID[0].id));
    }
    
}

function filterContactNameLookup(customerID) {

    var filter = "<condition attribute='parentcustomerid' operator='eq' value='" + customerID + "' />";
    Xrm.Page.getControl("mycontactfield").addCustomFilter(filter, "account");

}

The above example is a perfectly sensible means of implementing this. Because, surely, it make more practical sense to only obtain the ID of our Customer Lookup field in one place and then pass this along to any subsequent functions? The problem is that CRM 2016 Update 1 throws some rather cryptic errors in the developer console when attempting to execute the code, and does nothing on the form itself:

Yet, when we re-write our functions as follows, explicitly obtaining our Customer ID on two occasions, this runs as we’d expect with no error:

function main() {

    //Filter Contact lookup field if Customer lookup contains a value

    var customerID = Xrm.Page.getAttribute('mycustomerlookupfield').getValue();

    if (customerID != null) {
   
        Xrm.Page.getControl("mycontactfield").addPreSearch(filterContactNameLookup);
    }
    
}

function filterContactNameLookup() {

    var customerID = Xrm.Page.getAttribute('mycustomerlookupfield').getValue()[0].id;
    var filter = "<condition attribute='parentcustomerid' operator='eq' value='" + customerID + "' />";
    Xrm.Page.getControl("mycontactfield").addCustomFilter(filter, "account");

}

I’ve been scratching my head at why this doesn’t work, and the only thing I can think of is that the first function – main – would be executed as part of the forms OnLoad event, whereas the filterContactNameLookup is only triggered at the point in which the lookup control is selected. It’s therefore highly possible that the first instance of the customerID is unobtainable by the platform at this stage, meaning that you have to get the value again each time the lookup control is interacted with. If anyone else can figure out what’s going on here or confirm whether this is a bug or not with Dynamics CRM 2016 Update 1, then do please let me know in the comments below.

Conclusions or Wot I Think

It could be argued quite strongly that the examples shown here in this article have little or no use practical use if you are approaching your CRM/D365E implementation from a purely functional point of view. Going back to my earlier example, it is surely a lot less hassle and error-prone to implement a solution using a mix of out of the box functionality within the application. The problem that you eventually may find with this is that the solution becomes so cumbersome and, frankly, undecipherable when someone is coming into your system cold. With anything, there always a balance should be striven for on all occasions and, with a bit of practical knowledge of how to write JScript functionality (something that any would-be CRM expert should have stored in their arsenal), you can put together a solution that is relatively clean from a coding point of view, but also benefits from utilising some great functionality built-in to the application.

The ability to modify the values within Option Set fields on a Dynamics CRM/Dynamics 365 for Enterprise (D365E) form is, depending on your business scenario, one of the best reasons to learn how to write basic JScript form functions. Microsoft makes available 3 methods via the Xrm.Page.ui control (which can be handily shortcutted to Xrm.Page.getControl):

  • addOption – Lets you add a new Option Set value, with the ability to optionally specify its position within the index.
  • clearOptions – Removes all options from an Option Set field.
  • removeOption – Allows you to remove a specific Option Set field value.

With these options at your disposal, you can begin to leverage functionality to accommodate the following business logic:

  • Only allow an Option Set to be populated with values (either all or just specific ones) once information has been entered into other fields.
  • Dynamically change the options available for selection, based on information entered within the primary entity form, or even a related one.

Now the key takeaway with all of this is that, ultimately, your code cannot do anything that would make the “base” Option Set collection invalid. For example – let’s assume you have a field called Fruit with the Option Set values of Apple, Pear and Apricot. If you then tried to introduce the value Banana on the form level, your code will more than likely error when saving the record.

From a CRM/D365E point of view, there are two additional field types which are also technically classed as Option Sets, thereby allowing you to utilise the above methods with them – the Status and Status Reason fields. If you look closely at the Status Reason field within the Customizations area of the application, you can see why: just like option sets, you specify a label and underlying value for it, that is dictated by your solution publisher:

The only difference worth noting is that, unlike normal Option Set fields, you have no choice when it comes to which underlying value is used. If your organisation is prone to changing Status Reason values often for an entity, you may begin to notice large gaps in Option Set values over time; annoying, but I can understand why it’s in place.

All of the above code examples should be sufficient for common scenarios, such as when you want to remove single option set values or are interacting with the Status Reason field on the main part of the form. What I wanted to do as part of today’s blog post is highlight two non-standard scenarios for the above and illustrate a solution for each – which, when brought together, demonstrate an example I was recently involved in developing a solution for.

The Problem

The business in question was using an entity across multiple parts of their business. On the entity, there was then a flag field to indicate which side of the business the entity record belonged to. This would then assist when running reports and, when coupled with Business Unit segregation for the records, ensured that colleagues in the business only saw records that they needed to as part of their day-to-day usage of the application.

Because the entity was shared, fields within the entities – such as Status Reason – were also shared and, by implication, contained values that were only relevant to a specific part of the business. We were therefore tasked with finding a solution to ensure that the Status Reason value list was modified for each person to reflect the record type they were working with. Colleagues primarily worked within the Web Application and the Status Reason field was on each form, within the Header area.

Solution #1: Efficiently Removing Multiple Option Set Values

When we take a look at the code examples, we are given pretty much everything we need to start implementing a solution. So if we assume that our Status Reason field contains 15 values, 10 of which we want to remove, we may be tricked into writing the following code and doing the good ol’ copy & paste:

Xrm.Page.getControl('statuscode').removeOption(100000000);
Xrm.Page.getControl('statuscode').removeOption(100000001);
Xrm.Page.getControl('statuscode').removeOption(100000002);
Xrm.Page.getControl('statuscode').removeOption(100000003);
Xrm.Page.getControl('statuscode').removeOption(100000004);
Xrm.Page.getControl('statuscode').removeOption(100000005);
Xrm.Page.getControl('statuscode').removeOption(100000006);
Xrm.Page.getControl('statuscode').removeOption(100000007);
Xrm.Page.getControl('statuscode').removeOption(100000008);
Xrm.Page.getControl('statuscode').removeOption(100000009);

Now, this code will work fine and can be pretty clearly deciphered. The problem lies in the number of lines it is expressed in (thereby increasing load times/processing time when CRM is processing your function). We can get round this by thinking back to Programming 101 and making the correct assumption that JScript has the ability to loop through similar commands – in this case, via the for Loop. By introducing an array into the mix as well, we can then express our code much more simply:

var statusCodes = [100000000, 100000001, 100000002, 100000003, 100000004,
                   100000005, 100000006, 100000007, 100000008, 100000009];
                   
    for (index = 0; index < statusCodes.length; ++index) {

        Xrm.Page.getControl('statuscode').removeOption(statusCodes[index]);

    }

Solution #2: Working with Header Controls A.K.A. Why I Hate ‘Object reference not set to an instance of an object’ Errors

I perhaps should have prefaced the above when I said ‘Now this code will work fine’. 🙂 Going back to the task at hand, when I attempted to deploy a version of the above code into the target environment, I immediately got the dreaded error message referenced above. This is generally the hallmark error to steer you towards checking your code for any typos, as the error message is basically telling you it can’t find the thing you are asking it to – in this case, the field statuscode.

After triple-checking the exact spelling of statuscode and making sure the field was on the form, I did some further diving into Developer Tools with the form loaded to figure out just what the hell was going wrong. When performing a search for the statuscode field on the DOM Explorer, I noticed that I got a few hits for statuscode – but that it was prefaced with the value header_. I followed this up with some further research online, which confirmed that, if fields in the Header need to be referenced, then they should be prefaced with header_. So our for Loop example would need to look as follows instead:

    for (index = 0; index < statusCodes.length; ++index) {

        Xrm.Page.getControl('header_statuscode').removeOption(statusCodes[index]);

    }

Bringing it All Together

After encapsulating all of the above into a single function, removeStatusCodes(), we can then call the following snippet as part of an OnLoad function to hide our Status Reason values if the flag field is set to the correct value:

if (Xrm.Page.getAttribute('new_myflagfield').getValue() == "100000000") {
                                                           
        removeStatusCodes();
    }

This will then work as intended and ensure that end-users only see the Status Reason values that directly concern them; no doubt assisting greatly in making the system as streamlined as possible to use and to avoid any potential for data-entry problems further down the line.

The Scenario: You are running CRM Online in conjunction with some legacy database/application systems. These systems are setup with a SQL Server Reporting Services instance that is looking to either an SQL Server, OLE DB etc. database.

The Problem: You need to make data from your legacy systems visible within your CRM. The information needs to be displayed on the Entity Form and show specific information from the legacy database that relates to the CRM record.

Admittedly, the above is perhaps somewhat unlikely situation to find yourself in, but one which I recently had to try and address. I suppose the most straightforward resolution to the above is to just say “Get rid of the legacy system!”. Unfortunately, the suggestion didn’t go down to well when I voiced it myself…

So at this point the next best answer looked to be try and utilise what we have within the existing infrastructure: an all singing, all-dancing SSRS and SQL Server database instance.

What if we were to try uploading an .rdl file that includes a FetchXML and our SQL/OLE DB database data source into CRM? Whenever you try to perform this, you will get this error message:

ReportUploadError_NoFetchXML

 

Rats! So there is no way in which we can include a non-fetch XML Data Source to our separate SSRS report instance. So is there anything else within CRM that can be utilised to help in this situation? Let’s first take a quick look at the following nifty little feature within CRM, courtesy of our good friend MSDN:

You can use an IFRAME to display the contents from another website in a form, for example, in an ASP.NET page. Displaying an entity form within an IFrame embedded in another entity form is not supported.

Use the getValue method on the attributes that contain the data that you want to pass to the other website, and compose a string of the query string arguments the other page will be able to use. Then use a Field OnChange event, IFRAME OnReadyStateComplete event, or Tab TabStateChange event and the setSrc method to append your parameters to the src property of the IFRAME or web resource.

You may want to change the target of the IFRAME based on such considerations as the data in the form or whether the user is working offline. You can set the target of the IFRAME dynamically.

Source: https://msdn.microsoft.com/en-gb/library/gg328034.aspx

Having worked extensively with SSRS in the past, I am also aware that you can use an SSRS URL string in order to specify properties about how the report is rendered, its size and – most crucially – what the value of required parameters should be. The friend that keeps on giving has a great article that goes through everything that you can do with an SSRS report URL and also how to use Parameters as part of your URL. So in theory therefore, we can place an IFRAME on our form and then use JScript to access form-level field values and modify the IFRAME URL accordingly.

Here are the steps involved:

  1. Go into Form Editor and add a new IFRAME to the form, specifying the following settings:

Name: The Logical name of the control, this will be required as part of the JScript code used later, so make a note of it.

URL: As this is a mandatory field, you can specify any value here as it will change when the form is loaded by the user. This is not practical as we don’t want this to be displayed if, for example, the field that we are passing to the URL has no value in it. Our JScript code will sort this out in a few moments

Label: This can be anything, and defaults to whatever is entered into the Name field

Restrict cross-frame scripting, where supported: Untick this option

Ensure that ‘Visible by default’ is ticked

Your settings should look something like this:

IFRAMESettings

  1. Create or modify an existing JScript Library for the form, adding in the following function (after modifying the values accordingly):
function onLoad_LoadSSRSReport() {

    //First get the page type (Create, Update etc.)

    var pageType = Xrm.Page.ui.getFormType();
    
    //Then, only proceed if the Form Type DOES NOT equal create, can be changed depending on requirements. Full list of form types can be found here:
    
    //https://msdn.microsoft.com/en-us/library/gg327828.aspx#BKMK_getFormType

    if (pageType != "1") {

        //Get the value that you want to parameterise, in this case we are on the Account entity and need the Account Name

        var accountName = Xrm.Page.getAttribute("name").getValue();

        //In order to "accept" the parameter into the URL, spaces need to be replaced with "+" icons

        accountName = accountName.replace(/ /g, "+");

        //Now, get the the name of the IFRAME we want to update

        var iFrame = Xrm.Page.ui.controls.get("IFRAME_myssrsreport");

        //Then, specify the Report Server URL and Report Name.

        var reportURL = "https://myssrsserver/ReportServer?/My+Reports/My+Parameterised+Report&MyParameter=";

        //Now combine the report url and parameter together into a full URL string

        var paramaterizedReportURL = reportURL + accountName;

        //Finally, if there is no value in the Account Name field, hide the IFRAME; otherwise, update the URL of the IFRAME accordingly.

        if (accountName == null) {
            iFrame.setVisible(false);
        }
        else {

            iFrame.setSrc(paramaterizedReportURL);
        }
    }
}
  1. Add the function to the OnLoad event handler on the form. Now, when the form loads, it will update the IFRAME with the new URL with our required parameter.

And there we go, we now have our separate SSRS instance report working within CRM! A few things to point out though:

  • If the report parameter supplied does not load any matching records, then SSRS will display a standard message to this effect. You would need to modify the report settings in order to display a custom message here, if desired.
  • It is recommended that you have https:// binding setup on your report instance and supply this to as part of the setSrc method. http:// binding works, but you may need to change settings on your Web Browser in order to support mixed mode content. Full instructions on how to set this up can be found here.
  • This may be stating the obvious here, but if your SSRS instance is not internet-facing, then you will get an error message in your IFRAME if you are not working from the same network as your SSRS instance. Fortunately, SSRS can be configued for an Internet deployment.
  • The steps outlined in 1) can also be used to specify a non-parameterised SSRS report within an IFRAME dashboard too. I would recommend using the following SSRS system parameters as part of the URL though:
    • rs:ClearSession=true
    • rc:Toolbar=false

e.g.

https://myssrsserver/ReportServer/Pages/ReportViewer.aspx?%2fMy+Reports%2fMy+Non+Parameterised+Report&rs:ClearSession=true&rc:Toolbar=false

One of the most challenging things about any system migration is ensuring that information from other business systems can be made available, and it is good to know that CRM has supported approaches that can help to bridge the gap.