The following diagram shows the work flow for creating and using external keys.

Figure: 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:

• Name portion (left side) declares the name of the Telogis Id ("Id") with the name of the external key used in the place of a data type.
• Value portion (right side) is the input file field that contains your external key value.

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

• For Update or Delete templates, this syntax looks up the object that matches the CRM Id (which you previously mapped in another template).
• For Create templates, this syntax imports the value in CRMId to be the CRM Id for the new object, such as a marker.
• For Retrieve templates, the value of the external key (CRM) will be the output.

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

• For Create templates, the primary key (Telogis Id) will be determined by TDE. TDE cannot assign a value to the Telogis Id (Id = Input.IdColumn would give an error), but it can assign external keys to the new item.
• For Update templates, TDE uses the primary key (the Telogis Id, if present) to look up the record to update. If the template only specifies external Ids, TDE uses the first one to look up the record. Any remaining external Ids are set for the record.
• For Delete templates, TDE uses the primary key (the Telogis Id, if present) to look up the record to delete. If the template only specifies external Ids, TDE uses the first one to look up the record. All external key columns in this record are deleted with it, even if they are not specified in the template.
• For Retrieve templates, TDE fetches (retrieves) based on the [Filter] section entries. Everything in the [Output] section is fetched for each record returned.

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.

Note
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:

1. group by the primary external Id
2. specify what to do with the multiple secondary Id values, with an accumulator function

[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.

Example:

[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

1. Id("A") has FieldCount = 3, FieldCollect = {1,2,3}, and FieldRandom = either 1, 2, or 3.
2. Id("B") has FieldCount = 2, FieldCollect = {101,102}, and FieldRandom = either 101 or 102.