Relations

Overview

Relations, at design-time model associations between two tables by joining on key data providers. At runtime, a relation becomes a context-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.

Get Started

Once the database server connection has been set up, developers now have the possibility to access and manage database tables and relations.

Create a Relation

The example below creates a relation from the orders table to the customers table and has the following steps:

Create a Relation

The Relation Editor; creating the relation orders_to_customers

1. From the Solution Explorer, right-click the "Relations" node and select "Create Relation". This will open the Relation Editor.

2. Set the Source by clicking the ... button to open the Data Source Chooser. Select the source table name.

3. Set the Destination by clicking the ... button to open the Data Source Chooser. Select the destination table name.

4. Create one relation item :

   1. select the primaryDataProvider in From column

   2. select the operator in Op column

   3. optionally select the modifier in Modifier column

   4. select the foreignColumnName in To column

5. Handle relation options:

   1. mandatory: Join Type

   2. optional: Initial Sort

   3. optional: Deprecated

   4. mandatory: Encapsulation

   5. optional: select / unselect the following properties:

      1. Allow creation of related records

      2. Allow parent delete when having related records

      3. Delete related records

   6. optional: Comment

6. Save the editor.

Notice you don't have to specify the relation name. The editor will always suggest source_to_destination, in this case orders_to_customers. This is a good convention to follow for ordinary relations.

Using a Relation at Design-Time

Relations can be used as dataproviders, same as tables' columns. Having first set the source table of the relation as the datasource of the form, you can further use related tables.

Examples: dataprovider for a component:

dataprovider for columns in a Data Grid:

relationName for a form container:

Using a Relation at Run-Time

At Run-Time relations behave like database tables.

Example: Create Related Records:

function addItem(event) {
	
	// create the record 
	orders_to_order_details.newRecord();
	
	// set the quantity default to 1
	orders_to_order_details.quantity = 1;
}

Here you can see that the relation, orders_to_order_details, can be used in code to reference the related JSFoundSet object and the newRecord method is available. You can also reference the data providers of the related foundset, such as quantity.

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 (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 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.

If the child forms are not shown through a relation, the approach may still work in many cases, because unrelated forms of the same table will share a single foundset. This is discussed in more detail in the section covering foundsets.