We use Oracle APEX for product development as it’s a great low-code cloud-based environment. We keep an eye on early adopter releases as they provide early preview into upcoming features. The new APEX 19.2 Early Adopter contains a bunch of new features, one that particularly appealed to us was the new Faceted Search. Faceted Search offers additional filters based on item classification within the data. The best way to see this is through an example.
I started by creating an instance on the early adopter site https://tryapexnow.com (Note please also refer to the notes from https://blogs.oracle.com/apex/oracle-apex-192-early-adopter-now-available which indicate that this website will likely disappear when the product goes GA and also consider that functionality can change).
Then I created some test data as follows. These are two simple tables called MYAUTHORS (containing my made-up book authors) and MYCATEGORIES (a list of sample book genres) complete with primary and foreign keys.
CREATE TABLE MyCategories ( Category_id NUMBER GENERATED ALWAYS AS IDENTITY, Category_Name varchar2(20) not null ); ALTER TABLE myCategories ADD ( CONSTRAINT myCategories_pk PRIMARY KEY (Category_id) ); insert into MyCategories(Category_Name) values ('SCIENCE FICTION'); insert into MyCategories(Category_Name) values ('ROMANCE'); insert into MyCategories(Category_Name) values ('MECHANICS'); CREATE TABLE MyAuthors ( Author_id NUMBER GENERATED ALWAYS AS IDENTITY, Author_First_Name varchar2(20) Not Null, Author_Last_Name varchar2(30) not Null, Age Number(2) NOT NULL, Sex varchar2(20) not null, Category_Id number not null, Earnings number(9,2) not null ); ALTER TABLE myAuthors ADD ( CONSTRAINT myAuthors_pk PRIMARY KEY (Author_id) ); ALTER TABLE myAuthors ADD ( CONSTRAINT Category_fk Foreign Key (Category_Id) REFERENCES MyCategories(Category_id) ); Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('MARY','CHRISTMAS',90,'F',1,90000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('DAVE','SMITH',58,'M',1,80000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('HELEN','KELL',27,'F',1,10000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('PHIL','MORGAN',48,'M',2,20000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('ELLIS','JONES',40,'M',3,21000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('SIMON','GULLIVER',19,'M',3,40000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('BETTY','CARTER',29,'F',3, 43221) ------ Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('ALICE','Jones',29,'F',3,23000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('BRYONY','BUDD',39,'F',3,18000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('SALLY','EMU',19,'F',2,5000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('BETTY','PALLISTER',29,'F',2,6000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('DANIEL','CARTER',68,'M',1,67000) Insert into myAuthors(Author_First_Name,Author_Last_Name,Age,Sex,Category_id,Earnings) values ('BURT','WHEELAN',92,'F',1,54000)
Now we have the data, we can create the application.
Click on NEW APPLICATION and give it a title.
Then click onto Add Page.
Click onto FACETED SEARCH
Now we select the table which is the driving table – in this case the MYAUTHORS table.
Now we can create the application and run it – just press Create Application and then run.
Wow! We can see that we now have a new special search panel created that shows a range of options derived from the data and allows us to quickly narrow down our searching. Note also that APEX has brought in the CATEGORY NAME into the main panel.
Here we narrow down the records to just those in the MECHANICS category,
We can edit the APEX application, including removing any auto-generated items we do not need (e.g. we may decide to remove the names) and then of course we can add additional elements to the application, including adding charts, etc.
So without a great deal of effort, we were quickly able to try out the new Faceted Search facility and start to contemplate how this concept could be employed and provide an enhanced experience for the users.
