This is an advanced topic intended for programmers.

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 in the directory where Stonefield Query's data files are located. 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. This one script Stonefield Query to treat a couple of fields as numeric and date values rather than the character data they're actually stored as.

loField = SQApplication.DataEngine.Fields.Item('MACHINERY.CAPACITY')
loField.Type         = 'N'
loField.OutputLength = 5
loField.Expression   = 'val(MACHINERY.CAPACITY)'
loField.Picture      = '99,999'
    
loField = SQApplication.DataEngine.Fields.Item('PRODUCTS.LASTDATE')
loField.Type         = 'D'
loField.OutputLength = 8
loField.Expression   = 'ctod(PRODUCTS.LASTDATE)'

The first line in this code asks the fields collection to return a field object for the CAPACITY field (which contains the desired value) of the MACHINERY table. The code then changes the field 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 LASTDATE field in the PRODUCTS table, 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.


© Stonefield Software Inc., 2023 • Updated: 02/01/21
Comment or report problem with topic