How to Include Calculated Data in Query Results
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>
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.
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.
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.
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.
- We can use SQL views to calculate, aggregate, or take advantage of other powerful elements of the SQL language
- 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.
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!
Sign up to get information like this delivered right to your inbox.