How To Query Case Activities in Dovetail Agent
I recently posted about how to Query Anything with Dovetail Agent. Specifically I showed how to setup a custom query for part inventory.
In this post, we’ll look at creating a specialized flavor of a existing query.
We have built-in query capabilities for work items (including cases), but instead, lets walk-through an example of allowing for querying on case activities.
For example, show me how many cases were dispatched to the benefits queue in the last 90 days.
For case activities, we’ll need to query the activity log (table_act_entry). In general, I discourage querying against this table using your online production database, as it’s typically the biggest table in the database, and queries against this table can adversely affect performance for everyone using the system. But, depending on your particular use case, your database, database size, database tuning, etc – this may be ok.
You could simply override the baseline case filter config, and join in the act_entry table. (See this post for details on how to use filter overrides to extend baseline queries.)
But, this would open up querying against act_entry to everyone. Likely not something we want to do.
Instead, we’ll create a new filter config for case activities, and we’ll restrict it so that only certain users can do this. For example, maybe we only want to expose this query to Admins.
Enough talk, lets get to work.
Create a filter.config file
In the previous post when discussing filter overrides, I explained the file naming convention of *.filter.config, and the use of the $\source\Web\Filters\custom directory.
This same convention is followed here. So lets create a file: $\source\Web\Filters\custom\case.activities.filter.config
In this file, we’ll build up the configuration that drives this query.
Filter element
First, we’ll create a filter element, we’ll give it a name, entity, and set allowAdhocQueries to true, so that this will show up in the query menu.
<filter name="Case Activities" entity="Cases" allowAdhocQueries="true">
Query element
We’ll add a query element, which defines what table or view we’re querying from.
Here, we’ll use the case_alst view.
<query from="case_alst">
This view includes the act_entry information, activity name (from table_gbst_elm), case objid, and the login_name of the user who performed the activity.
Facets
Then we add in our facets, which is how users will filter down the queries.
<addFacet key="login_name" field="login_name" dataType="string" /> <addFacet key="additional_info" field="add_info" dataType="string" />
Facet with list of values
We’ll add in the Activity Name as a facet ,and make the Activity Name list available for filtering
<addFacet key="activity_name" field="act_name" dataType="string" > <values> <!-- Make sure this list has a default value --> <addList name="Activity Name" /> </values> </addFacet>
This facet with a list of values will allow users to pick from a list, like so:
Join and Facet
We’ll create a join from the case_alst view to the case table, so that we can get the case ID number, which we’ll show as an output column.
<addJoin adhoc="true" from="parent_objid" to="objid" table="case"> <addFacet key="caseId" field="id_number" dataType="string" /> </addJoin>
What’s interesting here is that this is an adhoc join. This means that the schema definition doesn’t have a join from the case_alst view to the case table.
But we can use an adhoc join to simulate this. We simply define it as an adhoc join, tell it what table we’re joining to, and which two columns to use for the join (the To and From attributes)
This is much easier than having to modify the schema to create a real join.
Facet to Limit the Query
As I mentioned earlier, querying against the act_entry table can cause performance issues. One way to help with this is to limit the query. Here, we’ll limit the query so that it only queries activities since 1/1/2016.
<!-- Limit to recent activities --> <addFacet key="entry_time" field="entry_time" dataType="dateTime" > <where> <after value="1/1/2016" /> </where> </addFacet>
Columns
Add the columns that we want to be displayed to the users.
<columns> <addColumn key="entry_time" template="dateTime" width="100"/> <addColumn key="activity_name" template="string" label="Activity" width="150"/> <addColumn key="login_name" template="string" entity="Employee" idField="login_name" width="100"/> <addColumn key="caseId" template="entityLink" entity="Case" idField="id_number" width="100"/> <addColumn key="additional_info" template="string" width="300"/> </columns>
Notice that the case id column uses an entity link template, which will turn the case id into a link that navigates the user to that case.
Privileges
As I mentioned earlier, we’ll restrict the query so that only certain users can use it. We’ll do this by adding a privilege.
<privileges> <addPrivilege value="Admin" /> </privileges>
Execute the Query
And that’s it!
Save the file. Refresh the page in your browser, and we can now query on case activities (if you have the Admin privilege)
Filter Config File
The complete filter config file I used in this example is freely available at https://gist.github.com/gsherman/ec7dbe92a328f82bf3a722a156920e7c
Wrap Up
Even though we already allow for case queries out of the box, we may want to have a specialized flavor of a case query. In this instance, the flavor was Case Activities.
This allows us to create a very specific form of case queries, and restrict it to certain users.
Normal users can still create case queries like normal, and they never go near the activity log as part of their query. Our Admins get additional capabilities as well (since they tend to know what they’re doing).
And we were able to all of this via a filter config file – without having to write any C# or Javascript code.
Pretty cool stuff.
Dig This?
Sign up to get information like this delivered right to your inbox.
