Click or drag to resize
How to filter
Print this page
f

When you create a Retrieve template, you want to avoid getting more data than you need. The [Filter] section lets you restrict the data that TDE processes. The filter lists one or more Boolean expressions. When the template runs, TDE joins the expressions with the AND operator and uses the resulting expression to filter the data. Each row of the input data is evaluated against the filter, and TDE only processes rows that match every expression in the filter list.

Note Note
  • Certain Tables require you to use a predefined filter (a "fetcher") to ensure that your template brings back a manageable set of data. You can always supplement a fetcher with your own filter, to further restrict your results.
  • You can also filter on custom columns, but the column name must be prefixed with the string Custom. (for example: Custom.MyCustomColumn). See Custom columns for more information.

This topic contains the following sections:

How to add a simple filter

When you work with Tables that have no required fetchers, you do not need a [Filter] section at all in your Retrieve template. However, if you want to get back a more useful subset of data, just add a filter or two that tests the value of one of the table's columns: TDE will return only those rows that pass every test.

Suppose that you want to find out how many of your markers are located in areas with higher speed limits, such as those over 40 mph. You can do this in a few steps:

  1. Create a template that retrieves from the Marker table.
  2. Add a [Filter] section.
  3. Create a test for the SpeedLimit column that uses the GreaterThan operator:
    Copy
    SpeedLimit = GreaterThan(40)

Copy
[Template]
TemplateVersion = 1.0
TableID = Marker-1.0
TemplateName = GetMarkerBySpeed
Intent = Retrieve
OrderBy = MarkerType,SpeedLimit

[Output]
label = Input.Tag
speed = Input.SpeedLimit

[Filter]
SpeedLimit = GreaterThan(40)
Tip Tip

Apply sorting to your results by adding one or more columns to an OrderBy = statement in your [Template] section.

How to filter by static values

The FuelTransaction table requires a fetcher, which means that you need to use one of the five filter sets described in the table's Requirements.

Suppose that you want to fetch transactions by driver, which is the third fetcher option: PurchaseTime (Between) AND DriverName (Equals). You can set the filter to find static values, as long as you specify the datatype Timestamp for the strings you give for the dates:

Copy
[Template]
TemplateVersion = 1.0
TableID = FuelTransaction-1.0
TemplateName = ReadByDriver
Intent = Retrieve
OrderBy = PurchaseTime

[Filter]
PurchaseTime = Between("2013-02-28", "2013-03-02")
DriverName = Equals("Bruno Wesler")

[Output]
DriverName = Input.DriverName
Account = Input.AccountNumber
...
How to filter by run-time values

Suppose that you want to fetch transactions by dates that are specified at run-time, so that you do not have to change the template each time. You can add a [User] section to define a variable for each run-time parameter. You create user variables with a few steps:

  1. Add a [User] section to the template.
  2. Define a variable for each run-time parameter you need.
  3. Set the [Filter] section expressions to use your variables.
  4. When running the template, add your variables to the query string:
    Copy
    &Driver=Bruno%20Wesler&ReportStart=2013-08-01&ReportEnd=2013-09-01

Copy
[Template]
TemplateVersion = 1.0
TableID = FuelTransaction-1.0
TemplateName = ReadByDriver
Intent = Retrieve
OrderBy = PurchaseTime

[User]
Driver(Text) = ""
ReportStart(Timestamp) = ""
ReportEnd(Timestamp) = ""

[Filter]
PurchaseTime = Between(ReportStart,ReportEnd)
DriverName = Equals(Driver)

[Output]
DriverName = Input.DriverName
Account = Input.AccountNumber
...
How to filter by calculated values

Suppose that you want this template to always fetch a week's worth of fuel transactions at a time. You can add a [Calculated] section and use .NET functions for DateTime to work with Timestamp data. You create calculated variables with a few steps:

  1. Add a [Calculated] section to the template.
  2. Define a variable for the filter value you need.
  3. Set the [Filter] section expression to use your variable.
  4. When running the template, remove the variable that you're now calculating from the query string:
    Copy
    &Driver=Bruno%20Wesler&ReportStart=2013-08-01

Copy
[Template]
TemplateVersion = 1.0
TableID = FuelTransaction-1.0
TemplateName = GetFuelTransactionsForWeekByDriver
Intent = Retrieve
OrderBy = PurchaseTime

[User]
Driver(Text) = ""
ReportStart(Timestamp) = ""

[Calculated]
ReportEnd = ReportStart.AddDays(7)

[Filter]
PurchaseTime = Between(ReportStart,ReportEnd)
DriverName = Equals(Driver)

[Output]
DriverName = Input.DriverName
Account = Input.AccountNumber
...
How to filter by Id

The following example demonstrates how to filter by Id. For additional information, see How to use keys for explanation of how to use Ids (external keys) with TDE templates.

Copy
[Template]
TemplateVersion = 1.0
TableID = Signature-1.0
TemplateName = RetrieveSignature
Intent = Retrieve

[Filter]
Id(SigId) = Equals("10031")

[Output]
Id(SigId) = Input.Id
DriverId(DriverId) = Input.DriverId
...

As before, you can add a [User] section to transform the template into one that takes the Id as a run-time parameter:

Copy
&SId=10031

Copy
[Template]
TemplateVersion = 1.0
TableID = Signature-1.0
TemplateName = RetrieveSignature
Intent = Retrieve

[User]
SId = ""

[Filter]
Id(SigId) = Equals(SId)

[Output]
Id(SigId) = Input.Id
DriverId(DriverId) = Input.DriverId
...
How to filter by multiple Ids

What if you need a template that can find more than one driver? You can use the filter condition In([x,y,z]) to specify the set of Ids against which TDE will find matches.

Using this condition lets you add variables to your [User] section to transform the template into one that takes multiple Ids as run-time parameters:

Copy
&ExtId=Abner&ExtId1=Riecher&ExtId2=Bosh&ExtId3=Elda

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

[User]
ExtId(Text) = ""
ExtId1(Text) = ""
ExtId2(Text) = ""
ExtId3(Text) = ""

[Filter]
Id(AcmeDriver) = In([ExtId,ExtId1,ExtId2,ExtId3])

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

For a more efficient filter syntax, you can use a Set or Array data type to simplify the run-time parameter input:

Copy
[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
...