Developer Resource - Filters

Queries within the app are driven by filter config files. This includes:

Filter Config Files

Filter Config Files are XML files that define:

They are located in the $agent\source\Web\Filters\ directory and sub-directories. The filename format follows a convention of *.filter.config

Existing filter config files can be modified, and new ones added.

Baseline filter configurations can be over-ridden using filter override files. This allows customization without modifying the baseline files.

Filter Config File Format

<filter name="" entity="" allowAdhocQueries="">
  <categories>
    <addCategory value="" rank="" />
  </categories>

  <privileges>
    <addPrivilege value="" />
  </privileges>

  <query from="">
    <addFacet key="" field="" fields="" dataType="" fieldType="" required="" requiresFeature="" 
      select="" distinct="" requiresPrivilege="">
      <addSort isAscending="" index="" />
      <values>
        <addValue key="" value="" />
        <addList name="" />
      </values>
    </addFacet>

    <addField key="" field="" dataType="" fieldType="" distinct=""  >
      <where>
        <greaterThanOrEqual value="" />
      </where>
    </addField>

    <addJoin relationship="" type="">
      <addFacet key="" field="" dataType="" />
    </addJoin>
  </query>

  <values>
    <addTransform key="">
      <mapValues>
        <addMap from="" to="" />
      </mapValues>
    </addTransform>

    <addValue key="" value="" />
    <addValue key="">
      <convertStatusToString facetKey="" />
    </addValue>
    <addValue key="">
      <isOpen facetKey="" />
    </addValue>
    <addValue key="">
      <isOwner facetKey="" />
    </addValue>
    <addValue key="">
      <isEqual facetKey="" value="" />
    </addValue>
    <addValue key="">
      <isNotEqual facetKey="" value="" />
    </addValue>
    <addValue key="">
      <isNowBetween startFacetKey="" endFacetKey="" />
    </addValue>
  </values>

  <columns>
    <addColumn key="" template="" label="" entity="" idField="" width="" 
      requiresFeature="" />
    <addColumn key="" template="" label="" entity="" idField="" width="" 
      requiresFeature="">
      <addSort key="" />
    </addColumn>
  </columns>
</filter>

Example Filter Config File

The following is an example of a filter config file for performing adhoc queries on employees. 

<filter name="Employees" entity="Employee" allowAdhocQueries="true">
  <categories>
    <addCategory value="custom" />
  </categories>

  <privileges>
    <addPrivilege value="Query Employee" />
  </privileges>

  <query from="employee">
    <addFacet key="employeeId" field="objid" dataType="int" fieldType="identifier" />
    <addFacet key="firstName" field="first_name" dataType="string" />
    <addFacet key="lastName" field="last_name" dataType="string" >
      <addSort isAscending="true" index="0" />
    </addFacet>
    <addFacet key="phone" field="phone" dataType="string" />
    <addFacet key="email" field="e_mail" dataType="string" />
    <addFacet key="workGroup" field="work_group" dataType="string" >
      <values>
        <addValue key="{{MyWorkgroup}}" value="{{MyWorkgroup}}" />
        <addList name="WORKGROUP" />
      </values>
    </addFacet>

    <addFacet key="notification" label="Notification Preference (Any)" 
    fieldType="multiColumn" dataType="string" select="false" 
    fields="after_biz_low,after_biz_high,after_biz_mid,normal_biz_low,normal_biz_high,normal_biz_mid">
      <values>
        <addList name="Notification Types" />
      </values>
    </addFacet>

    <addJoin relationship="supp_person_off2site">
      <addFacet key="siteName" field="name" dataType="string" />
      <addFacet key="siteId" field="site_id" dataType="string" />
      <addField key="siteDatabaseIdentifier" field="objid" dataType="int" />
    </addJoin>
  </query>

  <values>
    <addValue key="employeeName" value="{lastName}, {firstName}" />
  </values>

  <columns>
    <addColumn key="employeeName" template="entityLink" idField="login_name" width="200" />
    <addColumn key="siteId" template="entityLink" entity="Site" idField="siteDatabaseIdentifier" width="100" />
    <addColumn key="siteName" template="string" />
    <addColumn key="phone" template="string" />
    <addColumn key="email" template="string" />
    <addColumn key="workGroup" template="string" />
  </columns>
</filter>

XML Reference

<filter>

The <filter> element is the top level element that defines the XML as a filter. This element is required.

Attribute Name Description
name A unique name. Useful for debugging and error reporting. Must be unique across all filters.
entity This attribute is used to indicate the base object type of the rows that are being returned. Case sensitivity matters here! Available entities include: Account, Case, ChangeRequest, Contact, Contract, Employee, Interaction, PartRequestDetail, PartRequestHeader, Site, SitePart, Solution, Subcase
allowAdhocQueries When set to true, this query will be available in the Query menu in the application, which allows a user to perform adhoc queries on this entity.
requiresFeature Optional. When specified, i.e. Interaction, the adhoc queries will only be allowed if the feature is enabled.
Child Nodes
<categories>
<privileges>
<query>
<values>
<columns>

<categories>

The <categories> element is a child of the <filter> element. Categories are optional for adhoc queries, but are otherwise used in combination with the filter entity to find the correct filter configuration for a query request.

Child Nodes Parent Nodes
<addCategory> <filter>
<addCategory>

Used to add a category to a filter.

Attribute Name Description
value Specifies on which entity the query will be made (sites, contacts)
console is a special category. Filters with this category will be included within queries for Work Items.
rank Used for console category filters to determine the order of the filter query results, which determines the order of the tabs on the console.
Parent Nodes
<categories>
<filter>

<privileges>

The <privileges> element is a child of the <filter> element. This is applicable to adhoc queries (filter.allowAdhocQueries = true). If the user has this privilege, then this filter will be available in the top-level Query menu.

Child Nodes Parent Nodes
<addPrivilege> <filter>
<addPrivilege>
Attribute Name Description
value Privilege Name.
Parent Nodes
<privileges>
<filter>

<query>

The <query> element is a child of the <filter> element. The <query> element contains all of the information required to build the SQL statement. All of the facets/fields can be included as SELECT columns, and the joins are used to specify the JOIN rules for joining tables. The facets provide information for what can be used to construct WHERE clauses. This element is required.

Attribute Name Description
from The root table or view to be used for the query.
Child Nodes Parent Nodes
<addFacet> <filter>
<addField>  
<addJoin>  
<addFacet>

The <addFacet> element is a child of the <query> element or of the <addJoin> element. The <addFacet> element defines the available filters that a user can pick when creating a query. At least one <addFacet> element is required to return any output data since queries are not executed without at least one facet being specified.

Attribute Name Description
key Unique Identifier
field The database column that will be part of the select clause and/or the where clause.
dataType int, string, bool, dateTime, float, decimal
fieldType Optional. Valid values are identifier, adhoc, multiColumn
select Optional. Whether the field will be included in the select clause or not. Defaults to true. Valid values are true, false.
required Optional. If this is set to true, then this facet must be supplied with a filter value
requiresFeature Optional. When specified, i.e. ParentChildCase, the facet will only be included if the feature is enabled.
distinct Optional. A true setting ensures only one result per value is returned and false allows for multiple results for the same value
requiresPrivilege Optional. If the user has this privilege, then this facet will be available to the user.
fields Optional. When the fieldType is multiColumn, this attribute is used to define the fields that will be evaluated.
when Optional. Name of a filter condition. The facet will only be added if that condition evaluates to true. More details on when conditions
Child Nodes Parent Nodes
<where> <addJoin>
<addSort> <query>
<values> <filter>
<addField>

The <addField> element is a child of the <query> element or of the <addJoin> element. The <addField> element includes this field in the select clause, but it's not filterable like a facet is.

Attribute Name Description
key Unique Identifier
field The database column that will be part of the select clause and/or the where clause.
dataType int, string, bool, dateTime
fieldType Optional. Valid values are identifier, adhoc, multiColumn
distinct Optional. A true setting ensures only one result per value is returned and false allows for multiple results for the same value
fields Optional. When the fieldType is multiColumn, this attribute is used to define the fields that will be evaluated.
when Optional. Name of a filter condition. The field will only be added if that condition evaluates to true. More details on when conditions
Child Nodes Parent Nodes
<where> <addJoin>
<addSort> <query>
<values> <filter>
<where>

The <where> element is a child of the <addField> element. The <where> element is used to add where clauses for its parent <addField> element.

Parent Nodes
<addFacet>
<addField>
<query>
<filter>

The where clauses are children of the <where> element.

Type Description
operator Used as the operator in the where clause for the field, i.e. greaterThanOrEqual
value Used for comparison, some use a comma separated string for multiple values 
<filter>
  <query>
    <addFacet key="lastUpdated" field="modify_stmp" dataType="dateTime">
      <where>
        <betweenOperator value="2016-09-01,2016-10-18" />
      </where>
    </addFacet>
    <addFacet key="quantity" field="demand_qty" dataType="int">
      <where>
        <greaterThan value="5" />
      </where>
    </addFacet>
  </query>
</filter>

Available operators:

<filter>
  <query>
    <addField key="someNumber">
      <where>
        <greaterThanOrEqual value="0" />
      </where>
    </addField>
  </query>
</filter>
<addSort>

The sort element is a child of the <addFacet> element.

Attribute Name Description
isAscending Set true to sort by ascending order or false for descending.
index The zero-based order in which sorts are applied to the query.
Parent Nodes
<addFacet>
<addField>
<query>
<filter> 
<filter>
  <query>
    <addField key="someNumber">
      <addSort isAscending="true" index="0" />
    </addField>
  </query>
</filter>

<values>

The <values> element is a child of the <addFacet> element.

Child Nodes Parent Nodes
<addList> <addFacet>
<addValue> <addField>
  <query>
  <filter>
<addList>

Include the elements of an application or user-defined list in the set of available values that a user can pick from. Multiple lists are allowed, and the resulting list will be a superset of all list values.

Attribute Name Description
name The name of an application or user-defined list.
Parent Nodes
<values>
<addFacet>
<addField>
<query>
<filter> 
<filter>
  <query>
    <addField key="type" field="type" dataType="string">
      <values>
        <addList name="Company Type" />
      </values>
    </addField>
  </query>
</filter>
<addValue>

Add a specific option to the list of available values.

Attribute Name Description
key The key. Supported keys are {{MyLoginName}}, {{MyWorkgroup}}, {{OpenOrRejectedFromQueue}}, or integers (often used for status facets).
value The value shown to the user for this key. Typically the same as the key, or an example of use, i.e. "0 (Active)" for a "0" key.
Parent Nodes
<values>
<addFacet>
<addField>
<query>
<filter> 
<filter>
  <query>
    <addFacet key="status" field="status" dataType="string">
      <values>
        <addValue key="0" value="0 (Active)" />
        <addValue key="1" value="1 (Inactive)" />
        <addValue key="2" value="2 (Obsolete)" />
      </values>
    </addFacet>
  </query>
</filter>

<addJoin>

The <addJoin> element is a child of the <query> element. Multiple joins are allowed. Nested joins are allowed.

Attribute Name Description
relationship The schema relation name for traversing from the parent table to the joined table.
type Optional. Inner or Outer join. Valid values are "inner", "outer". Defaults to "inner".
when Optional. Name of a filter condition. The join will only be added if that condition evaluates to true. More details on when conditions
Child Nodes Parent Nodes
<addFacet> <addFacet>
<addField> <addField>
<addJoin> <addJoin>
  <query>
  <filter>

Adhoc <addJoin> elements are also used to connect views and tables

Attribute Name Description
from The field on the parent table/view
to The field on the joined table
table The table being joined
adhoc true indicates that this is an adhoc join 
<filter>
  <query>
    <addJoin relationship="site2contact_role">
      <addFacet key="roleName" field="role_name" dataType="string" />

      <addJoin relationship="contact_role2contact">
        <addFacet key="firstName" field="first_name" dataType="string" />
      </addJoin>
    </addJoin>
  </query>
</filter>

<values>

The <values> element is a child of the <filter> element. Values are used when combining multiple facets/fields together into one column.

Child Nodes Parent Nodes
<addTransform> <filter>
<addValue>  
<addTransform>
Attribute Name Description
key unique identifier
<mapValues>
Child Nodes Parent Nodes
<addMap> <addTransform>
  <values>
  <filter>
<addMap>
Attribute Name Description
from value
to new value
Parent Nodes
<mapValues>
<addTransform>
<values>
<filter> 
<filter>
  <values>
    <addTransform key="isPrimarySite">
      <mapValues>
        <addMap from="1" to="True" />
        <addMap from="2" to="False" />
      </mapValues>
    </addTransform>
  </values>
</filter>
<addValue>

There are also methods that can be specified to provide the value data. <convertStatusToString facetKey="status" /> will translate a status facet into a string value, i.e. 0 gets returned as Active.

Attribute Name Description
key unique identifier
value The data returned for this value, consisting of query facets/fields, i.e. value="{firstName} {lastName}"
Child Nodes Parent Nodes
<isEqual> <values>
<isInQueue> <filter>
<isNotEqual>  
<isNowBetween>  
<isOpen>  
<isOwner>   
<filter>
  <values>
    <addValue key="fullName" value="{firstName} {lastName}" />
  </values>
</filter>
<isEqual>

This value property will check the facet value is equal to the specified value, and return true or false.

Attribute Name Description
facetKey The facet/field from the query containing the value for comparison
value The value to be compared against
Parent Nodes
<addValue>
<values>
<filter> 
<filter>
  <values>
    <addValue key="isSerialized">
      <isEqual facetKey="sNTrack" value="1" />
    </addValue>
  </values>
</filter>
<isInQueue>

This value property will check the specified facet value as an Queued condition, and return true or false.

Attribute Name Description
facetKey The facet/field from the query containing the object condition
Parent Nodes
<addValue>
<values>
<filter>
<isNotEqual>

This value property will check the facet value is not equal to the specified value, and return true or false.

Attribute Name Description
facetKey The facet/field from the query containing the value for comparison
value The value to be compared against
Parent Nodes
<addValue>
<values>
<filter> 
<filter>
  <values>
    <addValue key="isInstalled">
      <isNotEqual facetKey="levelToBin" value="999" />
    </addValue>
  </values>
</filter>
<isNowBetween>

This value property will compare the current date to see if it is between a start and end date, and return true or false.

Attribute Name Description
startFacetKey The starting date value to be compared against
endFacetKey The ending date value to be compared against
Parent Nodes
<addValue>
<values>
<filter> 
<filter>
  <values>
    <addValue key="isActive">
      <isNowBetween startFacetKey="startDate" endFacetKey="endDate" />
    </addValue>
  </values>
</filter>
<isOpen>

This value property will check the specified facet value as an Open condition, and return true or false.

Attribute Name Description
facetKey The facet/field from the query containing the object condition
Parent Nodes
<addValue>
<values>
<filter>
<isOwner>

This value property will compare the specified facet value against the current user's login name, and return true or false.

Attribute Name Description
facetKey The facet/field from the query to compare
Parent Nodes
<addValue>
<values>
<filter>

<columns>

The list of columns is the data that gets returned to the front-end of the application. For queries whose result is displayed in a grid, these are the columns that are displayed in the grid.

Child Nodes Parent Nodes
<addColumn> <filter>
<addColumn>

These are placed on the grid from left to right in the order in which they are added.

Attribute Name Description
key The key of the facet/field from the query definition
template int, string, date, dateTime, timeAgo, entityLink, currency, number. See Column Template Examples for more details.
precision Optional. If template="number", then the precision defines how many decimal places will be displayed. Ignored for other templates.
label Optional. The localization key for the column heading. If the localization key is not found, this label will be used as the actual column heading. If not specified, the column key is used to check for localization or as the actual header.
width Optional. The default column width, in pixels.
idField Optional. When a column is specified as an entityLink, this attribute can be used to override the facet/field used to build the link.
entity Optional. Identify which entity this column is related to. Currently in use for the entityLink template to determine the URL. The entity must match a key found in EntityUrlConstants.js. This only needs to be specified if linking to an entity that is different from the filter entity.
requiresFeature Optional. When specified, i.e. ParentChildCase, the column will only be included if the feature is enabled.
Child Nodes Parent Nodes
<addSort> <columns>
  <filter> 
<filter>
  <columns>
    <addColumn key="id" template="entityLink" idField="databaseIdentifier" width="120"/>
    <addColumn key="name" template="string" />
  </columns>
</filter>
<addSort>
Attribute Name Description
key The key of the facet/field from the query definitions
Parent Nodes
<addColumn>
<columns>
<filter> 
<filter>
  <values>
    <addValue key="owner" value="{ownerFirstName} {ownerLastName}" />
  </values>
  <columns>
    <addColumn key="owner" template="string">
      <addSort key="ownerLastName" />
      <addSort key="ownerFirstName" />
    </addColumn>
  </columns>
</filter>

Column Template Examples

Decimals

Given a decimal value of 12345.6789, this value will be displayed differently based on the template.

template precision output
string N/A 12345.6789
currency N/A $12,345.68
number N/A 12,345.6789
number 0 12,346
number 2 12,345.68
number 4 12,345.6789
number 6 12,345.678900

Floats

Given a float value of 12345.6789, this value will be displayed differently based on the template.

template precision output
string N/A 12345.6787
currency N/A $12,345.68
number N/A 12,345.6787
number 0 12,346
number 2 12,345.68
number 4 12,345.6787
number 6 12,345.678700

Date Time

template output
timeAgo a few seconds ago, a few days ago, in 22 days, etc.
date 12/08/2016
dateTime 09/04/2008 12:00 AM

multiColumn facets

multiColumn facets allow for querying mutiple columns at the same time.

For example, lets say I want to query for employees where any (of their 6) notification preferences is equal to SMS.

Within the employees.overrides.filter.config file, I can add a new multiColumn facet:

<addFacet key="notification" label="Notification Preference (Any)" fieldType="multiColumn" dataType="string" select="false" 
fields="after_biz_low,after_biz_high,after_biz_mid,normal_biz_low,normal_biz_high,normal_biz_mid">
  <values>
    <addList name="Notification Types" />
  </values>
</addFacet>

This enables users to create a query for employees such as: "Notification Preference (Any) Is equal To SMS"

The SQL that is generated for this query would basically look like:

  SELECT * FROM table_employee  
  WHERE 'SMS' IN (after_biz_low, after_biz_high, after_biz_mid, normal_biz_low, normal_biz_high, normal_biz_mid)

Note: The only operator enabled for multiColumn facets is "Is Equal To".

When Conditions

A when attribute allows for an element to be dynamically included or not.

Examples

<!-- user can only see general subcases -->
<addFacet key="subcaseType" field="sub_type" dataType="string" when="UserIsARestrictedUser">
    <where>
      <equals value="General" />
    </where>
</addFacet>

<!-- user can only see subcases in a wipbin named public -->
<addJoin relationship="subc_wip2wipbin" type="inner" when="UserIsARestrictedUser">
    <addField key="wipbin" field="title" dataType="string">
        <where>
          <equals value="public" />
        </where>
    </addField>
  </addJoin>

Using when attributes requires writing custom C# code. In the example above, UserIsARestrictedUser is a custom class that must be written within the Dovetail Agent application.

A when attribute can be added to:

Example

The following is an example of a custom filter condition.

If the current user's site type is CUST, then the user is considered a restricted user, and the UserIsARestrictedUser condition will return true. Else, it will return false. 

using Dovetail.SDK.Bootstrap.Clarify;
using FubuCore;
using FChoice.Foundation.Clarify;
using Dovetail.SDK.Bootstrap.Clarify.Extensions;
using Agent.Core.Filters;

namespace custom
{
  public class UserIsARestrictedUserCondition : IFilterCondition
  {
    public bool ShouldExecute(IServiceLocator services)
    {
      ClarifyDataRow siteRow;

      var logger = services.GetInstance<ILogger>();
      var user = services.GetInstance<ICurrentSDKUser>();
      var session = services.GetInstance<IClarifySession>();
      var dataset = session.CreateDataSet();

      var userGeneric = dataset.CreateGeneric("user");
      userGeneric.Filter(f => f.Equals("login_name", user.Username));

      var employeeGeneric = dataset.CreateGeneric("employee");
      employeeGeneric.TraverseFromParent(userGeneric, "user2employee");

      var siteGeneric = dataset.CreateGeneric("site");
      siteGeneric.TraverseFromParent(employeeGeneric, "supp_person_off2site");

      userGeneric.Query();
      siteRow = siteGeneric[0];

      var siteType = siteRow["site_type"].ToString();     
      var result = siteType.EqualsIgnoreCase("CUST");

      return result;
    }

  }
}