REQUEST A DEMO

Query Anything with Dovetail Agent

In this post, we’ll look at how to create new ad-hoc queries to query anything in your system, purely via configuration.

For those that have been around the Clarify/Dovetail/First Choice space for a while, you might remember that we used to have an add-on product for the Clarify Client named Query Anything. So yes, this post title is a nod to that. 

This is a continuation of the series on queries within Dovetail Agent, so it might be useful to first review the previous posts:

  1. An overview of configuring queries in Dovetail Agent
  2. How to override the baseline queries

 

query-anything

Out of the box, Dovetail Agent allows for ad-hoc queries on:

  • Accounts
  • Contacts
  • Contracts
  • Employees
  • Site Parts
  • Sites

These are all great – but its common that we want to query on other objects – other baseline Clarify objects, or even custom objects.

Lets walk through an example of how to do this.

 

Inventory Query

For this example, lets setup querying on part inventory.

This allows us to find out things like: Do I have any of these parts in stock, and if so, how many, and where are they?

 

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 named Inventory.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="Inventory" entity="Inventory"  allowAdhocQueries="true">

query-menu

 

Query element

We’ll add a query element, which defines what table or view we’re querying from.

Here, we’ll use the parts_view view.

<query from="parts_view">

 

Facets

Then we add in our facets, which is how users will filter down the queries

    <addFacet key="partNumber" field="part_number" dataType="string" />
    <addFacet key="revision" field="mod_level" dataType="string" />
    <addFacet key="description" field="part_descr" dataType="string" />
    <addFacet key="serialNumber" field="part_serial_no" dataType="string" />
    <addFacet key="location" field="location_name" dataType="string" />
    <addFacet key="goodQuantity" field="part_good_qty" dataType="int" />
    <addFacet key="badQuantity" field="part_bad_qty" dataType="int" />
    <addFacet key="goodQuantityReserved" field="good_res_qty" dataType="int" />
    <addFacet key="badQuantityReserved" field="bad_res_qty" dataType="int" />

 

facets

 

Columns

Finally, we can add the columns that we want to be displayed to the users.

  <columns>

    <addColumn key="partNumber" template="string" />
    <addColumn key="revision" template="string" width="80"/>
    <addColumn key="description" template="string" />
    <addColumn key="serialNumber" template="string" />
    <addColumn key="location" template="string" />
    <addColumn key="goodQuantity" template="int" label="Good Quantity" width="120"/>
    <addColumn key="goodQuantityReserved" template="int" label="Good Quantity Reserved"/>
    <addColumn key="badQuantity" template="int" label="Bad Quantity" width="120"/>
    <addColumn key="badQuantityReserved" template="int" label="Bad Quantity Reserved"/>

</columns>

 

Execute the Query

And that’s it!

Save the file. Refresh the page in your browser, and we can now query on our inventory parts.

inventory-search

 

And just like all queries, we can save it, favorite it, share it, download the results, etc.

Now that we have the basics, look at some additional enhancements to make this better.

 

More data

Lets add the header indicator and inventory class columns.

The header indicator tells us whether the part instance is a header which tracks a group of serialized part instances.

The inventory class tells us whether its an inventory location, capital GL account, or expense GL account.

Perhaps we want these to show as output columns, but we don’t want to allow users to filter on these. Rather than using addFacet, we’ll use addField

<addField key="hdr_ind" field="hdr_ind" dataType="int" />
<addField key="inv_class" field="inv_class" dataType="int" />

Then we’ll include them in the result set

<addColumn key="hdr_ind" template="int" label="Header Type" width="240"/>
<addColumn key="inv_class" template="int" label="Inventory Class" width="140"/>

Now lets look at our results:

inventory-search2

Well, we got the data, but since those columns are integer flags, it doesn’t really tell us what they are.

We can make this better.

 

Transforms

We can transform those integers into string values. Here’s how:

<values>
  
  <addTransform key="inv_class">
    <mapValues>
      <addMap from="0" to="inventory location" />
      <addMap from="1" to="capital GL account" />
      <addMap from="2" to="expense GL account" />
    </mapValues>
  </addTransform>

  <addTransform key="hdr_ind">
    <mapValues>
      <addMap from="0" to="serial tracked part instance" />
      <addMap from="1" to="serial tracked header instance" />
      <addMap from="2" to="quantity tracked part instance" />
      <addMap from="3" to="empty serial tracked header instance" />
    </mapValues>
  </addTransform>

</values>

Now lets look at our results again:

inventory-search3

Much better! Now we can understand what those really are.

 

Exclude Data

Lets say that we don’t want certain data to show up. For example:

  • exclude serial tracked part instance (hdr_ind = 0)
  • exclude empty serial tracked header instance (hdr_ind = 3)
  • exclude expense GL accounts (inv_class=2)

We do that by adding where clauses to a field or facet, like this:

<!-- exclude serial tracked part instance, and empty serial tracked header instance -->
<addField key="hdr_ind" field="hdr_ind" dataType="int" >
      <where>
        <notEqual value="0" />
        <notEqual value="3" />
      </where>
</addField>

<!-- exclude expense GL accounts -->
<addField key="inv_class" field="inv_class" dataType="int" >
      <where>
        <notEqual value="2" />
      </where>
</addField>

 

Now our result set is pre-filtered to only show the inventory records that make sense:

inventory-search4

 

Note: This pre-filtering is basically the same query that Clarify uses in the Clarify Client when doing a Summarized Inventory Part Search.

 

Favorite Query: Good Inventory Search

At the beginning of this post, I suggested that this query would allow us to answer this question:

Do I have any of these parts in stock, and if so, how many, and where are they?

Now that I have that capability, I can easily create a query that will search for good inventory based on a part number.

I’ll create a query with 2 filters:

  1. Good quantity greater than 0
  2. Part Number starts with %.

 

image

The Part Number filter is set to Prompt For Value, so whenever I run this query, it will prompt me for a part number.

This makes it super easy to find out if I have any inventory available for a certain part, and if so, what location its in.

I’ll click the Star icon to make it a favorite query – so it’s quickly accessible from the left navigation sidebar in the app.

 

Question: Do I have any relays in stock? If so, how many, and where are they?

Answer:

inventory-search-relay

 

Summary

We’ve just walked through how to easily add support for querying on other objects in your system – without writing any C# code, Javascript code, or having to re-compile anything.

This also works on any custom tables or views you’ve added to your system.

We also didn’t have to touch any baseline code or config files – which means that upgrades also become easier.

The complete inventory.filter.config file that I used for this example is available here: https://gist.github.com/gsherman/32898f72c526fe52ad82fee16c4d88c8

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.

Customizing baseline queries, and adding new queries is a common part of every implementation. We’ve worked hard to try and make this easy. Hope you dig it as much as we do.

Rock on.

 

 

Dig This?

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