Accessing subfields

Accessing subfields

Complex data input like Address or ServiceTimeWindow contains subfields that you can access directly from within a template using the syntax described in this topic. Subfields can be used in the following template sections:

[Output] section and [Script] section within any type of template
[Filter] section and [Calculated] section of output templates (Retrieve)
[Response] section of input templates (Create, Update, and Delete intents)

Subfields are accessed using dot notation. For example, Address.City.

When you work with columns that contain subfields, you can either work with the full entity as an object, or with the individual subfields that make up that piece of data.

This topic contains the following sections:

Accessing subfields in output templates
Accessing subfields in input templates
Accessing subfields in scripts
Parsing subfield inputs in the [Script] section

Accessing subfields in output templates

Within output templates, you specify the subfield to access on the right side of the line.

The following retrieve template shows how to access subfields of an Address data type when the input data format is CSV:

Retrieve template with CSV input format

Copy

[Template]
TemplateVersion = 1.0
TableID = Unit-1.0   
TemplateName = RetrieveUnitBaseAddress
Intent = Retrieve
Format = CSV

[Output]
ResourceName = Input.Tag
FullBaseAddress = Input.BaseAddress           # Retrieves the full BaseAddress
StreetNumber = Input.BaseAddress.StreetNumber # Retrieves only the StreetNumber of the BaseAddress
StreetName = Input.BaseAddress.StreetName     # Retrieves only the StreetName of the BaseAddress
Zip = Input.BaseAddress.PostalCode            # Retrieves only the PostalCode of the BaseAddress

StreetNumber, StreetName, and PostalCode are all accessed as subfields of the BaseAddress column, which has an Address data type.

If using JSON or XPATH as the output format, you can specify StructuredData = True to output subfields in a standard format.

Retrieve Template with JSON input format

Copy

[Template]
TemplateVersion = 1.0
TableID = Unit-1.0   
TemplateName = RetrieveUnitBaseAddress
Intent = Retrieve
Format = JSON
StructuredData = True

[Output]
ResourceName = Input.Tag
FullBaseAddress = Input.BaseAddress           # Retrieves the full BaseAddress as JSON object

In the example output below, FullBaseAddress is output in a standard JSON format, because StructuredData is set to True. Without this setting, the FullBaseAddress would instead be output as a | (pipe) delimited string.

StructuredData option outputs data in standard JSON format

Copy

"TableEntry": [{
    "ResourceName": "MyTruck",
    "FullBaseAddress": {
    "Region": "Arizona",
    "Country": "USA",
    "City": "Tempe",
    "Coordinates": {
        "Lat": "33.3881210320068",
        "Lon": "-111.93260547117"
    },
    "PostalCode": "85282",
    "StreetName": "E Manhatton Dr",
    "StreetNumber": "517"
    }
}]

Accessing subfields in input templates

Within input templates, you specify the subfield to access on the left side of the line.

This template shows how to access subfields of a column with a Location data type in an input template:

Create or Update template with CSV input

Copy

[Template]
TemplateVersion = 1.0
TableID =  HosEvent-1.0
TemplateName = CreateOrUpdateHosEvent
Intent = CreateOrUpdate
Format = CSV
IgnoreValue = ""

[Output]
Id(HosId) = Input.Id
DriverId(DriverId) = Input.DriverId
SignatureId(SigId) = Input.SignatureId
EventTime = Input.EventTime
ModifiedTime = Input.ModifiedTime
EventType = Input.EventType
Remarks = Input.Remarks
Location.Lat = Input.Lat    # Inputs the Lat subfield of the Location column
Location.Lon = Input.Lon    # Inputs the Lon subfield of the Location column

[Response]
Id(HosId)
Location.Lat    # Requests a detailed response for the Lat subfield
Location.Lon    # Requests a detailed response for the Lon subfield

In the [Output] and [Response] sections, Lat and Lon are accessed as subfields of the Location column, which has a Location data type.

If you have complex input data like Address or ServiceTimeWindows, you can simplify the code in the Output section by switching the input format to JSON or XPATH. Using these formats, the template imports the input data as an object, and you can reference any subfield value without explicitly declaring them in the [Output] section. The subfield name must be an exact of the name in the data type definition.

For example, if you provide the following JSON input to create or update site address data, you can import the address data as a single Address object specified in the [Output] section.

JSON input data with complex data

Copy

[{
    "Name": "13BEN025-5567",
    "Address": {
        "StreetNumber": "8214",
        "StreetName": "Princeton Square Blvd E",
        "City": "Jacksonville",
        "Region": "FL",
        "PostalCode": "32256",
        "Country": "USA"
        }
    },
    {
    "Name": "13BEN025-5568",
    "Address": {
        "StreetNumber": "9000",
        "StreetName": "Princeton Square Blvd E",
        "City": "Jacksonville",
        "Region": "FL",
        "PostalCode": "32256",
        "Country": "USA"
        }
   }]

Create or Update template with complex data inputs

Copy

[Template]
TemplateVersion = 1.0
TableID =  Site-1.0
TemplateName = CreateSite With Address Only
Intent = CreateOrUpdate
Format = Json
ElementName = .

[Output]
Name = Input.Name
Address = Input.Address   #Imports the primary Address inputs in standard JSON object format.

[Response]
Site(Name)
Address[0].City    # Requests the value for the City subfield
Address[0].Region  # Requests the value for the Region subfield

In this example, the Address input data is stored in the Address[0] object. In the [Response] section, you can access the subfield values using standard JSON object notation.

Note
When you use the JSON or XPATH format for complex data type input, subsequent references to the subfields must match the names in the TDE tables exactly. For subfields with quantifiable data, you must set the UnitsOfMeasure value in the template [Defaults] section.

Accessing subfields in scripts

You can also access subfields within the [Script] section. In the example below, the Country subfield is set by in the onCommit function, if it is empty within the input file.

Create template

Copy

[Template]
TemplateVersion = 1.0
TableID = Photo-1.0
TemplateName = PhotoCreate

Intent = Create
Format = Csv
Output = CompleteSuccessful

[Output]
Id(MyId) = Input.id
Name = Input.label
Path = Input.path
CaptureTime = Input.time
CaptureAddress = Input.address

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

Parsing subfield inputs in the [Script] section

Some subfield data types, like Location and Address, have standard string formats that you can use to input the subfield data. For example, you can input a Location using either the standard string format (Location = "30.3976,-97.72958") or using the individual subfields (Location.Lat = “30.3976”, Location.Lon = “-97.72958”).

If a subfield data type does not support standard string formats, such as a PhoneNumber, or if the string format you are working with is not standard, then to input the string you must parse it in the [Script] section.

For example, consider the following input file:

Copy

Id,Name,Location,Radius,PhoneNumber
tt1,site1,"44.085370,-123.160689",333,"Business:Bob Carol:555-555-1234"

The single string input for Location requires no extra processing, because the data type supports it. However, if you want to input the PhoneNumber as shown, then you must parse the string and assign each value to the appropriate subfield, as shown in the example below.

Create template

Copy

[Template]
TemplateVersion = 1.0
TableID = Site-1.0
TemplateName = CreateSite
Intent = Create
Format = CSV

[Output]
Id(ExternalId) = Input.Id
Name = Input.Name
Location = Input.Location
Radius(ft) = Input.Radius
PhoneNumbers = Input.SplitPhoneNumbers

[Script]
(function(tde) {
 tde.onRow = function (row) {
        var split = row.PhoneNumber.split(":");
        var splitType = split[0];
        var splitContact = split[1];
        var splitNumber = split[2];
        row.SplitPhoneNumbers = [new PhoneNumber({Type:splitType, Contact:splitContact, Number:splitNumber})];
  return [row];
 }
})(tde)

When you execute the template above, the PhoneNumber string is parsed and input into the Type, Contact, and Number subfields.