Overview

In this article we will examine a solution we created for a customer who wanted to do a linked entity in an AddPrefilterLookup. Out of the box Microsoft Dynamics 365 does not support linked entities when prefiltering a lookup.

The customer scenario was that they have multiple accounts for various companies, for example Microsoft in Ireland, Microsoft in France etc, so when they are creating an Opportunity they must specify the country first, and then this will execute AddPrefilterlookup so that only Accounts in that country can be selected.

To do this, when the country is selected, we will execute the prefilter by searching for Accounts where the primary contact is in the same country.

North52 Decision Suite

The North52 Decision Suite solution works like this:

• A Formula is set up to run in the OnLoad event of the form and the OnChange of the Opportunity Country field
  • Checks if the Opportunity Country contains data
    • If yes, it runs a FetchXML query to gather all relevant accounts
    • Builds up the FetchXML including the returned account ID's
    • Applies that FetchXML into the the AddPreFilterLookup() function on the Parent Account field
    • If no, removes the AddPreFilterLookup() from the Parent Account field

North52 Formula 

• Create a new formula, setting the following values in the Formula Guide:
  • Source Entity set to Opportunity
  • Set Formula Type to ClientSide - Perform Action
  • Select the Decision Table editor
• Change the Name of the formula to Opportunity - PreFilter Parent Account by Country
• Click Save
• Press F4 on your keyboard to open the Advanced Mode of the Decision Table
  • This will show you 3 addition sheets:
    • Global Calculations
    • Global Actions
    • Global FetchXML
• In Global FetchXML sheet add your FetchXML that will create your list of records:
  • In cell A2 enter ApplicableAccounts
  • In cell B2 enter the following FetchXML:

    '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="account">
        <attribute name="accountid" />
        <link-entity name="contact" from="contactid" to="primarycontactid" link-type="inner" alias="ab">
          <filter type="and">
            <condition attribute="address1_country" operator="eq" value="{0}" />
          </filter>
        </link-entity>
      </entity>
    </fetch>'

• In the DecisionTable sheet add the following:
  • Click on cell A2, and the click on the Opportunity Country field listed in the Source Tab on the left hand side
  • Click on cell A4 and CTRL-click on the ContainsData function in the Functions Tab
  • Right-click on column C and select Insert > Insert Inline Calculation to create a new Inline calculation column at position B
    • In cell B2 set the name to be ApplicableAccounts 
    • Select cell B4, click the Plus (+) Icon to expand the cell and enter the following:

      SmartFlow(
        SetVar('FetchXML', '<filter type="and"><condition attribute="accountid" operator="in">'),
        
        ForEachRecord(
          FindRecordsFD('ApplicableAccounts','true',SetParams([opportunity.cr8d1_opportunitycountryname])),
          SetVarConcat('FetchXML', '<value>' + CurrentRecord('accountid') + '</value>') 
        ),
      
        SetVarConcat('FetchXML', '</condition></filter>')
      )

  • In cell C4, add in the AddPreFilterLookup() function:

    AddPrefilterLookup('parentaccountid','oQi', GetVar('ApplicableAccounts'), 'account')

  • In cell C5, enter:

    RemovePrefilterLookup('parentaccountid')

DecisionTable Sheet

Global Fetch-XML Sheet 

Testing

When we open the Opportunity and set the value to France, only 1 account is displayed: