Note: this is an advanced topic intended for programmers.

Stonefield Query knows all about your AccountMate 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.

Because AccountMate is a customizable accounting system, you may need to customize the Stonefield Query data dictionary to report on custom tables and fields added to your AccountMate database. There are two ways you can do this: with scripting or using the Stonefield Query Developer's Edition.

Scripting

You can use a "script" file to programmatically customize Stonefield Query for AccountMate. After Stonefield Query for AccountMate 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 for AccountMate's data files are stored or in the Stonefield Query for AccountMate 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. This script creates a new calculated field, Invoice Month, which is the month part of an invoice date. This allows you to filter on those invoices in, for example, June.

loField = SQApplication.DataEngine.Fields.AddItem('ARINVC.ORDERMONTH')
loField.Caption    = 'Invoice Month'
loField.Type       = 'C'
loField.Length     = 9
loField.Expression = 'cmonth(ARINVC.DINVOICE)'
loField.Calculated = .T.
loField.FieldList  = 'ARINVC.DINVOICE'

The first line adds a new field named ORDERMONTH in the ARINVC table to the data dictionary. This field doesn't really exist in the database, but Stonefield Query for AccountMate treats 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 expression gives the spelled-out month of the specified date, such as "January"). Finally, it defines it as a calculated field and specifies that ARINVC.DINVOICE 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 AccountMate 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 SDK documentation.

The Stonefield Query data dictionary is in a table called AMMETA.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 AMMETA 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 REPMETA.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. Looking at the file, you will also see a second line with "file2" as the key in the Scripting section. This SFSCRIPT.DBF file will contain any custom scripts you create. Here's what the default file looks like:

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

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

[Configuration]
BuiltIn=Yes 

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

If you are working with Stonefield Query Studio in one folder but using Stonefield Query in a different folder (for example, you are customizing the data dictionary on your machine, and you want to deploy those changes to a client), you will need to copy SFQuery.INI and your custom table (for example, AMMETA.DBF, AMMETA.CDX, and AMMETA.FPT) to the Stonefield Query "data" folder (which is a subfolder of the program folder). Also, if you created any custom scripts, copy the custom script table (SFSCRIPT.DBF, SFSCRIPT.CDX, and SFSCRIPT.FPT) as well.

If you've added fields or tables to the AccountMate database, you'll want to refresh the Stonefield Query data dictionary so it reads in those new fields or tables. Before you can do that, you have to tell Studio how to access your AccountMate database. Select the "amw" database in the Databases panel and select the correct data source from the Data Source drop down list. You can then click the Refresh button in the toolbar to refresh the entire data dictionary.

Note: There are no Configuration and Scripts panels in Studio because the Stonefield Query configuration information is built-in and can't be changed.