Page History

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:

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 

   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

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 

   
   

   1. Expand
      title Click for selected attributes in 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: 

    Code Block
    language sql title Simple Select statement with ordering by a column collapse true
    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. Expand
   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:

      Expand
      title Click to see 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
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: 

      Expand
      title Click to see 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

7. Example of generated code: 

   Code Block
   language sql title Generated Code collapse true
   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 

   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

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 

      Expand
      title Click here to see Right Outer Join for related objects
      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

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: 

    Code Block
    language sql title Left Join on relation collapse true
    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 

      Expand
      title Finding the max age in patients Business Object...

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 

      Property	Value
      Type	Compare
      Expression 1	Function : Max, patient.age
      Operation	Greater
      Constant	14

   2. Expand

   3. Example of Generated Code 

      Code Block
      language sql title Group by and Having example collapse true
      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