Wednesday 8 June 2016

List of Values switcher example in ADF 12c

I would like to start with a little bit of history. A long time ago, while people were still modelling ERD diagrams, there was an interesting relation, that I'm used to call an "exclusive or" but in the various sources it can be called also "exclusive arc" or "exclusive bar". I'll use an image found using Google to picture the situation:    

Such notation implies that in the logical model, the MEMBERSHIP table contains one foreign key column that can point to CUSTOMERS table or (exclusively) to COMPANY table.

In the BC4J model layer there exist an ability to define list of values. Maybe you haven't noticed that one column can have assigned more than one list of values. Moreover, such an LOV model can be switched in the runtime.

In this post I'll focus on the middleware implementation, the database is just to provide background for the demo. To complicate more, the TYPE column won't be threated directly as switching attribute. It'll be used by a Groove expression on the VO level.

Definition for the database:    

-- schema creation
CREATE USER LOVSWITCHER IDENTIFIED BY lovswitcher;
GRANT  ALL PRIVILEGES TO LOVSWITCHER;

-- test structure and data
CREATE TABLE LOVSWITCHER.MAIN_TABLE ("ID" NUMBER NOT NULL ENABLE, "TYPE" VARCHAR2(100 CHAR), "ONE_KEY_COLUMN" NUMBER, CONSTRAINT "MAIN_TABLE_PK" PRIMARY KEY ("ID"));
CREATE TABLE LOVSWITCHER.DETAIL_TABLE1 ("ID" NUMBER NOT NULL ENABLE, "LABEL" VARCHAR2(100 CHAR), CONSTRAINT "DETAIL_TABLE1_PK" PRIMARY KEY ("ID"));
CREATE TABLE LOVSWITCHER.DETAIL_TABLE2 ("ID" NUMBER NOT NULL ENABLE, "LABEL" VARCHAR2(100 CHAR), CONSTRAINT "DETAIL_TABLE2_PK" PRIMARY KEY ("ID"));
INSERT INTO LOVSWITCHER.DETAIL_TABLE1 (ID, LABEL) VALUES (1, 'tab 1 label 1');
INSERT INTO LOVSWITCHER.DETAIL_TABLE1 (ID, LABEL) VALUES (2, 'tab 1 label 2');
INSERT INTO LOVSWITCHER.DETAIL_TABLE1 (ID, LABEL) VALUES (3, 'tab 1 label 3');
INSERT INTO LOVSWITCHER.DETAIL_TABLE2 (ID, LABEL) VALUES (1, 'tab 2 label 1');
INSERT INTO LOVSWITCHER.DETAIL_TABLE2 (ID, LABEL) VALUES (2, 'tab 2 label 2');
INSERT INTO LOVSWITCHER.DETAIL_TABLE2 (ID, LABEL) VALUES (3, 'tab 2 label 3');
COMMIT;

I'll create an ADF Fusion Middleware application with Model created with "ADF Business Components from Tables" wizard. There is one updateable view object based on MAIN_TABLE and two dictionaries will be just for read only purposes.


In the MainTableView view object please add read only DetailTable view accessors as following. After that please go to the Attributes tab and select OneKeyColumn. There, on the List of Values tab please assign two list of values using green plus icon.


At the moment please notice few things. First of all there, one of the wizards is disabled. That's why please ensure in the view object source that the label is selected as a display attribute for the second one.

Each list of values has its own unique name. It is obvious that there must be just one default list of values.

This is the moment for add technical field to control which model of the list should be used. I'll use transient attribute based on a Groove expression. It'll be pointed as a List of Values Switcher. Key idea is that it must return the name of LOV, that's why it is String based. It is calculating return value using Type attribute (which is persisted in the database). And, because it is technical field, it won't be exposed in the DataControl (Display attribute set to Hide).


For the test page, please drag&drop MainTableView from the Data Control to the page. Please include navigation and commit buttons. The Type input field must submit its value after each change (autoSubmit set to true) and the OneKeyColumn must refresh itself after such change (partialTrigger on the Type input field). Because the SQL script doesn't contain test data, please also drag&drop CreateWithParams operation. Quick trick for setting the primary key with the row count value is used in this example.


And please take a look on the list of values model after setting Type attribute with abc and not-abc string value.