Page History
...
Other than programming scope, the only difference between the two is that globals can be used as keys in relations.
Relations
Relations, at design-time model associations between two between two tables by joining on key data providers. At runtime, a relation becomes a contexta context-sensitive programming sensitive programming reference to related data. Thus, relations are simple, but powerful mechanisms to display, edit and search for data from any context. They can be used, not only to model simple database relations, but also to create sophisticated filters and searches.
Design-Time Properties of Relations
Relations have several design-time properties that dictate how related foundsets will behave at runtime.
Source
...
Table
Can be any database table or view from any named server connection.
At runtime, a related foundset will exist in the context of a single record from the source table. For example, the relation customer_to_orders, will become available in the context of any record in a foundset which is based on the customers table.
...
Destination Table
...
Can be any database table or view from any named server connection and is not limited to the same database as the destination table.
...
| Note |
|---|
The destination table can exist in a separate database from the source tablethe source table. This is a powerful feature, but it is worth noting that a related foundset, who's relation is defined across two databases will not be available when the source foundset the source foundset is in find mode. This is because a related find requires a SQL JOIN, which cannot be issued across databases for all vendors. |
...
Relation Items
...
The nature of the relation between the source and destination tables is defined by one or more Relation Items. Relation Items are expressions, each consisting of a pair of key data providers (one from each table) and a single operator.
Source Data Provider | Operator | Destination Data Provider |
The Relation Items will be used to constrain the records that are loaded in the related foundset, such that records are loaded only when all of the expressions evaluate to be true.
Example: This example creates a relation between the customers and the orders table. A related foundset will only load load orders records with a customerid equal to the customerid in the context of the source customer record.
Source (customers table) | Operator | Destination (orders table) |
|---|---|---|
customerid | = | customerid |
...
Data Providers
...
One data provider from each table will serve as an operand in the key-pair expression. Therefore, both data providers must share the same data type. Columns, calculations and global variables may all be used as the source data provider. However, only columns may be used for the destination data provider.
Source Data Provider - Available Types
- Columns
- Calculations
- Global Variables
Destination Data Provider - Available Types
- Columns Only
| Note |
|---|
Related foundsets are loaded in the context of a single source table record, which is already known. Therefore, any global variables, as well as the source record's calculations can be evaluated and used as a key. However, only columns from the destination table can be used as the as the dynamic data providers cannot be evaluated on behalf of destination records before they are loaded. |
...
Operators
...
Each key pair expression is evaluated using a single operator. Certain operators are only applicable to certain data types. Below is a list of all available operators and the data types for which they are applicable.
Operator | Description | Data Types |
|---|---|---|
= | Equals | Text, Integer, Number, Datetime |
> | Greater Than | Text, Integer, Number, Datetime |
< | Less Than | Text, Integer, Number, Datetime |
>= | Greater Than or Equal To | Text, Integer, Number, Datetime |
<= | Less Than or Equal To | Text, Integer, Number, Datetime |
!= | NOT Equal To | Text, Integer, Number, Datetime |
like | SQL Like use with '%' wildcards | Text |
not like | SQL Not Like use with '%' wildcards | Text |
#= | Case-Insensitive Equals | Text |
#!= | Case-Insensitive NOT Equal To | Text |
#like | Case- |
...
Insensitive SQL Like | Text |
#not like | Case- |
...
Insensitive SQL NOT Like | Text | |
^||= | Null OR Equals | Text, Integer, Number, Datetime |
^||> | Null OR Greater Than | Text, Integer, Number, Datetime |
^||< | Null OR Less Than | Text, Integer, Number, Datetime |
^||>= | Null OR Greater Than or Equal To | Text, Integer, Number, Datetime |
^||<= | Null OR Less Than or Equal To | Text, Integer, Number, Datetime |
^||!= | Null OR NOT Equal | Text, Integer, Number, Datetime |
^||like | Null OR SQL Like | Text |
^||not like | Null |
...
OR SQL NOT Like | Text | |
^||#= | Null OR Case-Insensitive Equals | Text |
^||#!= | Null OR Case-Insensitive NOT Equals | Text |
^||#like | Null OR Case-Insensitive SQL Like | Text |
^||#not like | Null |
...
OR Case-Insensitive SQL NOT Like | Text |
| Note | ||||
|---|---|---|---|---|
Text-Based Expressions Expressions which contain the contain the SQL Like or or SQL NOT Like operators operators should be used in conjunction with values that contain wildcards (%).
|
...
Join Type
...
A relation can specify one of two SQL Join Types. A SQL join used when a find or a sort a find or a sort is performed using related criteria and thus, the join type will affect behavior in these situations.
Inner Join
SQL Inner Join does not return any rows for parent records which have no related records. Therefore, if a sort or a find is performed when a related data provider is used for criterion, the related foundset may have records omitted due parents with no child records.
Left Outer Join
SQL Left Outer Join will return always return a row for the parent record even if there are no related records. Therefore Therefore, if a sort or a find is performed when a related data provider is used for a criterion, the related foundset will include all matching records, regardless of the presence of related records.
Example: Assume that the user chooses to sort a customer list containing 50 records. The sort is based on the account manager's last name, which is in the the employees table table. However, 3 of the customers don't have an employee listed to manage the account.
| Code Block |
|---|
foundset.sort('customers_to_employees.last_name asc'); |
...
foundset.getSize(); // returns 50 if the customers_to_employees relation specifies left outer join, 47 if the relation specifies inner join. |
...
Initial Sort
...
Foundsets, including related foundsets, have a sort property. By default, any foundset is sorted by the primary key(s) of the table upon which it is based. Relations have an Initial Sort property property, which overrides the default sort, such that any related foundset is initialized to use the sorting definition defined by the relation object. For more information see foundset sorting.
...
Referential Integrity Constraints
...
Relations have three options that support referential integrity in the data model. These options control both the actions that are permissible, as well as cascading actions in the data model.
Allow creation of related records
This option is enabled by default and it specifies that records can be created within a related foundset. Moreover, when records are created in a related foundset, the key columns in the new record may be automatically filled with the corresponding values from the source record.
Example: Assume a relation, customers_to_orders defined by a single key expression, customers.customerid = orders.customerid
| Code Block |
|---|
customerid; // 123, the customer's id |
...
customers_to_orders.newRecord();// create the new record |
...
customers_to_orders.customerid; // 123, the order record's foreign key is auto-filled |
Key columns will be auto-filled for expressions using the following operators:* =
- #=
- ^||=
If this option is disabled, then records cannot be created in a related foundset. If attempted a ServoyException is raised with the error code, NO_RELATED_CREATE_ACCESS.
Allow parent delete when having related record
This option is enabled by default. When disabled, it will prevent the deleting of a record from the source table if the related foundset contains one or more records. If the delete fails, a ServoyException is is raised with the error code, NO_PARENT_DELETE_WITH_RELATED_RECORDS.
Example: Assume the relation customers_to_orders has has disabled this option. An attempt to delete a customer record will fail, if that customer has one or more orders.
Delete related records
This option specifies that records in a related foundset can be deleted. Moreover, it also enforces a cascading delete, such that when a source record is deleted, all records in the related foundset will also be deleted, eliminating the eliminating the possibility of orphaned records.
Example: Assume the relation relation customers_to_orders has has enabled this option. The deleting of the customer record will cause all related order records to be deleted.
Special Relations
...
Dynamic Relation
...
In addition to database columns, calculations and global variables may be used as keys for the source table. This provides a means to implement dynamic data filters without writing any code or SQL. A related foundset is refreshed whenever the value of a source key changes. Thus, by using variables and calculations as keys, developers can articulate nuanced views of data that are contextual not only to the source record, but also the changing state of the application.
Example: Assume that one wants to filter a customer's orders by date in different ways, i.e. today, this month, last month, this year, last year, etc. One could define the following relation from customers to orders.
Source | Operator | Destination |
|---|---|---|
customerid | = | customerid |
globals.orderFilterStart | <= | orderdate |
globals.orderFilterEnd | >= | orderdate |
By simply changing the value of the global variables variables (either programmatically or through the GUI), the related foundset for a customer's orders is updated instantly.
...
Global Relation
...
Global relations are simply relations that use only global variables for source data providers. The key difference between global relations and regular table relations is that the related foundset exists in a global context, having no source record as a context. The obvious benefit is that the globally related foundset will be available ubiquitously, instead of being limited to the context of records based on a source table.
Example: Assume that a customer service rep should have a dashboard of all of their orders that are due today. This view could be accomplished using a global relation on the orders table, which could be used anywhere in the application, such as a form in tabpanel to show a dashboard view.
Source | Operator | Destination |
|---|---|---|
globals.currentUserID | = | sales_rep_id |
globals.today | = | orderdate |
...
Self Relation
...
Relations may have the same source and destination table. This is called a Self Relation and has a variety of applications, such as showing data which is hierarchical in nature, or simply showing other records in the same table, which have similar attributes.
Example: When looking at a particular order record, the user may like to see a portal containing a list of all of the other orders made by the same customer as the current order. This could be expressed using a Self Relation, orders_to_orders_by_customer, containing two relation items. The first specifies the same customer, the next ensures that the current order is omitted from the foundset.
Source | Operator | Destination |
|---|---|---|
customerid | = | customerid |
orderid | != | orderid |
Example: An employees table is organized such to reflect a companies chain of command. When looking at an employee record, a user should be able to easily see the employee's boss, as well as the people that the employee is managing.
employees_to_employees_manager: The current employee's boss
Source | Operator | Destination |
|---|---|---|
manager_id | = | employee_id |
employees_to_employees_managing: The employees managed by the current employee.
Source | Operator | Destination |
|---|---|---|
employee_id | = | manager_id |
...
Container Relation
...
Container relations are a type of Self Relation used to reference the source record's foundset. To create a container relation, the following must be true:
- Source table and destination table are the same
- There are no relations items
A container relation is applicable when a parent form must contain a child form in a TabPanel and the child form is based on the same table and should show the same foundset as the parent. Use of a container relation will ensure that the parent's foundset is shared with the child.
Example: There are three forms based on the customers table: customerMain, customerList, customerDetail. The customerMain form contains the other two forms in a single, unrelated TabPanel, such that the user can easily toggle between list and detail view. The The approach will work so long as the customerMain form is also using an unrelated foundset. However, if the customerMain form loads a related foundset (i.e. it is shown through a relation), then the two child forms will still be unrelated and therefore out of sync. The solution is to create a container relation for the customers table and show the 2 child forms through this relation. This guarantees that they will always share the same foundset.
...
Overview
Content Tools
Activity