Create, manage, and extend views
Like tables, a view has rows and columns that deliver data to the user in outputs such as queries, forms, and reports. A view can have fields from joined tables, and it provides focused and simplified access to data.
The components in views are similar to tables and include fields, field groups, indexes, relations, mappings, state machines, methods, and events.
You can create view extensions to enhance views, but you can’t create code extensions. Use an event handler class to create pre-events and post-events for methods.
Additionally, you can use Microsoft Visual Studio to create, manage, and extend views.
Create views
Add views to a project by using the Solution Explorer window. Right-click the project or activate the context menu and then select Add > New item. You can modify the views that you create by using the Designer and Properties windows. The Designer window has a top-level node with a view name, and the node should be unique for tables and views.
For queries, data sources, and similar, the system creates one list for all table and view names when you select a table name. The top-level properties include properties that are like tables, such as Label, Title Field1, and Title Field2.
The following table lists the top-level properties for views. For more properties, see Populate table and field properties.
| Property | Option | Description |
|---|---|---|
| Query | Blank or a query | Use a blank value in the Query property to create view metadata manually. Then, you can select a query in the property, and the system will use it as view metadata, including its fields, ranges, sorting, joins, and similar. You can’t change the view metadata if you’re selecting a query. |
The following table lists the nodes that are available for a view in the designer.
| Node | Options | Description |
|---|---|---|
| Metadata | Create manually or use the Query property on the view’s top level | The underlying data that the system uses for the view. Typically, it includes one or more tables or other views (data sources). Metadata isn’t stored in the database as a table. You can define views as a virtual table whose contents are defined by a query. The rows and columns of data come from tables that the metadata references. It’s important that you’re aware of performance issues when building a view’s metadata. |
| Fields | Field from the view metadata, a virtual field, or a computed column (virtual field) | Use this node to add fields from the view metadata by selecting a field on the view’s defined data source and then dragging it to the node fields on the view. Create fields manually by right-clicking the Fields node or by activating the context menu and then selecting New, Field. The system defines a virtual field as a computed column, but the property is set to virtual, and the field value is set in the view’s postLoad method. Add computed fields (calculated fields) as a field on the view by right-clicking the Fields node or by activating the context menu and then selecting the corresponding type for the computed column (described in a subsequent section). For fields and computed columns, you can create an Extended data type as a best practice. |
| Field groups | Similar to tables, field groups can be system created and custom groups. | |
| Indexes | Use this node to optionally create an index on a view and store it on the view’s database. | |
| Relations | View relations work, such as table relations. | |
| State machines | Specify a data field to use. | |
| Mappings | Specify a map to map to another object. | |
| Methods | Typically, you use this node for display methods (as you would on tables) or methods for computed columns that return values by using Microsoft Transact-SQL (T-SQL). NOTE: The purpose of a view is to write to data, so the insert, update, or delete methods aren’t necessary. | |
| Events | The node exists but isn’t used. |
A computed column is a virtual calculated column in the view, and the system calculates it on the database with T-SQL from a method.
Create computed columns on the Fields node by selecting the corresponding type for the computed column, which in earlier versions was called an unmapped field. Then, specify the View Method property, which is the calculation for the computed column that’s defined in the view’s Methods node.
Create methods by right-clicking the view in Solution Explorer or by activating the context menu and then selecting View Code (F7). If you already have the view open in the designer, you can right-click at the top level or activate the context menu and then select View Code (F7). When you’re creating a new method for a computed column, it must be static and it must return type str (T-SQL to be implemented on the database).
The method uses the following components to calculate the result:
- View name, which helps you find the DictView class.
- Data source name that’s found in view metadata, which helps you find the table’s data source.
- The field in the field list on the data source.
In the global DictView class, you can call the computedColumnString method with the data source and field to generate T-SQL to the return.
If you’re creating more complicated expressions, such as and, or, compare, or if, see the help methods in the SysComputedColumn class.
The following example shows the use of a method for a computed column (view AssetTransReverse):
/// <summary>
/// Defines the computed column for the negative <c>AmountMST</c> view field.
/// </summary>
/// <returns>
/// The SQL statement which defines the negative <c>AmountMST</c> view field.
/// </returns>
public static str negativeAmountMST()
{
#define.ViewName(AssetTransReverse)
#define.DataSourceName("AssetTrans_1")
#define.AmountMSTField("AmountMST")
str amountMST;
DictView dictView;
dictView = new DictView(tableNum(#ViewName));
amountMST = dictView.computedColumnString(#DataSourceName, #AmountMSTField, FieldNameGenerationMode::FieldList, true);
return SysComputedColumn::negative(amountMST);
}
When you’ve creating the view, save and build it as you would for other elements.
Manage views
Make sure that you sync views to the database to create the view in the underlying database.
During synchronization, the system detects computed columns by using the name on fields that have View Method selected and by adding the column to the view creation. Synchronization can fail if the computed column generates invalid T-SQL syntax. Using an unsynchronized view results in errors that display to the user.
Extend views
You can change views by creating an extension. Extend a view by locating it in the Application Explorer, right-clicking it or activating the context menu, and then selecting Create extension.
You can create extensions in a current project or a new one and then use them to complete the following tasks:
- Add new fields. You can’t add computed columns because you can’t add code to the view.
- Change view metadata, such as adding a range or a new data source.
- Add or change field groups.
- Add new mappings.
You can’t create a code extension for a view, but you can create an event handler class for the view so that pre-events and post-events are available for the methods.