Wednesday, June 25, 2008

Using the Field Description Property

When using Advantage Data Dictionaries every object (Tables, Indexes, Fields, Views …) has a description property. This allows developers to enter information about the object for other developers to see. Access to the various description (comment) property varies depending on the dictionary object.  Changing these properties requires the ALTER permission. These fields are a great mechanism for storing a revision history or design notes.

Cary Jensen discussed using the field description property as a way to provide tool tips to users during one of his presentations about Advantage. I thought this was a fantastic idea so I incorporated it into some of my training and created an example application.

To get the field description property use either the AdsDDGetFieldProperty API or the System.Columns table. I chose to use the system.columns table because I find it easier to implement. The field description can be read by any user or group who has READ, UPDATE, INSERT or DELET permissions for the field.

First I create a function which takes two string parameters and returns a string containing the comment. This function uses the following query: SELECT Comment FROM system.columns WHERE Parent = :Table AND Name = :Field.

   1: function TForm1.GetFieldComment( sTable: String; sField: String): String;
   2: begin
   3:   // Set the parameter values
   4:   qryComment.ParamByName('Table').Value := sTable;
   5:   qryComment.ParamByName('Field').Value := sField;
   6:  
   7:   try
   8:     // Execute the Query
   9:     qryComment.Open;
  10:  
  11:     // Read the comment value
  12:     if qryComment.IsEmpty  then
  13:       Result := 'No comment for field: ' + sField
  14:     else
  15:       Result := qryComment.Fields[0].asString;
  16:  
  17:     // Close the query
  18:     qryComment.Close;
  19:   except
  20:     on E: EDatabaseError do
  21:          begin
  22:             if ( E is EADSDatabaseError ) then
  23:               Result := 'ADS Error: ' + E.Message
  24:             else
  25:               Result := 'Error: ' + E.Message;
  26:          end;
  27:   end;
  28:
  29:   GetFieldComment := Result;
  30: end;

To set the hint for a particular control we simply provide the table and field name to the function. For example: dbeCustID.Hint := GetFieldComment( ‘Customer’, ‘CustID’);. The table name is included since many forms may use views or queries to retrieve the data. By supplying the table name we can get the description for fields from the proper table.

An example form is shown along with the information from the data dictionary.

Tool tip displayed on the form

LastName field properties

This example project is be posted on Code Central under the Delphi category.

1 comment:

Doug said...

An idea that I had on the description field was to store even more information in it in an XML format. That way I can use it to store the help information, but could also track documentation, cross references, where used, even which components might be legal for that field.

The function, modded from yours, would have table, field, and desired attribute, which would be parsed out from the XML.
A downside is a loss of readability within ARC, but the increased flexibility might, in some cases, make up for it.

I LOVE metadata