Views:

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

Did you know?

North52 Decision Suite helps streamline complex business processes

North52 Decision Suite is a cloud-based or on-premise decision management/decision engine platform for the Dynamics 365 and Power Platform that uses data, analytics and business rules to automate decisions.

Companies use North52 to streamline their processes, improve the quality of the decisions inside their Dynamics 365 systems, and shorten the time it takes to adjust to business and competitive changes.

Learn more about the North52 Decision Suite

Keywords: xCache, Decision Table, WebFusion, FindRecords, FindRecordsFD, FindRecordsValue, ContainsData, DecisionTable, GetVar, SmartFlow, CreateJArray, CreateJArrayChildren, CreateJObject, CreateJProperty, FindJArrayItem, CallRestAPI, SetRequestBaseURL, SetRequestResource, SetRequestDetails, SetRequestHeaders, SetRequestParams, SetRequestAuthenticationNone, SetRequestFiles, SetRequestExpected, SetRequestActionPass, SetRequestActionFail, xCacheGetGlobal, xCacheAddCalculatedFieldLocal, xCacheFilterLocal, xCacheGetLocal, xCacheLoadLocal, Formula Manager

xRM Formula #245 - Find nearest supplier to customer for quote line item