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'>
          <value>{YOUR_GUID_HERE1}</value>
          <value>{YOUR_GUID_HERE2}</value>
          <value>{YOUR_GUID_HERE3}</value>
        </condition>
</filter>

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}" />
                </filter>
            </link-entity>
        </link-entity>
    </entity>
</fetch>

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:

Smartflow(

  ForEachRecord(

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

    Case(RecordIndex(),

      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'))))

    )

  ),

  AddPreFilterLookup('ryan_primaryreviewerid', 
    'q1z', 
    GetVar('teammembers'), 
    'systemuser')

)

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:

Conclusions

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.