Customizing the Stonefield Query Data Dictionary |
However, you may need to customize the Stonefield Query data dictionary to report on other databases you have linked to your SalesLogix 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. 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 allows you to report on the ACCOUNTID field in the ACCOUNT 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.
FieldObject = SQApplication.DataEngine.Fields.Item('ACCOUNT.ACCOUNTID')
store .T. to FieldObject.Reportable, FieldObject.Sortable, ;
FieldObject.Filterable
The first line of code asks the fields collection (Fields) of the data dictionary (SQApplication.DataEngine) to return a field object (FieldObject) for the ACCOUNT.ACCOUNTID field. The second statement makes the field reportable, sortable, and filterable by setting the appropriate properties to True (.T.).
Here's another example. This one creates two new calculated fields, Birthday Month and Birthday Day, which are the month and day parts of the user-defined field BIRTHDAY. This allows you to filter on those people whose birthday is, say, in June so you can mail them birthday cards.
FieldObject = SQApplication.DataEngine.Fields.AddItem('CONTACT.BIRTHMONTH')
FieldObject.Caption = 'Birth Month'
FieldObject.Type = 'C'
FieldObject.Length = 9
FieldObject.Expression = 'cmonth(CONTACT.BIRTHDAY)'
FieldObject.Exact = .T.
FieldObject.Calculated = .T.
FieldObject.FieldList = 'CONTACT.BIRTHDAY'
FieldObject = SQApplication.DataEngine.Fields.AddItem('CONTACT.BIRTHDDAY')
FieldObject.Caption = 'Birthday Day'
FieldObject.Type = 'C'
FieldObject.Length = 2
FieldObject.Expression = "transform(day(CONTACT.BIRTHDAY), '99')"
FieldObject.Calculated = .T.
FieldObject.FieldList = 'CONTACT.BIRTHDAY'
The first line adds a new field named BIRTHMONTH in the CONTACT 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 CONTACT.BIRTHDAY is the field it has to read from the database to perform the calculation on. This code then does the same thing for the Birthday Day 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 the Stonefield Query Configuration Utility, the same tool Stonefield Software used to create the Stonefield Query for SalesLogix 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 for SalesLogix 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 "file3". If you wish to create your own custom scripts, do something similar in the [Scripting] section. Here's an example:
[Meta Data] file1=qwmeta.dbf file2=repmeta.dbf file3=CustomDataDict.dbf [Scripting] file1=qwscript.dbf;BuiltIn file2=sfscript.dbf;BuiltIn file3=CustomScript.dbf
Don't change any of the other lines in SFQuery.INI or Stonefield Query for SalesLogix 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 SalesLogix program folder. Also, if you created any custom scripts, copy the custom script table (CustomScript.DBF, CustomScript.CDX, and CustomScript.FPT) as well.