Click or drag to resize
[Filter] section
Print this page

In a template, the Filter section lets you restrict the data that TDE processes. The filter can include one or more Boolean expressions. When the template runs, TDE joins these expressions with the AND logical operator and uses the resulting expression to filter the input data.

Each entry in the [Filter] section has the following format:

  • The name (left-side) portion specifies a column from the input that is used to filter data.
  • The value (right-side) portion is a Boolean expression that is evaluated for each row of the input.

This topic contains the following sections:

Example: Filtering data

The following example shows a filter to retrieve Job table data by expected arrival time and job priority:

ExpectedArrivalTime = Between(2013-02-01,2013-03-01)
Priority = Equals("Highest")   # Find all jobs within this time range AND of highest priority
With this filter, a TDE Job table Retrieve template returns only the rows that match the following query string: &Start=2013-02-01&End=2013-03-01&Priority=Highest

This basic example shows how to retrieve data with specific values. TDE also allows you to express filter values using built-in value compare functions, run-time parameters, calculated variables, and fetchers that ensure the filter returns a manageable amount of data.

Note Note

For additional examples, see How to filter.

Built-in comparison functions

For easy record filtering, the Filter section supports a set of built-in comparison functions that you can use in the value portion of an item. These functions return True when the input value meets the evaluation criterion.

Note Note

String comparisons are case-sensitive.

Built-in FunctionDescriptionExamples

Between(lower, upper)

Filter by values between a lower and upper limit.

The record is included in the results if the value specified is greater than or equal to the lower limit and less than the upper limit.

Time = Between("2016-01-01","2016-01-03")


Filter the results by whether the column contains the value specified. You can provide either a single value or an array of values.

If you provide an array, then TDE checks that the column contains all of the elements specified.

Tags = Contains("Hourly")

Tags = Contains(["G","1"])

ContainsAny([array of values])

Filter the results by whether the column contains one or more of the values specified. Note that the ContainsAny filter must be an array of values.

Tags = ContainsAny(["Hourly","Salary"])


Filter the results by whether the column exactly matches the value specified.

City = Equals("Austin")

GreaterThan(lower bound)

Filter by values that are greater than the value specified.

The record is included in the results if the value specified is greater than the lower bound specified.

PurchaseAmount = GreaterThan(200)

In([array of values])

Filter the results by whether the column exactly matches one of the values specified. Note that the In filter must be an array of elements.

Id(ExtId) = In([Id1,Id2,Id3])

UnitId = In(["123456","679895"])


Filter by IDs that are within the hierarchy subtree rooted at the specified NodeId.

This function can only be used to filter on ID fields in the Driver, Unit, and User tables, or on fields that are of type Id<Driver>, Id<Unit>, or Id<User>.

Id = InHierarchy(123456)


Filter the results by whether the column does not match the value specified.

JobStatus = NotEquals("Unassigned")

Note Note
  • When you need to refer to IDs or timestamps in your templates, you have two syntax options. For example, when using IDs, you can refer to them as DriverId = Equals("123456...") or DriverId = Equals(Id("123456...")). For timestamps, you can refer to them as ExpectedArrivalTime = Between("2013-07-01", "2013-07-02") or ExpectedArrivalTime = Between(Timestamp("2013-07-01"), Timestamp("2013-07-02")). In both cases, the results are the same.
  • Array values can be expressed as a comma-separated list or by using a Set or Array value as shown in the Fetchers example.
Filtering by run-time parameters

The [User] section lets you create parameterized templates, which set the filter conditions dynamically at run-time. Use this section to define one or more variables that must be passed on the query string as parameters (&variable=value, no spaces or quotes).

In this example, the user variables Start and End are passed into the template on the query string: &Start=2013-02-01&End=2013-03-01

[User]                         # Add to enable run-time parameters, which the template will require to run
Start(Timestamp) = ""          # Set via query string: &Start=2013-02-01&End=2013-03-01
End(Timestamp) = ""            # (Timestamp) converts the incoming string to the correct data type

ExpectedArrivalTime = Between(Start,End)
Priority = Equals("Highest")   # Find all jobs within this time range AND of highest priority

Note that the additional filter on the Priority column is joined to the first condition as AND, so both must be satisfied for TDE to select the record.

Tip Tip

If you need to process OR, use JavaScript in the [Script] section.

Filtering by calculated variables

Variables in your filters do not have to get values from run-time parameters: you can define dynamic variables by adding a [Calculated] section that uses functions. Beyond the built-in functions provided by TDE, you can use .NET methods for system objects: DateTime (Timestamp), TimeSpan (TimeInterval), String (Text)

In this example, the template filters jobs based on the ActualArrivalTime exceeding a _PriorityThreshold value, which is calculated dynamically using a .NET DateTime method on the ExpectedArrivalTime:

_PriorityThreshold = DateTime.AddHours(Input.ExpectedArrivalTime, 2)

ActualArrivalTime = GreaterThan(_PriorityThreshold)
Priority = Equals("Highest")   # Find all jobs that arrived over 2 hours late AND of highest priority

Fetchers (for Retrieve)

For Retrieve templates, most tables require you to use one or more required filters ("fetchers"), to ensure that the data request is manageable.

Some tables have no required fetchers, and others have several to choose from. The nature of each table's data drives the need for fetchers: Point and Marker tables manage massive datasets that must be filtered, whereas Driver and Unit tables manage smaller datasets that do not require filtering.

Tip Tip

Using a fetcher — even when not required — lets TDE optimize the query that it builds from your retrieve template. Fetchers defined in TDE limit the dataset that is returned, whereas filters work on the returned data.

If fetchers are required for your table, you must add one to your [Filter] section, or TDE returns an error when the template runs. The required fetchers are listed in the Requirements section of each Tables topic. Fetchers are listed in order of efficiency (most filtering to least). You can also append your own filters to a fetcher:

[Filter]                              # Retrieve from the Route table:
UpdateTime = Equals(TimeSubmitted)    # Fetch routes by UpdateTime (required), AND
City = Equals("Austin")               # filter by City

You can fetch by way of any of the external Ids (External keys) that you use, which you can set or format using the [User] section or the [Calculated] section:

DId = String.PadLeft(Input.[Acme Employee Number], 9, "0")

DriverId(Acme) = Equals(DId)
[User]                         # Add if you want to enable run-time parameters
Id1 = ""                       # Set via query string: &Id1=ABC123&Id2=ABC456&Id3=ABC789
Id2 = ""
Id3 = ""

[Filter]                       # Telogis Ids: Id = In([Id("123456..."),Id("679895...")])
Id(ExtId) = In([Id1,Id2,Id3])  # Tests whether external key value matches any in the set

For a more efficient filter syntax, you can also express an array of values using a Set or Array data type:

TemplateVersion = 1.0
TableID = Driver-1.0
TemplateName = RetrieveDriverByIds
Intent = Retrieve
OrderBy = Input.Nickname

ExternalKeySet(Set<text>)=""    # Set via query string: &ExternalKeySet=Moe,Larry,Curly,& ...

Id(ExtKey) = In(ExernalKeySet)

Nickname = Input.Nickname
EmployeeNo = Input.EmployeeNo