Application Development

Create Views in SQL

All Dynamic pages in Stewardship Tier are based on a view.

Refer to SQL Code Guidelines and View Quick Reference for more general information.

When creating page views:

  • Do not include Order By statements. The Order By page property in Stewardship Tier must be used. Refer to Assign Page Properties to a Dynamic Page for more information.
  • Name the column names in the table so that the contents of the column are self-explanatory.
  • Do not alias column names in views solely for display purposes unless it is necessary to avoid duplicate column names in a view or to avoid adding labels and tabs. Translations in Stewardship Tier can be defined to change a column name. Refer to Catalogs for more information.
  • Only include the necessary tables.
  • For labels and tabs (column properties in Stewardship Tier), enter NULL in the column and enter the name of the label or tab in the Alias field. Refer to Add a Label and Add a tab for more information.
  • Only fields that are present in the page’s view and associated table from the page definition are supported for input and edit. However, other tables and/or fields can be joined in the view to provide and display read-only supporting information.
  • Functions can be used to display content in a column, but can affect performance. A field that displays data from a function is read-only and cannot be updated.
  • Use a view to reference tables in another database. The most common use of this practice is when referencing Collect (dgSAP) tables or Stewardship Tier tables.
  • A view can also be used in a page property as a table selection as long as the required key(s) are listed in the view and properly defined in a column property. 
  • Do not add boareserved columns to views (except for the boaStatus column). Refer to Append Syniti Reserved Columns to Tables for more information.

View Type refers to both the configuration of the page’s data views (Horizontal, Vertical, etc.) as well as the Page Column View Type specification. During page design, the Application Designer has to consider fast page load, versus the quantity of relevant data that displays on the page.

This section contains the following topics:

SQL View, Function, and Procedure Design

Non-schema object design is the next most important thing above table design when creating a WebApp. When lightweight and efficient, even complex functions, views, and procedures can be implemented with little overhead.

NOTE: Avoid Global Operators. Any SQL procedures or functions that have to enumerate or parse full sets of table data are dangerous due to their inability to scale efficiently.

View and Stored Procedure Naming Conventions

Naming conventions are essential as they make development easier, and a standardized set of naming conventions allows other application developers to understand behaviors and configuration.

Views have different sets of naming conventions. The default naming conventions display on the WebApps page’s Vertical View on the Naming Conventions tab. When naming views, follow these conventions.

By default, view names start with web* which allows a developer reviewing/debugging SQL to differentiate between table/view queries. Views must also include the relevant table name after the “web” to denote which base record set the view operates against if the target WebApp has the Enforce Strict Naming option enabled. The view may also contain a brief (one or two word) description of the view’s selection criteria. For instance, if a customer is “Inactive,” the view may start with “webCustomer_Inactive.” Refer to Naming Conventions and the Enforce Strict Naming Feature for more information.

Lastly, view names are suffixed with an operation abbreviation for their associated usage. For application development, a list of these can be found on the Vertical View of the WebApps page (Admin > WebApps > Vertical View > Naming Conventions tab). Other non-enforced naming conventions that are commonly used are “Sel” (to indicate it is selecting a subset of data) and “Count” (to indicate it is selecting some aggregate or metric data based on the primary table). Underscores can be used to increase readability of the view name.  For example, the full named view “webCustomer_NameStartsWithLetterASel” returns a record set of all customers with names starting with “A.”

Procedures have similar naming conventions, but suffix with Upd (for update), Del (for delete), and Ins (for insert logic).

Partition Logic Semantically

An application’s value is not measured in SQL object count. Centralizing logic can help consolidate an application into smaller, more manageable chunks.

For example, several “Customer” based pages select records from the Customer table. The table has an “Active” bit, and can be modified so that only “Active” customers show up on these pages. Many of these existing “webCustomer*Hor” views may already have complex WHERE clauses. Appending “[Active] = 1” to every WHERE clause adds complexity to the WHERE clause, and makes it less readable and cumbersome to maintain. Instead, create a supplemental active view such as “webCustomer_ActiveSel” and replace all of the “FROM [Customer]” sections with FROM [webCustomer_ActiveSel]. This way, if the definition of “Active” changes, it’s an easy change in one view, instead of many.

Procedures are another common place to consolidate logic. SQL stored procedures can be considered object oriented code in the eyes of the developer. Consolidate any logic that is more complex than a single SQL statement into a procedure. This greatly reduces the chance of bugs occurring, and centralizes the logic so that a developer doesn’t have to remember where everything is.

For example, place logic such as “When I see an update to set a field to 1, I also now need to go set this other field to 2” into a stored procedure. Updates to this logic can then be added in a single place.

Horizontal and Vertical Views

Every dynamic page in Stewardship Tier must have either a Horizontal and/or a Vertical View.

The basic principle of page design is to keep the Horizontal View simple, and have more lengthy elaborate data on the Vertical View. Multi-row rendering of the data is responsive and less overwhelming to the user, while the slower computation / dense data displays on the Vertical View, which is loaded on demand.

For example, if a table contains many columns for a single record, to avoid any horizontal scrolling and foster a better user experience, create Horizontal and Vertical Views. The Horizontal View contains the basic information about the columns, while the Vertical View contains the columns on the Horizontal View and all additional columns.

Guidelines for Horizontal Page Views

When creating a Horizontal Page View:

  • Make the first column(s) in the views the keys from the table that will be registered to the page to which the Horizontal View is assigned.
  • Limit the number of fields on the view to avoid horizontal scrolling in Stewardship Tier  
  • Add columns in an order so that the flow of the page processes from left to right.
  • Use the naming convention webXXXHor, where XXX is the name of the table.
  • The name of the underlying table, which will be registered to the page, must be included in the name of the view.

Use these controls sparingly on large data sets in Horizontal Views:

  • List/Combo Boxes (especially with Dynamic WHERE clauses)
  • nvarchar(max)
  • Other extremely lengthy data type fields

Guidelines for Vertical Page Views

The Vertical View should contain the columns on the Horizontal View and all additional columns. Additional properties can be applied to organize the Vertical View to eliminate vertical scrolling.

When creating a Vertical Page View:

  • Make the first column(s) in the view the keys from the table that will be registered to the page to which the Vertical View is assigned.
  • Add columns in an order so that the workflow of the page processes from top to bottom.
  • Include all field outputs in the Horizontal View assigned to the same page as the Vertical View, if applicable.
  • Organize data groups into tabs so that each page does not require excessive vertical scrolling or to provide categorical grouping.
  • Separate logical groups of data in a tab using labels.
  • Use the naming convention webXXXVer, where XXX is the name of the table.
  • The name of the underlying table, which will be registered to the page, must be included in the name of the view.

Improve Page Load Performance Related to List Boxes

List boxes can often be a source of performance degradation due to their elaborate configurations and lengthy lookup times that may have to occur at a cell level. Even if disabled, on the loading of the page, the cell value must be looked up to display to the user. If several list boxes are located on the Horizontal View, page load time can be severely inflated. An easy solution is to have a read-only view-level resolution of the list source (assuming it is a view in the same database) on the Horizontal View, and an editable list box on the Vertical View. This keeps the description field visible on the Horizontal View of the page, making it filterable and searchable, while greatly increasing performance.

Alternatively, the list box on the Horizontal View can itself be simplified when it is disabled. There is no need to include a complex WHERE clause that may be present in the list box configuration, if it is only meant to resolve values and not restrict user input or security. Even this simple change can improve page load.