HowTo:Create a Database View to Join Data from Related Objects

From AgileApps Support Wiki

For:   Users
Level: Advanced
Time: 20 minutes

See more:
    ◾ HowTo Guides

GearIcon.png > Customization > Database Views

A Database View lets you define a complex data model by specifying a join across Related Objects. Fields from the related records in each object are then available when running a Report, providing greatly augmented reporting capability.

Using a Database View

After a Database View has been created, it can be used as the basis for a Report. (It appears in the Categories tab of a Report, along with other Objects and Database Views.)

Using that Database View, a report operates on "records" that result from combining fields from each of the objects specified in the Database View.

Working with Database Views

Lock-tiny.gif

Users that have the Customize Objects permission can create a Database View. 
Considerations
  • Database Views can be used to build Reports or Web Tabs
  • If you change a Database View, Reports based on it may fail until they are adjusted, as well.
  • Fields can be removed from the Database View for various reasons - an object can be removed and replaced with a new object or a new set of fields can be selected
  • If any fields that are being removed are used in reports based on this Database View, the edits to the Database View will not be allowed; You will have to remove the fields from the affected reports before completing the edit on this Database View

Create or Edit a Database View

To create or edit a Database View:

  1. Click GearIcon.png > Customization > Database Views
  2. Choose one of the following options:
    • Click the [New Database View] button to create a new Database View
    • Click the name of the Database view to edit
  3. In the Select tab, specify the objects to use in the Database View. (more below)
    Note: At least two objects must be selected.
  4. Click [Next]
  5. In the Joins tab, define the relationship (Joins) between the selected objects. (more below)
  6. Click [Next]
  7. Give the database View a name.
  8. Click [Save]

Select Tab

Start by selecting objects to participate in the view.

Select Objects to Join
Select the desired Objects by moving them to the Selected Objects column).
(You need to select two at a minimum, in order to create a join.)
Filter by Record Ownership
For each object use the Checkbox Checkboxicon.gif icon to enable filtering by record ownership when creating a Report.
(A report can then be run on all available records, or on records owned by specific users or teams.)
Custom Aliases
This section is relevant only to programmers, who may need to reference the alias (name) for an object in a Database View when using one of the platform APIs.
The alias fields are auto-populated with default values, but can be changed as needed to make programming easier:
  • The default alias for a System Object is the object name.
    (These aliases rarely need to be changed.)
  • The default alias for a System Object is custom_{numeric_object_ID}
(These aliases are typically changed to be more readable, when programmers need to reference them. Otherwise, they can be left alone.)
The typical alias will be based on the object's plural label. So the alias for the OrderItems object might will be order_items.
Considerations
  • Because they are referenced in a program, aliases needs to follow the same Naming Rules that apply to field names.

Joins Tab

Joins are defined by specifying the fields that link a record in one object to a record in another object.

About Joins

A "join" is a concatenation of fields from two objects that have the same data for a field they have in common (even though the fields generally have different names).

There are three types of joins:

For example, if object A has records with values 1, 2, 3, 4 in field "x", and object "B" has values 3, 4, 5, 6 in field "y", then the different kinds of "joins" produce rows like the following:

Row Record data from "A" Record data from "B" Type of Join
1
2
x=1, ...field data...
x=2, ...field data...
none
none
Non-matching rows, returned only in a Left-Join.
3
4
x=3, ...field data...
x=4, ...field data...
y=3, ...field data...
y=4, ...field data...
Matching rows returned in a left join or right join.
The only rows returned by an Equi-Join (typically the default).
5
6
none
none
y=5, ...field data...
y=6, ...field data...
Non-matching rows, returned only in a Right-Join.

Warn.png

Important:
The "joining fields" should be indexed, for performance. In the example above,
field "x" should be indexed in A, and field "y" should be indexed in B.
Learn more: Indexes

Join Theory

In a join, choose at least one object that contains an ownership record. The reason:

About creating joins:

  • When two objects are selected, a single, mandatory relationship (Join) must be specified
  • When three objects are selected (e.g. A, B and C), two mandatory relationships must be specified
  • The drop down lists fields that available for use in a Join.
    In general, the following kinds of fields can be used:
    • Record ID fields (fields containing a record identifier)
    • Lookup fields
    • Fields that contain raw data values like number or string.

Notepad.png

Note:
One common convention for numeric identifier fields is to end them with '#'.
That convention can make it easier to identify fields that can be used for a join.
For example: SocialSecurity# or Account#.

Rules for Joins

When creating joins, note that each join is dependent on the previously defined join.

For example, consider the case where we have three tables: A, B and C. In these joins, the first join row defines the possibilities for the second row:

A.id <join type> B.id
A.id <join type> C.id

or

A.id <join type> B.id
B.id <join type> C.id

where <join type> is Left, Right or Equi

Example Join

In this example, we have a two objects: instruments and players in a band. As you might expect, the players in the band might play multiple instruments, and multiple instruments may be available to the band members. As is true with many bands, this band needs a drummer, and some players do vocals only.

Object A

Object B

instrument_ID Instrument player_ID Players Can Play Instrument
IN1 Guitar P1 Bill Bertie IN1
IN2 Keyboard P1 Bill Bertie IN4
IN3 Drums P2 Madeline Morris IN2
IN4 Accordion P2 Madeline Morris IN4
P3 Sissy Smith IN2
P3 Sissy Smith IN4
P4 Kerry Kline IN1
P5 Crissy Clemens
P6 Gary Green
Left-Join

When objects A and B are related with a Left-Join, the report includes all records in object A, and all matching records in object B. Using the example above, the resulting report would include these records:

Left-Join Report

instrument_ID Instrument Played by
IN1 Guitar Bill Bertie

Kerry Kline

IN2 Keyboard Madeline Morris

Sissy Smith

IN3 Drums
IN4 Accordion Madeline Morris

Sissy Smith

Right-Join

When objects A and B are related with a Right-Join, the report includes all records in object B, and all matching records in object A. Using the example above, the resulting report would include these records:

Right-Join Report

instrument_ID Instrument Played by
IN1 Guitar Bill Bertie

Kerry Kline

IN2 Keyboard Madeline Morris

Sissy Smith

IN4 Accordion Madeline Morris

Sissy Smith

Crissy Clemens
Gary Green
Equi-Join

When objects A and B are related with an Equi-Join, the report includes all records where a record in object B is related to a record in object A. Using the example above, the resulting report would include these records:

Equi-Join Report

instrument_ID Instrument Played by
IN1 Guitar Bill Bertie

Kerry Kline

IN2 Keyboard Madeline Morris

Sissy Smith

IN4 Accordion Madeline Morris

Sissy Smith

Create New Reports Using a Database View

To create a new report using a Database View

  1. Create a Database View that includes the Related Objects of interest
  2. From the application, click the Reports tab.
  3. Click [Add Report]
  4. In the Category tab, choose the Database View
    (Database Views are shown along with the standard single-object report categories.)
  5. The field list for the report includes all fields available in the objects that part of the Database View.
Learn more: Add or Edit a Report

Delete a Database View

To delete a Database View:

  1. Click GearIcon.png > Customization > Database Views
  2. Click the Delete link next to the Database View to delete it

Considerations

  • In order to delete a Database View, you must first delete any report that uses that category; If a Database View is used in any report, and you attempt to delete the Database View, you will get an error message
  • The report(s) must be deleted first, then the Database View can be deleted
  • If a Database View is used in a report and you attempt to delete the Database View, you will get an error message