Views

One of the ways to retrieve a list of data from Business Objects is through Views. Using a visual designer, you can create complex queries that collect information from different Business Objects under different filtering criteria. You can use Views to display data in your pages or reference them in Rules. The advantage of using Views is that you can define a complex query once and reuse it as well as make additional queries against that View.

The Views are structured the same way an SQL Select statement is with the following components:

Property	Description
General	Aside from the basic information, you'll have to make a decision whether to deploy the View as a database element or a function.
Objects	All of the Busines Objects that are related...
Fields	All the columns associated with the view
Where	Clause that filters the retrieved columns
Sort By	Used to sort view by a specific column(s)
Group By	Used to group columns and perform aggregate function on other columns. For example finding the total number of employees grouped by position
Having	Used in conjunction with the Group By statement. Used to Filter the result statement. For example finding number of employees grouped by position having position not equal to 'QA'.

NOTE: For naming issues please refer AppBase Black List of Names.

Create a View with simple SELECT statement and SORT BY clause

This section covers how to create a view that selects attributes from one business object

Open Application Studio of your solution
From the left menu, select Data Management ->Views
Click on New View

Enter the following properties as follows

Property	Description
Query Name	Enter a meaningful name.
API Permanent ID	This field is automatically pre populated and needs to be unique across all Views in your solution.
Primary Object	This will be the default object when selecting attributes of the business object
Description	Enter a meaningful description. This description should explain what the Views is displaying

After saving, the page view builder page will load
Click on the Field tab.
1. Notice that Field area is populated with the business object attributes
Select the desired attributes and click on the move button to include in the view
Click on the Sort By field. Add a column to sort by using
Notice that select statement is generated.

Example of generated code:

Simple Select statement with ordering by a column

 SELECT A0.COL_ID ID,
  A0.COL_LNAME CLIENTLNAME,
  A0.COL_FNAME CLIENTFNAME,
  A0.COL_CLIENTID CLIENTCLIENTID
From Tbl_Client A0
ORDER BY A0.COL_ID ASC

To finish click Save
To verify the result of the view, go to the view detail screen and click debug button then run the debugger

Create View using simple SELECT statement with WHERE clause

Repeat steps 1-7 in "Create Simple select view for one Business Object"
After selecting the attributes click on the Where tab
Click on the Root where clause
Can change
Click on Add Term for the Root Add (or Root Or)
1. The right side of the view builder creates an clause builder.
2. An example of creating an in clause:
  In this example:
  Property Value
  Type In
  Expression 1 Field: Client.Id
  Values
  Number: {1, 3, 6}
  Expression 2 n/a
3. Click Save
Add another term to the Root Add (or Root Or)
1. The right side of the view builder creates an clause builder.
2. An example of creating a Compare clause:
  In this example:
  Property Value
  Type Compare
  Expression 1 Field: Client.fname
  Operation Equal
  Expression 2 Constant: String = John Doe
3. Click Save

Example of generated code:

Generated Code

SELECT A0.COL_ID ID,
  A0.COL_ID CLIENTID,
  A0.COL_FNAME CLIENTFNAME,
  A0.COL_LNAME CLIENTLNAME
FROM TBL_CLIENT A0
Where ((A0.Col_Id In (1, 3, 6)
AND A0.COL_FNAME    = 'John Doe'));

Click Save in the lower right hand side
To verify the result of the view, go to the view detail screen and click debug button then run the debugger

Create a View using Join

Create a new View using previous step
In the Visual View builder, Click on the Objects tab
Notice that there are three folders in the Object tab
Property Description
Object Business Objects created in the solution
View Views created in the solution
Function Rules deployed as functions in the solution
Note: For this example the objects folder will be used
Choose object(s) by clicking on them and pressing the Next button.
To remove objects once they have been added click on delete button

In order to join the objects click on

Edit button for table(s) you wish to join

After clicking Edit button, the link properties screen will appear giving join conditions

Below is an example using a Join with a relation

NOTE: by default the object that is clicked to be edited will be the Right Object

Property	Value	Description
Alias	doctor	alias for the right object (the object that was clicked to be joined)
Left Object	patient	the object being joined too
Select All from Left	blank	For Left Outer Joins (to include unmatched columns from left table)
Right Object	doctor	The object that was clicked to join
Select All from Right	Checked	For Right Outer Joins (to include unmathced columns from right table)
Join Condition	Relation	Can use a predefined relation to join objects together

Click on the Fields tab
1. Notice: The Fields sectin contains attributes from the objects we added in the Object section.
Choose the appropriate fields
Click Save in the lower right hand side to save the view

Example of generated code:

Left Join on relation

SELECT A0.COL_ID ID
FROM TBL_PATIENT A0
LEFT JOIN TBL_DOCTOR A1
ON (A0.COL_DOCTORPATIENT = A1.COL_ID)

To verify the result of the view, go to the view detail screen and click debug button then run the debugger

Creating a View with Group By statement and Having clause

Note: The having clause is used with conjunction with the group by clause

Open Application Studio of your solution
Create a new View using previous step
Click on the Fields tab and add attributes from the primary object (chosen when creating the view)
In the selected fields section click on edit button for one the attributes
1. After clicking edit button the Linked field properties will pop up giving varies group by conditions
2. Below is an example of using field properties of finding the max of a patient
Notice that the Group By and Having tabs have now become usable.
Click on the Group By tab Notice that by default the view builder automatically add the required fields that MUST be involved in the Group By statement. To add any other fields to group by choose the fields in the left side and click on
.
Click on the Having tab
Notice that the layout of the Having clause tab is the same as the Where clause.
In this example, let us have Max(Age) > 14
1. Click on Add Term and fill in the following information
  Property Value
  Type Compare
  Expression 1 Function : Max, patient.age
  Operation Greater
  Constant 14
3. Example of Generated Code
  Group by and Having example Expand source
```
SELECT A0.COL_ID ID,
  A0.COL_NAME PATIENTNAME,
  MAX(A0.COL_AGE) PATIENTAGE
FROM TBL_PATIENT A0
GROUP BY A0.COL_ID,
  A0.COL_NAME
```

Using the above examples, user can generate complicated views and have a visual reference

Property	Value
Type	In
Expression 1	Field: Client.Id
Values	Number: {1, 3, 6}
Expression 2	n/a

Property	Value
Type	Compare
Expression 1	Field: Client.fname
Operation	Equal
Expression 2	Constant: String = John Doe

Property	Description
Object	Business Objects created in the solution
View	Views created in the solution
Function	Rules deployed as functions in the solution

Child pages

Views

Create a View with simple SELECT statement and SORT BY clause

Create View using simple SELECT statement with WHERE clause

Create a View using Join

Creating a View with Group By statement and Having clause