New! Use schedule board date ranges in custom queries in Universal Resource Scheduling

Applies to: Field Service for Dynamics 365, Project Service Automation for Dynamics 365, and Universal Resource Scheduling (URS) solution on Dynamics 365 9.0.x

In a previous release, we introduced scheduling extensibility, opening up countless new scenarios for field service organizations. Since then, we’ve heard from partners and customers about additional use cases that require being able to include a date range in custom queries on the schedule board.

For example, let’s say you want to display a count of total bookings scheduled during the date range the user is currently viewing.

Now, with the December 2017 update, a custom query can include the date range displayed on the schedule board to return matching bookings within that date range.

In the past, you could modify the custom query and resource card to count of booking for each resource, but not with the knowledge of the date range from the schedule board. Now, you can query the dates displayed on the board, and return data relevant to the date range.

There are two values you can use for the input bag of the retrieve resource query: schedule board visible start and schedule board visible end date. The keys for these two values are: $input/ScheduleBoard/StartDate and $input/ScheduleBoard/EndDate.

The values are bound to the current schedule board’s visible data range and  can be used in the UFX query condition to select data based on the visible dates.

For example, we can add the following UFX block to the default Retrieve Resource Query to query the total number of active bookings within current board’s start and end dates.

<!-- Booking join -->

<link-entity name="bookableresourcebooking" from="resource" to="bookableresourceid" link-type="outer">

<attribute name="name" aggregate="countcolumn" alias="bookingcount" />

<filter>

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

        <condition attribute="starttime" operator="le">

            <ufx:value select="$input/ScheduleBoard/EndDate" attribute="value" />

        </condition>

        <condition attribute="endtime" operator="ge">

            <ufx:value select="$input/ScheduleBoard/StartDate" attribute="value" />

        </condition>

    </filter>

</link-entity>

 

After the query is executed, the retrieved resources will contain a ‘bookingcount’ attribute. In the example, we use this attribute in the resource cell template.

Example:

<div class='resource-card-wrapper {{iif ResourceCellSelected "resource-cell-selected" ""}} {{iif ResourceUnavailable "resource-unavailable" ""}} {{iif IsMatchingAvailability "availability-match" ""}}'>

    {{#if imagepath}}

    <img class='resource-image' src='{{client-url}}{{imagepath}}' />

    {{else}}

    <div class='resource-image unknown-resource'></div>

    {{/if}}

    <div class='resource-info'>

        <div class='resource-name primary-text ellipsis' title='{{name}}'>{{name}}</div>

        <div class='secondary-text ellipsis'>

            {{#if (eq (is-sa-grid-view) false) }}

            <div class='booked-duration'>{{BookedDuration}}<div class='fo-sch-clock'></div></div>

            <div class='booked-percentage'>{{BookedPercentage}}%</div>

            {{/if}}

        </div>

        <div class='booked-duration'>Booking Count: {{bookingcount}}</div>

        {{#if (eq (is-sa-grid-view) false) }}

        <div class='matching-indicator'></div>

        {{/if}}

    </div>

    {{#if (eq (is-sa-grid-view) false) }}

    {{> resource-map-pin-template this }}

    {{/if}}

</div>

 

Here is the result (booking count) showing on the resource card in the schedule board:

 

Result showing on the resource card

 

For more information on how to access these queries, check out our introduction to extensibility, or the post on adding the total availability sort option into existing queries.

 

Happy Scheduling!

Dan Gittler, Program Manager, Dynamics 365 Engineering.

Deli Zhang, Software Engineer, Dynamics 365 Engineering.