Custom columns

Custom columns

Some tables support custom columns so that you can add columns to manage additional information that your organization requires.

Note
To determine whether a table supports custom columns, see the Tables reference page.

Note
The entire custom field list for an entity must be less than 256,000 characters.

This topic contains the following sections:

Column name syntax
Referencing custom columns
Calculating custom columns

Column name syntax

All custom column values are input, stored, and retrieved as text.

Note
Generally, custom column names are case-sensitive. However, if you add a custom colunm to the Job table, the value is stored as lowercase characters. For example, if you create a custom Job column named ParkingNotes, when viewed in Plan, the column name is displayed as parkingnotes.

Referencing custom columns

If a table has been augmented to store custom data, prefix those column references with the string custom.:

Copy

[Output]                                   # Create/Update template
custom.AddedTableColumn = Input.MyValue    # The prefix 'custom' denotes a custom table column 
custom.[Special Column] = Input.MyValue    # Surround the column name with brackets if it contains any non-alphanumeric characters

Copy

[Output]                                   # Retrieve template
MyValue = custom.AddedTableColumn          # The prefix 'custom' replaces the prefix 'Input' 
MyValue = custom.[Special Column]          # Use brackets if your column name contains any non-alphanumeric characters

The prefix prevents conflicts in column naming, which must be unique.

Custom columns can also be accessed in the [Script] section and the [Filter] section section. In both cases, the custom column must be prefixed with the custom. string as shown above.

Calculating custom columns

You can import custom data, and you can even use custom data to calculate values for additional custom columns.

For example, if you need to track special demographics about your drivers, you could add those columns to your Driver table and make full use of them for any reporting or calculations you may need to make. Suppose that your input file includes these fields:

Copy

# Input CSV fields:
# Id     CPRCertDate    DateLastIncident   RxCertifications
# 123    2013-01-08     2013-03-14         CPR,AED,First Aid

Suppose that, in addition to importing these non-TDE fields, you needed to infer CPR certification from the existence of the date and to log the date when these demographics were updated in TDE, for several new custom columns:

Copy

# New TDE custom columns desired:
# CPRCertDate, DateLastIncident, RxCertifications, DemographicsUpdated, HasCPR, Dosage Required

You can obtain DemographicsUpdated by taking the value from the [User] section and set HasCPR by using an If-Then-Else test in the [Calculated] section:

Copy

[Template]
TemplateVersion = 1.0
TableID = Driver-1.0   # Driver
TemplateName = AddCustomDriverDemographics
Intent = Create
Delimiter = \t                                   # tab delimiter

[User]
Date(Timestamp, YYYY-MM-DD) = ""

[Calculated]
_HasCPR(Booltype) = (Input.CPRCertDate != "") ? "True" : "False"

[Output]
DriverId(Acme) = Input.Id
Custom.RxCertifications = Input.RxCertifications   # Array inputs as text
Custom.HasCPR = _HasCPR                            # True/False as text
Custom.CPRCertDate = Input.CPRCertDate             # Dates as text
Custom.DateLastIncident = Input.DateLastIncident 
Custom.DemographicsUpdated = Date
Custom.[Dosage Required] = Input.[Dosage Required] # For names with custom text
Custom.[Custom CPR Cert Date] = Input.CPRCertDate