EasyTerritory provides a plugin that can be used to store EasyTerritory markup shapes in SQL Server and/or Dynamics 365/CRM entities.
Relationships can also be identified and updated in SQL tables with point data or CRM entities such as ‘Accounts’ or ‘Leads’ with latitude and longitude attributes.
The plugin can be used by itself, or alongside the CRM Geocoding Plugin and CRM Location Lookup Plugin. These plugins are part of a suite of tools to provide bidirectional synchronization between EasyTerritory and Dynamics CRM.
The plugin fires when a properly configured project is saved in EasyTerritory.
This plugin can perform several actions.
- The plugin can save all markup shapes (territories) on the map to a SQL table which can be used as the base data for layers in other projects.
- Any configured related tables in SQL Server will be updated with the related territory shape IDs based on their spatial relationship.
- The plugin can create new territory records in any target CRM entity (e.g. the ‘Territory’ entity or a custom one)
- The plugin can update the relationship between the CRM ‘Territory’ entity and any configured secondary entity by intersecting entity latitude and longitude data against territory polygons.
NOTE: If you’re using EasyTerritory Online, you’ll need to work with your dedicated EasyTerritory/CRM Expert to configure this plugin as we don’t allow direct access to your application’s web.config. This feature is only available for EasyTerritory Enterprise edition.
Plugins can be specified in the web.config file when you are first setting up your site, or in the administrative admin “settings” section at any time. Administrative settings override the web config, so this document focuses on that configuration.
THE PLUGINS SECTION
This section is applicable to all plugins. It informs EasyTerritory as to what plugins are in use, what projects they are used with, and what their individual configuration sections are.
Name: REQUIRED a friendly name for the plugin
Assembly: REQUIRED the assembly to load the plugin from. Select OpenIntel.Plugins.Shapes2SQLAndCRM
class: REQUIRED the name of the class implementing the EasyTerritory plugin interface. Select OpenIntel.Plugins.Shapes2SQLAndCRM.PostProjectSave
projectId: REQUIRED limits the plugin for use with the specified project only. The project ID is from a saved EasyTerritory project. Warning: this argument is technically optional, but omitting it for this particular plugin will result in VERY undesirable behavior, such as deleting all territories from CRM when saving a new project without markup.
affectedLayerIDs: OPTIONAL comma-separated list of MapDotNet layer IDs, these are any layers that map SQL tables affected by the plugin.
OPENINTEL.PLUGINS.SHAPES2SQLANDCRM CONFIGURATION SECTION
For “Config”, directly beneath the plugin selection panel, choose “OpenIntel.Plugins.Shapes2SQLAndCRM”.
The OpenIntel.Plugins.Shapes2SQLAndCRM DLL is used to associate project shapes with a spatial table in the database and/or a CRM entity. Additionally, it can be configured to test point features in other tables or entities against territory polygons and apply appropriate labeling (SQL) or set relationship values (CRM).
It therefore has both settings for SQL Server and for CRM. You must provide all the fields needed for at least one of these options, and you can provide both.
Attributes for SQL:
REQUIRED: If SQL connection string is specified
SQL Connection String: REQUIRED connection to SQL Server
SQL Table Name: REQUIRED table in SQL containing shape geometries (polygons)
SQL OIKey Column: REQUIRED field in shape geometry table that stores the EasyTerritory key. This is used to update matching records and delete records with no incoming match
SQL Geometry Column: REQUIRED column that holds geometry from EasyTerritory
SQL Name Column: REQUIRED column that holds the name value from EasyTerritory. Multiple columns are premitted with a delimiter, see “SQL Name Delimiter” under “Shared Attributes” below. All columns should be varchar or nvarchar.
SQL Timeout: REQUIRED SQL command timeout, default 30 seconds
Attributes for CRM:
REQUIRED: if CRM connection string is specified
CRM Connection String: REQUIRED connection to CRM (Dynamics Online). As linked entities may be using CRMSynch (see below) you should generally connect as the no-synch user.
CRM Entity Name: REQUIRED the CRM entity that corresponds to the primary shapes table in the Shapes2SQLAndCRM configuration
CRM OI Key Column: REQUIRED field in shape geometry table that stores the OI key. This is used to update matching records and delete records with no incoming match
CRM NameColumn: REQUIRED field that holds the shape name from EasyTerritory. Multiple columns are premitted with a delimiter, see “SQL Name Delimiter” under “Shared Attributes” below. All columns should be “single line of text”.
CRM Delete Relationships: OPTIONAL comma-separated list of relationships that must be explicitly cleared on a record before deletion. For instance, the system user for a CRM territory must be explicitly removed before the territory can be successfully deleted
CRM Timeout: REQUIRED CRM command timeout, default 120 seconds
SQL Name Delimiter: OPTIONAL Mixed in with the SQL fields in the panel, a single character used to delimit shape names in EasyTerritory to split between fields listed under “SQL Name Column” or “CRM NameColumn”. The field names should be split by the same delimiter. The name is a hold-over from earlier versions of the plugin that required a SQL tier.
Feature JSON Layer: OPTIONAL Below the CRM fields in the panel, the name of the custom layer with JSON to use instead of project markup
Column Value Constraints: OPTIONAL a list of constraints such that this plugin instance only applies where the constraints are met. If the plugin inserts a record, it sets the constraint values on the record. When it updates matched records or deletes unmatched records, it only applies these actions to records where the constraints are met. This allows you to have different plugin instances manage territories that are used for different purposes.
SQL Column Name: OPTIONAL The column name in the SQL table. PROHIBITED if SQL Connection String is not provided above. Either this or CRM Column Nam must be provided, and both may be.
CRM Column Name: OPTIONAL The field name in the CRM entity. PROHIBITED if CRM Connection String is not provided above. Either this or SQL Column Nam must be provided, and both may be.
Match Value: REQUIRED the value to match
.Net Type: OPTIONAL the .Net data type for column values (string, int, double, etc). Defaults to “string”, i.e. text data, if not provided.
Related Tables and Entities: OPTIONAL a collection of tables and/or entities that are updated based on their spatial relationship to the table above. These tables are never inserted or deleted by this process. These should be tables with point geometries or entities with latitude and longitude columns.
Attributes for SQL
REQUIRED: if SQL Table Name is specified, PROHIBITED if SQL Connection String is not provided above.
SQL Table Name: REQUIRED the SQL table name
SQL OI Key Column: REQUIRED column in the SQL table that stores the EasyTerritory shape key. Should be a varchar, at least 36 characters.
SQL CRM Key Column: DEPRECATED Present for backwards compatability with older versions of this plugin. This should be set to any arbitrary value if and only if both SQL Table Name and CRM Entity Name are provided.
SQL Geometry Column: REQUIRED column that holds the point location
SQL Name Column: REQUIRED column that stores the EasyTerritory shape name; if the primary table is delimited this should be similarly delimited with the same column count. All columns should be varchar or nvarchar.
SQL Unique ID Column: REQUIRED primary key column for the secondary table, should be an integer type
Attributes for CRM
REQUIRED: if CRM Entity Name is specified, PROHIBITED if CRM Connection String is not provided above.
CRM EntityName: REQUIRED name of the CRM entity that has a relationship to the territory entity
CRM Key Column: REQUIRED ID column for the CRM entity
CRM Relationship: REQUIRED name of the relationship between this entity and the territory entity referenced above
CRM Relationship Key: REQUIRED name of the lookup key to the territory entity
CRM Latitude Column: REQUIRED name of the field that holds latitude
CRM Longitude Column: REQUIRED name of the field that holds longitude
Related Tables and Entities Elements:
Column Value Constraints: OPTIONAL This works exactly like the Column Value Constraints element on the primary table. Note that it is NOT REQUIRED that the secondary tables be constrained in the same manner as the primary table. Two plugin instances with different constraints on the same primary table might have the same secondary tables, in which case those would likely be constrained in a similar manner, or they might have entirely different secondary tables.