Page History
...
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 and modifier.
Source Data Provider | Operator | Modifier | 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 countries table. A related foundset will only load orders load countries records with a customerid code equal (case insensitive) to the customerid countryCode in the context of the source customer record.
Source (customers table) | Operator | Modifier | Destination ( |
---|
countries table) |
---|
countryCode | = | case-insensitive |
code |
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.
...
- Columns
- Calculations
- Global Variables (single values or Arrays)
Destination Data Provider - Available Types
...
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, UUID, Array (in) |
> | 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, UUID, Array (not in) |
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
Operator modifiers
Modifiers can be defined for an operator, multiple modifiers can be combined for a relation item.
Modifier | Description |
---|---|
case-insensitive | case-Insensitive comparison |
or-is-null | allow null values in the value (will result in sql <cond> or column is null) |
remove-when-null | remove the condition when the value is null, this is usually used for icw a global variable holding an array of values |
Note | ||
---|---|---|
Text-Based Expressions Expressions which contain the SQL Like or SQL NOT Like operators should be used in conjunction with values that contain wildcards (%).
|
...
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 (either programmatically or through the GUI), the related foundset for a customer's orders is updated instantly.
...
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:
...
Example In its most basic form, the value list is simply a list of values from which to choose. The values which are displayed are also stored into a data provider.
Yes |
No |
Example When designing a value list, a developer may use a pipe character '|' to separate display values from stored values. In this example, the options Yes and No will be displayed to the user, but the values 1 and 0 will be respectively stored into a data provider.
Yes|1 |
No|0 |
Example Value lists can easily be made multi-lingual by using i18n keys in lieu of literal values. Here the same value lists is made multi-lingual and the Yes and No values will be displayed in the language of the user's locale.
i18n:yes|1 |
i18n:no|0 |
Example Value Lists can also evaluate Data Tags to store literal values that are already declared as global variables. This approach is recommended to store constant values, which are declared once in the entire application. Here a list of order statuses are used. The display values are made multi-lingual as above. And the stored values reference global variables defining constant values for each order status. The data tag takes the form %%globals.myVariableName%%.
i18n:orderStatusNew|%%globals.ORDER_STATUS_NEW%% |
i18n:orderStatusConfirmed|%%globals.ORDER_STATUS_CONFIRMED%% |
i18n:orderStatusShipped|%%globals.ORDER_STATUS_SHIPPED%% |
Global Method Values
A value list can be bound to a global method which supplies the values every time the value lists is used. This option gives the developer the most control, but also requires that the developer write code, and is therefore recommended to be used when the other value list types are not sufficient. The method is invoked often, each time the value list is referenced and each time the context changes.
...