Sonoma Partners Microsoft CRM and Salesforce Blog

Filtering Connection Roles by a Property of an Account

Today's blog post was written by Sonoma Partners' Principal Developer Argyris Anargyros and Senior Developer Jeff Young.

As part of a recent project, we had a client request to limit native and custom Connection Roles available to an Account based on a set of properties on the Account. While it is possible to filter the Connection Role field as you would any other lookup field, there is no native behavior to support filtering based on some specific property of an entity. Meeting this requirement required some custom development and, as we can see this being very useful, we are sharing the technique we developed.

The Use Case

In our case, the Account entity had been customized with a set of Yes/No fields that, if selected, would control the set of Connection Roles available to the Account, defining restrictions on how the Account could connect to other Accounts and Contacts.

Argy young 1

Figure 1: An example of the fields available to our entity. Here we have created a section on our form labelled 'Connection Role Filtering Fields' and added the fields controlling the available Connection Roles. In this example, the Connection Roles associated to fields 1, 3, 6, 7, 10 and 11 will be used to filter the Account’s available Connection Roles. The schema name for the field “Role Filter Field 1” would be new_rolefilter1.

Business logic determined the Connection Roles available to each field; the set of available Roles for the Account would be determined by which fields on the Account were selected as “Yes” and by the type of entity to which the Account was connecting.

Further, all the fields could be selected “Yes” or none could be; there could be any combination of fields.

Our first step was to determine what native functionality we could utilize, then decide how to extend it.

Filtering a Lookup

As we’ve said, CRM provides a way to filter values presented in a lookup field by JavaScript natively. Also, the Connection Roles form is just that - a form - so it’s possible to add JavaScript to it and have it run on form-related events, such as onLoad.

There are two JavaScript methods that are members of the lookup control that are utilized when applying a filter: addPreSearch and addCustomFilter.

addPreSearch is essentially an event listener that is attached to the lookup control, triggered when the lookup control is clicked. The argument for this method is the function that should run when the event is triggered. 

From within addPreSearch (and only from within addPreSearch) the addCustomFilter method can be called. addCustomFilter accepts two arguments: a subset of FetchXML which is the filter applied to the lookup and the schema name of the entity the filter is being applied to. This method is run on the lookup when the user clicks on it, which can easily be confirmed using a breakpoint on your favorite F12 tool.

It’s important to note that when using these methods, promises, or other non-synchronous methods can exit from the closure of the listener method and make applying the filter difficult to impossible. We discovered this as we have an extensive library of JavaScript tools at Sonoma Partners that we use to extend and enhance functionality of the Xrm API, including both synchronous and asynchronous use of the Org Service.

Our original implementation of this solution utilized the asynchronous Org Service methods, which gave us some problems because the promise, once invoked, had lost the context of the lookup (specifically, the closure in which the reference to the lookup had been exited). In our experience, it’s easiest to apply a filter using a sequence of synchronous calls instead of capturing the context of addCustomFilter and passing it to the promise when it runs (perhaps capturing ‘this’ and passing it as an argument to the promised method).

Our next step was to figure out some way of identifying the subset of Connection Roles to apply to the lookup filter.

Configuration Setting Entities

Configuration Setting entities are a common way of managing CRM behavior at Sonoma and elsewhere; giving System Administrators a convenient way to manage CRM behavior without editing code. In our solution for this project, we created a Configuration Entity that possessed simple key/value fields. We utilized these records to manage behavior of multiple entity scripts, just like this example.

For use with Connection Role filtering, we created some Configuration Setting records whose keys were a unique string based on our purpose and the schema name of the fields on the Account entity we wanted to control the filtering. This made the name of the record something like “Connection Filters for your_field_here”. So, if our Account entity had a field named “new_rolefilter1” and this field was checked as “Yes” we would retrieve the Configuration Setting record named “Connection Filters for new_rolefilter1.

Argy young 2

Figure 2: An example of the Configuration Settings record associated with Role Filter Field 1 (schema name new_rolefilter1)

 

To reflect the business logic the value of these records, then, were the names of the Connection Roles we want to filter by; specifically, comma separated string of the names of the connection roles, such as “Accounting,Administration,Coordination,etc…”

Strings are a convenient way to express this data as we can split them easily into an array once we’ve retrieved the record. They may not be so easy to administer, however, especially if the list of the record maintains becomes long. In this case, expanding the entity to include multiple fields would be one option which would present a more user-friendly way of managing the fields, but we would lose the programmatic convenience of getting and splitting a string and would be introducing issues with forward maintainability; adding Connection Roles would require editing this form, for example. For this reason, we went with strings.

Having all the pieces in place, now we need to bring them together.

Using the Configuration Entities to Filter Connection Roles

We attached a script to run on load of an Account form. This script retrieved the values of the fields that will control the available Connection Roles, determined which fields were set to “Yes” and then retrieved the configuration record for each one of these fields.

Using the value of each configuration record, we build an array of unique Connection Role names (some records may contain the same Connection Roles, see below) by retrieving the value of each configuration record and splitting it by a comma.

Then, using this array, we construct the Fetch XML fragment that will be applied to the lookup. The fragment is part of an ‘AND’ condition appended to the query for the lookup. Because we want to filter on a name or a set of names, we must nest the Role names we want to match against within an OR condition. The final XML looks much like below:

<filter type="and">

<filter type="or">

<condition attribute="name" operator="eq" value="Accounting"/>

<condition attribute="name" operator="eq" value="Advisory"/>

<condition attribute="name" operator="eq" value="Provisioning"/>

<condition attribute="name" operator="eq" value="Communications"/>

<condition attribute="name" operator="eq" value="Scheduling &#038 Maintenance"/>

<condition attribute="name" operator="eq" value="Logistics "/>

</filter>   

</filter>

The result of applying the filter will look similar to the below image:

Argy young 3

In sum, when we load the Connection Roles form for an Account, we:

  • We add a listener to the Connection Role lookup field.
  • When the lookup is selected, we query the account to get the set of properties that effect the Connection Roles.
  • Using this set, we query for the Configuration Records for each positive field.
  • From this query, we build an array of roles that are available to the Account.
  • From the array, we build XML that applies a filter to the lookup.

"Gotchas"

As we developed this solution, we encountered a few issues that provided some challenges.

  • Filters must be URL escaped. When testing our solution, we ran into immediate problems with the Fetch XML we were building; CRM was reporting Malformed XML. Looking closely at the XML, we noticed that custom Connection Roles created by the client contained the ampersand and forward slash characters, which are special characters in XML. In order to correctly handle these characters when applying the filter XML, they need to be replaced by their encoded HTML value (‘&’ becomes ‘&#038’ as can be seen in the example FetchXML above).
  • Make sure to remove duplicates from the filter list.
  • Methods used to filter the lookup can’t be asynchronous. Forewarned is forearmed!

We hope that this technique will help you towards your CRM endeavours.

Topics: CRM Best Practices