Sam Tyson

Getting Things Done - with Dovetail ArchiveManager (DIET)

styson — Fri, 24 Oct 2008 20:07:13 GMT

I am currently working on a project where I need to take a database from empty to usable with a script file. I am at the point where I need to import data files, and the data files I need to import don't exist. The customer has the data, and luckily they have a tool to create nice, tidy data files for me - Dovetail Software's Data Import Export Tool (DIET).

The challenge for me is to make it easy for them to use DIET, so I can get the data files that I need.

DIET is the perfect tool for the job, because it is designed to do exactly what I need. I need data from three related tables in the database for this file, so I created a directives file to specifically control what information to extract.

User-Defined Lists are one of the most challenging data structures within the database, but the directives file was pretty easy to build (with a little help from Gary Sherman). Here is the end result, which I will explain below:

EXPORT OBJECT hgbst_lst
UNIQUE_FIELD = "title"
ACTIONS = EXPORT, DELETE
    TO hgbst_show THROUGH hgbst_lst2hgbst_show
      ACTIONS = EXPORT, DELETE
        TO hgbst_elm THROUGH hgbst_show2hgbst_elm
          ACTIONS = EXPORT, DELETE
        END_TO
        TO hgbst_show THROUGH prnt_chld2hgbst_show
          ACTIONS = EXPORT, DELETE
            TO hgbst_elm THROUGH hgbst_show2hgbst_elm
              ACTIONS = EXPORT, DELETE
            END_TO
            TO hgbst_show THROUGH prnt_chld2hgbst_show
              ACTIONS = EXPORT, DELETE
                TO hgbst_elm THROUGH hgbst_show2hgbst_elm
                  ACTIONS = EXPORT, DELETE
                END_TO
                TO hgbst_show THROUGH prnt_chld2hgbst_show
                  ACTIONS = EXPORT, DELETE
                    TO hgbst_elm THROUGH hgbst_show2hgbst_elm
                      ACTIONS = EXPORT, DELETE
                    END_TO
                    TO hgbst_show THROUGH prnt_chld2hgbst_show
                      ACTIONS = EXPORT, DELETE
                        TO hgbst_elm THROUGH hgbst_show2hgbst_elm
                          ACTIONS = EXPORT, DELETE
                        END_TO
                    END_TO
                END_TO
            END_TO
        END_TO
    END_TO;

At the top level, I want to export data from the hgbst_lst table, and then also export any related data from the hgbst_show and hgbst_elm tables at the lower levels. Since I want to be able to extract this data from one database and import it into a different database, I avoided using any unique record identifiers (objid's). Using "title" as the unique identifier for the hgbst_lst table prevents multiple copies from being imported into the database.

Each lower level is reached by the "TO <table> THROUGH <relation>" phrases, and that traverses through the parent-child relations that make up the user-defined list structure.

The batch file that I set up and used for this data extraction is the other key piece of the puzzle. Here it is, and again the explanation follows:

diet -user_name <USERNAME> -password <PASSWORD> -db_name <DATABASE> -license <LICENSE> -directive hgbst.dir -archive -export hgbst.dat -where "title IN ('WORKGROUP', 'Recipient')"

This batch file is built for an Oracle database. The command line options that were key were:

-directive hgbst.dir - contains the directives file described above
-archive - copies the data from the database but does not remove it
-export hgbst.dat - specifies the file where the output data is written
-where "title IN ('Msg Reason', 'Recipient', 'Reimbursement_addr')" - controls which lists are exported

The other command line options control the database connection. Each one has to have a valid value, but otherwise they are self-explanatory.

Here is a sample of the data that gets created:

OBJECT TYPE="hgbst_lst", NAME="hgbst_lst_268435457"
    UNIQUE_FIELD=title
    FIELDS
        title = "WORKGROUP";
        description = "Employee's work group designation";
        deletable = 1;
    END_FIELDS
    RELATIONS
        TO_NAME="hgbst_show_268435457" REL="hgbst_lst2hgbst_show";
    END_RELATIONS
END_OBJECT NAME="hgbst_lst_268435457"

OBJECT TYPE="hgbst_show", NAME="hgbst_show_268435457"
    FIELDS
        last_mod_time = DATE "?/?/? ?:?:?";
        title = "Level 1";
    END_FIELDS
    RELATIONS
        TO_NAME="hgbst_elm_268435462" REL="hgbst_show2hgbst_elm";
        TO_NAME="hgbst_elm_268435461" REL="hgbst_show2hgbst_elm";
        TO_NAME="hgbst_elm_268435460" REL="hgbst_show2hgbst_elm";
        TO_NAME="hgbst_elm_268435459" REL="hgbst_show2hgbst_elm";
        TO_NAME="hgbst_elm_268435458" REL="hgbst_show2hgbst_elm";
        TO_NAME="hgbst_elm_268435457" REL="hgbst_show2hgbst_elm";
    END_RELATIONS
END_OBJECT NAME="hgbst_show_268435457"

OBJECT TYPE="hgbst_elm", NAME="hgbst_elm_268435457"
    FIELDS
        title = "Call Administrator";
        state = "Default";
    END_FIELDS
    RELATIONS
        TO_NAME="hgbst_show_268435457" REL="hgbst_elm2hgbst_show";
    END_RELATIONS
END_OBJECT NAME="hgbst_elm_268435457"

Hopefully this will help get the data that you need out of your database - DIET can definitely do the job.

Run-Time Query Parameter Evaluation

styson — Mon, 22 Sep 2008 20:47:20 GMT

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 query

Current 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 week

Last 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.

Spell Check feature added to Dovetail Agent

styson — Tue, 26 Aug 2008 18:45:00 GMT

As part of the continuous effort to enhance and improve our applications, a new Spell Check feature was added for the next release of Dovetail Agent.

This feature was added for every text area input in the pages of Agent. Currently there are almost 100 different pages in Dovetail Agent that use text area controls. Adding the new functionality to every page individually would be one way of implementing the change, but certainly not the most efficient. Instead, the new functionality is added dynamically to each page as it is opened in the browser. This approach makes a lot of sense. Since the code that adds the link under each text area is contained in one file, any future modifications will be very easy as well.

The Spell Check feature is made available to the user by adding a Check Spelling link under each text area control. jQuery is used to make this addition dynamically on each page that has a text area control, and will only add the link under a text area that is not "disabled" or marked as "Read-Only". Adding the jQuery module dynamically to every page also opens up a lot of other changes that are easier to implement.

On each page in Dovetail Agent where an entry has been made into a text area, clicking on the Check Spelling link will activate the Spell Check process. This process will analyze the contents of the text area, and if corrections are suggested the Spell Check window will be opened. The user then has the tools to correct the spelling as necessary, and any changes made will be reflected in the contents of the text area when the Spell Check window is closed.

If no other corrections are suggested, then the Check Spelling link changes to inform the user that the Spell Check is complete, as shown to the right. Changing the contents of the text area will revert the Check Spelling link back to its original state, ready to check the spelling again.

Opening View Objects from Query Results

styson — Thu, 07 Aug 2008 22:09:15 GMT

In Dovetail Agent, it is very easy to build a query against a table in the database, and there are standard routines to open most of the table objects to view the items returned in the query. Drilling down to view objects is a little tougher, but it can be done in a few easy steps.

In this example, I will show you how to build a query object for the queelm_case view, which will allow us to open each of the cases in the queue for inquiry. Building the fcQuery object in Dovetail Admin is the first step, so follow along.

After logging in to Dovetail Admin, click on the fcQuery Objects menu link to open the Manage fcQuery Objects page. Typing in queelm_case and clicking the List button locates the view object, and double-clicking the queelm_case row will add the object to the list of fcQuery objects for editing. This screen-shot to the right shows what the screen looks like after this step.

For a view, it is important to indicate what javascript function will be used to open the base object of the view. For this view it is easiest to use the OpenCaseWindow function, which is located in the code/open_window.js file in Dovetail Agent. To add this functionality, click on the queelm_case row in the grid, enter OpenCaseWindow('$objid$'); in the Open Function text box, and click the Save button.

The next step is to open the queelm_case object from the grid and add the columns that need to be shown in the query results. Click on the queelm_case row in the grid, then click on the Open button.

The most important field to add in this example is the elm_objid field. This field is what maps to the unique identifier of the case record, known as its objid. The tricky part is to make sure it gets indicated as the Record Id field when it is added as a column.

Adding the other columns follows the normal process, choosing the field by clicking the Path button and finding the desired field, picking the field by double-clicking it, then using the Quick Add button or setting the properties and clicking the Add button.

I chose to add a few other columns from the view regarding the cases, and the final fcQuery Object Columns are shown to the right.

Executing a query based on this object in Dovetail Agent will give me a list of all cases that are currently in queues, and when I double-click on a row in the results window the Case window will be opened so I can view all of the details of the case.

Without specifying the Record Id and Open Function in Dovetail Admin the view does not have enough information to know how to present the detailed view of the case.

Query objects provide a lot of power to Dovetail Agent users, and being able to get the most out of each query helps improve the efficiency of your applications.

jQuery = Simplification

styson — Fri, 11 Jul 2008 19:54:19 GMT

I came across this code today, and it took me a second to figure out what it was doing:

strSortOrder = document.getElementById("ad").options[document.getElementById("ad").selectedIndex].value;

To break it down, it locates a Select control on the page (FindElementById), finds the element again and gets its selected value's index (selectedIndex), and sets the variable to the value of the option at the specified index (options[index].value).

There is a jQuery plugin that really simplifies this process. Using jquery.selectboxes.js, the code now is as simple as this:

strSortOrder = $("#ad").selectedValues();

This code now does the same thing, but it is also easy to read, and understand. It shortened from 86 characters to 26, and now requires no explanation.

There are many different cases where jQuery can simplify the code.

Manage Queue Membership: Making It Easier

styson — Tue, 01 Jul 2008 19:31:53 GMT

Dovetail Software, Inc. recently recently received a request from one of our customers to make Queue Membership Management easier and more efficient to use. The screen image to the left shows the Manage Queues form in Dovetail Admin, Version 2.5. When a queue is selected at the top of the form, its current members and supervisors are displayed at the bottom right of the Membership tab.

Adding or changing members and supervisors was done one by one, and with only the login name for each user to determine the identity of each user. The login names are unique, but it can be hard to tell users apart based on login name alone.

The new requested functionality to make queue management easier includes adding the first and last name for each user, and allowing multiple users to be added or removed at one time.

The new version of the screen shows the changes. There is now more information for each user, and the checkboxes on each row of the grids give an indication that multiple users can be moved at the same time.

Users are selected by clicking on each row in the grids. The arrow keys in the middle of the tab are still used to add the selected users to the desired groups, and all of the business rules still apply to each one of the selected users.

From the administrator's point of view, the forms don't look much different other than the first and last names, but the added functionality will make member management easier. That was the real goal.

The other interesting part is what it looks like from a developer's point of view. Changing the functionality of the forms is an opportunity to update the technology, and in this case it makes a big difference. For more details on the development and a look at some of the code, take a look behind the user interface and read this post.

Manage Queue Membership: Behind the User Interface

styson — Tue, 01 Jul 2008 19:26:25 GMT

The first step in making the changes was to add the first and last names to each of the membership grids. This simply required changing the data source for the user information from the user table to the empl_user view in both of the user grid pages. The grid_queue_mem.asp and grid_queue_mem2.asp pages each retrieve a list of records from the database, and build a table of user information from the results.

Adding multi-select capability required much more change, so it was time to look for a better way to get the job done. This is where jQuery enters the picture. There is an excellent jQuery plugin available for grids, called jqGrid. There is a lot of capability available, and it allows us to simplify the code.

In the previous version of the form shown above, there is an IFRAME for each of the grids on the form. For each IFRAME that is used, there is a separate ASP page that gets the records from the database, and builds the table that displays the information if each grid.

Here is an example of the code that was used to build one of those grids:

<table id="ver_tbl">
<tr style="background-color: lightblue" class="clsgrid">
    <td nowrap class="clsgrid"><b>User</b></td>
</tr>
<%
while(!boUser.EOF) {
%>
<tr class="clsgrid" id=<%=boUser("objid")%> defColor="White" bgColor="White"
      onclick="BLOCKED SCRIPTGridRowClickHandler(this, 'ver_tbl', 'prev_grid_sel_row'); rowSel(this);" title="Click Row to Select">
    <td nowrap class="clsgrid"><% =boUser('login_name')%>  </td>
</tr>
<%
    boUser.MoveNext();
}
%>
</table>

Any information about a selected user has to be moved back and forth between to main page and each grid. By clicking on a row, the objid of the selected row is saved to the parent form, as shown here:

function rowSel(thisRow) {
parent.document.getElementById('userObjid').value = thisRow.id;
}

The jqGrid plugin allows all of the controls on the page to be in one file, and loads the data into the grids with an AJAX call to the server. This eliminates the need to pass values from one form to its parent, and results in more maintainable code. The code required for the Member grid using jqGrid consists of setting up the grid columns and properties, and providing the URL for the AJAX call to get the data. Here is an example that shows the Members grid:

$("#memberGrid").jqGrid({
   url:"loadMembers.asp?queue=<%=queueObjid%>&type=mem",
   datatype:"json",
   colNames:["user_id", "Login", "First", "Last"],
   colModel:[
      {name:"user_id",index:"objid",hidden:true},
      {name:"login_name",index:"login_name",width:70},
      {name:"first_name",index:"first_name",width:70},
      {name:"last_name",index:"last_name",width:70}
   ],
   hidegrid:false,
   loadonce:true,
   imgpath:"../code/jqGrid/themes/basic/images",
   sortname:"login_name",
   sortorder:"asc",
   height:105,
   width:225,
   loadComplete: function(){
      memTot = $("#memberGrid").getGridParam("records");
      if(memTot == null) memTot = 0;
   },
   multiselect:true
});

The loadMembers.asp page retrieves the data, and the grid control renders the information. Each grid has many properties that can be used to control its appearance and use. Another great benefit is that the columns can all be sorted and resized as needed. Setting the multiselect option to true as shown above is all that is required to allow multiple rows to be selected and processed. The checkbox for each row is generated automatically.

In the previous version, the each user had to be moved one user at a time. By allowing multiple users to be selected, each time one of the arrow buttons is clicked all of the selected rows can be processed as a group. In this example, all of the selected rows from the User grid are processed and added to the Member grid, but not if the user is already there:

var usersToAdd = $("#userGrid").getGridParam("selarrrow");
var currentMembers = $("#memberGrid").getDataIDs();

$.each(usersToAdd, function(i, objid) {
   if($(currentMembers).index(objid) < 0) {
      var userData = $("#userGrid").getRowData(objid);
      $("#memberGrid").addRowData(objid, userData);
   }
});

All of the data that is needed to complete this action is held within the grids themselves, eliminating the need for hidden controls to hold selected row identifiers. These hidden identifiers were updated in the previous version when each row was selected.

Overall, there is much less code to maintain, and it will be much easier to customize and enhance in the future.

Previous Page	Line Count	New Page	Line Count
queue_detail.asp	528	queue_detail.asp	466
grid_queue.asp	161	LoadQueues.asp	83
grid_queue_mem.asp	97	LoadUsers.asp	29
grid_queue_mem2.asp	113	LoadMembers.asp	57
grid_queue_sup.asp	168	(uses LoadMembers)	-
Total lines of code	1,067		635

Taking advantage of the jQuery javascript library and plugins such as jqGrid allow for more focus on adding value to the application, and makes the product better and easier to both use and change.

Other Cool Tools

styson — Mon, 14 Apr 2008 21:46:00 GMT

I learned about a couple of other cool tools from J.P. Boodhoo last week, and thought I would pass them along.

Console2

http://sourceforge.net/projects/console/

This is a replacement for the CMD shell. It is a tabbed window, and each tab can be defined as a different shell process.

Unlocker

http://ccollomb.free.fr/unlocker/

I just want to delete that folder...

Grrrr... here comes Unlocker to the Rescue!

Unlocker will pop up after you close the annoying pop-up window, and show you what programs have the file locked, and give you the option to easily free the file. Very nice UI to give you the control that you want (and need)!

Goodbye SlickRun! ...

styson — Mon, 14 Apr 2008 20:09:09 GMT

... and hello Launchy. This is a minor replacement, but makes life at the keyboard even easier. Like a helpful side-kick, you can train Launchy to open any program with just a few shortcuts. This is the same as SlickRun, but without the magic keyword setup.

You can easily change the configuration to use any hot-key combination for popping up Launchy, then just start typing the name of the program you are trying to launch. If you need to browse for it, it will help you traverse down the folder trees as you type folder names and hit tab for auto-completion.

I am using it like this so far:

vs - Visual Studio

ue - UltraEdit

ff - fireFox

fl - Flare

ie - Internet Explorer

dt - http://www.dovetailsoftware.com/

iis - IIS Console

Other cool stuff found under Tips and Tricks:

Search Google

Type in google, then tab, then your search query and press enter

Check the Weather

Type in Weather, then tab, then your zip code or city,state and press enter