By mapping and using external keys in place of the internal Ids for each table, you can integrate TDE with your third-party systems and support complex use cases.
See the template tutorial, How to use keys.
An external key is a value that you pair with a Telogis Id to uniquely identify an object in TDE (such as a job, marker, route, or unit). These external key values input as Text (of any length) and are stored as additional Ids in your table, associated with the read-only Telogis Ids.
Most columns of data type Id can be used as an external key:
This topic contains the following sections:
The following diagram shows the work flow for creating and using external keys.
Using the following syntax triggers TDE to do an external key look-up/creation using the values you have mapped. For templates of any intent, you can use the [Output] and [Filter] sections to indicate that a column in the input file is an external key. The syntax for identifying an input column as an external key is to use a name/value pair where:
If you failed to add an external key when creating entities in TDE, you need to map your values to the Telogis Id. This can be done in an Update template that inputs both the Id field and an external key value:
[Output] Id = Input.TelogisId Id(CRM) = Input.CRMId # populates new Id(CRM) with the CRMId field value
This indicates that the TelogisId column of the input CSV file contains the value of the Telogis Id for the entity. The CRMId field contains the identifier that you will use to identify the same entity. This is mapped to a new Id named "CRM", which you will use from here on.
Once you have run a template that defines the mapping between a Telogis Id and your external key, you can use the external key in subsequent templates without ever again using the Telogis Ids. The name you used to define the mapping is used like a data type to indicate that this is your external key:
Id(CRM) = Input.CRMId
You can use multiple external keys in templates of any intent. If you have external keys set in two different external systems (Sys1 and Sys2), note that the system name always goes on the left-hand side for templates of any intent, including Retrieve templates:
[Output] Id = Input.Id Sys1Id(Sys1) = Input.Id Sys2Id(Sys2) = Input.Id
How TDE processes multiple keys depends on their ordering and on the intent:
[Output] Id(SysX) = Input.SysXId # Primary key for Update/Delete, if no Id = Input.Id Id(SysY) = Input.SysYId # Additional keys Id(SysZ) = Input.SysZId
TDE can support having multiple external keys in the same system, as long as they are associated with a primary external key in another system.
|If you Retrieve an item that has multiple external keys in the same system, TDE returns only one of them, selected indeterminately.|
For example, in this input file, Sys1 is the primary Id (in System 1) and Sys2 has multiple Ids per object (in System 2):
Sys1,Sys2 Id-A,Id-1 Id-A,Id-2 Id-B,Id-3
When you insert data (using a Create, Update, or CreateOrUpdate template), TDE can preserve those multiple secondary external keys, so that Id-1 and Id-2 are both associated with Id-A. To achieve this, do two things:
[Template] ... GroupBy = Input.Sys1Id # group by the primary external key, Sys1Id [Output] Id(Sys1) = Input.Sys1Id Id(Sys2) = Collect(Input.Sys2Id) # for the item Sys1Id, collect and set all Sys2Ids
Be sure to use a built-in accumulator function (Sum, Min, Max, Count, Collect) for each of the non-GroupBy columns, to handle the column data for all the values in the group.
Using accumulator functions with the GroupBy function ensures deterministic, well-defined results: if you do not specify how to GroupBy, then TDE must default to one of the values from one of the grouped rows, which can be random.
[Template] ... Intent = Update GroupBy = Input.Id [Output] Id = Input.Id # Grouping on ID to make updates the item with that ID FieldCount = Count(Input.Data) FieldCollect = Collect(Input.Data) FieldRandom = Input.Data
Contents of input file:
Id,Data A,1 A,2 B,101 A,3 B,102