Setting Database Field Properties in Databases

Go to:

Databases

Applications built using Iron Speed Designer can use a variety of built-in data field validation types and their corresponding data input validation.  Iron Speed Designer creates your application’s data access layer based on these options.

Databases settings are applied to all web pages that use the particular table fields.  These settings can be customized for individual controls via the Property Sheet.

Iron Speed Designer applications include code that translates between the database storage format for a field, to the display and input formats, and then back again upon insertion to the database.  Using Iron Speed Designer, you can avoid writing such code.

Databases contains these principal folders:

Group

Description

Database

Shows the database tables, views and custom queries available to your application for each selected database.

  • See which tables are available to your application

  • View database table schemas and foreign key relationships

  • Set various default data display and field validation properties

Stored Procedures

Lists the application’s stored procedures (SQL) created by Iron Speed Designer.

Database properties

Property

Description

Connection name

The name of the connection key for this database in the application’s Web.config file.

Connection string

The database connection string in the application’s Web.config file.

Provider name

The database provider name in the application’s Web.config file.

Database table properties

These properties are available when you select a database table or view in the Application Explorer.

Property

Description

Include table in application

Indicates whether the supporting database access code should be created for the table.  Database tables and views used by the application automatically have this option selected.  However, you may instruct Iron Speed Designer to include code for tables not directly used by the application’s web pages.  This is useful in circumstances where a non-web interface is being used, such as a web service or a Windows application.

Allow insert, update and delete on table / view

Indicates whether database record updates are permitted.

This option is selected by default for tables with a primary key.  This option is not selected by default for tables or views without a primary key.  If not selected, then the application will throw an exception if it attempts to call the insert, update, or delete functions.

Note: this option is not available if the database is read-only, or if the table or view does not have a primary key or virtual primary key.

Query

Specifies the SQL query used for the custom query.

Paging method

Specifies the method used to retrieve pages in table controls.  (Microsoft SQL 2005 and above only)

Actions properties

Action properties let you evaluate (execute) formulas when specific events occur.  These Action properties are available in the Property Sheet when you select an individual database field.

Property

Description

Initialize when reading record

A formula applied to the selected field when a record is being read from the database.

Example: Password field can have a formula as

= Encrypt(Password)

Note: Whenever a ‘reading record’ formula is applied, changes made while reading a record must be undone while saving a record. The purpose of ‘reading record’ formula is just to display a value on a page by adding some formatting or formula. But while saving a record, web page values are always saved to the database. So, you may need strip or remove changes made when reading a record in your ‘inserting record’ and ‘updating record’ formulas.

As an example, if the Decrypt(Password) function is applied while reading then to undo these changes, the Encrypt(Password) function must be applied while saving a record. There are two different events for saving a record: The ‘Inserting record’ event occurs while adding a new record and the ‘updating record’ event occurs while modifying an existing record.

Initialize when inserting record

A formula applied to the selected field just before the record is inserted in the database.

Example: Initialize a date field

= Today()

Initialize when updating record

A formula applied to the selected field just before the record is updated in the database.

Validate when inserting record

A validation formula applied to the field just before the record is inserted in the database.

Example: Validation formula for a Quantity field

= IF(Freight < 100, "Error: Must be over 100", "")

Validate when updating record

A validation formula applied to the field just before the record is updated in the database.

Example: Validation formula for a Quantity field

= IF(Freight < 100, "Error: Must be over 100", "")

Label text

The default field name used on web pages referencing the table which can be specified as a formula.  The display name can be further customized on a control-by-control basis by setting the control’s Properties.

This sequence applies to the Label text display:

If a user interface formula is specified

            displays result of the user interface formula

Else if a Text property is specified in the Property Sheet

            displays text property value specified in the Property Sheet

Else if a database formula is specified

            displays result of the database formula

Else

            displays default text

Example: RegionID field can have a Label text formula as

= Resource(“Region”)

where Region is a resource name

Default value

The value used to populate the record when it is initially displayed and added to the database.  Default value can be specified as a formula.

Note: If you initialize a field for which the Display Foreign Key As option is set, the initialization value “supersedes” the Display Foreign Key As setting and the database will not be queried to display the string value corresponding to the initialization value unless the Control Type is a list box, dropdown list, or radio button list.  List boxes require the database to be queried to determine all of the foreign key values in order to build the list; this is not true for Control Types that display only a single value – the initialization value.

Example: Default value formula for an OrderDate can be

= Today()

Display as

Specifies a formula to display if the field is a foreign key to another table.  If this is specified, then an evaluated formula from the joined table (foreign key value) is displayed rather than the field in the local table.

Note: Iron Speed Designer does not support setting Display As for composite foreign keys or composite virtual foreign keys.  Only single, non-composite foreign keys may be used.

Example: Formula for a CustomerID field in the Orders table can be

= Customers.CompanyName + “ “ +Customers.Country

However, formula cannot contain fields from 2 different tables. For example,

= Customers.CompanyName + Orders.CustomerID would be invalid

Right mouse click on the database formula tab displays all the fields that can be used in the Display As formula.

Action event formulas  are available in both the Data Access Layer via Databases (described here) and in the Presentation Layer via the Property Sheet.  In some cases, you may have formulas in both the Data Access Layer and in the Presentation Layer, and the question arises as to which takes precedence.  Since formulas at both levels are always executed for their respective events, it is possible for one formula to override or overrule the result of another formula based on their execution sequence.  The chronological execution sequences (evaluation order) for formulas are:

Event

Execution sequence

Reading a record

1.  Initialize when Reading Record  (data access layer event)

2.  Initialize when Showing Record  (presentation layer event)

Adding a new record to the database

1.  Initialize when Adding Record (presentation layer event)

2.  Initialize when Inserting Record (data access layer event)

Updating an existing record in the database

1.  Initialize when Editing Record (presentation layer event)

2.  Initialize when Updating Record (data access layer event)

Data conversion properties

These data conversion properties are available when you select an individual database field.

Property

Description

Storage format

The field’s storage format, typically for numbers and dates.

See Storage Format Options for details.

Trim spaces

Indicates whether to trim leading and trailing spaces on data entered into the field when new records are created or existing records are updated.

Yes

Leading and trailing spaces are removed from the string.

No

Leading and trailing spaces are not removed from the string.  The string is unaltered.

Case conversion

Indicates how to process text entered into the field when new records are created or existing records are updated.

None

No conversion is applied to the text string.

All Lowercase

The text string is converted to all lowercase.  Leading and trailing spaces are removed.

All Uppercase

The text string is converted to all uppercase.

Capitalize All Words

Every word in the string is capitalized.

Database field definition

Iron Speed Designer is not a data modeling tool; it cannot create or modify database tables (schema definitions) nor will it graphically depict relationships between the various tables.  However, it does show the database tables you have elected to use in your application as well as database field definitions for each table.

Property

Description

Field name

The field (column) name in the database schema.

Data type

The field’s (column) data type in the database schema.

Field length

The field’s (column) length in the database schema.

Not NULL

Indicates whether the field has a Not Null constraint asserted on it.  Such fields must always be assigned a value when a record is added or updated.

Computed in Database

Indicates whether the field is computed in the database.  If a field is computed, it cannot be changed by an application built with Iron Speed Designer.

Index

Indicates whether the field is indexed in the database.

Foreign key properties

These foreign key properties are available when you select an individual database field.

Property

Description

Foreign keys

Specifies one-to-many relationships between related database tables and views.

Virtual foreign keys

Specifies one-to-many relationships between related database tables and views that are not explicitly defined in the database.

For additional information, see: Adding Virtual Foreign Key Relationships.

Primary key properties

These primary key properties are available when you select an individual database field.

Property

Description

Primary key

Indicates whether the field can have only unique values.  If selected, only one row in the table can have any particular value.  This is useful for enforcing uniqueness of numbers, such as record numbers.

Virtual primary key

Indicates whether the field should be treated as if it were a primary key for the table or view.  Zero or more fields may be selected which collectively comprise the primary key of the table (a composite key).

For additional information, see: Adding Virtual Primary Key Relationships.

Use database sequence

Indicates whether the field is automatically populated with a sequence number provided by the database.  This option applies only to Oracle databases.

User interface properties

These user interface properties are available when you select an individual database field.

Property

Description

Validation type

The display formatting and data validation used whenever the field is displayed or used as an input field.

See Field Validation Types for details.

Display format

The field’s display format, typically for numbers and dates.

See Display Format Options for details.

Text box columns

The maximum number of characters that can be entered into or displayed in a text box control.  This sets the HTML “maxlength” and “size” properties in the associated text box controls used to display the database field.

The number of characters displayed can be overridden for an individual control via the “Text box columns” setting in the Property Sheet for the control.  However, the maximum number of characters which can be entered is still governed by the “Text box columns” setting on Databases.

Note that this setting governs only the display width of the field, not the number of bytes that the underlying database field can accommodate.  Text box columns pertain primarily to string-based field validation types.

Use the “Maximum display length” property to control the amount of text displayed in a literal or label control.

Applies To

Text Box

Required

Indicates that the user must provide a value on input.  If selected, an application user must provide a value for the field when using the Add Record or Edit Record pages.

If the underlying database schema has a “not NULL” constraint for the field, Iron Speed Designer will automatically select the Required option as a convenience for you.

Note: The Required option may be globally set via Databases, and hence grayed out in the Display tab of the field’s Property Sheet.

Permitted values

A list of possible legal values for the field.

For more information, see:

Permitted Value List

Adding Not-Null Field Constraints

Exclude from page

Indicates whether to exclude this field when creating new pages.  "Default" uses settings in Application Generation Options.  (Note: Excluding required fields and primary key fields is not recommended.)

Database synchronization properties

A database field may be considered new if you renamed the field, its schema order was changed or its type was changed.  You can use the Map Field dialog to identify the old field currently used by Iron Speed Designer to its new (renamed) counterpart in the database schema.  By mapping the old field to the new one, you can retain previously entered table and page properties.  See Incorporating Database Schema Changes for details.

Property

Description

Map field to

Identifies the previous name of this field.

See Also

Field Validation Types

Display Format Options

Storage Format Options

Permitted Value List

Adding Not-Null Field Constraints

Adding Virtual Foreign Key Relationships