Setting a Lookup from a Workflow

One of the limitations of the workflow engine that I have found frustrating for a long time is the inability to dynamically set lookup fields based on the output of a FetchXML query, however I no longer have to worry as Alex Shlega has provided the answer to my problems with his TCS Tools Solution

I’ve used this tool for a few solutions in my environment and, after discussion with my good friend Megan Walker I realised it might be good to share a sample scenario.

The Scenario

There is a web form that is used by visitors to a website to submit queries.  The queries are added to CRM and are all FROM no-reply@company.com.  Within the body of the email is an email address for the submitter, and we need to extract the email address, find the related Contact and set a Lookup field (Regarding) to link the Contact to the Email.

The submitted email body has the following format:

[title] [Mr]
[first name] [Ben]
[last name] [Willer]
[email] [benwil@alliedholdingcompany.co.uk]
[phone] []
[address1] [Mounters]
[address2] [Marnhull]
[address3] [Sturminster Newton]
[address4] [Dorset]
[postcode] [DT10 1NR]
[how did you hear about us?] [Internet search]

 

The Solution

First things first, you will need to install the TCS Tools solution in your environment.  The link above will take you to Alex’s website to download the solution.  As ever, this is a free third-party tool, so install at your own risk.

Next, you will need to add a Single Line of Text field to your email entity to store the email address we’re going to extract from the body of the email above.  Rather imaginatively, I’ve named mine new_extractedemail.  We’ll need this schema name in the next step.

Create Lookup Configuration

Navigate to the TCS Lookup Configuration entity and create a new lookup configuration as follows:

TCS Lookup Configuration

The Entity Attribute should be the schema name of the lookup field you wish to set with your workflow.  In my case, I’m going to be setting the Regarding field on the Email, so I’ll be using regardingobjectid.

Next we need to create a Fetch XML expression to use in the Lookup Configuation.  The easiest way to do this is to create an advanced find, then download the Fetch XML.  For this one, I’m looking for a Contact where the Email Address equals the submitted email address, so my Advanced Find looks like this:

Create Fetch XML

Note: as you can see above, I’ve set the Email to equal #new_extractedemail#.  The hashtags are used by the TCS Tools solution to replace this value dynamically.

The Fetch XML expression will look as follows:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”contact”>
<attribute name=”fullname” />
<attribute name=”telephone1″ />
<attribute name=”contactid” />
<order attribute=”fullname” descending=”false” />
<filter type=”and”>
<condition attribute=”emailaddress1″ operator=”eq” value=”#new_extractedemail#” />
</filter>
</entity>
</fetch>

Extract the Email Address

In order to be able to use the email address that was submitted above, we need to extract it from the the body.  I use Jason Lattimer’s Regex Extract step from his String Workflow Utilities workflow solution.  In order to extract the email address we need to do two Regex Extract steps, as follows:

Step 1: Extract Email Address from Body

Regex 1

The Regex Pattern in this step is (?<=\[email\] )([\s\S]*)(?=\[phone\] )

The pattern essentially looks for any characters in between the [email] and [phone] sections in the email body, and therefore the output from the email above is [benwil@alliedholdingcompany.co.uk].

In order to be able to use this in my workflow, we need to remove the square brackets, so I do another Regex Extract on the output of this step.

Step 2: Extract Email Address from within Square Brackets:

Regex 2

The Regex for this step is (?<=\[)([\s\S]*)(?=\]).  This pattern looks for any content in between the opening square bracket and the closing square bracket, so the output now is benwil@alliedholdingcompany.co.uk.

Note: I am not a Regex expert, but I have found Regex 101 invaluable in learning and testing my expressions, because it lets you see how the expression works and explains what each element means

Once we have carried out the Regex steps, we update the new_extractedemail field with the output of the second step:

Update Extracted Email Step

Run the Lookup Setter

Now that we have the email address extracted and available on the Email entity, the last step is to run the TCS Lookup Configuration we created above to set the lookup:

Set Lookup Configuration

 

The final workflow should look a bit like this:

Workflow

 

Conclusion

This functionality is a really powerful addition to the workflow engine, and opens up a whole raft of advanced possibilities for CRM administrators to create workflows to solve complex problems.  I’ve used this internally to map Excluded Emails from ClickDimensions to error codes for the purposes of reporting, and I’m working on additional scenarios that we can use it for.

 

CRM Power Pane – Review

One of my favourite tools in my CRM Administrator Toolkit (*not an actual thing) is the Dynamics CRM Power Pane extension for Google Chrome and Mozilla Firefox.

The Power Pane add-on has been developed to help developers, testers and power users to accomplish tasks that they may require in their day-to-day role.  Installation of the add-on is as simple as installing the extension in your browser – no need to install any solutions.

Once you have the extension installed an orange lightning bolt icon will appear in the top-left corner of your Nav bar whenever you open CRM, and clicking this will enable the actions for you.

I should caveat at the top that this tool is very much not for end-users, and I’d caution against even making them aware that it exists.  It can undo the security settings you have put in place, and render your customisations meaningless.

With that out of the way, let’s have a look at what it does.   The CRM Power-Pane add-on is comprised of 3 sets of tools:

  • Record Actions
  • Form Actions
  • Navigation Tools

I’ll give an overview of each of these below:

Record Actions

There are 5 options under the Record Actions section

  1. Entity Info – opens a pop-up window with the Entity logical name and the Entity Type Code
  2. Record ID – opens a pop-up window with the GUID of the current record
  3. Record URL – opens a pop-up window with the direct URL of the current record
  4. Clone Record – creates an exact clone of the current record
  5. Record Properties – opens the record properties window

I particularly like the Record URL options – no more clicking “Email a Link” and then copying the link so I can send it to someone in Skype.  I also get a lot of use out of the Record ID option, it’s come in handy more times than I can count.

 

Form Actions

The Form Actions are where this tool really comes into it’s own, and where I think most other developers/administrators will get use out of it.  There are 12 options under Form Actions:

  1. Enable All Fields – makes all read-only fields editable
  2. Show Hidden Fields – unhides any fields that have been set as hidden
  3. Disable Field Requirement – removes any business recommended or business required options on fields, enabling you to save it without filling them in
  4. Schema Names as Label – changes the field labels to show the logical/schema name
  5. Scheme Name Copy Mode – enables the option to copy the schema name for a field by clicking on the field name
  6. Show Optionset Values – prefixes the optionset options with their value
  7. Show Field Value – opens a pop-up window to allow you to input the schema name for a field, and returns the value in the field, and the field type.  Depending on the type of field additional values will also be returned, e.g. for a lookup field you will get the text value, the record GUID, the lookup record Entity Name and Entity Type Code.
  8. Find Field in Form – opens a pop-up window where you can input the schema name for a field, and then moves the focus to that field on the form and highlights the field
  9. Highlight Dirty Fields – adds a highlight to any fields that have been changed since the form was loaded
  10. Refresh Ribbon – refreshes the command ribbon on the form.  This can be useful if you’re testing the visibility of buttons that appear/disappear based on field values
  11. Refresh Form – refreshes the CRM form, without the need to refresh the whole window.

I use a lot of these tools on almost a daily basis, in particular the Schema Names as Label  and Scheme Name Copy Mode.  If you’re writing any code then I’m sure you’ll find these useful too.  Similarly, being able to unhide and unlock fields has saved me numerous times when I’ve been carrying out testing.

As I said at the start, if your Users find this tool it could cause no end of headaches for you, but it also serves as a stark reminder that CRM is just a series of web pages, so if you want a field to be completely secure don’t put it on a Form that is accessible by Users. Reece Campbell wrote a great blog about CRM Forms and security recently, so I’d recommend it for more reading.

Navigations

The last set of tools in CRM Power Pane are the Navigations tools. There are 6 tools here:

  1. Go to record by ID – enables you to open any record by specifying the entity name and the record GUID
  2. Entity Editor – makes it really easy to open the Entity customisation in the default solution.  This defaults to the entity you’re currently in, but you can specify which one you’d like to open
  3.  CRM Diagnostics – opens the CRM diagnostics page to allow you to evaluate network performance
  4. Performance Center – opens the CRM Performance Center to allow you to evaluate the performance of form loading in CRM
  5. Mobile Express – opens the Mobile Express version of your CRM environment
  6. Mobile Client – opens the mobile version of your CRM environment

Conclusion

The Dynamics CRM Power Pane is an incredible useful tool, and I find myself using it pretty much every day.  The array of tools it offers are varied, and they deliver some much needed added abilities for me as a system developer/administrator.  I’d recommend installing it and seeing how you get on with it, just remember not to let your Users know about it.

Some of you may be aware of another Chrome extension called Level Up for Dynamics CRM/365, developed by Natraj Yegnaraman.  Level Up does a number of similar funcitons to CRM Power Pane, and has some additional options.  I have both installed in my browser, and have used them both extensively.  If you’d like to read more about Level Up, Kylie Kiser recently wrote a review of it and I’d recommend you have a look at her blog

Advanced Multiselect for Dynamics – Review

Following on from my last post about Multi Select Checkboxes, I have been continuing research to try and find a suitable solution for adding multiselect options to my CRM 2016 environment.  I really like the checkbox tiles, but the downside to them is the need to create fields, update forms and update web resource parameters when they need to change, which creates more work for the administrator.  I was therefore looking for a solution with less administrative overhead for situations in which the list is likely to change more often, as well as to offer additional user experience benefits.

My search led me to the Advanced Multiselect for Dynamics solution by Pavel Khorozhansky.  This solution is built on the use of N:N relationships, and allows for quick associate/dissociate of records.  In Pavel’s own words:

You most likely are interested in this solution if:

  • you have Many-To-Many (N:N) relationships and would like to associate / disassociate related records in a quick, convenient and flexible way using a set of appropriate checkboxes on a form
  • you are using either Dynamics CRM 2016 Update 1 (8.1) or Dynamics 365 (8.2) version and you would like to have multi select / multicheckbox functionality on a form and don’t want to create a lot of dedicated checkbox attributes for each an option
  • you are using the new version of Dynamics 365 (9.0) (where a new multi-select functionality introduced out-of-box) and find this out-of-box feature does not suit your needs (for example, you have to make changes in metadata each time you need to add/remove list options (instead of giving some users an ability to easily maintain the lists), you cannot support ‘obsolete’ options, and so forth).

There is an extensive wiki on the GitHub repository covering installation and configuration, so I won’t repeat it here.  I found it incredibly easy to install and get using following the instructions, so I’m sure anyone else will too

What I like

Easy Installation

The solution is really easy to install, and the configuration wizard that Pavel has built into the solution makes it really easy to get up and running.  It is worth noting that there is a small element of the solution which would be considered unsupported, so this should be borne in mind if you wish to install this solution in your production environment.

Better User Experience

The solution works with any custom N:N relationship, including self-referential relationships, and it is significant improvement to the User Experience when associating records.  With the out-of-the-box approach using sub-grids there are up to six steps to follow to associate a record:

Add records to Sub-grid

In comparison, with the Advanced Multiselect solution it’s simply a case of selecting the appropriate records from the available list:

Advanced Multiselect

From a User Experience point of view, this is a much more efficient and elegant solution.; it minimises the amount of mouse clicks that are required and makes it really easy to train new users.

I also like that it shows the unselected records, so that system users are provided with a visual reference to decide if perhaps one of the unselected choices could also be selected.

Multiple Selection Types

With the solution you get the options of a few different types of selectors.  You can select from one, two, three or four columns of checkboxes, which look as you’d expect:

two_columns

Where I think the solution excels however is in the following two options:

One Column with custom descriptions, which is a great way to add context to the options.  I like the ability to add a custom description and can envisage numerous advantages to this solution

One_Column_with_descriptions

Selectize, which works kind of like a Tagging option. This is particularly useful if you have a large list of options:

Selectize

There is also the possibility to create your own template styles, if you’re familiar with HTML/CSS and knockout.js or selectize.js.  This add a massive amount of extensibility to the solution.

Conclusion

Overall I would say Advanced Multiselect for Dynamics is an excellent solution, and a very handy addition to my toolbox of solutions.  I have barely scratched the surface of the solution with this review, but I believe it will help deliver improvements to the user interface in your CRM system and therefore make the user experience more enjoyable.  I’ve demoed the solution to selected users in my organisation and the response has been overwhelmingly positive.