Wednesday Nov 30, 2011

Simulating Date Picker in ADF Desktop Integration

About

ADF Desktop Integration doesn't have built-in Date-Picker like component to choose a date from the calendar instead of entering it manually as in ADF Faces.

In this Article, I would like to discuss how an ADF Input Text component can be made use as a Calendar component using the power of excel macros.

Usecase

Let us take a simple case of Employee form with Hiredate field as date-picker

Solution

Assumption: An ADF Web Application with ADF Desktop Integration enabled workbook having Employee form as in below screenshot is readily available

Form_DT.PNG
Now, Our goal is to make the InputText component bound to Hiredate as Date-Picker

We can achieve this either by using Microsoft's ActiveX Calendar Control components or by building our own calendar component using macros.

The disadvantage of using Microsoft's ActiveX Calendar Control component is that we need to  register MSCAL.OCX or MSCOMCT2.OCX with every machine we are going to access this workbook and in real-time it would be difficult to maintain the control everywhere.

Here, I discuss the second approach and for which I made use of the macro code given by VBA Express to build calendar.

Procedure

-  Open Microsoft Visual Basic Editor by clicking on 'View Code' button under 'Developer' Tab

-  Insert a new user form using Insert -> UserForm option and change the name of the form as CalendarFrm

Insert_Form.png

- Design the form as in below screenshot and name First two combo boxes as CB_Mth and CB_Yr, Sun to Sat from Label2 to Label8 and remaining Day cells from D1 to D42 (Easiest way of designing the form is to download the workbook attached at the end and copy the form to your excel workbook so that the layout and names everything gets copied)

Calendar_Form.png
- Right click on the Form in Project Explorer and choose ViewCode option

Calendar_Form_ViewCode.png
- Copy below code to form to add calendar functionality

Option Explicit
Dim ThisDay As Date
Dim ThisYear, ThisMth As Date
Dim CreateCal As Boolean
Dim i As Integer

Private Sub UserForm_Initialize()
    Application.EnableEvents = False
     'starts the form on todays date
    ThisDay = Date
    
    ThisMth = Format(ThisDay, "mm")
    ThisYear = Format(ThisDay, "yyyy")
    For i = 1 To 12
        CB_Mth.AddItem Format(DateSerial(Year(Date), Month(Date) + i, 0), "mmmm")
    Next
    CB_Mth.ListIndex = Format(Date, "mm") - Format(Date, "mm")
    For i = -20 To 50
        If i = 1 Then CB_Yr.AddItem Format((ThisDay), "yyyy") Else CB_Yr.AddItem _
        Format((DateAdd("yyyy", (i - 1), ThisDay)), "yyyy")
    Next
    CB_Yr.ListIndex = 21
     'Builds the calendar with todays date
    CreateCal = True
    Call Build_Calendar
    Application.EnableEvents = True
End Sub

Private Sub CB_Mth_Change()
     'rebuilds the calendar when the month is changed by the user
    Build_Calendar
End Sub
Private Sub CB_Yr_Change()
     'rebuilds the calendar when the year is changed by the user
    Build_Calendar
End Sub
Private Sub Build_Calendar()
     'the routine that actually builds the calendar each time
    If CreateCal = True Then
        CalendarFrm.Caption = " " & CB_Mth.Value & " " & CB_Yr.Value
        For i = 1 To 42
            If i < Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
                Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
                Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
            ElseIf i >= Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
                Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) _
                & "/1/" & (CB_Yr.Value))), ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
                Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
            End If
            If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
            ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "mmmm") = ((CB_Mth.Value)) Then
                If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H80000018 '&H80000010
                Controls("D" & (i)).Font.Bold = True
                If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy") = Format(ThisDay, "m/d/yy") Then Controls("D" & (i)).SetFocus
            Else
                If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H8000000F
                Controls("D" & (i)).Font.Bold = False
            End If
        Next
    End If
End Sub
Private Sub D1_Click()
     'this sub and the ones following represent the buttons for days on the form
     'retrieves the current value of the individual controltiptext and
     'places it in the active cell
    ActiveCell.Value = D1.ControlTipText
    Unload Me
     'after unload you can call a different userform to continue data entry
     'uncomment this line and add a userform named UserForm2
     'Userform2.Show
     
End Sub
Private Sub D2_Click()
    ActiveCell.Value = D2.ControlTipText
    Unload Me
     
End Sub
Private Sub D3_Click()
    ActiveCell.Value = D3.ControlTipText
    Unload Me
     
End Sub
Private Sub D4_Click()
    ActiveCell.Value = D4.ControlTipText
    Unload Me
     
End Sub
Private Sub D5_Click()
    ActiveCell.Value = D5.ControlTipText
    Unload Me
     
End Sub
Private Sub D6_Click()
    ActiveCell.Value = D6.ControlTipText
    Unload Me
     
End Sub
Private Sub D7_Click()
    ActiveCell.Value = D7.ControlTipText
    Unload Me
     
End Sub
Private Sub D8_Click()
    ActiveCell.Value = D8.ControlTipText
    Unload Me
     
End Sub
Private Sub D9_Click()
    ActiveCell.Value = D9.ControlTipText
    Unload Me
     
End Sub
Private Sub D10_Click()
    ActiveCell.Value = D10.ControlTipText
    Unload Me
     
End Sub
Private Sub D11_Click()
    ActiveCell.Value = D11.ControlTipText
    Unload Me
     
End Sub
Private Sub D12_Click()
    ActiveCell.Value = D12.ControlTipText
    Unload Me
     
End Sub
Private Sub D13_Click()
    ActiveCell.Value = D13.ControlTipText
    Unload Me
     
End Sub
Private Sub D14_Click()
    ActiveCell.Value = D14.ControlTipText
    Unload Me
     
End Sub
Private Sub D15_Click()
    ActiveCell.Value = D15.ControlTipText
    Unload Me
     
End Sub
Private Sub D16_Click()
    ActiveCell.Value = D16.ControlTipText
    Unload Me
     
End Sub
Private Sub D17_Click()
    ActiveCell.Value = D17.ControlTipText
    Unload Me
     
End Sub
Private Sub D18_Click()
    ActiveCell.Value = D18.ControlTipText
    Unload Me
     
End Sub
Private Sub D19_Click()
    ActiveCell.Value = D19.ControlTipText
    Unload Me
     
End Sub
Private Sub D20_Click()
    ActiveCell.Value = D20.ControlTipText
    Unload Me
     
End Sub
Private Sub D21_Click()
    ActiveCell.Value = D21.ControlTipText
    Unload Me
     
End Sub
Private Sub D22_Click()
    ActiveCell.Value = D22.ControlTipText
    Unload Me
     
End Sub
Private Sub D23_Click()
    ActiveCell.Value = D23.ControlTipText
    Unload Me
     
End Sub
Private Sub D24_Click()
    ActiveCell.Value = D24.ControlTipText
    Unload Me
     
End Sub
Private Sub D25_Click()
    ActiveCell.Value = D25.ControlTipText
    Unload Me
     
End Sub
Private Sub D26_Click()
    ActiveCell.Value = D26.ControlTipText
    Unload Me
     
End Sub
Private Sub D27_Click()
    ActiveCell.Value = D27.ControlTipText
    Unload Me
     
End Sub
Private Sub D28_Click()
    ActiveCell.Value = D28.ControlTipText
    Unload Me
     
End Sub
Private Sub D29_Click()
    ActiveCell.Value = D29.ControlTipText
    Unload Me
     
End Sub
Private Sub D30_Click()
    ActiveCell.Value = D30.ControlTipText
    Unload Me
     
End Sub
Private Sub D31_Click()
    ActiveCell.Value = D31.ControlTipText
    Unload Me
     
End Sub
Private Sub D32_Click()
    ActiveCell.Value = D32.ControlTipText
    Unload Me
     
End Sub
Private Sub D33_Click()
    ActiveCell.Value = D33.ControlTipText
    Unload Me
     
End Sub
Private Sub D34_Click()
    ActiveCell.Value = D34.ControlTipText
    Unload Me
     
End Sub
Private Sub D35_Click()
    ActiveCell.Value = D35.ControlTipText
    Unload Me
     
End Sub
Private Sub D36_Click()
    ActiveCell.Value = D36.ControlTipText
    Unload Me
     
End Sub
Private Sub D37_Click()
    ActiveCell.Value = D37.ControlTipText
    Unload Me
     
End Sub
Private Sub D38_Click()
    ActiveCell.Value = D38.ControlTipText
    Unload Me
     
End Sub
Private Sub D39_Click()
    ActiveCell.Value = D39.ControlTipText
    Unload Me
     
End Sub
Private Sub D40_Click()
    ActiveCell.Value = D40.ControlTipText
    Unload Me
     
End Sub
Private Sub D41_Click()
    ActiveCell.Value = D41.ControlTipText
    Unload Me
     
End Sub
Private Sub D42_Click()
    ActiveCell.Value = D42.ControlTipText
    Unload Me
     
End Sub
Please note that this step doesn't have anything specific to ADF Desktop Integration it is purely excel macro so not explaining much on the code part

- Finally, add below code to the Worksheet _BeforeDoubleClick event of the sheet having employee form to invoke Calendar on double-clicking on Hiredate

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        ' $D$7 is the cell containing Hiredate
        If ActiveCell.Address = "$D$7" Then
            CalendarFrm.Show
        End If
End Sub
- Save the changes and Run the design time of workbook

Run_DT.png
- Navigate to first record, double-click on Hiredate field and notice date-picker dialog and date selected will be shown back to Hiredate field


Form_Calendar_RT.png


Sample design-time workbook developed in 11.1.2.0.0 can be downloaded Here (Right-Click on the link and choose "Save Link As..." option to download the workbook)


Monday Nov 14, 2011

Combo LOV - How to display Description for Selected Value instead of ID

Problem

The common problem that any one working with Combo LOV in a faces page faces is that the list of values component shows Id for selected value inspite of the dropdown showing Description for Options.
Unfortunately, this is a known issue with ADF faces for which there is already an enhancement request which we would expect to see in future releases.

Solution

In this article, I discuss a way to overcome this and display Description rather than Id.
In simple words, The workaround is to define LOV on a transient attribute instead of a View Attribute(Probably a Foreign Key attribute) and set the selected value back to View attribute on change of the LOV and make use of this transient attribute instead of View attribute wherever Combo LOV is needed.

Usecase

I take a simple usecase with well known Emp and Dept tables to display an Emp form with Deptno Combo box List of Values with selected value of LOV being the Department name instead of Deptno
Combo_LOV.png

Here are the detailed steps for the same:

- Add a transient attribute to EmpView with the name "Dname"
Transient_Dname_Attr.png

- Define Combo LOV matching Dname and Deptno both (Deptno matching helps in setting value back to Deptno view attribute on change of Dname LOV value)
- Keep a note of List Data Source name(i.e, DeptViewAccessor in screenshot)
Dname_Combo_LOV.png

- Finally, Set the Department name of respective Deptno as default value to transient attribute(To show current department as selected) using below groovy expression:
Dname_Groovy.png



Now, when you run the page you should be able to see the combo lov showing Department name for the selected value and change in LOV automatically updates Deptno attribute.

Incase, anyone don't like to have groovy, they can overwrite getDname method in RowImpl and add this code there.

Friday Sep 16, 2011

Contextual events with EJB DC

This article describes contextual events with EJB Datacontrol

Use Case Description

Let us consider a scenario wherein the user wants to display deptno list & commandButton in region1 & region2 displays the Emp records corresponding to selected deptno in region1.We will try to achieve this using contextual events.

When the user selects deptno & clicks on the commandButton ,a contextual event with payLoad parameter is broadcasted by taskflow1.This event is then consumed by the  taskflow2 and the handleEvent() handler passes payLoad parameter(i.e deptno) to sessionBean.SessionBean has findByDeptno() method that returns the Emp records & taskflow2 displays the respective Emp records .

Implementation steps

Let us suppose that we have created Java EE Web Application with Entities from Emp & Dept table
.Also add 'ADF Faces Components 11' under ViewController->project properties-> JSP Tag Libraries.

Edit Emp.java & create a custom named query that returns Emp Object for the respective deptno :


 @NamedQuery(name = "findEmpByDeptno", query = "select o from Emp o
 where o.dept.deptno = :deptNo")
Let us create Stateful Session Bean and expose the Named Query through the Session Facade.Create a contextual handle event that passes deptno as payload parameter:   

 public void handleEvent(Object payload) 
    {
           this.setDepartNo((BigDecimal)payload);
    }
Modify findEmpByDeptno namedQuery as below:

 public List<Emp> findEmpByDeptno() 
{
 
 return em.createNamedQuery("findEmpByDeptno").setParameter
 ("deptNo",  this.getDepartNo()).getResultList();
}

Generate Data control for the above Session Bean & expose the above method through the Session Facade.

Create a ADF taskflow1 with page Fragments & drop  view activity(DeptView) on that.In the same taskflow drop Execute operation((findByDeptno()->Emp->Operations->Execute).Define control flow case 'execute' from DeptView to Execute operation in taskflow.

Drop deptno as selectOneChoice & commandButton 'showEmpRecords' on the DeptView page Fragment page.Set showEmpRecords action property  as 'execute'

In its page definition create deptno attribute value that points to deptFindAllIterator &  handler event method action binding to handle the event:

handleEvtAction.JPG
              
Select commandButton 'showEmpRecords' & create new contextual event on that:


contextEvent.JPG

Now we will create new Event subscriber in its contextual events binding editor tab:

subscribeEvt.JPG

Create taskflow2 that displays Emp table(findByDeptno()->Emp) & create main.jspx page,drop both the taskflows as region

Run the page & now if user selects deptno,table below shows Emp records corresponding to the selected deptno.


Thursday Jun 30, 2011

Using Groovy Aggregate Functions in ADF BC

This article explains how groovy aggregate functions(sum, count, min, max and avg) can be used in ADF Business components and demonstrates how these can be used at entity and view level

Let's consider EMP and DEPT tables and an usecase to track number of employees in each department

 

AGGREGATE_COUNT_FN_RT_NEW.png

Entity-Level

To use aggregate functions at entity level, we need to have association between entities representing master and child relationship and the destination accessor name is what we are going to use in our groovy

Syntax: <Accessor>.count(Groovyexpression)

- Note down the destination accessor name(EMP) in the association or AccessorAttribute name in source entity

DEPT_EMP_ASSOC_NEW.PNG

- Add a transient attribute in source entity with persistent property set to false and provide the groovy expression in the syntax provided above

ENTITY_TRANS_ATTR.PNG

- Finally, Add newly added attribute to view object

View-Level

To use aggregate functions at view level, we need to have a view link between viewobjects representing master and child relationship and the destination accessor name is what we are going to use in our groovy

Syntax: <ViewLinkAccessor>.count(Groovyexpression)

- Note down the destination accessor name(EmpView) in the view link or viewLinkAccessor name in source view

DEPT_EMP_VIEW_LINK.png
- Add a transient attribute in view object and provide a groovy aggregate function count as a value to it in the syntax provided above

VIEW_TRANS_ATTR_NEW.png

Now, If you run application module tester and execute DeptView / ViewLink, you should see employee count in EmpCount field 

In similar way, one can use other groovy aggregate functions sum, avg, min and max.

Highlighting new rows in ADF Table

About

This article explains how to hightlight newly inserted rows in an ADF Table without writing any extra java/javascript code.

Introduction

Sometimes we may wish to give more clarification to the end user by differentiating between newly inserted rows and the existing rows(i.e the rows from DB) in a table by highlighting new rows in different color as in the figure shown below.

HIGHLIGHT_ROW_COLOR.JPG
Solution

We can achieve the same by giving following EL to inlineStyle property of every column inside af:table:

#{row.row.entities[0].entityState == 0?'background-color:#307D7E;':''}
Explanation

Here is the explanation for row.row.entities[0].entityState given inside EL which returns the state of the row(i.e, New, Modified, Unmodified, Initialized etc.)

row - Refers to a tree node binding(instance of FacesCtrlHierNodeBinding) at runtime
row.row - Refers to an instance of row that the tree node is based on
row.row.entities[0] - Gets the Entity row at zeroth index. In most of the cases, the table will be based on single entity. If your table is based on multiple entities then the index needs to be given accordingly.
row.row.entities[0].entityState - Gets Entity Object's current Entity-state in the transaction.
(0 - New, Modified - 2, Unmodified - 1, Initialized - -1,  etc.,)
About

Tips and Tricks from Oracle's JDeveloper & ADF QA

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today