APEX UI Default have been around for a long time. I spent some time trying to find which version it appeared in, but I couldn’t nail it down exactly. I’m going to guess it predates APEX 4.0, it’s an ancient feature that’s at minimum 15 years old
This is part 3 (and possibly the final entry) of series of blogs entitled Things in APEX I've used for years, but not fully understood how they work.
Part 1 : Sorting in APEX: Classic Reports and Card Regions Explained
Part 2 : Deep Dive into Oracle APEX Globalization: Understanding Multi-Language and Locales
I don’t really use UI Defaults and therefore I didn’t really understand their purpose. I thought it was about setting Labels & Configurations at Column Level, so that when the Wizards construct a Form or Report, they use those Labels, etc. It turns out…. that’s exactly what they’re used for. Phew, so I wasn’t far from the mark.
So should I start using them now I’ve researched them? … or should I continue to ignore them for another 15 years? … Let’s find out
Straight in to an Example
Lets build a table…
CREATE TABLE football_league (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Unique ID for each team
name VARCHAR2(100) NOT NULL, -- Team name
pos NUMBER, -- Position in the league
p NUMBER DEFAULT 0, -- Games played
w NUMBER DEFAULT 0, -- Wins
d NUMBER DEFAULT 0, -- Draws
l NUMBER DEFAULT 0, -- Losses
gf NUMBER DEFAULT 0, -- Goals For
ga NUMBER DEFAULT 0, -- Goals Against
gd NUMBER, -- Goal Difference
pts NUMBER, -- Points
created_by VARCHAR2(50), -- Who created the record
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- When the record was created
updated_by VARCHAR2(50), -- Who last updated the record
updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- When it was last updated
);
Now lets populate it with data.
INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Leeds United', 1, 5, 3, 1, 1, 10, 5, 'admin', 'admin');
INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Norwich City', 2, 5, 3, 1, 1, 9, 6, 'admin', 'admin');
INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Sunderland', 3, 5, 2, 2, 1, 8, 4, 'admin', 'admin');
INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Sheffield Wednesday', 4, 5, 2, 1, 2, 7, 7, 'admin', 'admin');
INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Coventry City', 5, 5, 1, 3, 1, 6, 6, 'admin', 'admin');
If I create a Report with a Form using the wizard, it looks just like I expected it to. Its fine for a start, I guess. What’s with those floating point numbers?
The Form looks a bit socially awkward though 😏
Normally, I’d get cracking on making the App more usable, adding help texts, choosing format masks, correcting the labels, that sort of thing.
What happens if there was a better way? Du du du du du duu APEX UI Defaults!
Gets go back in time, 5 mins ago, at a point just before we create our app. Lets go to SQL Workshop \ Utilities \ UI Defaults and we see…
We see two options:
Table Dictionary
Attribute Dictionary
Lets start on Table Dictionary, because its more interesting.
Table Dictionary
We can, if we want, hit that big green Synchronize Defaults button. But its going to take some time for my 97 tables. I remember doing this a long time ago, It did take a long time and it crashed too. This might be best shunted to a Background Job in a future release.
See that text above? Several times, whilst researching this blog, my brain almost malfunctioned ⛓️💥 when reading things like “This will … remove columns that no longer exist”… hey! hands off my DB tables!!! Actually, relax, its talking about UI Default columns, i.e Rows, not DB columns
Lets start by clicking FOOTBALL_LEAGUE link.
You can create Table Defaults on Tables or Views. We can now click the Create Defaults button.
When complete, instead of rather than opening up the defaults that it’s just created for you, It throws you back to the previous page, meaning you have to click on FOOTBALL_LEAGUE link again (see picture below).
However, this time can see that Defaults now exist. Click the FOOTBALL_LEAGUE link, which brings you to the page below.
Click the Next click Edit Table Defaults button.
Check the fields and give it a description. Why? because we are giving AI valuable information about the nature of this table - OK you probably don’t need to give as much information as I did below
As you can see this 15+ year old feature has now been repurposed to provide additional information to the APEX Generative AI Service which enables it to generate SQL and build Applications.
Be warned that not all fields assist AI, in fact very few, so do check the Inline Help Texts.
Finally, we get to see our columns.
From here we can start to give these ridiculous Columns some proper labels. For this we need to click on the columns, lets start with Name. I’ll give it a Label of Team and a Help Text.
The next Section is about the columns’ appearance in a Form, i.e which Page Item it should use (i.e. Text, Number, Select, Combobox, etc), what Template to use, is it Required, etc.
The Display is whether it should be shown or not in the Form… or percent-symbol, which I worked out is a Null Value. I don’t really see the purpose of Null here… However, in my tests, the Item is always displayed regardless of which of the 3 settings I had chosen.
The next section is about Report & Tabular Forms. Which is a bit odd as the creation of Tabular Forms was removed 2 years ago and UI Defaults do not apply retrospectively to existing components. Some of the Display As entries feel a bit legacy Tabular-Formy.
At the bottom you can create an LOV
If you create a Static LOV you cannot see if you have set it up correctly unless you click it again - there are some usability issues here. I had some real issues with LOVs on this page. I’m not going to go into the full detail on this blog, but you can tell I’m not thrilled.
Its difficult to describe, but this page feels legacy, but it does do useful stuff.
In my next step, I’ll add some proper labels to the all the fields
Whilst I was on a Goals Column, I clicked the Create Column Group button
… and bobbed all my Goals Columns (For, Against, Diff) in that Group. I did the same for a new Results Column Group (Played, Win, Draw & Loss).
As mentioned earlier, all this work has had no effect on my existing Page components. It only works in the Wizards. It does not even apply when creating a new Form Region on a table on an existing page - which is a lost opportunity in my opinion.
Therefore I need to repeat the steps to create a Report with a Form on new pages. Note: During the wizard, there used to be a switch to use UI Defaults (default enabled), but this has now been removed and you can’t opt out of it.
Now if I run my App with the new page it has just built…
As you can see, the labels have changed.
But the wizard added Format Masks of 999G999G999G999G999G999G999G999G999G990D0 to the report Columns etc (Won) even though I specifically wanted a Null Format Mask and to be Left Aligned which APEX basically ignored.
Also, where are my column groups? It specifically said that I would be given Column Groups in Interactive Reports and in Forms….
…No Interactive Reports column groupings and, yup, my Forms look no different, no groupings.
Lets have a quick look at Attribute Dictionary
Attribute Dictionary
In Attribute Dictionary, I’m going to create some regularly used columns across all my tables. I’m just going to set the label & as you can see, these 5 are not associated with any table in particular.
Lets create a new table, that features those 5 columns
CREATE TABLE office_supplies (
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, -- Unique identifier for each supply
supply_name VARCHAR2(100), -- What's the name of this precious item?
is_stapler CHAR(1) DEFAULT 'N', -- Y/N, is this item a stapler?
mysteriously_gone CHAR(1) DEFAULT 'N', -- Did it vanish into the office void? Y/N
complaints NUMBER DEFAULT 0, -- How many complaints about missing items?
created_by VARCHAR2(50), -- Who created the record
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- When the record was created
updated_by VARCHAR2(50), -- Who last updated the record
updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- When it was last updated
);
Add some data
INSERT INTO office_supplies (supply_name, is_stapler, mysteriously_gone, complaints, created_by, updated_by)
VALUES ('Red Swingline Stapler', 'Y', 'N', 2, 'Milton', 'Milton');
INSERT INTO office_supplies (supply_name, is_stapler, mysteriously_gone, complaints, created_by, updated_by)
VALUES ('Box of Paper Clips', 'N', 'Y', 10, 'Pam', 'Dwight');
INSERT INTO office_supplies (supply_name, is_stapler, mysteriously_gone, complaints, created_by, updated_by)
VALUES ('Fancy Gel Pens', 'N', 'Y', 25, 'Jim', 'Michael');
Now, without creating Table UI Defaults, if I build a Form using the Wizard… Hooray… it uses the Attribute Dictionary
I’ve deliberately not shown you a Classic or Interactive Reports because… sigh.. it seems Attribute Dictionary only works with Forms, in APEX 24.1 at least. Even though the documentation tells you otherwise.
If I create Table Defaults from my office_supplies table, it fails to copy the Attribute Dictionary version across and instead tries to come up with a new label for those columns.
In the wizard, Table Defaults supersede the Attribute Dictionary.
So to be honest, what is the actual point of Attribute Dictionary? If one would curate a list of columns in the Attribute Dictionary, that work is completely wasted should someone create Table Defaults.
Summary
Hmm, well that was underwhelming. My experience has been mixed with this, whilst there are things that are undoubtedly useful and do ensure standards, there are several surprising settings, of which some just don’t work.
UI Defaults have been around for years and perhaps, like the Advisor, It needs some General Improvements. I do see some traction on a 3 year old logged idea FR-1970 that is now on the road map.
This is not an exhaustive deep dive and it will take me hours to identify all the issues, which I’m not going to do as I want to keep my resting heart-beat low.
My takeaway from this is that UI Defaults may provide some consistency in Forms for some teams. It may be good practice to incorporate creation of UI Defaults on creation of a new table. Just don’t expect miracles from this feature.
ENJOY!
What’s the picture? I do not tire of this view point in Knaresborough. Visit Yorkshire!