OraFAQ Forum: Forms » Automatically display unique ID for new record (merged)

Home » Developer & Programmer » Forms » Automatically display unique ID for new record (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Automatically display unique ID for new record (merged) [message #238878]

Sun, 20 May 2007 14:39

Basit
Messages: 38
Registered: April 2007

Member

hi,

how to display a unique,new customer number for each new record and ensure that it cannot be changed.

and i have created a sequence but don't know how to do it!

need your help!

regards
basit

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #238881 is a reply to message #238878]

Sun, 20 May 2007 15:34

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Set item's value in the WHEN-NEW-RECORD-INSTANCE trigger.

Set 'Keyboard Navigable' properties to NO (so that you can't enter into the item by keyboard) or, set its 'Updateable' property to NO (so that you could enter into an item, but couldn't modify its value), or set its 'Enabled' property to NO which would disable entering this item by all means.

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #238882 is a reply to message #238881]

Sun, 20 May 2007 16:00

Basit
Messages: 38
Registered: April 2007

Member

hello,

Can you tell me what is wrong here in this code?
I have given the following trigger on ITEM LEVEL (ID) or it should be on BLOCK LEVEL?

begin
select s_customer_id.nextval into :s_customer.id from sys.dual;
end;
every time i compile it ,it gives an error

Error 201 at line 2,column 58
identifier 'SYS.dual' must be decalred.

i tried many times, but know what to do?

regards
basit

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #238883 is a reply to message #238882]

Sun, 20 May 2007 16:11

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Block-level should do it; try to remove 'sys.':

select s_customer_id.nextval into :s_customer.id from dual;

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #238886 is a reply to message #238883]

Sun, 20 May 2007 16:24

Basit
Messages: 38
Registered: April 2007

Member

Yes, it worked and thnx bro for your generocity, but one thing else i might disturb you alot but i found you very friendly, is that necessary to Set item's value in the WHEN-NEW-RECORD-INSTANCE trigger. like you mentioned of the following properties

'Keyboard Navigable' properties to NO, or set its 'Updateable' property to NO could enter into an item, but couldn't modify its value), or set its 'Enabled' property to NO which would disable entering this ,

if i do it with PROPERTY PALLETE does it differs?

regards
basit

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #238960 is a reply to message #238886]

Mon, 21 May 2007 01:24

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

WHEN-NEW-RECORD-INSTANCE trigger seems to be appropriate for this action. If you don't like it, choose another trigger which will do the same.

As of your second question: all those properties (actually, "Enabled = NO" should be enough for what you've asked for) are to be set using the Property Palette; I didn't mean to do it programatically.

Report message to a moderator

WHEN-NEW-RECORD-INSTANCE RESTRICTING ME! [message #239628 is a reply to message #238878]

Tue, 22 May 2007 16:54

Basit
Messages: 38
Registered: April 2007

Member

Hi,
Title *WHEN-NEW-RECORD-INSTANCE*
I have implemented this trigger on BLOCK LEVEL, actually
what i want is that,when i run my FORM a NEW ID (NUMBER)
should be generated automatically, this trigger is doing
fine till here, but when i run my FORM a new ID is GENERATED
even i did'nt do anything and every time i test my form i
lose a sequence generated number and it, and it is not
allowing me to execute query b'coz WHEN-NEW-RECORD-INSTANCE
Trigger takes it to Insert Mode,

i want to generate new ID number when i press INSERT RECORD
button in DEFAULT&SMARTBAR,now it should create it, not
every time i run my form.

Need ur reply.

Regards
Basit

Report message to a moderator

Re: WHEN-NEW-RECORD-INSTANCE RESTRICTING ME! [message #239636 is a reply to message #239628]

Tue, 22 May 2007 22:11

karthick2809
Messages: 50
Registered: April 2007
Location: Chennai

Member

if u wanna create a id for every new record,
my advise is better code it in pre-insert trigger..are u using sequence to generate id?

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #239651 is a reply to message #238960]

Tue, 22 May 2007 23:39

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Hey guys, we're had this discussion before. When-New-Record-Instance EVERY time you go to a record. You need to use When-Create-Record if you want it to fire the first time that a record is CREATED otherwise you have the potential to 'set' the 'key' EVERY time you enter an already existing record.

Also consider NOT displaying the key field.

David

Report message to a moderator

Re: WHEN-NEW-RECORD-INSTANCE RESTRICTING ME! [message #239968 is a reply to message #239636]

Wed, 23 May 2007 15:03

Basit
Messages: 38
Registered: April 2007

Member

hi,
yes i use sequence to generate ID number, what can you suggest me? let me know the right Trigger for this, i m confused with it.

regards
Basit

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #239975 is a reply to message #239651]

Wed, 23 May 2007 15:32

Basit
Messages: 38
Registered: April 2007

Member

Hello,

as you said to use When-New-Record trigger for this, for Automatic ID generation, i used that too but the problem is that
, when i was using when-new-record-instance the problem was that , that when i run my form so new ID was generated and it was not leaving me to Execute Query, now with When-New-Record trigger one problem is solved b'coz it is leaving me to execute, but now the main problem is that when i run my form new ID is generated and lost, when i reach to last record then it fires their too. as i stated that i need to create a new Id number when i Press CREATE RECORD button in DEFAULT&SMARTBAR.

plz help me cause this is making me feel weak.

regards
basit

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #239984 is a reply to message #239975]

Wed, 23 May 2007 16:21

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Did you read David's post? He suggested you to use the WHEN-CREATE-RECORD trigger.

Also, you have opened a new topic for the same problem. Please, do not do that - continue discussion in existing one.

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #239990 is a reply to message #239984]

Wed, 23 May 2007 16:38

Basit
Messages: 38
Registered: April 2007

Member

yes i have seen his post and used that too, but still have problem, i m getting the desired result. as i said in previous
post, and mistakenly i wrote the wrong trigger in previous, supposed to write WHEN-CREATE-RECORD.

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #239992 is a reply to message #239990]

Wed, 23 May 2007 16:42

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Impossible! David is impeccable! /forum/fa/1601/0/

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #239994 is a reply to message #239992]

Wed, 23 May 2007 16:46

Basit
Messages: 38
Registered: April 2007

Member

i think i made a mistake , i can send you sample of my form if you want too see what i have done to it, and i will send the .SQL file for tables. if not busy!
shoud i?

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #239998 is a reply to message #239994]

Wed, 23 May 2007 17:04

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Why not? It won't cost much, and you might get a proper answer.

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #239999 is a reply to message #239992]

Wed, 23 May 2007 17:08

Basit
Messages: 38
Registered: April 2007

Member

this is my form and i will send you the .sql file after this
and let me tell you i use FORMS6i

Attachment: CUSTOMER.fmb
(Size: 44.00KB, Downloaded 1020 times)

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #240001 is a reply to message #239992]

Wed, 23 May 2007 17:11

Basit
Messages: 38
Registered: April 2007

Member

this is the tables.sql file, this script will create alot of tables with few rows, so you need to create another user

Attachment: Tables.sql
(Size: 41.43KB, Downloaded 1093 times)

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #240003 is a reply to message #239998]

Wed, 23 May 2007 17:21

Basit
Messages: 38
Registered: April 2007

Member

sorry i forgot to say, the sequence which i m using is also created when you run TABLES.SQL file

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #240047 is a reply to message #240003]

Thu, 24 May 2007 00:39

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Here's what you might do:

First approach is to avoid creating a new ID when you start the form; so, run it in query mode. If you want to insert a new record, you'd have to create it (pushing a button on toolbar). KEY-DOWN trigger will prevent users from creating a new record "accidentally" (when they reach the last record in the block).

- move WHEN-CREATE-RECORD trigger to the block level
- create a PRE-FORM (form level) trigger:

EXECUTE_QUERY;

- create a KEY-DOWN trigger (block level) which looks like this:

if :system.last_record = 'TRUE' then
   message('This is the last record');
   null;
else
   down;
end if;

Another way would be this: don't use WCR trigger at all, but create a push button which will do all the job:

- remove the WHEN-CREATE-RECORD trigger
- create a push button with such a code

create_record;
select s_customer_id.nextval into :s_customer.id from dual;
go_item('name');

I've attached a, let's say, some sort of combination of what I've said. See how it looks like and adjust it to suite your needs.

Attachment: CUSTOMER.fmb
(Size: 56.00KB, Downloaded 1081 times)

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #240384 is a reply to message #240047]

Thu, 24 May 2007 13:29

Basit
Messages: 38
Registered: April 2007

Member

Hi,

it worked and thnx finally i got it with your help, i did what ever you said to do, but your form which you have send is not running, it gives an error like,

PDE-PEP006 Enclosed Program unit has an unknown format.
and the other one is

Form:CUSTOMER
FRM-30085: Unable to adjust form for output.

Report message to a moderator

Re: Automatically display a unique, new customer number for each new record? [message #240410 is a reply to message #240384]

Thu, 24 May 2007 15:08

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The error you got must be because of the fact that you use Developer 6i, while my version was created using Developer 10g. But never mind that, I've explained it in previous post. And, more important, you made it work.

Report message to a moderator

Re: Automatically display unique ID for new record (merged) [message #242823 is a reply to message #238878]

Tue, 05 June 2007 04:13

yahya_mukhtar
Messages: 12
Registered: June 2007
Location: pakistan

Junior Member

hi basit
u can use the pre-insert trigger at block level to use the new id when the new record is inserted. in the pre-insert trigger u use

select nvl(max(emp_id),0)+ 1 into :item
from emp

u no need to generate the sequence bez it automatially reterive the max value and then add+1 into it.
try it and then enjoy .....

Report message to a moderator