If you were importing new markers into TDE, you would associate appropriate drivers using a field holding your external ID (here, AcmeId). However, some of those drivers might not yet be defined in TDE, so TDE would not be able to match them on import.

Tip

You can avoid errors by controlling the sequence of your data imports. Be sure to import your data so that it populates "look-up" tables before the tables that use them, such as running a Create template on the Category table before updating Markers to use those new categories. As a rule, update your units, drivers, hierarchies, and markers before updating things that need to reference them, such as routes and jobs.

[Template]
TemplateVersion = 1.0
TableID = Marker-1.0
TemplateName = MarkerMakeDriver
Intent = Create

[Output]
Tag = Input.label
Lat(Degree) = Input.lat
Lon(Degree) = Input.lon
DriverId(AcmeId) = Input.driver

You can simplify Create/Update templates for tables with complex data input like collections or nested data by inputting data in either JSON or XPATH format. Using these formats, you can import and store the data as an object and access the values within the object using standard object notation.

To illustrate the advantages of using JSON or XPATH, consider the following input data for a Schedule template. Note the Load and TimeWindows data input.

    [{
        "Id": "Weekdays",
        "Name": "Weekday_Depot",
        "JobTypeId": "Depot",
        "MarkerId": "Marker2",
        "PreferredDriverId": "Driver2",
        "FixedTimeOnSite": "01:00:01",
        "StartDate": "2018-9-1",
        "AnchorDate": "2018-9-1",
        "Frequency": 1,
        "PeriodCount": 1,
        "PeriodType": "Week",
        "Load": {
        "Apple": -100,
        "Banana": 60
        },
        "TimeWindows": [{
        "Days": ["WeekDays"],
        "EarliestArrival": "22:00:00",
        "LatestArrival": "7:00:00"
        },
        {
        "Days": ["WeekDays"],
        "EarliestArrival": "9:00:00",
        "LatestArrival": "14:00:00"
                },

}]

The following Schedule template reads the JSON input and saves the Load and TimeWindows data as JSON objects. After the data is imported, you can reference subfield values within the object using standard JSON object notation, for example TimeWindows[0].Days or TimeWindows[0].EarliestArrival.

[Template]
TemplateVersion = 1.0
TableID = Schedule-1.0
TemplateName = ScheduleCreateOrUpdateWithNestedInput
Intent = CreateOrUpdate
Format = Json
ElementName = .

[Output]
Id(test) = Input.Id
Name = Input.Name
JobTypeId(test) = Input.JobTypeId
MarkerId(test) = Input.MarkerId
PreferredDriverId(test) = Input.PreferredDriverId
FixedTimeOnSite = Input.FixedTimeOnSite
StartDate = Input.StartDate
AnchorDate = Input.AnchorDate
Frequency = Input.Frequency
PeriodCount = Input.PeriodCount
PeriodType = Input.PeriodType
Load = Input.Load
TimeWindows = Input.TimeWindows

If you use CSV format for the input data, you must save the nested data values with explicit references in the [Output] section of the template as shown in the following example.

[Template]
        TemplateVersion = 1.0
        TableID = Schedule-1.0
        TemplateName = ScheduleCreateOrUpdate
        Intent = CreateOrUpdate
        Format = CSV
        IgnoreValue = ""

       [Output]
       Id(test) = Input.Id
       Name = Input.Name
       JobTypeId(test) = Input.JobTypeId
       MarkerId(test) = Input.MarkerId
       PreferredDriverId(test) = Input.PreferredDriverId
       FixedTimeOnSite = Input.FixedTimeOnSite
       StartDate = Input.StartDate
       AnchorDate = Input.AnchorDate
       Frequency = Input.Frequency
       PeriodCount = Input.PeriodCount
       PeriodType = Input.PeriodType
       Load["Apple"] = Input.Apple
       Load["Banana"] = Input.Banana
       TimeWindows[0].Days = Input.Days
       TimeWindows[0].EarliestArrival = Input.EarliestArrival
       TimeWindows[0].LatestArrival = Input.LatestArrival
       TimeWindows[1].Days = Input.Days_1
       TimeWindows[1].EarliestArrival = Input.EarliestArrival_1
       TimeWindows[1].LatestArrival = Input.LatestArrival_1

Suppose that you need a template that writes to the Job table, but you realize that each job being created also needs to include a marker and a route, which are in different tables entirely. The solution lies in how you use the Id type columns, which let you associate and reference data in other tables.

As a rule, you follow Id column names with parentheses. The contents of the parentheses tell TDE how to identify the Id, such as the name of the custom column that your organization chose to store your External keys for those markers and routes. This technique lets you join data across several tables:

• TDE links the new job to the Marker that is identified by the external Id column name in parentheses.
• TDE links the new job to the Route that is identified by the external Id column name in parentheses.
• TDE links the new job to its underlying Jobtype by way of the Telogis key "Name".

[Template]
TemplateVersion = 1.0
TableID = Job-1.0
TemplateName = CreateJobWithMarkerRoute
Intent = Create

[Output]
Id(AcmeId) = Input.Tag
MarkerId(AcmeId) = Input.Marker
RouteId(AcmeId) = Input.Route
JobtypeId(Telogis, Name) = Input.JobType
...

Certain tables (such as Driver, Job, Marker, and Unit) have the ability to let you create additional columns, where you can store custom data for your organization.

Whenever you create or reference a custom column, be sure to prefix those column references with the string Custom., such as for this custom column that inputs a "Severity" rating value:

[Template]
TemplateVersion = 1.0
TableID = Job-1.0
TemplateName = JobUpdate
Intent = Update

[Output]
Id(AcmeId) = Input.Tag
ExpectedArrivalTime = Input.ExpectedArrival
EarliestArrivalTime = Input.EarliestArrival
LatestArrivalTime = Input.LatestArrival
Custom.Severity = Input.Severity            # Input by prefixing with Custom.*

Note
When you Retrieve this custom value, you must use the Custom. prefix in place of the usual Input. prefix: Copy [Output] JobSeverity = Custom.Severity # Retrieve using Custom.* in place of Input.*

Suppose that you maintain unique naming for your vehicles and that you would prefer to use that name for both the external key to the Unit and for your name ("Tag") for each vehicle. In other words, you want your single external Tag value to be the only way by which you retrieve, update, and name your vehicles in the Unit table.

To achieve this, your input templates need to map your external name to two columns: both the Id column and the Tag column. This double-mapping means that your incoming value is written identically to different columns, which effectively makes your Tag value your external key for your vehicles.

Note
Ensure that all of your input templates for this table use and preserve this double-mapping, so that the columns remain synchronized.

[Template]
TemplateVersion = 1.0
TableID = Unit-1.0
TemplateName = CreateUnitExtKeys
Intent = Create

[Output]
Id(AcmeId) = Input.UnitId
Tag = Input.UnitId