Views:

Scenario Overview


This example shows how to set up a Multi-Sheet Decision Table to apply the residency rules that full-time students attending Oxford University must adhere to.

The documentation for these requirements can be found under Place of residence here.

These rules require that a student live within a certain distance of the Carfax Tower as it considered to be the center of Oxford City.

In order to work out the distance between the Student's address and Carfax Tower we use the Locations API in Bing Maps to geocode the Students postcode and then use the Haversine function to calculate the distance in miles from the target point.

Undergraduate Students

Undergraduate students must live within 6 miles of Carfax Tower.
This limit is increased to 25 miles if:
  • You hold the status of Senior Student
  • Already have an undergraduate degree from Oxford
  • Reside in the home of your parent / guardian
Graduate Students

Graduate students must reside with 25 miles of Carfax unless given
special permission to work away from Oxford for a period.
Dispensation

You may apply for dispensation from the residence limits set out above to your college or other society,
who will apply to the Proctors’ Office on your behalf, together with a written statement of support.
A statement of support from your department or faculty is also required. Applications received directly
or without the necessary support will not be considered.

To accommodate these business rules we have added the following fields to the Contact entity in CRM.

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 Source Properties are set to Student Level, Address 1, Senior Student and Lives in Parent's Home
  • The Event is set to Create & Update
  • Exit this Decision Table on First Match is turned On on all Decision Tables
  • Exit All Decision Tables on First Match is turned Off on all Decision Tables

    Multi-Sheet Decision Table


    Dispensation Requests



    Evaluate Accommodation


    As well as deciding if the Student's accommodation is acceptable under Oxford University rules, this Decision Tablewill update the Distance from Carfax Tower field on the Contact record.

    Calculations


    This Multi-Sheet Decision Table uses 2 Calculations: 

    • DistanceCalc - This is used to determine the distance between the students resident postcode and the the postcode of Carfax Tower
    • PreviousDegree - This is used to check if the student has previously been awarded a degree from Oxford University

    Calculations : 

    DistanceCalc
    
    SmartFlow(
    
      SetVar('Result1', GeoCodeBing('', '', '', [contact.address1_postalcode],'')),
      SetVar('Lat1', Split(GetVar('Result1'), '|', '0')),
      SetVar('Long1', Split(GetVar('Result1'), '|', '1')),
    
      SetVar('Result2', GeoCodeBing( '','','', xCacheGetGlobal('Oxford_Uni_Carfax_Postcode'),'')),
      SetVar('Lat2', Split(GetVar('Result2'), '|', '0')),
      SetVar('Long2', Split(GetVar('Result2'), '|', '1')),
    
      SmartFlowReturn( HaversineDistance(GetVar('Long1'), GetVar('Lat1'), GetVar('Long2'), GetVar('Lat2')))
    )
    
    PreviousDegree
    
    If( FindCount('new_award', 'new_awardedto', [contact.contactid], 'new_awardid', 0) > 0, true, false)

     

    Note 1 : 

    In this example the CRM system doesn't store all the data and instead lifts it from other systems via web service calls etc. This way the Decision Table is able to execute without the need to store vast amounts of data within the CRM system itself.