Using Relational Views

Overview

PhixFlow can display data from any Table and any Tables that it has relationships with. Relationships between tables are defined on an Entity Relationship Diagram (ERD). For more information on ERD's, see Entity Relationship Diagrams.

This page describes how to utilise these relationships to display data, using a grid as its example. The concepts can be used on all data bound components.

Relational Attributes

An Employees table has a relationship with a Company table. See the below ERD:


Each employee belongs to a company and has a company ID associated to it. It is stored in a foreign key attribute, Company. This attribute is linked to the primary key (id_1) of the Companies table.

The Companies table also has a display name attribute (CompanyName). This is the attribute that is displayed on the front end when adding a foreign key to any data bound component.

For more information on ERD diagrams and relational views, see Entity Relationship Diagrams.

Adding Foreign Key Attributes

When a foreign key, e.g. Company on the table Employees, is added to the grid, PhixFlow does the following automatically:

  • adds the display name attribute (CompanyName) from the related table (Companies).
    • This attribute is marked with in the grid header,
  • hides the foreign key.

Adding Relational Attributes from a many:1 Relationship 

On the above grid the user also wants to display the companies address on the same grid. This data is stored on the Company table and can be accessed using the relationship. To add the company address

  1. Right click on the grid and click Show attributes for 'Employees'
  2. Click the Related Table Companies(Company)
  3. Drag and drop the attribute Address onto the grid. This attribute is marked with  on the grid header.

Adding Relational Attributes from a 1:many Relationship 

The user creates another screen to display the company data and wants to display the number of employees for that company using their 1:many relationship. Using an attribute from a 1:many relationship causes aggregation of the data. The most simple case is to use the count() attribute.

To add a relational attribute from a 1:many relationship:

  1. Right click on the grid and click Show attributes for 'Companies'
  2. Click the Related Table Employees(Company)
  3. Drag and drop the attribute Count() onto the grid. 
    This attribute is marked with  on the grid header. It is counting all the records on the Employees table which have that company as its foreign key value.
  4. Rename the attribute using the label to Number of Employees in the Grid Settings section on the attribute editor.

Adding any other attribute on the table will also cause aggregation. A default aggregate function is added to the attribute added. If more than one attribute is added the aggregation is combined.

The default aggregate function can be changed by:

  1. Open the attribute editor by clicking on the attribute column header
  2. Add an Aggregate function using the drop down under Basic Settings.

PhixFlow sets the following defaults: 

Data TypeDefault Aggregation
String/Bigstring

Count

Integer/Float/DecimalSum
Date/DateTimeMax
TrueFalseMax

For more information on aggregation, see Aggregating Data.