Extending URS Schedule Board - Universal FetchXml - Filter By Territory

Universal Resource Scheduling (URS) Schedule Board has many great extensibility features.  While there are many knobs, switches, and levers to configure Schedule Board to suit our specific needs, we can open up the panels and play around with the wires, fuses, and printed circuit boards (PCBs) to fine-tune the Schedule Board.  Following are some of the advanced features provided by the Microsoft URS team.

  1. Filter Layout
  2. Resource Cell Template
  3. Retrieve Resources Query
  4. Booking Template
  5. Schedule Assistant Filter Layout
  6. Schedule Assistant Resource Cell Template
  7. Schedule Assistant Retrieve Resources Query
  8. Schedule Assistant Retrieve Constraints Query

In this article, I will explain a scenario where we can modify the Filter Layout and Retrieve Resources Query to achieve the required behaviour.

If you would like to know more about the main components of the Schedule Board, read this article first – http://dyn365apps.com/2019/07/12/extending-urs-schedule-board-components-of-the-schedule-board/

Filter Layout

Filter Layout is a feature we can use to configure fields on the Filter panel.

Retrieve Resources Query

Retrieve Resources Query is a feature we can use to customise the query used to retrieve resources and their bookings onto the Schedule Board.  The query uses UFX (Universal FetchXml), an advanced query language that allows us to query data using dynamic FetchXML.

Scenario

We need to create a new Tab on the Schedule Board to be used by schedulers in Melbourne, Australia.  We have created a number of records for the Territory entity (e.g. Melbourne, Sydney).  Resource Territory records are created to associate Territories to Resources.  The new Tab “Melbourne” should only show resources who are associated with “Melbourne” territory by default.

UFX - Resource Territory

 

UFX - Resource Territories

Step 1 – Create a new Tab

Click on the + button on the right-hand side of the Tabs.

UFX - Create new tab

Enter a name for the new Tab and select Everyone from the Shared With drop-down list.

UFX - Add tab

Step 2 – Create a new Retrieve Resource Query

Scroll down to Other Settings section.  Click on the gear button on the right-hand side of the Retrieve Resources Query.

UFX - Create new Retrieve Resources Query 1

The default Retrieve Resources Query is displayed.  It is recommended to NOT modify this directly.  Instead, click on the Save As button and create a new query.  Enter a suitable name and click Save.  Click Add.

UFX - Create new Retrieve Resources Query 2

Please note, behind the scenes, the system creates a new record of type “Configuration”.

Step 3 – Generate the FetchXml using Advanced Find

Open Advanced Find and configure the required query.  In this example, we need to find all Bookable Resources, where the associated Resource Territory record’s Territory field is equal to “Melbourne”.  Click on the Download Fetch XML button to download the query.  Click on the Results query to make sure we get the right results.

UFX - Advanced Find

Step 4 – Update the Retrieve Resources Query

The downloaded query is as follows.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="bookableresource">
    <attribute name="name" />
    <attribute name="createdon" />
    <attribute name="resourcetype" />
    <attribute name="bookableresourceid" />
    <order attribute="name" descending="false" />
    <link-entity name="msdyn_resourceterritory" from="msdyn_resource" to="bookableresourceid" link-type="inner" alias="ab">
      <filter type="and">
        <condition attribute="msdyn_territory" operator="eq" uiname="Melbourne" uitype="territory" value="{1B9319B2-3C10-E811-A82C-000D3AE0A7F8}" />
      </filter>
    </link-entity>
  </entity>
</fetch>

The segment we are interested in is as follows.

<link-entity name="msdyn_resourceterritory" from="msdyn_resource" to="bookableresourceid" link-type="inner" alias="ab">
  <filter type="and">
	<condition attribute="msdyn_territory" operator="eq" uiname="Melbourne" uitype="territory" value="{1B9319B2-3C10-E811-A82C-000D3AE0A7F8}" />
  </filter>
</link-entity>

Open the Retrieve Resource Query.  The section we are interested in is as follows.

<!-- Territory join -->
<link-entity ufx:if="$input/Territories/bag | $input/UnspecifiedTerritory[. = 'true']" name="msdyn_resourceterritory" from="msdyn_resource" to="bookableresourceid" alias="territory" link-type="outer">
  <filter>
	<condition attribute="statecode" operator="eq" value="0" />
	<condition attribute="msdyn_territory" operator="not-null" />
  </filter>
</link-entity>

<!-- Territory filter --> 
<filter type="or">
  <condition ufx:if="$input/UnspecifiedTerritory[. = 'true']" entityname="territory" attribute="msdyn_territory" operator="null" />

  <condition ufx:if="$input/Territories/bag" entityname="territory" attribute="msdyn_territory" operator="in">
	<ufx:apply select="$input/Territories/bag">
	  <value>
		<ufx:value select="@ufx-id" />
	  </value>
	</ufx:apply>
  </condition>
</filter>

Since we are not interested in using the Filter panel’s territory input to filter by territory, we can safely remove this section and replace it with the segment generated by the Advanced Find.  The final complete query is as follows.

<?xml version="1.0" encoding="utf-8" ?>
<bag xmlns:ufx="http://schemas.microsoft.com/dynamics/2017/universalfetchxml">
  <Resources ufx:source="fetch">
    <fetch mapping="logical" aggregate="true">
      <entity name="bookableresource">
        <attribute name="bookableresourceid" alias="bookableresourceid" groupby="true"/>
        <attribute name="name" alias="name" groupby="true"/>
        <attribute name="calendarid" alias="calendarid" groupby="true"/>
        <attribute name="resourcetype" alias="resourcetype" groupby="true"/>
        <attribute name="msdyn_startlocation" alias="startlocation" groupby="true"/>
        <!-- Let the database sort by name, unless we have characteristics - in which case we'll sort by the count of characteristics -->
        <order ufx:if="not($input/Characteristics/bag/characteristic)" alias="name" />
        
        <!-- Characteristic join -->
        <link-entity name="bookableresourcecharacteristic" from="resource" to="bookableresourceid" link-type="inner" ufx:if="$input/Characteristics/bag/characteristic">
          <attribute name="characteristic" aggregate="countcolumn" alias="characteristiccount" distinct="true" />
          <order alias="characteristiccount" descending="true" />

          <link-entity name="ratingvalue" from="ratingvalueid" to="ratingvalue" link-type="outer">
            <attribute name="value" aggregate="sum" alias="proficiencyscore" distinct="true" />
            <order alias="proficiencyscore" descending="true" />
          </link-entity>

          <filter>
            <condition attribute="statecode" operator="eq" value="0" />
          </filter>
        </link-entity>

        <!-- Characteristic filter -->
        <filter type="or" ufx:if="$input/Characteristics/bag/characteristic">
          <ufx:apply select="$input/Characteristics/bag">
            <filter type="and">
              <condition entityname="bookableresourcecharacteristic" attribute="characteristic" operator="eq">
                <ufx:value select="characteristic" attribute="value" />
              </condition>
              <condition entityname="ratingvalue" attribute="value" operator="ge" ufx:if="ratingvalue">
                <ufx:value select="ratingvalue" attribute="value" />
              </condition>
            </filter>
          </ufx:apply>
        </filter>

        <!-- Category join -->
        <link-entity name="bookableresourcecategoryassn" from="resource" to="bookableresourceid" link-type="inner" ufx:if="$input/Roles/bag">
          <attribute name="resourcecategory" aggregate="countcolumn" alias="rolecount" distinct="true" />

          <filter>
            <condition attribute="statecode" operator="eq" value="0" />
            <condition operator="in" attribute="resourcecategory">
              <ufx:apply select="$input/Roles/bag">
                <value>
                  <ufx:value select="@ufx-id" />
                </value>
              </ufx:apply>
            </condition>
          </filter>
        </link-entity>

        <!-- Territory join -->
		<link-entity name="msdyn_resourceterritory" from="msdyn_resource" to="bookableresourceid" link-type="inner" alias="ab">
		  <filter type="and">
			<condition attribute="msdyn_territory" operator="eq" uiname="Melbourne" uitype="territory" value="{1B9319B2-3C10-E811-A82C-000D3AE0A7F8}" />
		  </filter>
		</link-entity>

        <filter type="and">
          <condition attribute="statecode" operator="eq" value="0" />

          <!-- Preferred resource filter -->
          <condition ufx:if="$input/PreferredResources/bag" attribute="bookableresourceid" operator="in">
            <ufx:apply select="$input/PreferredResources/bag">
              <value>
                <ufx:value select="@ufx-id" />
              </value>
            </ufx:apply>
          </condition>

          <!-- Restricted resource filter -->
          <condition ufx:if="$input/RestrictedResources/bag" attribute="bookableresourceid" operator="not-in">
            <ufx:apply select="$input/RestrictedResources/bag">
              <value>
                <ufx:value select="@ufx-id" />
              </value>
            </ufx:apply>
          </condition>

          <!-- DisplayOnScheduleBoard and DisplayOnScheduleAssistant filter -->
          <condition attribute="msdyn_displayonscheduleboard" operator="eq" value="1" ufx:if="$input/DisplayOnScheduleBoard[. = 'true']" />
          <condition attribute="msdyn_displayonscheduleassistant" operator="eq" value="1" ufx:if="$input/DisplayOnScheduleAssistant[. = 'true']" />

          <!-- Organizational unit filter -->
          <condition operator="in" attribute="msdyn_organizationalunit" ufx:if="$input/OrganizationalUnits/bag">
            <ufx:apply select="$input/OrganizationalUnits/bag">
              <value>
                <ufx:value select="@ufx-id" />
              </value>
            </ufx:apply>
          </condition>

          <!-- Resource type filter -->
          <condition attribute="resourcetype" operator="in" ufx:if="$input/ResourceTypes/bag/option">
            <ufx:apply select="$input/ResourceTypes/bag/option">
              <value>
                <ufx:value select="." />
              </value>
            </ufx:apply>
          </condition>
        </filter>
        
        <link-entity name="systemuser" from="systemuserid" to="userid" link-type="outer">
          <!-- If Business Units or Teams are supplied, assume only users are to be returned -->
          <ufx:value ufx:if="$input/BusinessUnits/bag | $input/Teams/bag" select="'inner'" attribute="link-type" />

          <attribute name="systemuserid" alias="systemuserid" groupby="true" />
          <attribute name="entityimage_url" alias="userimagepath" groupby="true"/>
          
          <!-- User and Teams filter -->
          <link-entity name="teammembership" from="systemuserid" to="systemuserid" link-type="inner" ufx:if="$input/Teams/bag">
            <filter type="and" >
              <condition operator="in" attribute="teamid">
                <ufx:apply select="$input/Teams/bag">
                  <value>
                    <ufx:value select="@ufx-id" />
                  </value>
                </ufx:apply>
              </condition>
            </filter>
          </link-entity>

          <!-- User Businessunits filter -->
          <filter type="and" ufx:if="$input/BusinessUnits/bag">
            <condition operator="in" attribute="businessunitid">
              <ufx:apply select="$input/BusinessUnits/bag">
                <value>
                  <ufx:value select="@ufx-id" />
                </value>
              </ufx:apply>
            </condition>
          </filter>
        </link-entity>

        <link-entity name="contact" from="contactid" to="contactid" link-type="outer">
          <attribute name="contactid" alias="contactid" groupby="true"/>
          <attribute name="entityimage_url" alias="contactimagepath" groupby="true"/>
        </link-entity>
        
        <link-entity name="account" from="accountid" to="accountid" link-type="outer">
          <attribute name="accountid" alias="accountid" groupby="true"/>
          <attribute name="entityimage_url" alias="accountimagepath" groupby="true"/>
        </link-entity>
      </entity>
    </fetch>

    <bag>
      <imagepath ufx:select="accountimagepath | contactimagepath | userimagepath" />

      <accountimagepath ufx:select="$null" />
      <contactimagepath ufx:select="$null" />
      <userimagepath ufx:select="$null" />
    </bag>
  </Resources>
  
  <Resources ufx:if="$input/Characteristics/bag/characteristic" ufx:select="list(Resources/bag[characteristiccount = count($input/Characteristics/bag/characteristic)])" />
  <Resources ufx:select="order(Resources, iif($input/Orders/bag, $input/Orders, 'name'))" />
</bag>

Now, by default, The new Melbourne Tab only shows Resources with associated Resource Territory records with Territory set to “Melbourne”.

Step 5 – Update the Filter Layout

At this stage, changing the Territory filter from the Filter Panel has no impact on the Retrieve Resources Query since we removed the Territory input from the query.  Let’s remove this field from the Filter Panel.  To do this, we need to modify the Filter Layout.

UFX - Filter Layout

Similar to the last time, it is recommended to create a new Filter Layout instead of modifying the default.

UFX - Add new filter layout

 

Remove the following line.  This will remove the Territory control from the Filter Panel.

<control type="combo" source="entity" key="Territories" unspecified-key="UnspecifiedTerritory" label-id="ScheduleAssistant.West.Territories" entity="territory" multi="true" />

The complete Filter Layout is as follows.

<?xml version="1.0" encoding="utf-8" ?>
<filter>
  <controls>
    <control type="characteristic" key="Characteristics" label-id="ScheduleAssistant.West.Skills" />
    <control type="combo" source="entity" key="Roles" inactive-state="1" label-id="ScheduleAssistant.West.Roles" entity="bookableresourcecategory" multi="true" />
    <control type="combo" source="entity" key="OrganizationalUnits" label-id="SB_FilterPanel_OrganizationalUnitsFilter_Title" inactive-state="1" entity="msdyn_organizationalunit" multi="true" />
    <control type="combo" source="optionset" key="ResourceTypes" label-id="SB_FilterPanel_ResourceTypesFilter_Title" entity="bookableresource" attribute="resourcetype" multi="true">
      <data>
        <value id="2" />
        <value id="3" />
        <value id="4" />
        <value id="5" />
      </data>
    </control>
    <control type="combo" source="entity" key="Teams" label-id="SB_FilterPanel_TeamsFilter_Title" entity="team" multi="true" />
    <control type="combo" source="entity" key="BusinessUnits" label-id="SB_FilterPanel_BusinessUnitsFilter_Title" entity="businessunit" multi="true" />
    <control type="order" key="Orders" label-id="FilterControl_OrderLabel">
      <order name="name" entity="bookableresource" attribute="name" />
      <order name="proficiencyscore" entity="bookableresourcecharacteristic" attribute="ratingvalue" />
    </control>
  </controls>
</filter>

The Filter Panel now looks as below.  The filter control is not available.

UFX - Modified Filter Panel

References

Docs – https://docs.microsoft.com/en-us/dynamics365/customer-engagement/field-service/configure-schedule-board

Docs – https://docs.microsoft.com/en-us/dynamics365/customer-engagement/common-scheduler/developer/extending-urs-step-by-step

Docs – https://docs.microsoft.com/en-us/dynamics365/customer-engagement/common-scheduler/schedule-anything-with-universal-resource-scheduling

Docs – https://docs.microsoft.com/en-us/dynamics365/customer-engagement/common-scheduler/developer/universal-fetchxml

Docs – https://docs.microsoft.com/en-us/dynamics365/customer-engagement/common-scheduler/developer/extensibility-release-notes

D365 CE Team Blog – https://cloudblogs.microsoft.com/dynamics365/it/2017/10/16/blog-post-july-2017-update-for-field-service-and-project-service-automation-universal-resource-scheduling-part-1/

D365 CE Team Blog – https://cloudblogs.microsoft.com/dynamics365/it/2017/10/16/july-2017-update-for-field-service-and-project-service-automation-universal-resource-scheduling-part-2

 

Thank you for visiting Dyn365Apps.com.

Follow me on Twitter – 

Until next time…

About the Author

Nadeeja Bomiriya is a Microsoft MVP, Chapter Lead – Dynamics 365 Saturday – Australia, Sri Lanka, Committee Member – Melbourne Dynamics 365 User Group, Technical Architect, and Dynamics 365 Practice Lead who lives in Melbourne, Australia.

Disclaimer: This blog post contains opinions of my own and not the views of my employer.