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:
This topic contains the following sections:
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.
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.
String comparisons are case-sensitive.
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")
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")
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.
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.
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 ...