| How to use keys |
To integrate your systems with TDE and to access one Id through another, you need to make use of keys (special identifiers).
External keys - You will import your organization's Id values so that they pair with Telogis Ids, which are read-only internal integers or GUIDs (abcdef10-1234-567a-bcde-f10123456789).
With TDE, you create and retrieve using your existing keys (unique identifiers) for drivers, units, and such. These keys input as Text and are stored as additional Ids in your table, associated with the underlying, read-only Telogis Ids.
Telogis key - To look up record Ids using Ids other than the one that is the primary key for the table, you can use alternative primary keys, called Telogis keys. Along with external keys, Telogis keys give you more options for accessing data.
For example, when you need to look up an existing driver, you likely have the driver's name rather than his internal Telogis Id value. For input templates, you can use that name (Driver.Nickname) as a Telogis key, to look up the driver's Id using the value you know.
TipYou can find out which columns can be used as Telogis keys in the TDE Tables documentation.
This topic contains the following sections:
How to create an external Id
This template shows how to create a new record using an external Id, which will be stored with the Telogis Id in your table.
[Template] TemplateVersion = 1.0 TableID = Driver-1.0 TemplateName = CreateDriverWithNewId Intent = Create [Output] Id(ERP) = Input.Id Nickname = Input.Tag EmailAddress = Input.Email
The identifier Id(ERP) can be read as:
"For the Telogis Id of the driver that I am currently creating, import and associate the string value from the Id field of my input file, storing it in an Id named ERP."
| Tip |
|---|
Be sure to make note of the Id name (ERP) that you used in the Create template, because you will need it to look up this record in the future. |
How to update with an external Id
Once you have stored your external Id in TDE, you will now use that same Id name whenever you need to look up the record.
[Template] TemplateVersion = 1.0 TableID = Driver-1.0 TemplateName = UpdateDriver Intent = Update [Output] Id(ERP) = Input.Id EmailAddress = Input.Email Nickname = Input.Tag || "_" || Input.FirstName
The identifier Id(ERP) can be read as:
"With the value of Id in my input row, find a matching Id value in the Id named ERP, and open that record for the updates that follow."
How to use keys with referenced tables
Many tables refer to Ids in other related tables, which effectively joins them. The Driver table, for example, refers to both the Unit table and the Marker table, since drivers have exclusive associations with them: there is one vehicle to which they are assigned and one location (marker) that is their home base.
Just as you can use an external key to look up each driver, you can use external keys to look up each unit and marker (in place of the Telogis Ids), to uniquely identify those associated records.
The following template updates the Driver table, which has two columns that are unique identifiers into other tables (indicated by the <angle brackets> after the data type):
- UnitId (Id<Unit>) - The ID of the vehicle to which the driver is assigned.
- Depot (Id<Marker>) - The ID of the marker that is the driver's base location.
[Template] TemplateVersion = 1.0 TableID = Driver-1.0 TemplateName = UpdateDriver Intent = Update [Output] Id(ERP) = Input.EmployeeNumber UnitId(ERP) = Input.Unit Depot(ERP) = Input.Marker LoginKey = Input.LoginKey LogoutKey = Input.LogoutKey
The identifier UnitId(ERP) can be read as:
"Having matched the incoming EmployeeNumber value to an ERP Id in the Driver table and opened that record for updates, match the incoming Unit value to an ERP Id in the Unit and assign this driver to that unit."
Because these tables are joined, both tables update appropriately: the Driver table updates with the vehicle details associated with the new Unit assignment, and the Unit table updates with the details for this newly assigned driver. The same effect occurs for the new Depot assignment, from the Marker table.
How to import multiple external keys
Most organizations have more than one enterprise system, so you might have more than one unique identifier to use and maintain for a given entity. Fortunately, TDE lets you name and use multiple external keys.
This template solves the problem of how to import an external name to identify a route, yet also import a second external key for that same route. Here, "ERP" and "Clock" signify the separate data sources for these Ids, an ERP system and a punch-clock tracker for the driver's timesheets:
[Template] TemplateVersion = 1.0 TableID = Route-1.0 TemplateName = RouteCreate Intent = Create [Output] Id(ERP) = Input.Name Id(Clock) = Input.AltId DriverId(ERP) = Input.DriverId UnitId(ERP) = Input.UnitId Name = Input.Name StartTime = Input.Start EndTime = Input.End
The identifier Id(Clock) can be read as:
"Having imported the Name field to be stored as the ERP Id for this route, now import the AltId value as the Clock Id."
Note that the Driver table and Unit table can use the same external key name as the Route table: "ERP". This is because they are stored in separate tables, so these column names do not conflict.
How to look up using Telogis keys
To look up record Ids using other unique, non-Id columns, you can use special alternative primary keys, called Telogis keys. Like external keys, Telogis keys give you convenient ways to access your data.
This template solves the problem of how to look up an existing driver from the Unit table when you don't know the Driver.Id but you do know Driver.Nickname. Since Nickname is a Telogis key, you can use the special keyword Telogis to enable lookup by the non-Id column name from the other table:
[Template] TemplateVersion = 1.0 TableID = Unit-1.0 TemplateName = UpdateUnitsTelogisKey Intent = Update [Output] Id(Telogis, Tag) = Input.Tag DriverId(Telogis, Nickname) = Input.DriverName
The identifier DriverId(Telogis, Nickname) can be read as:
"Having used the non-Id Tag value to look up this vehicle, now use the DriverName value to match on the non-Id Nickname value from the Driver table, to specify the driver being assigned to this vehicle."