How to Include Calculated Data in Query Results

September 19, 2017

I’ve blogged before about the Filter Config and Filter Overrides within Dovetail Agent.

I totally dig this feature, as it makes it super easy to customize the queries within the app without having to write code.

We know it’s easy to show data that comes from a database column within the query results – such as a contact’s name or phone number.

But what if we wanted to show something that is more dynamic? i.e. something that needs to be calculated?

 

For example, in the result set for contact queries, what if we wanted to show the number of open cases that a customer has?

There’s not an existing field in the database that contains this data. The number of open cases needs to be summed up.

So how can we do this in a query?

SQL Views to the rescue

In the past, I’ve discussed SQL views, and how they can be super powerful. As a refresher, here’s what they are:

SQL views allow an administrator to define the actual SQL for a view, as opposed to the schema tool dynamically building the SQL.

SQL views allow for:

• A view with only one table. A common use of this is to take advantage of aggregates (max, min, count, sum, avg, etc.).
• A view using multiple, unrelated tables.
• A view that uses the UNION keyword to consolidate between multiple tables or views.

Number of Open Cases for a Contact

So, what we can do is to create a SQL View that contains the number of open cases for a contact.

Our view will return the contact objid, and a count of the number of open cases.

A simple Dovetail SchemaEditor schemascript will do the trick here.

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">

   <addSqlView name="dt_contct_open_cases" id="502">
      <baseTable>case</baseTable>
      <groupName>Dovetail</groupName>
      <description>Number of Open Cases for a given contact</description>
      <sql>
      select c.objid as contact_objid, count(e.elm_objid) as num_open_cases 
      from table_contact c
      LEFT OUTER JOIN table_extactcase e ON c.objid= e.contact_objid AND e.condition like 'Open%' 
      group by c.objid
      </sql>
   </addSqlView>

   <addSqlViewColumn name="contact_objid" sqlView="dt_contct_open_cases" dataType="Integer"/>
   <addSqlViewColumn name="num_open_cases" sqlView="dt_contct_open_cases" dataType="Integer" />

</schemaScript>

SQL Query

We can now run a simple SQL query that returns the number of open cases for a contact, given their primary key (objid).

 

Now that we have the schema setup to return the data we want, lets include this data as part of the contact query.

Filter Config Overrides

I previously blogged about how to use filter config override files to customize queries.

For this scenario, we’ll create an override config file for the contacts query.

We’ll add a join from the existing query to the new view that we just created.

<addJoin from="con_objid" to="contact_objid"  table="dt_contct_open_cases" adhoc="true">

And then make the # of Open Cases data available for filtering

<addFacet key="numberOpenCases" field="num_open_cases" dataType="int" label="# of Open Cases"/>

And finally make the # of Open Cases data available as a output column

<columns>
    <addColumn key="numberOpenCases" template="number" label="# of Open Cases" index="3" width="140"/>
</columns>

Here’s the complete contacts.override.filter.config file:

<filter overrides="Contacts">

  <query from="rol_contct">

  <addJoin from="con_objid" to="contact_objid"  table="dt_contct_open_cases" adhoc="true">
    <addFacet key="numberOpenCases" field="num_open_cases" dataType="int" label="# of Open Cases"/>
  </addJoin>

</query>

  <columns>
    <addColumn key="numberOpenCases" template="number" label="# of Open Cases" index="3" width="140"/>
  </columns>

</filter>

Simply copy that file to the $dovetailAgent\Filters\custom directory.

That’s it.

Test it

Within Dovetail Agent, we can create a new contact query, and we can see that the Number of Open Cases is now displayed in the results.

Filtering

We can also filter the query based on that data.

For example, lets filter the query to only show those contact with more than 50 open cases.

Summary

While the example here is cool, it very well may not exactly fit your specific needs.

But that’s OK – I think it’s the concepts here that are most interesting.

1. We can use SQL views to calculate, aggregate, or take advantage of other powerful elements of the SQL language
2. We can use filter config override files to easily add filters and output columns based on the SQL view data

And both of these these tasks – a schema change and a config file – are within the wheelhouse of Clarify/Dovetail sys admins.

There’s no code involved here, such as C# or JavaScript, so these types of changes should not require your development team.

 

So what do you think? Could you see this being useful in your environment?

Have some ideas of things that would fit into this process?  Drop a comment below and let me know!

 

Dig This?

Sign up to get information like this delivered right to your inbox.

Sign up for the Dovetail Newsletter