Scenario Overview

In this scenario the business requirement was to calculate the driving distance and travel time for a student to Oxford University College. If a student's home address is more than 70 miles away then they are to be marked as eligible for Student accommodation. 

To achieve this we are going to use the Bing Maps REST API to do the driving distance and travel time calculations and then update the student's contact record with the results.

You can sign up for a Bing Maps account and API here. For this sample we are using the Routes API.

The Contact record has the following fields that we want to update.

N52 Formula Manager Solution

The Formula Manager solution works like this:

  • A Formula of type 'Save - Perform Action' is created on the Contact entity
  • The triggering event is the 'Create & Update'
  • The Source Properties are set to the 'address fields'
  • xCache records are created to store the University Address* and the Bing Maps API Key
  • The Formula first creates the Student Address by combing the Address 1 fields on the Contact
  • Next it passes the Student Address, the College Address, the desired Distance Unit** and the Bing Maps API Key to the Bing Maps REST web service
  • The response is then stored in 2 variables TravelDistance & TravelDuration
  • Next the Formula checks of the travel distance to see if the student has to drive more than 70 miles. If yes they they are flagged as being eligible for the Student Accommodation
  • Finally the formula updates the Contact record

*Note 1: Oxford University use Carfax Tower as the basis of their distance calculations so its not precisely the University Address we are using here

**Note 2: Bing Maps defaults its results to Kilometers so we pass in distanceUnit as mi so the result will be returned as miles

Note 3: The TravelDistance value is returned in the JSON response as a string value, so we use Truncate() to convert it to a number value and remove any decimal portion to compare with the 70. Also the TravelDuration is returned in seconds, so we divide it by 60 to get the duration in minutes.

N52 Formula Manager Steps

The following set of steps outline how to create this Formula

  • Create the xCache records to store the University Address and the Bing Maps API key
  • Create a new formula of type 'Save - Perform Action'
  • Set the Source Entity to 'Contact'
  • Set the Event to 'Create & Update'
  • Copy and paste the formula below into the formula
  • Click Save & Test!

xCache Records


    /* Build up the Student's Address */
    SetVar('StudentHomeAddress', [contact.address1_line1] + ',' + [contact.address1_line2] + ',' 
            + [contact.address1_line3] + ',' + [contact.address1_city] + ',' + 
      /* Call BING Maps API */
           SetRequestParams(  'wayPoint.1', GetVar('StudentHomeAddress'),
                        'Waypoint.2', xCacheGetGlobal('Oxford_Uni_Carfax_Tower'), 
                        'distanceUnit', 'mi', 
                        'key', xCacheGetGlobal('BingMaps_Key')),
                          GetVarJsonValue('resourceSets{0}.resources{0}.travelDistance', 0)),
                          (GetVarJsonValue('resourceSets{0}.resources{0}.travelDuration', 0) / 60)))),    
  /* Check if the Travel Distance is greater than 70 miles */
    If(Truncate(GetVar('travelDistance')) > 70, 
          SetVar('QualifyForAccomodation', '1'),
        SetVar('QualifyForAccomodation',  '0')),
    /* Update Contact Record */
            SetAttribute('north52_qualifycampusaccommodation', GetVar('QualifyForAccomodation')),
            SetAttribute('north52_traveltime', GetVar('travelDuration')),
            SetAttribute('north52_traveldistance', GetVar('travelDistance'))) 

Below is a Contact after formula has executed and decided the student is not entitled to use the student accommodation since they are only 42 miles driving distance away.
The example address was chosen at random Bing Maps.

Wizard - xCacheGetGlobal

Please see below the wizard you can use to create the xCacheGetGlobal() function call used in this formula. 

Note you will need to have created the key name beforehand 

Did you know?

North52's Data Packager helps move Portal configuration data between instances

North52 Data Packager can be used to easily manage the transport of portal data from one instance to another. Specifically for Dynamics/PowerApps Portals, we provide templates that let you quickly move all portal entity data with ease. You can move whole sites or only the records you specify, either manually or via FetchXML queries.

And, because we use the Solution framework for the export and import of Data Packager data, you can easily version each data package. This is especially useful if you need to build up a new dev/test portal to a specific version – just apply and execute the Data Packages you need.

Learn more about the North52 Data Packager