Configurable and Customizable Queries in Dovetail Agent

November 8, 2016

I’ve talked in the past about the power of queries within Dovetail Agent and how end users can build and use them.

In this post, I’ll dive a bit deeper into how queries are configured, and how they can be configured and customized.

Query Configuration

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
- Custom queries
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

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. I’ll cover this topic in more detail in a future post.

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">
  <privileges>
    <addPrivilege value="Query Employee"/>
  </privileges>

  <query from="employee">
    <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>

    <addJoin relationship="employee2user">
      <addFacet key="status" field="status" dataType="string">
        <values>
          <addValue key="1" value="Active" />
          <addValue key="0" value="Inactive" />
        </values>
      </addFacet>

      <addFacet key="loginName" field="login_name" dataType="string"/>

      <addJoin relationship="user_access2privclass">
        <addFacet key="privilegeClass" field="class_name" dataType="string"/>
      </addJoin>
    </addJoin>

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

  <values>
    <addTransform key="status">
      <mapValues>
        <addMap from="1" to="Active" />
        <addMap from="0" to="Inactive" />
      </mapValues>
    </addTransform>
  </values>

  <columns>
    <addColumn key="firstName"      template="entityLink" idField="loginName" width="200"/>
    <addColumn key="lastName"       template="entityLink" idField="loginName" width="200"/>
    <addColumn key="loginName"      template="string" />
    <addColumn key="siteId"         template="string"     width="100" />
    <addColumn key="siteName"       template="string"     label="SITE" />
    <addColumn key="phone"          template="string"     width="150" />
    <addColumn key="email"          template="string" />
    <addColumn key="status"         template="string"     width="100"/>
    <addColumn key="workGroup"      template="string" />
    <addColumn key="privilegeClass" template="string" />
  </columns>
</filter>

Filter Config File elements

Here’s a rundown of the common elements we can use within a filter config file.

Filter

The filter element defines the name of the query, which entity (within the application) this is related to, and whether it’s an Adhoc query or not.

An Adhoc Query is one that will be available in the Query menu in the application.

<filter name="Employees" entity="Employee" allowAdhocQueries="true">

Because allowAdhocQueries is set to true, we get an Employees option in the Query menu:

Privileges

Privileges allows us to define which privileges are required in order for the user to have access to this query.

If the user doesn’t have this privilege, then this query will not be displayed.

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

Base query table/view

The query element defines what table or view it’s querying against. In this example, our query is from the employee table.

<query from="employee">

Joins

Use the addJoin element to traverse to another table

<addJoin relationship="supp_person_off2site">

Facets

The facets define the available filters that a user can pick when creating a query.

<addFacet key="firstName" field="first_name" dataType="string" />

This list is built using the addFacet elements:

Fields

A field is a database column that is returned as part of the query, but hidden from users. For example, objids.

<addField key="siteDatabaseIdentifier" field="objid" dataType="int" />

This is useful when we want to construct a link to an entity that needs an objid as part of the URL.

Columns

Columns define what data is presented to the user.

<addColumn key="loginName" template="string" />

Output columns for an employee query:

Advanced Features

Where

Where clauses can be added to facets to limit the result set automatically.

For example, this example would limit the query so that it only returns inventory where the good quantity is greater than zero:

<addFacet key="goodQuantity" field="part_good_qty" dataType="int" />
  <where>
    <greaterThan value="0" />
  </where>
</addFacet>

Values

Single Value

As part of a facet, we can present the user with some options.

For example, when filtering employees by workgroup, we can include the “{{MyWorkgroup}}” variable in the list of options

<addFacet key="workGroup" field="work_group" dataType="string" >
  <values>
    <addValue key="{{MyWorkgroup}}" value="{{MyWorkgroup}}" />
  </values>
</addFacet>

Lists

We can also include a list of options, wither an application list or user-defined list

<addFacet key="workGroup" field="work_group" dataType="string" >
  <values>
    <addList name="WORKGROUP" />
  </values>
</addFacet>

And we can combine single values and lists together:

<addFacet key="workGroup" field="work_group" dataType="string" >
  <values>
    <addValue key="{{MyWorkgroup}}" value="{{MyWorkgroup}}" />
    <addList name="WORKGROUP" />
  </values>
</addFacet>

Example of the workgroup filter, with a single list value ({{MyWorkgroup}})and the WORKGROUP user-defined list:

Facet Mappings

Often in Clarify, integers are used rather than strings when storing data in the database. But user’s don’t know this, nor should they.

We can use Values to make this transparent to users.

<addJoin relationship="employee2user">
  <addFacet key="status" field="status" dataType="string">
    <values>
      <addValue key="1" value="Active" />
      <addValue key="0" value="Inactive" />
    </values>
  </addFacet>
</addJoin>

Column Transforms

While the Value mappings are useful for facets, we can use a similar mechanism for output columns.

<values>
  <addTransform key="status">
    <mapValues>
      <addMap from="1" to="Active" />
      <addMap from="0" to="Inactive" />
    </mapValues>
  </addTransform>
</values>

Here we can see that the employee status is displayed as Active or Inactive, as opposed to 1 or 0 (which is what their actual values are in the database).

Templates

Output columns have a template, which allows us to control how the output column is rendered.

Template	Description
int	Simple integer value
string	Simple string value
date	Short Date. Example: 11/04/2016
dateTime	Short Date and Time. Example: 11/04/2016 4:10 PM
timeAgo	Time Ago. Example: “3 days ago”
entityLink	Creates a link to the entity. More details follow.

Entity Link

The entityLink template within addColumn creates a link to the entity (defined in the filter element) with the idField at the end.

For example, given this:

<filter name="Employees" entity="Employee"  allowAdhocQueries="true">
…
<addColumn key="firstName"      template="entityLink" idField="loginName" />
…

The first name column would be a hyperlink to the employee entity, using the loginName at the end of the url, like so:

http://myserver/agent/support/employee/{loginName}

Or a specific example:

http://myserver/agent/support/employee/carrier

Summary

This new query configuration is pretty powerful, and allows for a good deal of configuration and customization without having to modify any code. It also makes upgrades to newer versions of Dovetail Agent much easier.

The Dovetail Agent documentation has tons more specifics on filter config files, and all the available elements and attributes. Check out the Developer Resource – Filters section in the docs.

Next up, I’ll continue this series on Query configurations:

How to override the baseline filter configs
How to add custom filter configs

Stay tuned.

Query Configuration

Filter Config Files

Example Filter Config File

Filter Config File elements

Filter

Privileges

Base query table/view

Joins

Facets

Fields

Columns

Advanced Features

Where

Values

Single Value

Lists

Facet Mappings

Column Transforms

Templates

Entity Link

Summary

Dig This?

Related

Dovetail

HR SOLUTIONS

CLARIFY SOLUTIONS

Configurable and Customizable Queries in Dovetail Agent

Query Configuration

Filter Config Files

Example Filter Config File

Filter Config File elements

Filter

Privileges

Base query table/view

Joins

Facets

Fields

Columns

Advanced Features

Where

Values

Single Value

Lists

Facet Mappings

Column Transforms

Templates

Entity Link

Summary

Dig This?

Share this:

Related