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:

PropertyDescription
GeneralAside from the basic information, you'll have to make a decision whether to deploy the View as a database element or a function.
ObjectsAll of the Busines Objects that are related...
FieldsAll the columns associated with the view
WhereClause that filters the retrieved columns
Sort ByUsed to sort view by a specific column(s)
Group ByUsed to group columns and perform aggregate function on other columns. For example finding the total number of employees grouped by position
HavingUsed 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 

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

  4. Enter the following properties as follows 

    PropertyDescription
    Query NameEnter a meaningful name.
    API Permanent IDThis field is automatically pre populated and needs to be unique across all Views in your solution.
    Primary ObjectThis will be the default object when selecting attributes of the business object
    DescriptionEnter a meaningful description. This description should explain what the Views is displaying
  5. After saving, the page view builder page will load
  6. Click on the Field tab.
    1. Notice that Field area is populated with the business object attributes

  7. Select the desired attributes and click on the move button   to include in the view 


  8. Click on the Sort By field. Add a column to sort by using  
  9. Notice that select statement is generated. 

  10. Example of generated code: 

     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
  11. To finish click Save 
  12. 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

  1. Repeat steps 1-7 in "Create Simple select view for one Business Object"
  2. After selecting the attributes click on the Where tab
  3. Click on the Root where clause

  4. Can change

  5. 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:

      PropertyValue
      TypeIn
      Expression 1Field: Client.Id
      Values

      Number: {1, 3, 6}

      Expression 2n/a

    3. Click Save
  6. 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:

      PropertyValue
      TypeCompare
      Expression 1Field: Client.fname
      OperationEqual
      Expression 2Constant: String = John Doe

    3. Click Save

  7. Example of 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'));
  8. Click Save in the lower right hand side
  9. 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

  1. Create a new View using previous step
  2. In the Visual View builder, Click on the Objects tab

  3. Notice that there are three folders in the Object tab 

    PropertyDescription
    ObjectBusiness Objects created in the solution
    ViewViews created in the solution
    FunctionRules deployed as functions in the solution

    Note: For this example the objects folder will be used

  4. Choose object(s) by clicking on them and pressing the  Next button.
  5. To remove objects once they have been added click on  delete button
  6. In order to join the objects click on Edit button for table(s) you wish to join
    1. After clicking Edit button, the link properties screen will appear giving join conditions

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

      PropertyValueDescription
      Aliasdoctoralias for the right object (the object that was clicked to be joined)
      Left Objectpatientthe object being joined too
      Select All from LeftblankFor Left Outer Joins (to include unmatched columns from left table)
      Right ObjectdoctorThe object that was clicked to join
      Select All from RightCheckedFor Right Outer Joins (to include unmathced columns from right table)
      Join ConditionRelationCan use a predefined relation to join objects together

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

  10. Example of generated code: 

    SELECT A0.COL_ID ID
FROM TBL_PATIENT A0
LEFT JOIN TBL_DOCTOR A1
ON (A0.COL_DOCTORPATIENT = A1.COL_ID)
  11. 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

  1. Open Application Studio of your solution
  2. Create a new View using previous step
  3. Click on the Fields tab and add attributes from the primary object (chosen when creating the view)
  4. 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 

  5. Notice that the Group By and Having tabs have now become usable.
  6. 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 
    .

  7. Click on the Having tab
  8. Notice that the layout of the Having clause tab is the same as the Where clause.
  9. In this example, let us have Max(Age) > 14

    1. Click on Add Term and fill in the following information 

      PropertyValue
      TypeCompare
      Expression 1Function : Max, patient.age
      OperationGreater
      Constant14

    3. Example of Generated Code 

      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