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.
Thank you very much. I was going crazy about this issue because there is a side effect of getting the “t1000” value in the item. You can not perform a “not null validation” because the item has the t1000 value.
Hi again, applying the solution above solved the editing problem but I was unable to perform a Dynamic Action that would be triggered “if item is null” on the xxx_SHOW item. The DA property palette showed the xxx_SHOW item as V(invalid).This is how I solved it. Deleted the xxx_SHOW column. Made the original column visible. I created a new DA “row initiliazition” on the region level. The DA clears the item when a row is initialized (removes t1000). Now I can create a DA for item can not be null validation and raise an alert on item change event.
Very late response, but I think I should point out one thing here. The T1000 is not a bug – I state this in the post – so when you mess with the value in the actual column used by IG as PK you are likely to mess with the internals of IG. If it really works now, it is likely to stop in any upgrade you do. I would be wary of this. You may be better off having a technical key that IG can control and you set the value you want to a column you have a Unique Key constraint on.
How can we get the default value to be displayed though? What if we want the dept no to be populated automatically based on another item selection such as a drop down list? So, instead of showing a blank I would want to show the value that was selected in the list elsewhere
You should be able to do this just as how you set any value in an IG. Though, again I want to reiterate one thing. This is a column set by IG as it is a PK to it. If youo want to set the value yoourself it should probably not be the column you tell IG to use as the PK.
I stumbled over this while searching T1000 having the same problem, excellent explanation!
There are two problem to consider:
1. T1000 will only be shown on editable items (text, number, etc), otherwise it’s empty and on save the DB will complain PK cannot be NULL.
2. Sadly doesn’t help on INSERT as there are two DB items accessing the same column and therefore on submit: “ORA-00957: duplicate column name”
I think you missed setting this column to be “query only”. Thus, it should not be sent to the database. The hidden PK is the one that IG will send and it will make the IG handle its value according to the settings, most of the time pulling a new value from a sequence. Both your problems ought to stem from that if I understand the issues and the reason for them occurring.
Have you tried using ROWID as a Primary Key instead of using any data column? That should resolve all of your issues.
I have not looked at this for quite a while. From my memory I seem to recall it having caused an issue of some sort. Have you implemented it that way?
thanks alot,
can you give us example how to set defualt value for primary key in IG when you want to add multi rows
WHat do you want to achieve, setting a PK automatically on save. Would numerical from a trigger work for you?
Thank you for discussing this issue in your blog. My requirement is not to display id column but this blog helped me to understand the mechanism underneath. Appreciate it.