Relation Item

Overview

A relation item is a logical object telling the application how 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) 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.

Relation _customers_to_countries

Property Summary

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.
Number	modifier	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.

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

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.

Properties Details

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

"="

modifier

The operator that defines the relationship between the primary dataprovider and the foreign column. 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 icw a global variable holding an array of values

Text-Based Expressions

Expressions which contain the SQL Like or SQL NOT Like operators should be used in conjunction with values that contain wildcards (%).

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

Type Number

Sample

"case-insensitive"

primaryDataProvider

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

Type String

Sample

"orderid"