| How to increment a column in Developer 6i forms automatically? [message #151777] |
Mon, 19 December 2005 03:29  |
muralidaran_r
Messages: 6
Registered: July 2004
|
Junior Member |
|
|
Hi gurus
1) i am having a 'Batchno' varchar2(10 column in 'BATCHCARD' table.
it should start with 0500001
I developing a entry screen in developer 6i forms
My requirement is to increment the batchno (0500002, 0500003, .... and so on) automatically
in my entry screen every time the user want to insert a new record.
Next financial year it should start with 0600001, 0600002....
2) I have another column called dipcode & dipdate
if dipdate = 01-apr-05
then dipcode should be calculated automatically as '5D01'
coding meaning
--------------
5 - year
D - April (4th month, A- Jan ... L - Dec)
01 - date
Please guide me to how to do this.
thanks & regards
muralidaran r
|
|
|
|
| Re: How to increment a column in Developer 6i forms automatically? [message #151919 is a reply to message #151777] |
Mon, 19 December 2005 17:59  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Use a seperate field for each piece of data and then create the compund field which you only display. Do this via a database trigger or the When-Validate-Record trigger. Note that if fields are found missing in the WVR trigger you will need to write a timer to handle the navigation to the empty or incorrect field as the validate trigger does not support the 'go_item' command.
David
[Updated on: Mon, 19 December 2005 23:33] Report message to a moderator |
|
|
|
| Re: How to increment a column in Developer 6i forms automatically? [message #151935 is a reply to message #151777] |
Mon, 19 December 2005 21:56 |
Scarlet.Zhu
Messages: 22
Registered: December 2005
Location: Shanghai
|
Junior Member |
|
|
hi,
1> pseudo code for your q1:
create sequence s
select s.nextval into val1
insert to_varchar(val1) into table
or
select max(..) from table
to_number
:= +
insert
2> hint:
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'MM','NLS_DATE_LANGUAGE = American') from dual;
Select (Case
When To_Char(Sysdate, 'MM', 'NLS_DATE_LANGUAGE = American') = 01 Then
'A'
End)
From Dual;
|
|
|
|
| Re: How to increment a column in Developer 6i forms automatically? [message #151956 is a reply to message #151777] |
Tue, 20 December 2005 01:28 |
Atul P
Messages: 61
Registered: June 2003
Location: Mumbai-Jakarta
|
Member |
|
|
1. Make one Sequence :
CREATE SEQUENCE YOUR_SEQUENCE START WITH 500000 INCREMENT BY 1;
2.
In your FORM in BATCHCARD table write
PRE_INSERT trigger
CODE:
SELECT LPAD(YOUR_SEQUENCE.NEXTVAL,7,0)
INTO Batchno
FROM DUAL;
This will automatically keep it incrementing.
3.
SELECT
TO_NUMBER(TO_CHAR(DIPDATE,'YY')) || DECODE(TO_CHAR(DIPDATE,'MM'),'01','A','02','B','03','C','04','D','05','E') || TO_CHAR(DIPDATE,'DD')
FROM DUAL
In above case you may continue decode till Z.
I know its not the smartest solution but stil can help u solve ur case.
Ciao
Atul
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
