This discussion is archived
7 Replies Latest reply: Feb 5, 2013 2:35 AM by G.Y RSS

create serial number on form level

MAHA REDDY Newbie
Currently Being Moderated
Hi All,

I am trying to generate the serial no on the form by this coding
i have taken non data base field SI_NO

IF :system.record_status IN ( 'NEW' ,'INSERT' ) THEN
:XXEAM_JOB_TEMPLATE_LINES.SI_NO := :System.CURSOR_RECORD ;
END IF ;
If I am deleteting a record from the middle of the record the sequence on the serial number does not gets changed eg. if there r 5 records if I delete 3 record then the sequence of serial number will become 1,2,4,5 where as it should become 1,2,3,4

same is the issue if I add a record in the middle the serial number gets duplicate eg if there r 5 record and i am adding another record after 2 then its coming like this 1,2,3,3,4,5 where as it should come like this 1,2,3,4,5,6


Regards,
Maha.
  • 1. Re: create serial number on form level
    HamidHelal Guru
    Currently Being Moderated
    MAHA REDDY wrote:
    Hi All,

    I am trying to generate the serial no on the form by this coding
    i have taken non data base field SI_NO

    IF :system.record_status IN ( 'NEW' ,'INSERT' ) THEN
    :XXEAM_JOB_TEMPLATE_LINES.SI_NO := :System.CURSOR_RECORD ;
    END IF ;
    If I am deleteting a record from the middle of the record the sequence on the serial number does not gets changed eg. if there r 5 records if I delete 3 record then the sequence of serial number will become 1,2,4,5 where as it should become 1,2,3,4

    same is the issue if I add a record in the middle the serial number gets duplicate eg if there r 5 record and i am adding another record after 2 then its coming like this 1,2,3,3,4,5 where as it should come like this 1,2,3,4,5,6
    Hi Maha,
    You can use PRE-RECORD trigger at block level and if you write
    :TXT_SL:=:system.trigger_record;
    you will get serial no. After deleting or adding it will not re-organize your serial no un till you navigate at other record. If you navigate through out the record, it will
    re-organized.

    If you want it dynamically, you need to do a loop after any interaction in records.


    Hope understand..

    Hamid
  • 2. Re: create serial number on form level
    G.Y Newbie
    Currently Being Moderated
    Hi Maha
    try this
    trigger When-new-record-instance
    level at= block-level
    <your item> := :system.trigger_record
    this is for new record creating, even if you create your record with in your record it will return correct serial numer.

    likewise if you want to do it for deleting then modify/use it at that trigger.
    but the method is same.
    hope it helps you.
  • 3. Re: create serial number on form level
    MAHA REDDY Newbie
    Currently Being Moderated
    Hi Ghulam Yassen ,

    I have tried when_new_record_instance and pre_record triggers same problem repeating.
    <Block Name.item Name> := :system.trigger_record on block level

    please give me any solution...

    Regards,
    Maha
  • 4. Re: create serial number on form level
    Christian Erlinger Guru
    Currently Being Moderated
    MAHA REDDY wrote:
    If I am deleteting a record from the middle of the record the sequence on the serial number does not gets changed eg. if there r 5 records if I delete 3 record then the sequence of serial number will become 1,2,4,5 where as it should become 1,2,3,4

    same is the issue if I add a record in the middle the serial number gets duplicate eg if there r 5 record and i am adding another record after 2 then its coming like this 1,2,3,3,4,5 where as it should come like this 1,2,3,4,5,6
    I suggest you should forget about the fact that you can have gaps and the order as this complicates things unnecessary and makes the whole process very unscalable and slow as you'd need to update the primary key of a table for every record when deleting a record with in itself is a bad idea. Also you'd have to lock the entire table which transforms a multiuser application to a single user application. If you need a gapless number for some reason then use rownum in your query. For storing the data use a sequence instead

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#CNCPT611

    cheers
  • 5. Re: create serial number on form level
    MAHA REDDY Newbie
    Currently Being Moderated
    Hi christian erlinger,

    How to use rownum in form level i have taken non data base field (SI_NO) ...could you give any idea please.

    Regards,
    maha
  • 6. Re: create serial number on form level
    Christian Erlinger Guru
    Currently Being Moderated
    what is SI_NO? Is it the primary key or part of the primary key or unique in any way? If so then using rownum for this would be a very bad idea.

    cheers
  • 7. Re: create serial number on form level
    G.Y Newbie
    Currently Being Moderated
    MAHA REDDY wrote:
    Hi Ghulam Yassen ,

    I have tried when_new_record_instance and pre_record triggers same problem repeating.
    <Block Name.item Name> := :system.trigger_record on block level

    please give me any solution...

    Regards,
    Maha
    Dear Maha as per your question that is the solution which already told you.

    plz tell what error is showing...
    or send me ur team ID and paswrd at ghulamyassen@gmail.com

    thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points