Tips & Tricks from Arun on JDev ADF, Forms, SQL & PL/SQL.

Friday Mar 02, 2012

When using ADF Desktop Integration, quite frequently, we get into a
situation where we would like to refresh one column based on the value
of another column. In ADF Faces, we can achieve this by setting the
autoSubmit property and partialTriggers property for the corresponding
columns.

However, in ADFdi, we do not have such option. Though we
can achieve this by using LOVs and Dependent LOVs. But, in some
scenarios we would like to achieve this when using an Input Text
Component.

In this article, we will simulate this Auto Refresh functionality in a ADFdi Table.

Note : Since we would be using VBA code to achieve this, we can use this only on the Macro Enabled Excel Workbooks.

Let
us assume that we have a View Object based on the Emp table. We could
take an example of having a transient attribute in the VO, that gives
the sum of Salary and Commission attributes.

In
the above example, we've added a new transient attribute (SalPlusComm)
to the EmpVO, that would give the sum of Sal and Comm attributes. Since
we need this attribute to get refreshed when either Sal or Comm
attribute changes, we set the Sal and Comm attributes as Dependencies.
Also, we set the AutoSubmit property (under UI Hints tab) for the Sal
and Comm attributes.

Now,
we are done with the model layer. We can now, create a jspx page and
then Drag and Drop EmpView as ADF Table. After this, we create an Excel
Workbook (macro enabled), enable ADF Desktop Integration for it, set the
required Workbook Properties, and then add a Table based on the
EmpView.

As
there are no straight forward way in ADFdi to trigger a request to
server when a value of a cell is changed, we will now add a
DoubleClickActionSet for the Sal and Comm columns. This
DoubleClickActionSet will have the Table.RowUpSync and Table.RowDownSync
actions.

Above
example image shows the DoubleClickActionSet for Sal column. In the
same manner, we need to add the DoubleClickActionSet for the Comm column
as well.

Now, we have the workbook, that would fetch the
SalPlusComm attribute (after recalculation in the model), when we change
the Sal / Comm attribute and then double click on that column. To do
this automatically when the user tabs out / presses enter key on the
cells, we'll write a bit of VBA Code on the Worksheet where we've this
table (Go to Developer Tab and Click on Visual Basic).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Or Target.Column = 15 Then
Target.Select
Application.CommandBars("Cell").Controls("Invoke Action...").Execute
End If
End Sub

Excel
would trigger Worksheet_Change event when a cell in the worksheet is
modified. So, we would code our logic in that event. The above code
assumes that Sal column is present in the N (14th) column on the
worksheet and Comm column is present in O (15th) column. So, we would
execute our logic only when the contents in these two columns change.

ADFdi
would provide a context menu (Invoke Action...) when a
DoubleClickActionSet is added to a particular column. We'll make use of
that context menu and invoke it programatically.

We invoke that context menu programatically using the following line of code

Application.CommandBars("Cell").Controls("Invoke Action...").Execute

Now, we run our workbook, modify the value of Sal column
for any row and tab out of that field would automatically update the
value of SalPlusComm column.

Here,
a simple example (Transient Attribute) is taken for the explanation. In
the similar fashion, we can also have a DoubleClickActionSet to contain
a method in the Impl that would perform this calculation as well.

Tip: If you are not able to view the image fully, right click on the image and choose View Image option to see it completely.