Stonefield Query knows all about your ALERE 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 ALERE 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 Visual FoxPro code.

Here's an example of a script that creates a new calculated field, Order Month, which is the month part of the Date field of the Company Activity Log table. This allows you to filter on those Orders that were received in June for example.

loField = SQApplication.DataEngine.Fields.AddItem('COACTLOG.MONTH')
loField.Caption = 'Order Month'
loField.Type = 'C'
loField.Length = 9
loField.Expression = "cmonth(evaluate('{^' + COACTLOG.DATE + '}'))"
loField.Calculated = .T.
loField.FieldList = 'COACTLOG.DATE'

The first line adds a new field named MONTH in the COACTLOG 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 COACTLOG.DATE is the field it has to read from the database to perform the calculation on.

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 Stonefield Query Studio, the same tool Stonefield Software used to create the Stonefield Query for ALERE 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 Stonefield Query Studio, please see the Stonefield Query Studio documentation.

The Stonefield Query for ALERE 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.

To tell Stonefield Query to use a custom data dictionary table, edit SFQuery.INI in the Stonefield Query program directory and add a line in the [Meta Data] section with a key of "file2". If you wish to create your own custom scripts, do something similar in the Scripting section. Here's an example:

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

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

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

If you are working with the Stonefield Query Configuration Utility in one folder but using the 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 your custom table (for example, CustomDataDict.DBF, CustomDataDict.CDX, and CustomDataDict.FPT) to the Stonefield Query for ALERE program folder. Also, if you created any custom scripts, copy the custom script table (CustomScript.DBF, CustomScript.CDX, and CustomScript.FPT) as well.