Have you been plagued with the issue of getting values like t1001 show up in columns you marked as primary key in your interactive grid? In case you do not feel it is a good interface for your end users you may have found yourself spending quality time with Google looking for clues. I have had a couple of people asking me about this in just the last week. Googling turns up very little and mostly about the T1000 processor from SUN.
What you can find are some discussions about how this is a way for APEX to manage records that have not yet been saved to the database. No matter what you select for default value of the column, it still shows t1001 or some such value.
First things first, is this a bug? No, I don’t think so. It is part of how interactive grid manages new rows, the question is how to design applications to met your requirements. Hopefully this post will help you do just that.
In the following walkthrough I have been using apex.oracle.com with version 19.1.0.00.15.
Lets first show the problem using the DEPT table.
– Create a new page with an Interactive Grid based on this SQL.
select deptno
, dname
, loc
from dept;– Go into the attributes for the interactive grid and set it to be editable.
An editable interactive grid needs to have a primary key defined.
– Go into the attributes and select DEPTNO.
– Set it to be a primary key in the source section.
The report is now able to be used and you can add new departments. Run it and click on new row and see what happens.
The new line shows t1001 in the primary key column. It is used by APEX for processing new rows, but it is not always what an end user would like to see.
In many cases the primary key is not useful to display and can simply just be hidden from view.
In this case it is a column the end user should (according to the user) see but not be allowed to set, it ought to show nothing for new rows and there ought to be a visual clue that it is a key. There are probably many ways to achieve this, but finding a straightforward solution can be hard even if the completed solution seems obvious in hindsight.
Here is my way to handle it. I like it because it is purely declarative, no-code, and it feels 100% logical – at least to me – once we accept that the primary key really is a data element APEX and the interactive grid controls.
What we do now is hide the primary key from view, but we need the data to be shown.
Let’s begin with just hiding the primary key.
– Set DEPTNO to be of type hidden.
Now Let’s add a column that just shows the values but cannot be edited by the user
– Right click on the columns heading (under the IG in the rendering pane).
– Select “Create Column”
– Give it a good name. Something like “DEPTNO_SHOW”
– Set the label to “Deptno”
– In the “Source” section change Type to be “Database Column”
– Enter DEPT_NO in the “Database Column” field.
– Chose “Number” as the datatype.
– Set “Query Only” to Yes.
With this done we’re ready to take it out for a spin. Run the page and start with adding the column if it doesn’t show up. Drag it to the beginning of the line where it used to be.
The first thing to notice is that the “Deptno” column indicates that it cannot be edited by having a different background than editable columns. Now click on “Add Row” and lets see if it works.
Much better. Deptno cannot be edited and it does not show any data when a new row is created that can confuse an end user. It has a visual indication that it is a key or at least an uneditable field. When you save the data, DEPTNO is given a value and the column is updated in the report. It is done by a sequence and a trigger on the dept table assigns it a value when a row is inserted. You can of course use all the features for default value on the DEPTNO primary key column if you prefer to manage its value from your APEX-application.
Updating and deleting of course also works as it should. We have not messed with the APEX model for how to do this, only adjusted how the users sees the data. For cases when showing no value until it gets set during the insert is requested this will provide the end user with the interface they want while still letting the interactive grid do the heavy lifting.
Please let me know if this helped you or if something in this post was confusing.
12 Comments