Run-Time Query Parameter Evaluation
Overview
One of the new features coming soon in Dovetail Agent 4.1 is the enhanced capability of using parameters in fcQuery that will be evaluated when a query is executed. This allows queries to be designed with much more flexible structure.
Dates are a very important parameter to be evaluated at run-time, and being able to build the query to do this automatically keeps the user from having to enter the date manually when running the query. Queries can be created to look at specific date-oriented information (i.e. This Week) without having to change to values of the date parameters each time the query is run.
Shared queries are also a very easy place to see where run-time evaluation of a query parameter will be helpful. For example, if a query is created (and shared to all users) to find all Cases for the current case owner, then any user that runs that query will find their cases without having to provide their login-name themselves. When the query is run, the current user’s login name (i.e. My Login Name) is matched against each case owner field.
Required Changes
There were a some changes that were necessary to implement this feature:
The fcQuery Edit form was modified to allow the run-time parameters to be used. In this release of Dovetail Agent there are run-time parameters for date comparisons and current user comparisons.
The SQL that is executed for a query is now evaluated every time it is run. Previously the SQL was built when the query was created or modified, and only regenerated at run-time if the query had changeable parameters. This also guarantees that if the base object was changed without updating the query itself, the resulting SQL that gets executed is current.
Defining the Parameters
The parameters that are set up to be used in the queries are currently defined within the source code of Dovetail Agent. The source code is contained in fcQueryVariables.js, and it can be customized by updating that file. Each parameter is built and evaluated based on the following properties:
Name – the visible name of parameter that is used on the fcQuery Edit page (i.e. My Login Name)
Value – how the parameter is referred to within the application itself (i.e. MyLoginName)
Operation – how the parameter is used in an expression when it is evaluated (i.e. "is equal to")
Expansion – indicates the run-time data source the parameter is evaluated against (i.e. FCSession(‘user.login_name’))
DataType – the data type of the table columns where the parameter will be available (i.e. "string")
The following parameters are currently defined for use in Dovetail Agent:
My Login Name – the login name of the user running the query
My First Name – the employee first name of the user running the query
My Last Name – the employee last name of the user running the queryCurrent Date/Time – the data and time the query is run – used to find any date Before the current time
This Year – this evaluates to January 1 of the current year (12:00 AM) – used to find any date Since the start of the current year
This Quarter – this evaluates to 1st day of the first month of the current quarter (12:00 AM) – used to find any date Since the start of this quarter
This Month – this evaluates to 1st day of this month (12:00 AM) – used to find any date Since the start of this month
This Week – this evaluates to Sunday day of this month (12:00 AM) – used to find any date Since the start of this weekLast Month – this evaluates to all of last month – used to find any date During the month prior to the current month
Last Week – this evaluates to all of last week (Sunday thru Saturday) – used to find any date During last week
Evaluating the Parameters
All of the user parameters are evaluated against the current Session object that is created during the login process. The user login_name and employee first and last name for the user are both properties of the session object, so that information is easily accessed at run-time.
All of the date parameters are evaluated at run time against the server clock. The current date is retrieved from the system clock, and then the proper date comparisons are built based on the criteria defined for the parameter. The SQL that is generated allows for SQL server and Oracle differences.
Implementing the New Feature
Once you have installed or upgraded to Dovetail Agent 4.1, you can get started with run-time parameters. The best way to understand this feature is to add or modify a query and put it to use. Any fcQuery object that has a date property can be used to get familiar with the run-time parameters. On the fcQuery Edit form, just select the date column, and the Substitution Values will be shown in a new drop-down list for selection. When one of the values is chosen from the list, the operator gets set to the correct option, and the clause can be added to the query. Finish the edits and save the changes, then use the Find button to see the results.
Summary
This new feature will be very useful when building queries for time-sensitive analysis. In previous versions, this type of query required the use of changeable properties, which had to be altered manually every time the query was executed. Now, the query can be built using the run-time evaluation parameters, and the targeted information will always included correctly without user intervention.
This will also be helpful when building queries to be shared with other users. The current user parameters can be used in the query, and any user that runs the query will see their own data without having to type in their login name when they run the query.
As part of the continuous effort to enhance and improve our applications, we welcome any comments or suggestions about this new feature.
