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

[Filter]
ExpectedArrivalTime = Between(2013-02-01,2013-03-01)
Priority = Equals("Highest")   # Find all jobs within this time range AND of highest priority

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
For additional examples, see How to filter.

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
String comparisons are case-sensitive.

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.

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

[Filter]
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
If you need to process OR, use JavaScript in the [Script] section.

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:

[Calculated]
_PriorityThreshold = DateTime.AddHours(Input.ExpectedArrivalTime, 2)

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

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
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:

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

[Filter]
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:

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

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

[Filter]
Id(ExtKey) = In(ExernalKeySet)

[Output]
Nickname = Input.Nickname
EmployeeNo = Input.EmployeeNo
...