Postcode Region Mapping via Workflow

I recently delivered a session at Dynamics 365 Saturday Scotland covering some advanced functionality you can implement in your Dynamics 365 environment using free custom workflow activities.

To read my thoughts on #D365SatSco and how amazing it was, see the article I posted on LinkedIn

One of the scenarios I covered in my session was looking at how we can carry out regional analysis of our account using workflows, and I’ve outlined my solution below.

The Scenario

For this scenario I wanted to be able to check if the postcode that had been entered for the address on an Account was valid, and if so I wanted to be able to extract the outward code and use this to map the Account to it’s postcode area, locale, sub-region and region.

The Setup

For this scenario I added the following to my environment:

• A new Entity called Region Mapping, containing
  • An Option Set with 4 options:
    1. Postcode Area
    2. Locale
    3. Sub-Region
    4. Region
  • A hierarchical Parent lookup field
• Added fields to the Account entity
  • A single line of text field called “Extracted Postcode”
  • 4 lookup fields to the Region Mapping entity (one for each Option in the Option Set

Once this is all created, I imported my dataset, which I derived from data sources from the Office for National Statistics. You can download a copy of my dataset below:

The Workflow

To create my workflow I used tools from two different custom workflow assemblies:

1. Jason Lattimer – String Workflow Utilities
   1. Regex Match
   2. Regex Replace with Space
2. Alex Shlega – TCS Tools
   1. Attribute Setter

Step 1 – Postcode Verification

In the UK, all postcodes follow standard formats, so it makes it relatively easy to determine if the postcode is valid or not. For my workflow I’m using the Regex Match step, so I need a Regex pattern to use. I wanted to be able to separate out the outward and inward sections of the postcode, so the expression I ended up with is:

((?:(?:gir)|(?:[a-pr-uwyz])(?:(?:[0-9]?)|(?:[a-hk-y][0-9]?)))) ?([0-9][abd-hjlnp-uw-z]{2})

I am not an expert at Regex, but I am very good at googling! I added this pattern to Regex101, which does a great job of explaining the component parts if you’d like to understand it further

The output from a Regex Match step will be True or False. If it returned false you could use a cancel step in your real-time workflow to display an error message to your user informing them that their Postcode was not valid

Step 2 – Extract Postcode Area

As I mentioned above all UK Postcodes follow standard formats, and this particularly true for the second part of the postcode which is always one number followed by two letters. To carry out my region mapping I needed to be able to extract the first part of the postcode, so I used the Regex Replace with Space step to replace the second part of the postcode with 0 spaces, in effect just deleting it.

From my Regex pattern in the previous step, I used the second capturing group to match with the second part of the postcode:

?([0-9][abd-hjlnp-uw-z]{2})

The output from this step leaves us with the first part of the postcode, so we update the Extracted Postcode field on the Account entity with this, and we’ll use that in the next step.

Step 3 – Run the Attribute Setter

I’ve previously discussed Alex Shlega’s Attribute Setter, and it’s one of my favourite custom workflow activities. It’s super easy to work with and allows you to dynamically set lookup fields from within your workflow.

The first thing to do is to create a Lookup Configuration with a FetchXML query to find the record you will be setting in the lookup field. For mine, I’ll be looking for the Region Mapping record that matches the extracted postcode. As I’ve discussed before, the magic in the Lookup Configuration is the ability to dynamically pass values to the FetchXML query by putting the schema name of the field that contains the value inside a pair of # marks.

The key part of the FetchXML query abouve is the second condition:

<condition attribute=”ryan_name” operator=”eq” value=”#ryan_extractedpostcode#” />

By putting the schema name of my Extracted Postcode field, whatever value is in there will be added to my query when it is run by the workflow. The Attribute Setter will output the GUID of the Region Mapping record (i.e. the Fetch Result Attribute) and it will set it in the Postcode Area lookup field on the Account (i.e. the Entity Attribute)

Step 4 – Update Account

The final step, now that the Postcode Area has been updated, is to run a child workflow to update the Locale, Sub-Reigon and Region fields. For each of these fields, we’ll run an Update Record step, and select the Parent of the predecessor (i.e. for the Locale we will find the Parent of the Postcode Area field value

Conclusion

This is a relatively simple approach to allow you to carry out regional segmentation of your Accounts, which can be used for marketing purposes or for reporting.

If you’ve found it useful, or if you have any other ideas then please reach out to me on Twitter or LinkedIn