| Stonefield Query SDK |
| Field Properties |
as the image. Fields can be displayed in alphabetical order or in their physical order in the table; the Sort Fields Alphabetically function in the Edit menu, the shortcut menu, and the toolbar toggles between these two sorts.When you select a field in Stonefield Query Studio, the properties pane displays the properties for that field. There are four pages of properties. Click the buttons at the top of the properties pane to select the desired page.
The Main page contains the main properties for a field. The following properties appear on this page:
| Symbol | Description |
|---|---|
| Blob | Used for large binary values, such as images. |
| Character | Used for alphanumeric text. Character fields can be up to 254 bytes long. For longer values, use Memo instead. |
| Currency | Used for monetary values. The range of values supported is -$922,337,203,685,477.5807 to $922,337,203,685,477.5807. |
| Date | Used for date fields. |
| DateTime | Similar to Date, but includes the time as well. |
| Double | This is a double-precision, floating point number, with a range of +/-4.94065645841247E-324 to +/-8.9884656743115E307. |
| General | This is provided for images stored as an image object rather than image binary data. |
| Integer | A 4-byte integer value. The range is -2,147,483,647 to 2,147,483,647. |
| Logical | A Boolean field capable of containing True or False (or Yes or No) values. |
| Memo | Used for variable length alphanumeric text. |
| Numeric | Used to hold numeric values from -.9999999999E+19 to .9999999999E+20. |
| Varbinary | Used to hold binary data, such as GUIDs. |
| Varchar | Like character fields, but contain variable length text up to the value specified in the Width property. |
| Symbol | Description |
|---|---|
| $ | Displays the currency symbol specified in the Windows Regional Options Control panel. |
| ^ | Displays numeric data using scientific notation. |
| L | Displays leading zeros instead of spaces. |
| R | Indicates the Picture property may contain characters not found in the data value. For example, to display "12345" as "12-345," use "R" for Format and "99-999" for Picture. |
| Z | Displays the value as blank if it is 0 (for numeric fields) or empty (for Date or DateTime fields). |
| ! | Converts alphabetic characters to uppercase. |
| D | Displays the date and time of a DateTime field (although this can be turned off in the Field Properties dialog in Stonefield Query). If this isn't specified, Stonefield Query displays only the date portion of a DateTime field by default. |
| J | Use right alignment for a field that's normally left-aligned. |
| I | Use center alignment. |
| Symbol | Description |
|---|---|
| ! | Converts lowercase letters to uppercase letters. |
| # | Displays digits, blanks, and numeric signs (such as a minus sign). |
| $ | Displays the currency symbol specified in the Windows Regional Options Control panel in a fixed position. |
| $$ | Displays the currency symbol specified in the Windows Regional Options Control panel in a floating position (adjacent to the digits). |
| , | Displays the digit grouping symbol specified in the Windows Regional Options Control panel. |
| . | Displays the decimal separator symbol specified in the Windows Regional Options Control panel. |
| 9 | Digits and numeric signs. |
| A | Alphabetic characters only. |
| N | Letters and digits only. |
| X | Any character can be displayed. |
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.
You can use a dynamic expression for the Picture by surrounding the expression with curly braces. For example, suppose you have a numeric field with four decimals places but each record could have a varying number of decimals displayed; that number is contained in a different field (for example, NumberOfDecimals). You can create a script called, for example, FormatDecimals that returns the desired Picture for the specified number of decimals, such as:
lparameters tnDecimals
local lnDecimals
lnDecimals = val(transform(tnDecimals))
return '999,999,999' + iif(lnDecimals = 0, '', '.' + replicate('9', lnDecimals))You would then specify that script in an expression in the Picture property of the field to display the varying decimals, passing the name of the field containing the number of decimals to display (NumberOfDecimals in this case):
{FormatDecimals(MyTable.NumberOfDecimals)}

To move a field up or down in the list, click the button in front of its name and drag up or down. When you click OK, the fields are reordered and the new order is displayed in the data dictionary.
The Calc page is used for calculated fields or those where the data displayed to the user is different from how it's stored. For example, most order entry systems don't store the extended price of an item, but derive it from the unit price multiplied by the quantity. However, you may want your users to be able to report on extended price, so you would create a calculated field for it.
All of the properties on this page are disabled if you've chosen any of the options on the Special page (discussed below).
The following properties appear on the Calc page:
To make it easier to enter the expression, click the button with the ellipsis (...) beside this option. This brings up the Expression Builder, which allows you to select from a list of available fields rather than having to type a field name. This is the same Expression Builder used in Stonefield Query.
If the expression uses syntax the database engine can understand, such as simple arithmetic functions (for example, UnitPrice * Quantity) or functions built into the database engine, turn this setting off. In that case, the expression is sent as is to the database engine.
The advantage of having this setting turned on is the ability to call scripts or the hundreds of functions built into Stonefield Query, so you have more flexibility. The advantage of having this setting turned off is better performance, especially if the field is used in a filter, and taking advantage of functions built into the database engine.
The Special page is used for three things: enumerated fields, displaying fields from related tables, and versioning. The first two items are disabled for calculated fields or those that you've specified an Output Expression for.
The version setting contains the field's version number. A blank value means the field is not versioned: it appears in Stonefield Query regardless of the version of the database. For a field that isn't available in every version of the database, enter the version number followed by "+" to indicate the field appears in that version and higher versions and should not appear in lower versions (that is, the field was added in that version), "-" to indicate the field appears in that version and lower versions and should not appear in higher versions (that is, the field was removed in the next version), or no suffix to indicate the field appears only in that version and should not appear in any other version. For example, "5.3+" indicates the field is available starting in version 5.3 while "5.3-" indicates it was removed in version 5.4.
Use a comma-delimited list of values if the field was added in one version and later removed. For example, "5.3+,5.5-" means it was added in version 5.3 and removed in version 5.6.
Enumerated fields are those that contain a pre-defined set of codes that mean something. For example, in the sample Northwind database that comes with SQL Server, the ShipVia field in the Orders table contains a 1, 2, or 3. These values aren't foreign keys looked up in another table, but rather hard-coded values, each of which represents a different shipping type. Often, these codes are displayed to the user in the application differently than they're stored. For example, the user might see "Fedex," "UPS," or "Mail" for the ShipVia field rather than 1, 2, or 3.
The Special page allows you to define the codes and display values for the selected field. To enable this feature, turn on the Enumerated Values option, then click Add (or press Alt+A) to add a new value to the list. In the Value column, specify the code value as stored in the field, and in the Description column, enter the value as the user sees it. Continuing with the above example, enter "1" for Value and "Fedex" for Description, "2" for Value and "UPS" for Description, and "3" for Value and "Mail" for Description. Use the Remove button (or press Alt+R) to remove the highlighted line.
See the Defining an Enumerated Field Programmatically topic for information on how to define an enumerated field at runtime.
For those fields that contain foreign keys, you likely don't want to display the foreign key values to the user since they probably don't see these values in your application and wouldn't understand the values stored in the field. Instead, you may want to display a particular field from the table that this field contains the foreign key for. For example, the Products table in the Northwind database has a CategoryID field that contains the foreign key to the Categories table. It might make sense to display the CategoryName field from the Categories table when the user selects the CategoryID field, since that's likely how they think of the category. Not only does this make it easier for the user to display the category name for a product (they can don't have to select the Categories table and then the CategoryName field), it also provides better performance, since Stonefield Query does an in-memory lookup rather than a two-table join.
To specify that a field from a related table is displayed in place of this field, choose the Special page, turn on the Display Field From Related Table option, select the desired table from the Table drop-down list (only tables that are parent tables in relationships to the current table are available; also, the relationships must be simple ones, as complex relationships aren't supported with this feature) and the desired field from the Field drop-down list.
Note that you can click "Table" or "Field" to jump to the selected table or field, respectively.
If you want comments about a field available to your user, such as a detailed description of the purpose or contents of a field, enter them on the Comment page. Any comments you enter are displayed in the Comments page of the Field Properties dialog in Stonefield Query.
| Last Updated: 07/30/12 |