Home » Developer & Programmer » Forms » Automatically display unique ID for new record (merged)
Automatically display unique ID for new record (merged) [message #238878] Sun, 20 May 2007 14:39 Go to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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;
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.
WHEN-NEW-RECORD-INSTANCE RESTRICTING ME! [message #239628 is a reply to message #238878] Tue, 22 May 2007 16:54 Go to previous messageGo to next message
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

Re: WHEN-NEW-RECORD-INSTANCE RESTRICTING ME! [message #239636 is a reply to message #239628] Tue, 22 May 2007 22:11 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Re: WHEN-NEW-RECORD-INSTANCE RESTRICTING ME! [message #239968 is a reply to message #239636] Wed, 23 May 2007 15:03 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Impossible! David is impeccable! ./fa/1601/0/
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 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why not? It won't cost much, and you might get a proper answer.
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 Go to previous messageGo to next message
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 162 times)
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 Go to previous messageGo to next message
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 226 times)
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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 221 times)
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.
Re: Automatically display unique ID for new record (merged) [message #242823 is a reply to message #238878] Tue, 05 June 2007 04:13 Go to previous messageGo to next message
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 .....
Re: Automatically display unique ID for new record (merged) [message #242888 is a reply to message #242823] Tue, 05 June 2007 07:39 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd like to see you enjoying in a multi-user environment with such a solution.
Re: Automatically display unique ID for new record (merged) [message #243071 is a reply to message #242888] Tue, 05 June 2007 22:41 Go to previous messageGo to next message
yahya_mukhtar
Messages: 12
Registered: June 2007
Location: pakistan
Junior Member
hi .
ok fine.....
Re: Automatically display unique ID for new record (merged) [message #243108 is a reply to message #238878] Wed, 06 June 2007 02:13 Go to previous message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

..unless the field is designed as unique Cool
Previous Topic: How to Assign LOV values to Global Variables?
Next Topic: Parameter passing in report(multiple)
Goto Forum:
  


Current Time: Fri Dec 02 19:04:04 CST 2016

Total time taken to generate the page: 0.06063 seconds