JDev 12c: Change Label depending on Data in Field

A question on OTN forum JDev & ADF caught my attention. A user ask how to change the label of a field in an af:query depending on data entered in another field of the af:query.

This is an interesting problem as it can be used in other use cases, e.g. in forms, too.

Use case

Before going into detail on how this is implemented, let’s look at the use case in detail. Starting with a normal af:query component showing a search form for locations

We want to change the label of the ‘State’ field depending on the selected Value of the ‘CountryId’ field. The page is simply created by dragging the named criteria ‘All Queryable Attributes’ onto the page as ‘Query with Table’.

To make the UI more interesting we use an af:selectOneChoice to select the country. Depending on the selected country we like to show different labels for the ‘State’ field. If we select the ‘United States of America’ as country, the label should show ‘US States’, if we select ‘Germany’ we want to see ‘Bundesland’ and for Switzerland we want to show ‘Kanton’. For the remaining countries we show ‘State’.

Here we see that the label changed to ‘Kanton’ for the country Switzerland. Selecting the USA will change the label to ‘US State’

Implementation

To implement this we only need to add some groovy script to the model project. To be precise we add groovy to the attribute label of the view which is used in the UI for the af:query.

Adding the groovy to the view will guarantee that the UI always shows the effect. In the sample (download instructions below) you’ll find a second page where the view is dropped as a af:form. Running this page you’ll see the same effect.

OK, let’s have a look at the groovy script

if (CountryId == 'US') {
  return 'US State';
} else if (CountryId == 'DE') {
  return 'Bundesland';
} else if (CountryId == 'CH') {
  return 'Kanton';
} else if (CountryId != null) {
  return CountryId + ' State';
} else {
  return 'State';
}

The script checks for specific countries and depending on which country is currently selected it return a special label. For country ‘DE’ it return ‘Bundesland’, for country ‘CH’ it returns ‘Kanton’. For other countries we return the country code we get from the country attribute and add ‘State’ to it. A special case we have to handle is if country is null or empty. In this case we return ‘State’.

Below we see that we add the script to the attributes ‘UI Hint’ tab as ‘Label’. This is only possible in 12c, so if you are using an earlier version, you have to use java code to get the same result.

This is all we have to do. The sample which is build using JDev 12.2.1.2.0 can be downloaded from BlogChangeQueryLabel. The sample uses the HR DB schema.

The power of calculated fields in ADFbc

Lately I saw a couple of posts on the OTN JDev & ADF forum where users tried to add redundant data into their data model and store it to the DB table. One common use case here is to have the result of a calculation as an attribute of a table.

In general you should be very careful when doing this. This is error prone and will you get into trouble almost every time. If you do add an attribute for such a calculation to a table in the DB, you have to think of the integrity of the data. Let’s look into the use case and the integrity problem.

Use Case

We have a table in the DB which holds start and end for multiple data types like integer, data and timestamp:

We use the different start and end attributes to calculate the difference between start and end.

We do have the option to add attributes to the table and calculate the difference using a trigger in the DB each time the data is inserted or updated. Problem here is that the user will see the result only after the insert or update is done. For web pages this isn’t a good design.

Another option is to add the fields but do the calculation in the business component layer in ADFbc and store them in the DB together with all other changes done to the data. The your see the calculation, but other applications won’t see them until you store the record.

Problem with storing redundant data in a DB table

Both options have one flaw. When you store the result of a calculation in the DB, what happens if someone, person or program, changes one of the attributes used in the calculation?

Assume STARTINT is set to 5, ENDINT is set to 10. The result of the calculation is 5. This result we store in an attribute in the DB table. Now a bad programmer who does not know about the calculation, changes the ENDINT to 15 and commits the change.

When the other program looks at the data again the data is inconsistent. Which of the values is correct? The result? The STARTINT value? The ENDINT value? Or is the calculation simply wrong?

In this simple use case it’s fairly easy to find the problem. In more complex use cases where other workflows depend on the numbers it’s not as easy.

This leads to the solution shown in this post: don’t store results of calculations in the DB if possible. Do the calculation when they are  needed.

There are cases where storing the result would be the better way to archive the whole use case, but this has to be decided on the use case and weighted against the complications. Most simple use cases don’t need to store the results and should not.

The remainder of this post we see how to implement such calculated fields using ADFbc.

Implementing calculated fields in ADFbc using Groovy

We start with creating a new Fusion Web Application and building the ‘ADF Business Components from a Table’. The sql script to create the table is

CREATE TABLE "HR"."CALCULATION"
 ( "ID" NUMBER(*,0) NOT NULL ENABLE,
 "STARTINT" NUMBER(*,0),
 "ENDINT" NUMBER(*,0),
 "STARTTIME" DATE,
 "ENDTIME" DATE,
 "STARTTIMESTAMP" TIMESTAMP (6),
 "ENDTIMESTAMP" TIMESTAMP (6),
 CONSTRAINT "CALCULATION_PK" PRIMARY KEY ("ID")
 );

REM INSERTING into CALCULATION
 SET DEFINE OFF;
 Insert into CALCULATION (ID,STARTINT,ENDINT,STARTTIME,ENDTIME,STARTTIMESTAMP,ENDTIMESTAMP) values ('1','1',null,to_timestamp('24-DEZ-15','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('26-DEZ-15','DD-MON-RR HH.MI.SSXFF AM'),null,null);
 Insert into CALCULATION (ID,STARTINT,ENDINT,STARTTIME,ENDTIME,STARTTIMESTAMP,ENDTIMESTAMP) values ('2','4','6',to_timestamp('31-DEZ-15','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('05-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),null,null);

We use the HR DB schema to add the table, but it can be added to any schema you want. The CALCULATION table consists of some start and end values of different types to later show how to work with them. To work with the table we add two records resulting in the following data

I don’t show the steps to create the basic application from the wizards as the application is available via the link GitHub base application.

Once you downloaded and unzipped the workspace you should see the base application as it will be created by following the wizard.

The first step is to create a transient field in the Calculation EO to hold the result of the calculation of the difference of STARTINT and ENDINT. The difference here  is, that we store the result in the EO as transient attribute which is not stored into the DB.

Add transient attribute and set the name to Durationint

Select expression and click the edit expression icon

edit the expression and select the attributes the calculation is dependent on

in the UI-Hints tab set the ‘autosubmit’ flag for Startint and Endint to ‘true’

Add the attribute to the VO from the EO

The real work is shown in the third image above ‘edit expression…’. Here we enter a Groovy expression to calculate the difference between STARTINT and ENDINT as

if (Endint == null) 
  {return 0} 
else 
  {return Endint-Startint}

The Groovy expression uses the attribute names from the EO not the ones from the DB table. First we check if the Endint is given, if not we return 0. If there is an Endint we return the (Endint-Startint).

We then add notifications to the calculated attribute whenever the attributes Startint or Endint change to recalculate the Durationint attribute (lower half of the dialog). Next we set the AutoSubmit  property of the Startint and Endint attributes to true to make sure we get the new values when we calculate the result.

Finally we add the new calculated attribute to the VO. We can now test the application module using the application module tester:

Initial state

Set Endint and get the calculated result

change Endint to get another result

We now add a index page to the View Controller project to add an UI to the application. We can just drag the CalculationView1 and drop is as an ADFForm with navigation and submit onto the page.

In the resulting form we set the Startint and Endint fields to autosubmit=’true’ to make sure the new values are submitted. As the Durationint field isn’t updateble we set it to read only.

Running the application will show you

The application in this state can be downloaded from GitHub (feature/calculated_int_field).

To show that this can be done with other data types we can use the other attributes of the table. As the way to do this is the same I spare to give detailed instructions. You can download the final application from GitHub (final).

All samples yre using the HR DB schema and table called CALCULATION. The needed SQL code to create the table and to insert data to the table is posted in here.