Overview

When a customer purchases a product, that product should be shipped from the nearest supplier or warehouse for efficiency. In this article we show you how you can use a combination of North52 and Bing Maps Distance Matrix API to find the nearest supplier and associate it to a Quote line item when the Quote line item is being created.

North52 Decision Suite

The North52 Decision Suite solution works like this:

• A Formula is set up on the Create and Update of a Quote Line Item which:
  • Finds all Suppliers for the given product which have Latitude and Longitude coordinates
  • Creates a JSON representation of the Customer and Suppliers in the format required by the Bing Maps Distance Matrix API
  • Calls the Bing Maps Distance Matrix API using the CallRestAPI function
  • Uses North52's xCacheLoadLocal function to load the returned results into memory and sort them to find the nearest supplier 
  • Finally, it updates the Quote Line record with the closest supplier

Set up xCache and Formula

Prerequisites

• Create a many-to-many relationship between the Product and Account entities:
  
• Create a Lookup field (referencing the Account entity) on the Quote Line entity called Closest Supplier and add this to the form
  
• The Accounts which are to be used for the Customer and Suppliers will need to be Geocoded with latitude and longitude values
• Obtain a Bing Maps Api key: https://docs.microsoft.com/en-us/bingmaps/getting-started/bing-maps-dev-center-help/getting-a-bing-maps-key

xCache

To securely store the Bing Maps Api key we use an xCache record. Only System Administrators or specifically authorized users will be able to access this value.

• Navigate to Settings > North52 BPA > N52 xCache
• Create a new xCache record
  • Enter Bing for the Category
  • Add MapsKey to the Base Key so that the full BaseKey name is Bing_MapsKey
    
  • Set the DataType to String (Password)
  • In the Value Information tab enter your Bing Maps Api key into the Value (Secured) field
    
• Click Save

Formula

• Create a new formula, setting the following values in the Formula Guide:
  • Source Entity set to Quote Line
  • Set Formula Type to Save - Perform Action
  • Select the Decision Table editor
• Change the Name of the formula to Quote Line - Save - Associate Nearest Supplier
• Expand the Source & Target section
• For Source Property select Existing Product
• Click Save

Create FetchXML to find Suppliers

• Using the Advanced Find functionality, create a query which finds Suppliers (Accounts) for a given Product (we'll make this dynamic later), ensuring that they have latitude and longitude values:
  
• Click the Edit Columns button and add the Address 1: Latitude and Address 1: Longitude columns and remove all others
• Ensure the query returns results so that you can use this Product later for testing
• Click the Download FetchXML button, open the file in Notepad and copy (Ctrl + c) the FetchXML code
• On the Formula editor, select the Fetch-Xml tab and then click the + button
• Enter Find Suppliers for the Name
• In the Query field paste (Ctrl + v) the FetchXML copied above and on the line containing condition attribute="productid" remove the uiname and uitype attributes and replace the GUID with {0} (this gets replaced with GUID of the product when the formula executes):

  <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="account">
      <attribute name="accountid" />
      <attribute name="address1_longitude" />
      <attribute name="address1_latitude" />
      <filter type="and">
        <condition attribute="address1_latitude" operator="not-null" />
        <condition attribute="address1_latitude" operator="not-null" />
      </filter>
      <link-entity name="north52_product_account" from="accountid" to="accountid" visible="false" intersect="true">
        <link-entity name="product" from="productid" to="productid" alias="ae">
          <filter type="and">
            <condition attribute="productid" operator="eq" value="{0}" />
          </filter>
        </link-entity>
      </link-entity>
    </entity>
  </fetch>

• Click Save
• Click on the Formula lookup link Quote Line - Save - Associate Nearest Supplier to open the Formula editor

Create an Inline Calculation (variable) to store the Find Suppliers entity collection

• Select column B, right-click and select Insert > Insert Inline Calculation
• Replace Set Name with Find Suppliers
• Select cell B2
• Select the Functions tab, type FindRecords into the Search Functions input box and press enter
• From the filtered Functions list, Shift-click on the FindRecordsFD function to open the function wizard:
  • Ignore the Friendly Name field as we are going to insert the function directly into cell B2
  • Select Find Suppliers from the search for Formula Detail (Query)
  • Leave No-Lock set to True
  • Click inside the Parameters field, then:
    • Select the Source tab
    • Expand Source
    • Select Existing Product (Value) to add it to the Parameters field
      
  •  Click Generate
• Click Save
  

Add Conditions to check Customer is Geocoded

• Select column A, right-click and select Insert > Insert Condition
• Select cell A2
  • Select the Source tab
  • Expand Related (N:1)
  • Expand quote (quoteid)
  • Expand account (customerid)
  • Expand (Attributes)
  • Click on Address 1: Latitude
• Select cell B2
  • Click on Address 1: Longitude from the same expanded (Attributes) node in the previous step
• Select cell A4
  • Select the Functions tab
  • Search for contains
  • Ctrl-click the ContainsData function
• Select cell B4
  • Ctrl-click the ContainsData function
• Click Save
  

Define JSON to send to Bing Maps Distance Matrix API

The Distance Matrix API requires that information is sent via a POST request in JSON format. The structure of this JSON is as follows:

{
    "origins": [{
        "latitude": lat0,
        "longitude": lon0
    }],
    "destinations": [{
        "latitude": lat0,
        "longitude": lon0
    }, 
    {
        "latitude": latN,
        "longitude": lonN
    }],
    "travelMode": travelMode,
    "distanceUnit": distanceUnit
}

We use the JSON functions in North52 to build this structure.

• Select column D, right-click and select Insert > Insert Inline Calculation
• Replace Set Name with Create JSON
• Select cell D4 and expand the formula editor for this cell by clicking the + icon at the top right of the sheet
• Copy and paste the following formula (the JSON functions can be found in the Functions tab under WebFusion > Process - JSON):

  CreateJObject(
    CreateJProperty('travelMode', 'driving'),
    CreateJProperty('distanceUnit', 'mi'),
    CreateJProperty('origins', 
      CreateJArray(
        CreateJObject(
          CreateJProperty('latitude', [quotedetail.quoteid.customerid.address1_latitude.?]),
          CreateJProperty('longitude', [quotedetail.quoteid.customerid.address1_longitude.?])
        )
      )
    ),
    CreateJProperty('destinations', 
      CreateJArrayChildren(GetVar('Find Suppliers'), 
        CreateJObject(
          CreateJProperty('latitude', FindJArrayItem('address1_latitude')),
          CreateJProperty('longitude', FindJArrayItem('address1_longitude'))
        )
      )
    )
  ) 

• Close the cell editor by clicking the X icon at the top right of the sheet
• Click Save
  

Call the Bing Maps Distance Matrix API

• Select column E, right-click and select Insert > Insert Inline Calculation
• Replace Set Name with Bing Distance Matrix
• Select cell E4 and expand the formula editor for this cell
• Copy and paste the following formula:

  CallRestAPI( 
    SetRequestBaseURL('https://dev.virtualearth.net/REST/v1/Routes/'),
    SetRequestResource('DistanceMatrix?key=' + xCacheGetGlobal('Bing_MapsKey')),
    SetRequestDetails('Post'),
    SetRequestHeaders('Content-Length','450','Content-Type','application/json'),
    SetRequestParams('RawContentApplicationJSON',GetVar('Create JSON')),
    SetRequestAuthenticationNone(),
    SetRequestFiles(), 
    SetRequestExpected('OK'),  
    SetRequestActionPass(GetVarJsonEC('resourceSets[0].resources[0].results')),
    SetRequestActionFail(GetVar('responsecontent'))
  )
  

• Close the cell editor
• Click Save
  

Note: The GetVarJsonEC('resourceSets[0].resources[0].results') function returns the portion of the response that we need to evaluate the closest supplier.

Sort the results to find the closest

The Bing Maps Distance Matrix response returns a JSON array of distances like the sample below:

{
 "destinationIndex": 0,
 "originIndex": 0,
 "travelDistance": 281.261777777778,
 "travelDuration": 9560.7
}

We need to sort this to find the smallest travel distance and use the destinationIndex parameter to find the matching Supplier (Account) sent in the request. To do this we use the xCacheLoadLocal functions to load the returned entity collection into memory, sort the collection and return the destionationIndex value which is in position 0 (i.e. the first record in the collection).

• Select column F, right-click and select Insert > Insert Inline Calculation
• Replace Set Name with Find Closest
• Select cell F4 and expand the formula editor for this cell
• Copy and paste the following formula:

  SmartFlow(
    xCacheLoadLocal('BingResults', GetVar('Bing Distance Matrix')),
    xCacheFilterLocal('BingResults', '', 'travelDistance ASC'),
    xCacheAddCalculatedFieldLocal('BingResults', 'n52index', 'autoincrement', ''),
    xCacheGetLocal('BingResults', 'destinationIndex', '?', '0')
  )
  

• Close the cell editor by clicking the X icon at the top right of the sheet
• Click Save
  

Find the closest Supplier (Account)

We have the position value of the record that we sent in the request, so we need to find the GUID of that account and update the Quote Line record Closest Supplier lookup value. To do this we use the FindRecordsValue function which finds a specific value of a row in an entity collection. 

• Select cell G2
  • Select the Source tab
  • Expand Source
  • Select Closest Supplier (Value) to add it to cell G2
• Select cell G4
  • Select the Functions tab, type FindRecordsValue into the Search Functions input box and press enter
  • Shift-click on the FindRecordsValue function to open the function wizard:
    • Ignore the Friendly Name field as we are going to insert the function directly into cell G2
    • Click inside the Entity Collection field:
      • Select the Source tab
      • Expand Decision Table Calculations
      • Expand Decision Table
      • Click on Find Suppliers
    • Set Field Name to accountid
    • Set Default Value to ?
    • Click inside the Row Number field:
      • Select the Source tab
      • Expand Decision Table Calculations
      • Expand Decision Table
      • Click on Find Closest
    • Click Generate
      
• The formula in cell G4 will look like this:

  FindRecordsValue(GetVar('Find Suppliers'),'accountid','?',GetVar('Find Closest'))

• Click Save
  

The Final Formula

Testing

Ensure that you have your reference data set up and add a Quote Line item to a quote.

Tip: turn on tracing for the Formula and review the trace file to see the response values from the Bing Maps Distance Matrix

Product with associated suppliers

Quote Line item with Closet Supplier set