After creating a custom form, I am able to add records to the new Custom LU. However, when I add a new record, there is nothing that checks to see if that record already exists. As an example, my new form has a field called Project Name. If I add a new record, I want it to check to make sure that the project name does not already exist. Essentially, I want to designate the Project Name as the primary key. Is there a way to do this?
Solved
Custom Form Primary Key
Best answer by Rusiru Dharmadasa
Apart from the asanka’s answer above, you can mark your field as an alternate key. After that users would not be able to create the data row, if this column value is duplicated.
Anyway, in the underline design, the _CFT table is having rowkey as the primary key. You should not remove it or change it to something else even from the db code.
I can think of two options:
- Add a primary key to the _cft table using ALTER TABLE. Haven’t tried this myself on a custom field table, but it should work.
- Add a custom event to check if a record exists, and fire an error message. Be wary with the table mutation error.
~hope is a good thing, maybe the best of things, and no good thing ever dies...
+19Apart from the asanka’s answer above, you can mark your field as an alternate key. After that users would not be able to create the data row, if this column value is duplicated.
Anyway, in the underline design, the _CFT table is having rowkey as the primary key. You should not remove it or change it to something else even from the db code.
Rusiru
I have tried to add the alternate key, however, I receive an error message: “COMPILE_ERROR2: Error during comilation of LU. Error details: -1408 in \CUSTOM LU CODE\OUR_CUSTOM_LU_CLT.CRE\ALTERNATE_KEY.”
+19Not sure what is going wrong here, can you send us the generated code from your LU? You can RMB on the header → View generated objects to get the code. Also a screen shot showing all details of your custom LU so we can try the same here and see whats going wrong
Rusiru, thank you for the quick reply. Attached to this is the generated code as well as a word document with screen shots. The error we receive comes after we select the alternate primary key on the PROJECT_NAME field and then try and synchronize the custom lu.
+19it seems like the RMB → view generated objects collected without you selecting the “alternate key” option. isn’t it? If yes, can you collect and resend generated objects after setting “alternate key” option and saving?
Also is it possible to send us the complete custom LU here? using RMB → export option? This will create an .ins file.
Yes, I generated objects without having the Alternate Key selected. I am attaching the generated objects with the Alternate Key selected along with the export. Thanks for the help!
+19asanka wrote:
Add a primary key to the _cft table using ALTER TABLE. Haven’t tried this myself on a custom field table, but it should work.
I do this all the time, but I create a unique index rather than a primary key. The primary key is still really the ROWKEY column. This is also important because the out-of-the-box configuration doesn’t let you create a multi-column index.
Kevin and Asanka,
The problem with adding the primary key to the table using Alter Table command is that if the custom lu is re-published (added a new column, etc) we would have to remember to add the primary key to the table after it is re-published. I would prefer to use the built in logic if possible
+19tjhendrikse wrote:
Kevin and Asanka,
The problem with adding the primary key to the table using Alter Table command is that if the custom lu is re-published (added a new column, etc) we would have to remember to add the primary key to the table after it is re-published. I would prefer to use the built in logic if possible
This could be addressed in change management. I don’t have a better answer. For example, when you add custom fields, you’re probably familiar with the chore of updating Crystal reports and user profiles.
+19When you select “Alternate key” option, behind the scene it will create a “unique” index in oracle table. However your field (CF$_PROJECT_NAME) has already configured as searchable and “indexed” which will create another index. Since it is not allowed to create multiple indexes, this is failed.
RMB → Edit your CF$_PROJECT_NAME file and set it as “not” indexed in the below screen.
Now make sure the “Alternate key” option is selected and then Save → RMB on header Synchronize.
This should work.
Rusiru
I was able to figure out the issue. On the custom lu, I performed a cleanup (rmb in the header, cleanup, remove obsolete table columns). I also removed the data from the table. Once I did that, I was able to set the column I wanted to be the Alternate Key. I guess the lesson is, set your primary key (Alternate Key field) before adding any data to your new custom lu.
+19I’m not sure when this was fixed/changed (or what I might have been doing wrong before), but today in Apps 9 UPD13, I discovered I can create a multi-column unique index by checking the Alternate Key checkbox on multiple attributes.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.