Display SharePoint Documents related to Parent on Child Record in Dynamics 365

Recently my friend Sara Lagerquist asked if there was a way to show the documents stored on SharePoint for a parent entity record on a child entity record (e.g. seeing Documents related to an Account on an Opportunity) without having to copy the documents between SharePoint sites.

This sounded like an interesting challenge so I decided to see if I could find a solution.

The Scenario

In my organisation, we have a custom entity called Engagement which is a child of the Account entity that we use as part of our project management processes. It’s not uncommon whilst on an Engagement to want to refer to a contract or other reference file which may be contained on the SharePoint site related to the Account, so finding it can involve a lot of clicking around between records, or navigating back and forth between D365 and SharePoint. The relationship between the entities and the SharePoint sites can be seen below:

In practice, this means that if I was on an Engagement record and there was a reference document in Document Library 1 on the Client Site that I wished to view, I’d have to navigate back to the Account, then look for the related Documents on the Account to see the information I want, then go back to the Engagement. It would be much simpler if I was able just to view this directly from the Engagement.

The Solution

The solution to this situation was to create a simple Flow:

This Flow is made up of two actions:

  1. Find all related Document Locations for the Parent record
  2. For each Document Location, create a copy and Set Regarding to the Child Record

The key thing to note when creating the new Document Locations is that you must ensure you copy the Relative URL, Parent Site or Location and Parent Site or Location Type from the Document Location you’re copying, and then set the Regarding Type to the entity type of the Child Entity, and set the Regarding to the record you’ve triggered the Flow from.

Once you’ve completed these steps, you’re good to go

The Output

Before Flow

As you can see from the screenshot below, on my Account record I have a Document Location called Reference Documents that has two files in it

On my Engagement record I have 3 documents in the Document Locations related to the Engagement record. If I want to access the Client Contract file from the Account I need to navigate to the Account record.

After Flow

After I run my Flow I now have a new Document Location on the Engagement record and I can see the documents from the parent Account record.

Note that the Path for the Engagement related documents is different from the Path for the Account related documents; the Document Location in D365 acts as a signpost to the actual location on SharePoint. This means that the Document only exists in one location, so any changes to it from either the Account or the Engagement will be visible from both records.


This is a simple way to improve the experience for your Users, allowing them to find the information they need quickly and easily, and reducing the amount they might need to click around your system. Let me know in the comments if you think this will be useful for you!

Filtered Lookup Field based on Linked Entity using North52

If you’ve ever had a requirement to filter lookup fields then you’ll no doubt be aware that this is possible in Dynamics 365, but that there are some limitations to the functionality.

Microsoft have done a great job of enabling out of the box filtering for simple scenarios using the “Related Records Filtering” options or by limiting the records returned using specific view(s)

To read more about the options available out of the box I’d recommend referring to Carl de Souza‘s blog post – https://carldesouza.com/filtering-lookup-fields-in-dynamics-365/

For the more developmentally minded amongst us there is also the option to use the addCustomFilter JavaScript function, more information on which can be found on the Microsoft Docs site – https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/controls/addcustomfilter

For those who are comfortable with JavaScript I’d recommend reading Aileen Gusni‘s posts about this for some tips and tricks – http://missdynamicscrm.blogspot.com/2016/09/utilize-custom-action-to-help-filtering-lookup-view.html

The Scenario

In my scenario we have a Peer Review entity to record the outputs of peer reviews carried out for activities related to an Account. The Peer Review entity has several Reviewer Roles which are lookups to the User entity. The lookups need to be filtered to only show Users who are in the Account Team. I’ve mapped the relationships between the entities below:

I tried to get this to work with the OOTB options, but found that I couldn’t quite get them to work for this scenario. I also looked at the JavaScript options but again ran into issues, primarily because I need the filtering criteria to be dynamic on each Peer Review record depending on the selected Account, whereas the JavaScript was a bit prescriptive for me. (Note: I’m not a coder, so someone cleverer than me could probably get it to do what they needed).

However, in exploring the JavaScript i stumbled upon a potential solution. You can use an “in” operator in a condition in your FetchXML to specify the list of values to be returned, like so:

<filter type='and'> 
        <condition attribute='YOUR_FIELD_HERE' operator='in'>

If I could figure out a way to make this list of values dynamic then that would solve my problem!

The Solution

To solve this issue I turned to my trusty old friend North52. I’ve written previously about using looping functions and I’ll be doing something similar here.

The first step is to get the FetchXML to get the Users from the Team, which I’ve done using advanced find to output:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="systemuser">
        <attribute name="systemuserid" />
        <link-entity name="teammembership" from="systemuserid" to="systemuserid" visible="false" intersect="true">
            <link-entity name="team" from="teamid" to="teamid" alias="ab">
                <filter type="and">
                    <condition attribute="teamid" operator="eq" value="{0}" />

As you can see above the value in the teamid condition is set to {0}, and we’ll set this dynamically in the ClientSide – Perform Action formula, which is below:



    FindRecordsFD('TeamMembers', true, SetParams([ryan_peerreview.ryan_accountid.ryan_accountteam.teamid.?])), 


      When(0), Then(SetVar('teammembers', StringFormat('<filter type="and"><condition attribute="systemuserid" operator="in"> <value>{0}</value>', CurrentRecord('systemuserid')))),

      When(RecordTotal()-1), Then(SetVarConcat('teammembers', StringFormat('<value>{0}</value></condition></filter>', CurrentRecord('systemuserid')))),

      Default(SetVarConcat('teammembers', StringFormat('<value>{0}</value>', CurrentRecord('systemuserid'))))





I’ll explain the key elements of this formula below:

SmartFlow: SmartFlow allows you to run multiple actions in one Formula

ForEachRecord: ForEachRecord is a looping function, and it iterates through the output of the FetchXML query we created above using the FindRecordsFD function and carries out the actions specified. As mentioned above, I set the value of the TeamID to be {0}, and now I use the SetParams function to define the value that will be put in here.

As we’re using ForEachRecord to loop through the records returned by the FetchXML, I will use the Case function to create a variable for the filter that I will be putting on the lookup field using the SetVar/SetVarConcat functions

The Case function works by splitting the Filter FetchXML into 3 parts:

  1. The Opening section, which includes the open tags for the Filter and Condition, and the first value returned from the FindRecordsFD function
  2. The Looping section, for all the values between the first and last values returned from the FindRecordsFD function
  3. The Closing section, which includes the closing tags for the Filter and Condition, and the last value returned from the FindRecordsFD function

To make this work with the Case function, we use the RecordIndex function, which contains an integer with the current index number of the loop, so the Case function can be described in plain English as:

WHEN we are on the first loop, THEN create a variable with the opening section of the Filter FetchXML;
WHEN we are on the last loop, THEN concatenate the variable with the closing section of the Filter FetchXML;
OTHERWISE if we are not on the First or Last loops, THEN concatenate the variable with another value

When we have created the Filter FetchXML we use the AddPreFilterLookup function to add the filter to the selected field.

Once we’ve done all of this, the field will show only the people who are in the Team related to the Account on the Peer Review record:


I think this is a good method of dynamically altering the available options in a lookup field, and I can envision a number of useful scenarios for this functionality, but please leave a comment below or reach out to me on social media with your thoughts.

Update a User’s Business Unit and retain their Security Roles

If you have ever had to change the Business Unit of a User then you will know that the change causes their security roles to be dropped, so you need to ensure the security roles are reassigned. This is mildly frustrating when you have one or two Users to update, but what happens when you have to update all Users in your organisation?

I recently encountered a scenario where the organisation needed to completely remodel their Business Unit structure and move the Users to the new Business Units; the prospect of doing this manually filled me with dread.

I’ve done a bit of research and seen methods that others have used (see CRM Tip of the Day 1134 for a good example), but I’m a massive North52 nerd so I felt sure there would be a way I could achieve this dynamically using this tool.

The Scenario

In the scenario, I had ~800 Users in 4 existing Business Units, while the new Business Unit structure was comprised of 7 Business Units. This was further complicated because the Users could have anywhere between 3 and 14 Security Roles (don’t ask…). We needed to be able to assign them to the new BU and ensure their Security Roles were reassigned when their BU was changed.

The Setup

The first step in setting up was to add an Option Set field to the User entity to hold the name of the Business Unit we would be moving the User to.

The next step was to create a FetchXML query to get the Security Roles assigned to a User, which would be called from within the North52 formula. The FetchXML expression I used is:

 <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="role">
    <attribute name="name" />
    <attribute name="businessunitid" />
    <attribute name="roleid" />
    <order attribute="name" descending="false" />
    <link-entity name="systemuserroles" from="roleid" to="roleid" visible="false" intersect="true">
      <link-entity name="systemuser" from="systemuserid" to="systemuserid" alias="ad">
        <filter type="and">
          <condition attribute="systemuserid" operator="eq" value="@systemuserid@" />


The important thing to note from the FetchXML expression is that the value in the condition searching for the systemuserid is @systemuserid@. North52 uses the @…@ tags to find the value in the field with the schema name that is enclosed in the tags, so it will substitute the GUID of the User on each record this is triggered on.

The Formula

The North52 Formula is actually relatively straightforward:




      When( 150000000), Then (FindValueQuickId('businessunit','Actuarial & Benefits')),

      When( 100000000), Then (FindValueQuickId('businessunit','Business Support Unit')),

      When( 100000001), Then (FindValueQuickId('businessunit','Commercial Group')),

      When( 100000002), Then (FindValueQuickId('businessunit','Insights & Analytics')),

      When( 150000001), Then (FindValueQuickId('businessunit','Investment')),

      When( 100000003), Then (FindValueQuickId('businessunit','Life & Financial Services')),

      When( 150000003), Then (FindValueQuickId('businessunit','Third Party Administration')),

      Default(FindValueQuickId('businessunit','Business Support Unit'))




    SetVar('Role' + recordIndex(), FindValue('role','roleid',currentrecord('roleid'),'name','?','true')),

    SetVar('Roles', RecordTotal())

    SetAttributeLookup('businessunitid', 'businessunit', GetVar('BusUnitID'))



        SetFindAnd(GetVar('BusUnitID'),GetVar('Role' + DoLoopIndex())),



I’ll try to explain some of the key elements of this N52 Formula below:

Case: The Case function is used to check the value in the new Business Unit Option Set field, and it sets a variable to hold the ID of the new Business Unit (‘BusUnitID’) so we can use it later in the formula

ForEachRecord: ForEachRecord is a looping function, and it iterates through the output of the FetchXML query we created above using the FindRecordsFD function and carries out the actions specified. In this instance I have two actions:

  1. Create a Variable to hold the Name of the Security Role (‘Role’). The RecordIndex() function outputs an integer with the number of the current loop, so I’ve used it to create iterative Variables (i.e. each loop of the ForEachRecord function will create a new Variable (Role1, Role2, Role3, etc.)
  2. Create a Variable (‘Roles’)to hold the Total Number of loops we’re running, which will be used later in the Formula

DoLoop: DoLoop is another looping function that allows us build an iterative function to complete actions. We use the variable that we created in the ForEachRecord function step to hold the total number of loops (‘Roles’) to specify the number of iterations of the DoLoop function to carry out.

For each role we will carry out an AssociateEntities function to associate the User to the Security Role. To find the right Security Role(s) to associate we do a FindValue function and use SetFindAnd to enable us to specify multiple input parameters that must be met. In this case we want to find Security Roles using the following criteria:

  1. The businessunitid of the new Business Unit we’ve updated on the User Record, using the ‘BusUnitID’ variable we set at the start of the formula
  2. The Security Role name, which we retrieve by getting the Variable using the DoLoopIndex() function, which outputs an integer with the current loop number, identical to the RecordIndex() function we used to set the Variable name in the ForEachRecords function.


This relatively straightforward formula allowed me to dynamically update all of my Users to their new Business Units and to ensure their security roles were applied properly. It saved me a huge amount of time over a manual approach, and hopefully has demonstrated some of the capabilities of the North52 solution.

I am sure I will be able to reuse the functionality of setting and getting a dynamic number of variables using ForEachRecord and DoLoop functions in this way, but I’d love to hear from others if they can think of any other scenarios in which this could be applied, so please feel free to reach out!

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 LattimerString Workflow Utilities
    1. Regex Match
    2. Regex Replace with Space
  2. Alex ShlegaTCS 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:


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


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

Custom Rollup fields

Microsoft introduced Rollup Fields to the Dynamics CRM platform in 2015, and this added significant positive functionality to the platform. Rollup fields contain aggregated values that have been calculated from child records for a parent record. Typical examples of this would be total value of Open Opportunities for an Account, or the total number of Emails received for a Contact.

Whilst there are benefits to Rollup Fields, there are also some “gotchas” to be aware of before you implement them in your environment. Jukka Niiranen has outlined some of these on his blog, and some of the other considerations are outlined on the Microsoft site. For me, the two main considerations are:

  1. Rollup fields are asynchronous, so they don’t update in real-time
  2. You can only have a maximum of 100 rollup fields across your organisation, and 10 per entity

With these drawbacks in mind, you may wish to explore alternatives to creating rollup fields.

The Scenario

In my environment, we define our Opportunities in two ways:

  1. Is the Opportunity from a New Client, or is it Account Development with an existing Client?
  2. Which Segment of our business is the Opportunity associated with?

In order to capture this information, we have a custom entity for the Segments with a lookup field on the Opportunity, and an Option Set field for the Opportunity Type.

For each Segment, we wanted to be able to see:

  1. What is the total value of the Estimated Revenue of All Open Opportunities?
  2. What is the total value of the Weighted Revenue of All Open Opportunities?
  3. How many Open Opportunities are there?
  4. What is the total value of the Estimated Revenue of all Open Account Development Opportunities ?
  5. What is the total value of the Weighted Revenue of all Open Account Development Opportunities?
  6. How many Account Development Open Opportunities are there?
  7. What is the total value of the Estimated Revenue of all New Client Open Opportunities?
  8. What is the total value of the Weighted Revenue of all New Client Open Opportunities?
  9. How many New Client Open Opportunities are there?

This is easily achievable using standard Rollup Fields, however it would require 9 rollup fields which is a significant proportion of the available rollup fields on the entity and the organisation.

The Solution

One of my favourite things about the Dynamics community is the amount of free content that contributors have made available to help others solve problems. In this case, I’m going to be using the Dynamics 365 Workflow Tools created by Demian Raschkovan

Dynamics 365 Workflow Tools – List of Functions

In this set of Custom Workflow Activities there is an option called Rollup Functions that we will be using. This allows you to use a FetchXML query to define the records we’ll be summing up.

Setting up the Entity

On the entity that you’d like to add the “rollup” fields to (in this case the Segment entity), create Simple fields to capture the data you’ll be summarising. In my case, I have added six Currency fields (for the Estimated Revenue and Weighted Revenue calculations) and three Whole Number fields (for the Quantity calculations)

Creating the Workflow

Create a real-time workflow on the entity you’ll be summarising the data from (in this case the Opportunity entity), and set the trigger to run on Create, or on Update of the Segment, Estimated Revenue or Probability fields.

The first step is to get the GUID of the referenced Segment on the Opportunity, and Demian has a function for that too – Get Record ID. We need the GUID to reference in the FetchXML we’ll be using.

Note: in the description for the Rollup Functions activity it suggests you can use a {PARENT_GUID} tag to pass in dynamic data, but I couldn’t get it to work consistently, so I use the Get Record ID activity to bypass that requirement.

The next step is to add a Rollup Functions step to your workflow, and then define the FetchXML you’ll be using. There are two methods to get your FetchXML, either using the Advanced Find, or using the FetchXML Builder by Jonas Rapp.

I want to find all Open Opportunities against a specific segment so I add two conditions, and ensure the field I will be aggregating is first in the list, which gives me a FetchXML query that looks like this:

We’ll paste this into the Workflow Step properties for the Fetch XML and replace the GUID with the output from the Get Record ID step

We repeat this step for each FetchXML query we want to run. In my case, I’ve ended up with six Rollup Function steps in my workflow. The Rollup Functions can output the Average, Count, Max, Min or Sum of the query.

The final step of the Workflow is to update the Segment and update the values with the Rollup Function outputs

The workflow is now ready and will provide you with real-time rollup functions to count the values in your pipeline.

Updating the Previous Segment

Ah, what’s that I hear you say, we’re not done yet? Well, you’re right of course. The above is all well and good, but changing the Segment means the Opportunity is double-counted in the pipeline analysis, unless we change the previous segment too, so how do we do that?

You need to create a second lookup to the Segment entity on the Opportunity, to hold the Previous Segment. Then create another real-time workflow on the Opportunity that is triggered Before the Segment field changes, and copy the value in the Segment field to the Previous Segment, and then trigger a Child Workflow to update the Previous Segment. The Child Workflow will be a copy of the one we created above, however it will need to be a background workflow to ensure it captures the data correctly.


This offers a viable alternative to the OOTB rollup fields. It should be noted that using real-time workflows can add performance overhead to your environment, so consider what is best for your particular deployment. As ever, using third-party tools is at your own risk, so ensure it has been tested thoroughly before you consider deploying it.