Editable grid with primary key column hidden on the form fails to insert multiple rows at a time

Claire_Denton
Claire_Denton E Community Moderator
in AppWorks #1

Hi Everyone,

 

This will be fixed in a future release but I wanted to make this work around available to you.

 

We have a workaround as follows:

 

  1. Have a non primary key column which uses a combination of trigger and sequence to keep unique rows - this column will be the primary key after the product defect is fixed.
  2. Add a varchar(250) column and make it the primary key (this will be temporary primary key until defect is fixed)
  3. Default this column values to UserName + FolderId
  4. On client side, on cell entry event append the milliseconds since 1970 to this value to generate a unique key
  5. After the fix is available, this column can simply be dropped and the trigger based column can be made the primary key. There should be no need for the trigger and the client side code either.

 

A sample solution is attached. This is just one way to do it, I am sure there may be other better options out there.

 

Hope this helps.

Claire Denton, Director OCP Product Management

Tagged:

Comments

  • Jerome_Pearce
    Jerome_Pearce
    #2

    This is with Oracle, I assume (this is where I have noted the issue).

  • Jerome_Pearce
    Jerome_Pearce
    #3

    BTW, if you want to retrieve these rows based on the folder id, the value would be much better as FolderId+UserName+milliseconds. That will keep the records together rather than all over the table.

    And please note that steps 2 & 5 will need to be done manually.

     

    [edited]

  • Claire_Denton
    Claire_Denton E Community Moderator
    #4

    Yes this was tested against Oracle and SQL Server by professional services.

     

    Thanks,

    Claire

    Claire Denton, Director OCP Product Management

  • Jerome_Pearce
    Jerome_Pearce
    #5

    But it works perfectly well with SQL Server as far as I am aware as it uses an Identity column. The only time you get a problem is with Oracle surely?

  • Jennifer Methlie
    Jennifer Methlie
    #6

    Can you please clarify - I'm relatively new to Metastorm and Oracle but trying to test the fix.     I have an ID column with triggers and sequences which has been the PK for the table.   If I now create the new column as the PK, I will have null values for the pre-existing table rows.   Isn't that a problem?  I'm a little nervous to proceed even in our DEV environment since we use the data for a lot of testing.

     

    Thanks!

  • Jerome_Pearce
    Jerome_Pearce
    #7

    Yes, it will be a problem. You would need to fill the new column with valid data for the PK to be set. It need not be the same, but it should at least contain the Folder Id and anything else to make the value unique.

  • Jennifer Methlie
    Jennifer Methlie
    #8

    Have a recommended way to do that?   Updating db tables makes me nervous.

     

    Thanks!

  • Jerome_Pearce
    Jerome_Pearce
    #9

    If updating tables is not something you are comfortable with, you would be best advised to get some help in that area from a DBA or database developer.

Categories

image
OpenText CE Products
AppWorks
Gateway & Platform