All Dynamic pages in Construct Preview are based on a view.
Refer to SQL Code Guidelines for Construct and View Quick Reference for more general information.
When creating page views:
Do not include Order By statements. The Order By page property in Syniti Construct must be used.
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 Syniti Construct can be defined to change a column name using dictionary property that extract translations data from the Syniti Knowledge Platform. Refer to Dictionaries for more information.
Only include the necessary tables.
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 Syniti Construct tables.
Data Catalog, Datastores, Datasets and Views
Note
If you are familiar with the Syniti Stewardship Tier (formerly known as CranSoft or DSP) or the legacy version of Construct, there is a major functional difference between the old platforms and the new. Construct Preview relies on the schema information stored in the Data Catalog module for SQL object definitions. Changes to tables or views need to be scanned into the Data Catalog module before Construct Preview can recognize them. The Data Store configuration pages allow for scanning of specific tables or views to get the latest schema definition, simplifying the scanning process.
Once a view is created, a scan must be executed, after which you can use your new view for a Grid or a Property Card, or as a source for a List or a Validation view. For Grid or Property Card containers or List sources, a new Dataset is generated with all fields currently present in your view assigned to the new dataset. Refer to Datasets for more information.
For Grid and Property cards, if this dataset is a view, additional dataset configuration is required. You should modify the dataset record and set the “Affected Table” value to the physical table that is the basis for your page. Only fields that exist in both the Dataset and the Affected Table are supported for input or editing. All other fields are read only. At this point in time, all fields registered to the dataset must belong to the single table or single view used to create the dataset.
If you add or remove columns from a view or table used in a Dataset, you will need to rescan those objects and manually update the Dataset with the added or removed columns.
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 Properties Card 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 can also include the relevant table name after the “web” to denote which base record set the view operates against. 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”.
Lastly, view names are suffixed with an operation abbreviation for their associated usage. Other 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.
Guidelines for Grid Views
When creating a Grid View:
Make the first column(s) in the views the keys from the table that will be registered to the page to which the Grid View is assigned.
Limit the number of fields on the view to reduce horizontal scrolling in Syniti Construct.
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 datasets in Grid Views:
List/Combo Boxes (especially with Dynamic WHERE clauses)
nvarchar(max)
Other extremely lengthy data type fields
Guidelines for Properties Card Views
The Properties Card View should contain the columns on the Grid View. Additional properties can be applied to organize the Properties Card to reduce vertical scrolling.
When creating a Properties Card View:
Make the first column(s) in the view the keys from the table that will be registered to the page to which the Properties Card 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 Grid View assigned to the same page as the Properties Card View, if applicable.
Organize data groups into tab_containers 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 Grid and Properties Card views, 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 Grid View, and an editable list box on the Properties Card View. This keeps the description field visible on the Grid View of the page, making it filterable and searchable, while greatly increasing performance.
Alternatively, the list box on the Grid View can itself be simplified when it is disabled. 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.