Query Converter – a New Tool to Migrate ClarifyCRM Queries to fcQuery Objects
Long time ClarifyCRM users may have a number of traditional Clarify queries accumulated over time. After such a business acquires Dovetail Software CRM products for Clarify database, it becomes one of the migration issues: how to reuse Clarify queries in the new thin client.
Dovetail Software Agent has no built-in support for Clarify queries, and for a good reason. fcQuery is far superior to traditional Clarify query model and all the users are highly encouraged to abandon Clarify queries in favor of fcQueries. If the business no longer uses Clarify Classic Client, migration is a must.
Until the release of Dovetail Agent v.4.2. the only way to move a Clarify query to fcQuery was to recreate it manually.
The new Dovetail Query Converter makes migration of Clarify queries into fcQueries very easy.
Clarify Query vs. fcQuery
These are the main points of distinction between both designs:
ClarifyCRM Queries:
- only nine database objects can have queries defined,
- search results are presented using a view not the base table, which limits the set of available columns,
- search results can be sorted only by one data element,
- to add a column to a query three Clarify forms and the view must be customized,
- a query executes in two steps: filtering step followed by presentation step. First, a base-table query is executed using defined keywords and properties to retrieve a list of objids. Next, a view query uses the list of found objids to retrieve the final set of records.
fcQueries:
- any database object can have queries defined,
- the way search results are presented are defined by system administrator who decides which columns are included and are visible,
- search results can be sorted by up to two data elements,
- to add a column to a query no customizations are required, can be done online by system administrator,
- a query executes in one step, all selection criteria are applied immediately.
There are many more goodies available in fcQuery making it flexible and efficient. More details can be found in these blog posts: Opening View Objects from Query Results and Run-Time Query Parameter Evaluation by Sam Tyson.
The query migration process
The tool is designed as a batch script to run on the server in either ‘preview’ or ‘convert’ mode. The ‘preview’ mode allows for evaluation of final results to make sure they meet the expectations. Even after the actual conversion is done and it is found that a resulting query is deficient, it can be modified in Dovetail or deleted and recreated.
The script can be run against a single query, all the queries created by a single user, or all the queries in the database. The original Clarify queries are not affected in any way, they remain available for use. The script may be re-run over and over again, if a query has already been migrated it will be ignored (if it has changed since last run, it won’t be recreated or updated. In such a case it is better to delete it in Dovetail and recreate entirely).
As a result:
- each Clarify query will have its fcQuery counterpart created under the same name,
- all selection and sort criteria will be preserved,
- the output columns will have the same headings and sequence as the original result view,
- query ownership will be preserved,
- if a query was iconified in Clarify it will be iconified in Dovetail Agent,
- if a query was shared in Clarify it will be shared in Dovetail Agent with the same users.
Things to consider
When it is done manually, fcQueries are created in two places, in this sequence:
- system administrator defines an fcQuery Object which defines a custom ‘view’ of the data. It is not a view in database terms, rather, it is a ‘mask’. Only the ‘unmasked’ data elements defined for the fcQuery Object will be available later to the users.
- agents personalize the fcQuery Object and create actual queries used to retrieve data.
Query Converter will create one fcQuery Object for each base Clarify query table – that means that up to nine fcQuery Objects may be created. Then, all the Clarify queries for a specific object will be tied to the same fcQuery Object, i.e. all the Clarify case queries defined by all the users will refer to the same fcQuery Object with alias name of ‘case query’. This ‘case query’ fcQuery Object will then contain all the columns:
- defined as result view columns in the database,
- defined as search criteria properties in any case query,
- defined as sort properties in any case query,
- case_history column because it is used by keyword search.
It may also happen that Clarify Query forms have been customized to include additional result view columns and/or search/sort criteria properties. Some users may be grouped in base resource configuration having access only to out-of-box queries while others may have access to the customized ones. Query Converter treats these queries as they are separate entities, both base and custom queries will be migrated. The custom ones for cases will be associated with a separate fcQuery Object with alias name of ‘case query ver_customer‘ where ver_customer is the version name of Clarify Query Edit form #806.
After the migration
After the Clarify queries are migrated to fcQueries, it is recommended that system administrator does a thorough review of the fcQuery Objects and eventually add/remove/reorder the columns. Then it is up to the owners to further personalize, iconify, share and refuse the queries.
