Relation Item

Overview

A relation item is one of the (potentially multiple) logical objects from a relation that tell the application how the two tables are related.

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 (the first can be a global variable as well) and a single operator and modifier. 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 countries table. A related foundset will only load countries records with a code equal (case insensitive) to the 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.

Source Data Provider - Available Types:

• Columns

• Calculations

• Global Variables (single values or Arrays) 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 dynamic data providers cannot be evaluated on behalf of destination records before they are loaded.

modifier

The operator that defines the relationship between the primary data-provider and the foreign column can have a modifier. Modifiers can be defined for operators, so multiple modifiers can be used in 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 icw a global variable holding an array of values
For Text-Based Expressions

Expressions which contain the SQL Like or SQL NOT Like operators should be used in conjunction with values that contain wild-cards (%):

customers.city like New%        // Starts with: i.e. New York, New Orleans
customers.city like %Villa%     // Contains: i.e. Villa Nova, La Villa Linda
customers.city like %s          // Ends with: i.e. Athens, Los Angeles

Properties Summarized

Type	Name	Summary
String	foreignColumnName	The name of the column from the destination table that this relation item is based on.
Number	operator	The operator that defines the relationship between the primary dataprovider and the foreign column.
String	primaryDataProvider	The name of the column from the source table that this relation item is based on.

Properties Detailed

foreignColumnName

The name of the column from the destination table that this relation item is based on.

Type String

Sample

"orderid"

operator

The operator that defines the relationship between the primary dataprovider and the foreign column. 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

Type Number

Sample

"="

primaryDataProvider

The name of the column from the source table that this relation item is based on.

Type String

Sample

"orderid"

---