These TDE data types convert directly to native JavaScript types and objects:

• BoolType converts to JavaScript boolean
• Array and Set convert to JavaScript array
• Complex and Map data types convert to JavaScript objects with all the same keys as the data type
• EnumType converts to JavaScript string
• Number converts to JavaScript number
• StreetNumber converts to JavaScript string
• Text converts to JavaScript string
• Timestamp converts to JavaScript date
• Address converts to a JavaScript object that includes address and coordinate information and can be used to geocode between the two
• Location converts to a JavaScript object that includes latitude and longitude information

The TDE data types for quantifiable data (numeric values with units of measure) are all stored as double and are handled the same: the constructor is new DataType({Unit:Value}) and the available fields for (get,set) are the valid units of measure:

• Angle - Units: Degree
• Distance - Units: Kilometer, Mile, Meter, Feet, Radian, Hectometer
• FlowRate - Units: LitersPerHour, GallonsPerHour
• Frequency - Units: Hertz, RotationsPerMinute
• Speed - Units: MilesPerHour, KilometersPerHour, Knots, MetersPerSecond, FeetPerSecond
• Temperature - Units: Celsius, Fahrenheit, Kelvin
• TimeInterval - Units: Year, Month, Day, Hour, Minute, Second, Millisecond, Microsecond, Nanosecond
• Volume - Units: Liter, "US gallon", "US fluid ounce", "US quart", "US pint", "Imperial gallon", Mililiter, "Cubic inch"
• Weight - Units: Kilogram, Gram, Pound

  Tip
  The units given above are a simplified subset of those listed in Data types. Use initial capitals on TDE units of measure and property names. Use double quotes around TDE units of measure that contain spaces. Every quantifiable data type is stored as double, so use ranges as needed for testing equivalency (e.g., 1 may actually be stored as 0.999999).

The remaining TDE data types have custom JavaScript type conversions:

• Id maps to a Custom Id, whose constructor is new Id({Native: ""123""}) and whose available field is Native (get, set) (case-sensitive with the "." operator)
• Id<T> maps to a Custom Id, whose constructor is new Id({Native: ""123""}) and whose available field is Native (case-sensitive with the "." operator)
• Polygon maps to a Custom Polygon, whose constructor is new Polygon({WKT: "POLYGON ((30 10, 10 20, 20 40, 40 40, 30 10))"}) and whose available field is Wkt (get)

The [Script] section not only lets you write your own JavaScript functions to extend TDE's set of built-in functions, but it also lets you insert custom processing around regular transformations. You achieve this by using TDE methods that hook into the template's execution.

How JavaScript accesses data:

• receives a JavaScript object containing the input values

• returns an array of objects, which results in a single input row being affected

onRow

Copy

tde.onRow = function(row) { ... }

Runs before each row. For templates of all intents, OnRow functions can filter the input fields and can operate on the values (both TDE table columns and CSV input file fields) using the JavaScript methods supported for each data type.

Note
TDE's input processing for CSV files supports strings, not complex data types.

In this example, the OnRow function makes TDE skip records that have bad data. Note where the Lat and Lon columns need to have their units specified:

Copy

[Script]
(function (tde) {
    tde.onRow = function (row) {
        if (
            // If LatLon is undefined or is 0,0, skip record:
            row.Lat == null ||
            row.Lon == null || (
                row.Lat.Degree ==
                0 && row.Lon.Degree ==
                0)) {
            return [];
        }
        return [row];
    }
})(tde)

In a retrieve template, reading from and writing to Custom columns are supported in onRow. Write temporarily modifies the custom columns when they are referenced in the Output section. The following code filters out rows without a custom column Foo, and prepends it with a row count:

Copy

[Script]
(function(tde) {
  count=0;
  tde.onRow = function (row) {
    var fooString = row.custom.Foo;
      if (!fooString) {
       return [];
      }
    row.custom.Foo = count+':'+row.custom.Foo;
    count++;
    return [row];
  }
})(tde)

Note
Only string values can be assigned to custom properties in JavaScript.

onRowsFinished

Copy

tde.onRowsFinished = function() { ... }

Runs immediately after TDE finishes processing all rows, which lets you perform aggregation as soon as the last row is processed.

Suppose that you have an input file of historical data ordered by ID in which all of the data was valid at some point in time, but only the most recent data should be imported:

Copy

ID,Name,Date,...
1,Tom,2014-01-01,...
1,Tom,2014-01-02,...
2,Joe,2014-01-01,...
2,Joe,2014-01-03,...

Rather than import every line, overwriting the old data, you can use onRow() to skip the unwanted rows and onRowsFinished() to ensure that you don't miss processing the final row to import:

Copy

[Script]
(function (tde) {
    var lastRow;
    tde.onRow = function (row) {
        var tempLastRow = lastRow;
        lastRow = row;

        if (row.Id !== tempLastRow.Id) { // we are on a new row, so commit the old one
            return [tempLastRow];
        }
        return [row];
    }

    tde.onRowsFinished = function () { // we need to persist the last row in the file
        return [lastRow];
    }
})(tde)

onCommit

Copy

tde.onCommit = function(row) { ... }

Runs after each row, for input templates. It lets you modify and filter your incoming data beforeTDE validates it and commits it to the database.

• For Create/Update/Delete templates, OnCommit functions can operate on TDE table columns using the JavaScript methods supported for each data type.

• For Retrieve templates, OnCommit functions cannot be used.

With OnCommit functions, you can solve many types of data challenges:

• Calculate and populate missing values

• Detect and correct erroneous values

• Eliminate records with erroneous values

In this example, the onCommit function sets missing values to a default. Since the Country column is a simple JavaScript String, no units are needed:

Copy

[Script]
(function (tde) {
    var defaultCountry = "USA";
    tde.onCommit = function (row) {
        if (row.Country == "") {
            // If the record has no defined country, apply a default:
            row.Country = defaultCountry;
        }
        return [row];
    }
})(tde)

When running a script in an input template, you can write to Custom columns in the onCommit function. It will store custom columns in the database if supported.

Copy

[Script]
(function(tde) {
  count=0;
  tde.onCommit = function (row) {
    row.custom.Foo = count+':'+row.tag;
    return [row];
  }
})(tde)

In this example, the onCommit function sets a hardcoded value for the PostalCode subfield of an Address column.

Copy

[Script]
(function (tde) {
  tde.onCommit = function (row) {
    row.Address.PostalCode = "78741-4545";
    return [row];
  }
})(tde)

Data transformation

In the tde.onRow function, you can create a JSON object or array of JSON objects that can be used for template input. This feature lets you take an input object and transform it to some other object without the limitations of the previous object.

You can use this feature to transform a single input record into multiple objects. The following example shows a CreateOrUpdate template that reads driver data input from a CSV file and transforms the data into an array of driver data with the values in each record stored in separate fields.

Template input data in CSV format

Copy

city,state,driverData
Austin,TX,"key1,Joe,employee1;key2,John,employee2"
Houston,TX,"key3,Jack,employee3;key4,Jerry,employee4;key5,Jim,employee5;key6,Jimmy,employee6"
Dallas,TX,"key7,Jen,employee7"

Copy

[Template]
TemplateVersion = 1.0
TableID = Driver-1.0
TemplateName = JavascriptOnRowMultiplex
Intent = CreateOrUpdate

[Output]
Id(key) = Input.key
Nickname = Input.name
EmployeeNo = Input.employeeId
City = Input.city
State = Input.state
PostalCode = Input.postCode
Teams(key) = Input.teams

[Script]
(function(tde) {
    tde.onRow = function(row) {
    var parts = row.driverData.split(';');
    var records = [];
    for (var i = 0; i < parts.length; i++) {
      var teamKeys = [];
      for (var j = 1; j <= i; j++) {
        teamKeys.push(j.toString());
    }
    var subParts = parts[i].split(',');
    var record = {
         key: subParts[0],
         name: subParts[1],
         employeeId: subParts[2],
         city: row.City,
         state: row.State,
         teams: teamKeys
    }
    records.push(record);
    }
    return records;
   }
})(tde)

Custom functions

Copy

tde.functions.MyFunction = function(col1, col2) { ... }

You can create your own functions that you can use exactly as you do the built-in functions.

TDE initializes any declarations and functions that you scripted before it processes any rows.

This example shows a complete template that uses a custom script:

[Template]
TemplateVersion = 1.0
TableID = Unit-1.0
TemplateName = Retrieve Vehicles with Friendly Make and Model

[Intent = Retrieve
Format = Csv
Output = CompleteSuccessful
[OutputHeader = true

[Calculated]
CalculatedVehicleInfo = Input.Color || " " || Input.Make || " " || Input.Model
CalculatedFriendlyName = GetFriendlyName(Input.Make, Input.Model)

[Output]
Id = Input.Id
Tag = Input.Tag
Year = input.year
Vin = Input.Vin
Make = Input.Make
Model = Input.Model
Odometer(Mi) = Input.Odometer
VehicleInfo = CalculatedVehicleInfo
FriendlyName = CalculatedFriendlyName

[Script]
(function (tde) {
  tde.functions.GetFriendlyName = function (make, model) {
     if (!make && !model) {
       return null;
     }
     if (!make) {
       return model;
     }
     if (!last) {
       return make;
     }
     return make + " " + model;
   }
  })(tde)

Creating your own function lets you OR several properties. For example, suppose that you need to retrieve points that meet any of these requirements:

• SeatBeltFastened = False
• HarshAcceleration = True
• HarshBraking = True
• J1587_DTC is populated

Tip
You can use either dot notation (row.HarshAcceleration) or index notation (row["HarshAcceleration"]). Properties are case-insensitive.

[Script]
(function(tde) {
    tde.onRow = function (row) {
        if (!row.SeatBeltFastened ||    // SeatBeltFastened is false, OR
           row.HarshAcceleration ||    // HarshAcceleration is true, OR
           row.HarshBraking ||         // HarshBraking is true, OR
           row.J1587_DTC)              // J1587_DTC is true (string is not empty)
           {
            return [row];
           }
               return [];
            })
})(tde)

This example shows one method of enhancing an HOS Event template retrieve to add an Address field from the HOS LatLon:

[Template]
TemplateVersion = 1.0
TableID = HosEvent-1.0
TemplateName = GetHosEventsWithAddress
Intent = Retrieve
Format = JSON
StructuredData = true

[Output]
EventTime = Input.EventTime
EventType = Input.EventType
Location = Input.Location
Address = getAddress(Input.Location)

[Script]
(function (tde) {
 tde.functions.getAddress = function (Location) {
  return new AddressData({Coordinates: Location});
 }
})(tde)