Posted on 21. November 2015

Option-Set, Lookup or Autocomplete

In the constant struggle to improve data quality it is common to avoid using free-text fields in favour of select fields. This approach has the advantage of ensuring that data is entered consistently such that it can easily be searched and reported upon.

There are a number of choices of approaches to implementing select fields in Dynamics CRM. This post aims to provide all the information you need to make an informed choice of which to use on a field by field basis. The options are:

  • Option-Set Field - stored in the database as an integer value which is rendered in the user interface as a dropdown list. The labels can be translated into multiple languages depending on the user's language selection.
  • Lookup Field - a search field that is stored in the database as a GUID ID reference to another entity record. The Lookup field can only search a single entity type.
  • Auto Complete Field - a 'free text' field that has an auto complete registered using JavaScript to ensure that text is entered in a consistent form. The term 'autocomplete' might be a bit misleading since the field is not automatically completed but instead you must select the suggested item from the list. This is a new feature in CRM 2016 that you can read more about in the preview SDK.
The following table provides an overview of the aspects that this post discusses for each option:
  • Number of items in list – The larger the list and the likelihood that it will grow, the more this becomes important.
  • Filtering based on user's business unit - This is especially important where you have different values that apply to different parts of the business and so the list of options must be trimmed to suit.
  • Adding new Items - Ease of adding values frequently by business users.
  • Removing values – Ease of removing values without affecting existing records that are linked to those values.
  • Multi-Language – Having options translated to the user's selected language
  • Dependant/Filtered options - This is important where you have one select field that is used to filter another such as country/city pairs.
  • Additional information stored against each option - This is important if you have information that you need to store about the selected item such as the ISO code of a country.
  • Mapping between entities - Is the option on multiple entity types? Often the same list of items is added as a field in multiple places and multiple entities. This can be important when using workflows/attribute maps to copy values between different entity types.
  • Number of select fields - The more select fields you have across all your entities, the more important this becomes.
  • Filters, Reports and Advanced Find - When creating advanced finds and views, a user should be able to select from a list of values rather than type free text.
  • Configure once, deploy everywhere – One key design goal should be that once a field is configured, it should be easily used across the web, outlook, tablet and phone clients.

Option-Set Fields

Option-Sets are the default starting point for select fields.

Number of items in list (Option-sets)

No more than ~100 for performance reasons. All items are downloaded into the user interface which will cause performance problems for large lists – especially where there are lots of option-sets on the same form.

Filtering based on user's business unit (Option-sets)

Requires JavaScript to filter items dynamically based on the user's role/business unit.

Ease of adding values frequently by business users (Option-sets)

Option-Sets require a metadata configuration change and a re-publish that would usually be done out of hours by an administrator. It is best practice to do this on a development environment and then import a solution into production. Adding new values to the list isn't something that can be done by business users.

Removing values over time (Option-sets)

Removing items causes data loss in old records. Items can be removed using JavaScript to preserve old records, but Advanced Find will still show the values.

Multi-Language Options (Option-sets)

Each option-set item can be translated into multiple languages.

If you need to have the select field multi-language then an option-set is probably your best choice unless it is going to be a long list, in which case you'll need to make a compromise.

Dependant/Filtered options (Option-sets)

Requires JavaScript to filter options.

Additional information stored against each option (Option-sets)

It is not possible to store additional data other than the label and integer value of the option-set. You would need to store somewhere else in a lookup table format.

Mapping between entities (Option-sets)

Use a global option-set that can be defined once and used by multiple option-set fields.

Number of select fields (Option-sets)

You can have as many select fields as your entity forms will allow. The more fields you have the slower the form will load and save. 

Search/Filtering (Option-sets)

Option-sets are always presented as a drop down in advanced fine and view filters.

Configure once, deploy everywhere (Option-sets)

Works across all clients including phone and tablet native apps.

Option-sets are the most 'native' choice for select fields and will work in all deployment types without much consideration.

 

Lookup Fields with Custom Entity

Lookup fields allow selecting a single reference to a custom entity using a free text search.

Number of items in list (Lookup)

Unlimited list items subject to database size. Since all list items are not downloaded to the user interface (unlike option-sets) the user can search quickly for the item they need.

Filtering based on user's business unit (Lookup)

Security Roles can be used in combination with a user owned lookup entity so that lookup records are visible to subset of users.

Ease of adding values frequently by business users (Lookup)

New records can easily be added by users using the 'Add New' link. Control over who can add new items can be done using Security Roles.

If you need business users to add values regularly then a Lookup field is a good choice. The Configuration Migration tool in the SDK can be used to easily move values between environments.

 

Removing values over time (Lookup)

Old items can be easily deactivated and will no longer show in lookup fields (including in advanced finds) however existing records will retain their selected value (unlike when option-set items are removed).

If you need to make changes constantly to the list and remove items without affecting previous records then a lookup field is most likely your best choice.

 

Multi-Language Options (Lookup)

Not possible without complex plugin server side code to dynamically return the name in the current user's language.

Dependant/Filtered options (Lookup)

Lookup filtering options can be added in the form field properties or can be added via JavaScript for more complex scenarios.

Lookups are the easiest and quickest to setup dependant lists without writing code. This filtering will also work on tablet/mobile clients without further consideration.

Additional information stored against each option (Lookup)

Additional information can be stored as attributes on the lookup entity records. Lookup views can show up to 2 additional attributes within the inline lookup control.

If you are integrating with another system that requires a special foreign key to be provided, lookup entities are good way of storing this key.

 

Mapping between entities (Lookup)

Lookups can easily be mapped between records using attribute maps/workflows or calculated fields.

Number of select fields (Lookup)

CRM Online is limited to 300 custom entities.

This is an important consideration and it's unlikely to be a good idea to use Lookup entities for all of your select fields.
If you are using CRM online you'll likely have to always use a combination of lookups and option-sets due to the limit of 300 custom entities. Don't take the decision to make all your select fields as lookups.

 

Search/Filtering (Lookup)

Lookups are presented as search fields in Advanced Find and Filters.

Configure once, deploy everywhere (Lookup)

Works across all clients including phone and tablet native apps. If working offline however, all lookup values may not be available.

Text Field Auto Completes (CRM 2016)

Autocompletes are a free text field with an on key press event added to show an autocomplete. The great thing about autocompletes is that they can show icons and additional action links.See below for an example of how to use autocompletes in Javascript.

Number of items in list (Autocomplete)

An autocomplete field can only show as many items as you return at a time but you'll want to put a limit for performance reasons.

If you need the select field to be more like a combo-box where users can type their own values or select from predefined items then autocomplete text fields are a good choice.

 

Filtering based on user's business unit (Autocomplete)

You can add any search filtering you need using JavaScript.

 

Ease of adding values frequently by business users (Autocomplete)

If the autocomplete is using a lookup entity to store the data displayed then the same considerations would apply as for Lookup Entities. If the values are hard coded into the JavaScript then this would be more akin to the Option-Set solution import.

Removing values over time (Autocomplete)

Since the actual field is stored as a text field there is no issue with removing values. Existing data will still be preserved.

Multi-Language Options (Autocomplete)

You can detect the user interface language and return a specific language field to the user via JavaScript however it will be stored in the textbox and other users will not see it in their language (unlike an option-set). One solution to this would be to use the autocomplete for data entry and then use a web resource to present the field value in the local user's language.

Dependant/Filtered options (Autocomplete)

You can apply whatever filtering you need using JavaScript.

Additional information stored against each option (Autocomplete)

If you use the autocomplete to search a custom entity you can store additional data as additional attributes. The autocomplete flyout can display multiple values for each result row.

Autocomplete fields have the advantage that they can show an icon that is specific to the record (e.g. The flag of the country). If you need this feature, then Auto completes are a good choice.

 

Search/Filtering (Autocomplete)

If you use a free text autocomplete it's advisable to additionally populate a backing lookup field to facilitate searching/filtering. This would also allow you to ensure that 'unresolved' values cannot be saved by using an OnSave event to check that the text field matches a hidden lookup field that is populated in the OnChange event.

Configure once, deploy everywhere (Autocomplete)

Autocomplete does not work on phone/tablet native apps yet.

Show me the Code!

I have added support for the Auto Complete SDK extensions in CRM2016 to SparkleXRM. To show a country autocomplete lookup, you'd add onload code similar to:

public static void OnLoad()
{
    Control control = Page.GetControl("dev1_countryautocomplete");
    control.AddOnKeyPress(OnCountrySearch);
}

public static void OnCountrySearch(ExecutionContext context)
{
    string searchTerm = Page.GetControl("dev1_countryautocomplete").GetValue<string>();
    string fetchXml = String.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                              <entity name='dev1_country'>
                                <attribute name='dev1_countryid' />
                                <attribute name='createdon' />
                                <attribute name='dev1_name' />
                                <attribute name='dev1_longname' />
                                <attribute name='dev1_iso' />
                                <attribute name='entityimage_url' />
                                <order attribute='dev1_name' descending='false' />
                                <filter type='and'>
                                  <condition attribute='dev1_name' operator='like' value='{0}%' />
                                </filter>
                              </entity>
                            </fetch>", searchTerm);

    OrganizationServiceProxy.BeginRetrieveMultiple(fetchXml, delegate(object state)
    {
        try
        {
            // We use an aysnc call so that the user interface isn't blocked whilst we are searching for results
            EntityCollection countries = OrganizationServiceProxy.EndRetrieveMultiple(state, typeof(Entity));
            AutocompleteResultSet results = new AutocompleteResultSet();

            // The Autocomplete can have an action button in the footer of the results flyout
            AutocompleteAction addNewAction = new AutocompleteAction();
            addNewAction.Id = "add_new";
            addNewAction.Icon = @"/_imgs/add_10.png";
            addNewAction.Label = "New";
            addNewAction.Action = delegate()
            {
                OpenEntityFormOptions windowOptions = new OpenEntityFormOptions();
                windowOptions.OpenInNewWindow = true;
                Utility.OpenEntityForm2("dev1_country", null,null, windowOptions);
            };
            results.Commands = addNewAction;
            results.Results = new List<AutocompleteResult>();
            
            // Add the results to the autocomplete parameters object
            foreach (Entity country in countries.Entities)
            {
                AutocompleteResult result = new AutocompleteResult();
                result.Id = country.Id;
                result.Icon = country.GetAttributeValueString("entityimage_url");
                result.Fields = new string[] { country.GetAttributeValueString("dev1_name"),                           
                    country.GetAttributeValueString("dev1_iso"),
                     country.GetAttributeValueString("dev1_longname")
                };
                ArrayEx.Add(results.Results, result);                    
            }
            if (results.Results.Count > 0)
            {
                // Only show the autocomplete if there are results
                context.GetEventSource().ShowAutoComplete(results);
            }
            else
            {
                // There are no results so hide the autocomplete
                context.GetEventSource().HideAutoComplete();
            }
        }
        catch(Exception ex)
        {
            Utility.AlertDialog("Could not load countries: " + ex.Message, null);
        }   
    });
}

This would result in JavaScript:

ClientHooks.Autocomplete = function ClientHooks_Autocomplete() {
}
ClientHooks.Autocomplete.onLoad = function ClientHooks_Autocomplete$onLoad() {
    var control = Xrm.Page.getControl('dev1_countryautocomplete');
    control.addOnKeyPress(ClientHooks.Autocomplete.onCountrySearch);
}
ClientHooks.Autocomplete.onCountrySearch = function ClientHooks_Autocomplete$onCountrySearch(context) {
    var searchTerm = Xrm.Page.getControl('dev1_countryautocomplete').getValue();
    var fetchXml = String.format("<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>\r\n                                      <entity name='dev1_country'>\r\n                                        <attribute name='dev1_countryid' />\r\n                                        <attribute name='createdon' />\r\n                                        <attribute name='dev1_name' />\r\n                                        <attribute name='dev1_longname' />\r\n                                        <attribute name='dev1_iso' />\r\n                                        <attribute name='entityimage_url' />\r\n                                        <order attribute='dev1_name' descending='false' />\r\n                                        <filter type='and'>\r\n                                          <condition attribute='dev1_name' operator='like' value='{0}%' />\r\n                                        </filter>\r\n                                      </entity>\r\n                                    </fetch>", searchTerm);
    Xrm.Sdk.OrganizationServiceProxy.beginRetrieveMultiple(fetchXml, function(state) {
        try {
            var countries = Xrm.Sdk.OrganizationServiceProxy.endRetrieveMultiple(state, Xrm.Sdk.Entity);
            var results = {};
            var addNewAction = {};
            addNewAction.id = 'add_new';
            addNewAction.icon = '/_imgs/add_10.png';
            addNewAction.label = 'New';
            addNewAction.action = function() {
                var windowOptions = {};
                windowOptions.openInNewWindow = true;
                Xrm.Utility.openEntityForm('dev1_country', null, null, windowOptions);
            };
            results.commands = addNewAction;
            results.results = [];
            var $enum1 = ss.IEnumerator.getEnumerator(countries.get_entities());
            while ($enum1.moveNext()) {
                var country = $enum1.current;
                var result = {};
                result.id = country.id;
                result.icon = country.getAttributeValueString('entityimage_url');
                result.fields = [ country.getAttributeValueString('dev1_name'), country.getAttributeValueString('dev1_iso'), country.getAttributeValueString('dev1_longname') ];
                Xrm.ArrayEx.add(results.results, result);
            }
            if (results.results.length > 0) {
                context.getEventSource().showAutoComplete(results);
            }
            else {
                context.getEventSource().hideAutoComplete();
            }
        }
        catch (ex) {
            Xrm.Utility.alertDialog('Could not load countries: ' + ex.message, null);
        }
    });
}

ClientHooks.Autocomplete.registerClass('ClientHooks.Autocomplete');
});

 

 

 

Posted on 6. November 2015

New version of Network Visualisations for Dynamics CRM

Many of you will have seen (and possibly used) my previous version of NetworkView. Thank you for all the feedback you've given. The top 5 points of feedback were:

  1. Can I show connections on the graph?
  2. Can I show custom entities on the graph?
  3. I have very large graphs which makes it hard to understand what is going on.
  4. I have records owned by Teams but I cannot see that on my graph.
  5. How do I know which node is the one that I've opened the graph from?

With version 1.6 I've addressed these points as follows:

Showing Connections

You can now show connections between entities! The connection roles that are discovered (e.g. Partner, Influencer etc.) are listed so that you can then highlight all the connections of a particular role on the graph by selecting the role. Loading connections may be turned on or off on a per-entity basis in the configuration.

Connections shown on graph

Activity Overflow

Since most of the time folks are only interested in the links between records via activities rather than the actual activities themselves, the graph now loads the first 10 records and then shows an overflow node that you can double click to load the next 5 activities. Under the covers, all that activities that match the FetchXml filter are loaded and the links worked out but the links are shown to and from the 'overflow' node rather than the individual activities until you expand the node.

Expanding Activities

Iteration Limit

Network graphs can often grow to a large size if you have many connections, the graph will now pause loading after a set number of iterations and ask if you want to load more. The most frequent cause of graph growth is where there are connections that are also users – for this reason, the graph will supress connections that have the same email domain as any of your users.

Loading Paused

Team Ownership

In addition to showing users who own records and participate on activities the graph now shows Teams in same list and allows highlighting records that are associated with that team.

Team Ownership on Graph

Root Node identification

You can now see which record you started the graph from highlighted in green.

Cycle Mode

When you first load the Network view, the users and connection are cycled through so that the various cohorts on your graph are highlighted for your viewing pleasure! This can be turned off by clicking the cycle button and the configuration controls if it is on by default.

Configurable

The graph is now configured using a customisable web-resource named:

dev1_/js/NetworkViewConfig_1033.js

To add your custom configuration you simply need to edit this web resource and publish the changes. You can use the following options:

iterationCountPerLoad

The number of iterations of the load mechanism before prompting the 'Load More' option.

Default:10

trace

Output trace to F12 console whilst loading. This is good for working out why your graph looks the way it does

Default: false

demoModeInitialState

Turn on the demo cycle when the graph first loads.

Default: false

connectionFetchXml

The query to fetch connections. The placeholder {0} must be inserted where the ID filter should be added.

acitvityFetchXml

The query to fetch activities. The placeholder {0} must be inserted where the ID filter should be added.

entities

Array of Entities to load (including custom entities)

Each Entity configuration has the following fields:

displayName

The name of the entity used in messages.

logicalName

The logical name of the entity to load

nameAttribute

The logical name of the attribute that holds the display name of the record.

idAttribute

The logical name of the attribute that holds the unique ID of the record

parentAttributeId

The logical name of the parent Record ID when hierarchical loading is enabled.

loadActivities

True when the graph should show activities for this entity

loadConnections

True when the graph should show connections for this entity (connections must be supported by this entity)

hierarchical

True when the parent/child relationships should be traversed using the hierarchical operators.

fetchXml

The query to get the records. The placeholder {0} must be inserted where the filter conditions should be added.

joins

Holds an array of joins to other entities.

 

Installation

To install or upgrade from the previous version :

  1. Install SparkleXRM v7.2.8 or later
  2. Install the NetworkView managed solution

The usual disclaimer & license applies.

Special thanks goes to @dynamiccrmcat for help with testing this version.

@ScottDurow