Note: this is an advanced topic designed for programmers.
Stonefield Query knows all about your GoldMine 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 GoldMine database. There are two ways you can do this: with scripting or using the Stonefield Query Software Development Kit (SDK).
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.
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).
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 that allows you to report on the
ACCOUNTNO field in the
CONTACT1 table; this field is in the Stonefield Query data dictionary, but is marked as not reportable, so it doesn't appear in the list of fields you can select.
Field = SQApplication.DataEngine.Fields.Item('CONTACT1.ACCOUNTNO') store .T. to Field.Reportable, Field.Sortable, Field.Filterable
Here's another example. This one tells Stonefield Query to treat a couple of detail type fields as numeric and date values rather than the character data they're actually stored as.
Field = SQApplication.DataEngine.Fields.Item('XXMACHINERY.ZIP') Field.OutputType = 'N' Field.OutputLength = 5 Field.Expression = 'val(XXMACHINERY.ZIP)' Field.Picture = '99,999' Field = SQApplication.DataEngine.Fields.Item('XXPRODUCTS.LINKACCT') Field.Type = 'D' Field.OutputLength = 8 Field.Expression = 'ctod(XXPRODUCTS.LINKACCT)'
The first line in this code asks the fields collection of the data dictionary to return a field object for the
ZIP field (which contains the desired value) of the Machinery detail type (detail types appear in the Stonefield Query data dictionary as
XX plus the detail type name, with any spaces converted to underscores; for example, the "Admin Data" detail type would appear in the data dictionary as
XXADMIN_DATA). The code then changes the output data type to numeric ("N"), the output length to 5, the output expression to the expression that converts a character value to a numeric value (the
VAL() function), and sets the picture to "99,999" so numbers are formatted with commas. The code then does the same thing for the
LINKACCT field in the Products detail type, using the appropriate changes for a date field ("D" instead of "N", a width of 8, and
CTOD(), which converts a character to a date, rather than
VAL() for the output expression).
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:
|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:
|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:
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:
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 GoldMine 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
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 Stonefield Query Studio 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.
If you've added fields or tables to the GoldMine 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 GoldMine database. Select the GoldMine 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.
© Stonefield Software Inc., 2022 • Updated: 01/14/22
Comment or report problem with topic