Customizing the Stonefield Query Data Dictionary

Note: this is an advanced topic designed for programmers.

Stonefield Query knows all about your HEAT database because it has a data dictionary. A data dictionary defines the fields and tables in a database, providing features such as descriptive names so you don't have to know the real names, how tables are connected, or joined, together, and calculated values such as extended price that aren't normally stored in the database.

However, you may need to customize the Stonefield Query data dictionary to report on other databases you have linked to your HEAT database. There are two ways you can do this: with scripting or using the Stonefield Query Software Development Kit (SDK).

Scripting

You can use a "script" file to programmatically customize Stonefield Query. After Stonefield Query sets up its data environment (which it does only one time after a particular database has been chosen), it looks for a file named SETUP.SQS and executes the code in that file if it exists. As with user-defined functions, you have to be familiar with dBase or FoxPro to write such a script file.

To create a script file, create a text file called SETUP.SQS, either in the directory where Stonefield Query's data files are stored or in the Stonefield Query program directory. Edit this file using any text editor (not a word processor, such as Microsoft Word, which stores binary files, but an editor such as Notepad that stores text files). This file can contain any code you wish, but it must be dBase or FoxPro code.

Although script code can be used for anything, the most common use is to change information in the Stonefield Query data dictionary.

Here's an example of a script that creates a new calculated field, Received Month, which is the month part of the Date Received field of the Call Logging table. This allows you to filter on those tickets that were received in, say, in June.

loField = SQApplication.DataEngine.Fields.AddItem('CALLLOG.RECVDMONTH')
loField.Caption = 'Received Month'
loField.Type = 'C'
loField.Length = 9
loField.Expression = "cmonth(evaluate('{^' + CALLLOG.RECVDDATE + '}'))"
loField.Calculated = .T.
loField.FieldList = 'CALLLOG.RECVDDATE'

The first line adds a new field named RECVDMONTH in the CALLLOG table to the data dictionary. This field doesn't really exist in the database, but Stonefield Query will treat it as if it does; you can report on it, filter on it, etc. The next set of lines set the various properties of the field: the caption (heading), data type ("C" means "character"), size (the longest month name, September, is 9 characters), and output expression (this dBase expression gives the spelled-out month of the specified date, such as "January"). Finally, it defines it as a calculated field and specifies that CALLLOG.RECVDDATE is the field it has to read from the database to perform the calculation on.

In previous versions of Stonefield Query, scripting was used to create your own calculated fields. However, that's no longer necessary because you can use the Formula Editor to do that.

Here are the properties of field objects:

Name Data Type Description

AllowValues Boolean True if the Values button in Stonefield Query is enabled for this field.

Calculated Boolean True if this is a calculated field. If the SQExpression property of a calculated field is False, set this property to False as well.

Caption Character The caption for the field.

Comment Character The comment for the field.

Decimals Numeric The number of places after the decimal.

Expression Character The expression to determine the value of this field if it's a calculated field.

FieldList Character A comma-delimited list of aliased field names used to calculate the value of this field if it's a calculated field. Leave this property blank if SQExpression is False.

FieldName Character The aliased name of the field. You don't have to fill in this property because that's done automatically when you add the field to the collection.

Filterable Boolean True if the user can filter on this field.

Format Character This indicates how the value of the field is formatted. Enter one of the following characters:

$: displays the currency symbol specified in the Windows Regional Options Control panel.
^: displays numeric data using scientific notation.
L: displays leading zeros instead of spaces.
R: indicates the Picture property may contain characters not found in the data value. For example, to display "12345" as "12-345," use "R" for Format and "99-999" for Picture.
Z: displays the value as blank if it is 0 (for numeric fields) or empty (for Date or DateTime fields).
!: converts alphabetic characters to uppercase.
D: displays the date and time of a DateTime field (although this can be turned off in the Field Properties dialog in Stonefield Query). If this isn't specified, Stonefield Query displays only the date portion of a DateTime field by default.
J: use right alignment for a field that's normally left-aligned.
I: use center alignment.

Heading Character The default column heading for this field in a report.

Length Numeric The width of the field.

OutputLength Numeric The output width of the field. You don't have to fill in this property unless it's different than Length.

OutputType Character The output data type of the field; see "Type" below for a list of valid data types. You don't have to fill in this property unless it's different than Type. Note setting this doesn't mean Stonefield Query converts the values in the data to the specified type; you are simply informing Stonefield Query what the data type of Expression is.

Picture Character This property indicates how each character in the field's value is displayed. For each position in the value, enter one of the following characters:

!: converts lowercase letters to uppercase letters.
#: displays digits, blanks, and numeric signs (such as a minus sign).
$: displays the currency symbol specified in the Windows Regional Options Control panel in a fixed position.
$$: displays the currency symbol specified in the Windows Regional Options Control panel in a floating position (adjacent to the digits).
,: displays the digit grouping symbol specified in the Windows Regional Options Control panel.
.: displays the decimal separator symbol specified in the Windows Regional Options Control panel.
9: digits and numeric signs.
A: alphabetic characters only.
N: letters and digits only.
X: any character can be displayed.
For example, "9,999,999.99" indicates that values are formatted with thousands separators (such as commas) up to seven places before the decimal and have two decimal places.

Reportable Boolean True if the user can report on this field.

Roles Character A comma-delimited list of roles that can access the field (blank means everyone can access it).

Sortable Boolean True if the user can sort on this field.

SQExpression Boolean True if this calculated field uses a Stonefield Query expression; False if the expression is sent as is to the database engine.

Type Character A single character representing the data type of the field:
W: Blob
C: Character
Y: Currency
D: Date
T: DateTime
B: Double
G: General
I: Integer
L: Logical
M: Memo
N: Numeric
V: Varchar
Q: Varbinary

Name	Data Type	Description
AllowValues	Boolean	True if the Values button in Stonefield Query is enabled for this field.
Calculated	Boolean	True if this is a calculated field. If the SQExpression property of a calculated field is False, set this property to False as well.
Caption	Character	The caption for the field.
Comment	Character	The comment for the field.
Decimals	Numeric	The number of places after the decimal.
Expression	Character	The expression to determine the value of this field if it's a calculated field.
FieldList	Character	A comma-delimited list of aliased field names used to calculate the value of this field if it's a calculated field. Leave this property blank if SQExpression is False.
FieldName	Character	The aliased name of the field. You don't have to fill in this property because that's done automatically when you add the field to the collection.
Filterable	Boolean	True if the user can filter on this field.
Format	Character	This indicates how the value of the field is formatted. Enter one of the following characters: $: displays the currency symbol specified in the Windows Regional Options Control panel. ^: displays numeric data using scientific notation. L: displays leading zeros instead of spaces. R: indicates the Picture property may contain characters not found in the data value. For example, to display "12345" as "12-345," use "R" for Format and "99-999" for Picture. Z: displays the value as blank if it is 0 (for numeric fields) or empty (for Date or DateTime fields). !: converts alphabetic characters to uppercase. D: displays the date and time of a DateTime field (although this can be turned off in the Field Properties dialog in Stonefield Query). If this isn't specified, Stonefield Query displays only the date portion of a DateTime field by default. J: use right alignment for a field that's normally left-aligned. I: use center alignment.
Heading	Character	The default column heading for this field in a report.
Length	Numeric	The width of the field.
OutputLength	Numeric	The output width of the field. You don't have to fill in this property unless it's different than Length.
OutputType	Character	The output data type of the field; see "Type" below for a list of valid data types. You don't have to fill in this property unless it's different than Type. Note setting this doesn't mean Stonefield Query converts the values in the data to the specified type; you are simply informing Stonefield Query what the data type of Expression is.
Picture	Character	This property indicates how each character in the field's value is displayed. For each position in the value, enter one of the following characters: !: converts lowercase letters to uppercase letters. #: displays digits, blanks, and numeric signs (such as a minus sign). $: displays the currency symbol specified in the Windows Regional Options Control panel in a fixed position. $$: displays the currency symbol specified in the Windows Regional Options Control panel in a floating position (adjacent to the digits). ,: displays the digit grouping symbol specified in the Windows Regional Options Control panel. .: displays the decimal separator symbol specified in the Windows Regional Options Control panel. 9: digits and numeric signs. A: alphabetic characters only. N: letters and digits only. X: any character can be displayed. For example, "9,999,999.99" indicates that values are formatted with thousands separators (such as commas) up to seven places before the decimal and have two decimal places.
Reportable	Boolean	True if the user can report on this field.
Roles	Character	A comma-delimited list of roles that can access the field (blank means everyone can access it).
Sortable	Boolean	True if the user can sort on this field.
SQExpression	Boolean	True if this calculated field uses a Stonefield Query expression; False if the expression is sent as is to the database engine.
Type	Character	A single character representing the data type of the field: W: Blob C: Character Y: Currency D: Date T: DateTime B: Double G: General I: Integer L: Logical M: Memo N: Numeric V: Varchar Q: Varbinary

Stonefield Query SDK

It's much easier to customize the Stonefield Query data dictionary using the Stonefield Query SDK than it is via scripting. For one thing, you don't have to know how to write code. Also, if a lot of customization is required (for example, there are several new tables with many fields in each one), you'd have to write a lot of code if you used a script. Also, if the structures of the tables change, you'd have to change the code.

The Stonefield Query SDK provides the Stonefield Query Configuration Utility, the same tool Stonefield Software used to create the Stonefield Query data dictionary in the first place. Using this tool, you can easily add new tables or fields to the data dictionary, customize existing fields any way you need, and define new calculated fields. For more information about the Stonefield Query SDK, please visit our Web site: www.stonefieldquery.com. For information on how to use the Stonefield Query Configuration Utility, please see the Stonefield Query Configuration Utility documentation.

The Stonefield Query data dictionary is in a table called REPMETA.DBF. When you add tables or fields using the Stonefield Query SDK, you'll be adding them to a different table. The reason for a separate data dictionary table is to protect your changes when a new version of Stonefield Query is released. Since that new version's REPMETA table would overwrite yours, your customization would be lost. So, using a different table for your custom changes means that your work won't be lost when you install a new version.

By default, the name of the custom table is CUSTOMMETA.DBF. However, if you want to change that name, edit SFQuery.INI in the Stonefield Query program directory and change the name specified in the "file2" line in the [Meta Data] section. If you wish to create your own custom scripts, add a new line with "file2" as the key in the [Scripting] section. Here's an example:

[Meta Data]
file1=repmeta.dbf
file2=custommeta.dbf

[Scripting]
file1=sfscript.dbf;BuiltIn
file2=CustomScript.dbf

Don't change any of the other lines in SFQuery.INI or Stonefield Query may not work properly!

If you are working with the Stonefield Query Configuration Utility in one folder but using Stonefield Query in a different folder (for example, you are customizing the data dictionary for someone else, such as a client), you will need to copy SFQuery.INI and your custom table (for example, CustomMeta.DBF, CustomMeta.CDX, and CustomMeta.FPT) to the Stonefield Query program folder. Also, if you created any custom scripts, copy the custom script table (CustomScript.DBF, CustomScript.CDX, and CustomScript.FPT) as well.

Note: There is no Configuration node in the Configuration Utility because the Stonefield Query configuration information is built-in and can't be changed.