Dovetail Agent Documentation

Developer Resource - Filters

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

Adhoc Queries available from the top-level Query menu, including:
- Work Items (Case/Subcase/Solution/Change Request/Part Request)
- Accounts, Contacts, Contracts, Employees, Site Parts, Sites
Tabs that contain filterable queries (such as the Contacts tab on the Site page)

Filter Config Files

Filter Config Files are XML files that define:

what tables/views are being queried
joins
what filters (facets) are available for the user to filter the query by
what non-facet fields are queried for use in output data
what output data is returned to the front-end of the application (columns)
required privilege to make the entry available in the top-level Query menu
required features to make the columns available in the output data

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, 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.

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.

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.

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:

Date / DateTime
- after
- before
- betweenOperator (requires 2 comma separated values)
- notBetween (requires 2 comma separated values)
- on
- onOrAfter
- onOrBefore
- today
- tomorrow
- yesterday
String
- contains
- endsWith
- isInString
- isNotInString
- notContains
- notEndsWith
- notStartsWith
- startsWith
- stringLike
- stringNotLike
Number
- equals
- greaterThan
- greaterThanOrEqual
- isInInteger
- isNotInteger
- lessThan
- lessThanOrEquals
- withinLastDays
- withinLastHours
- withinNextDays
- withinNextHours

<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".

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 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".