OraFAQ Forum: Forms » How to get record number and total record number in record indicator?

Home » Developer & Programmer » Forms » How to get record number and total record number in record indicator?

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

How to get record number and total record number in record indicator? [message #151082]

Tue, 13 December 2005 00:57

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

In that form,in record indicator i need to get current record number and total record number ..

When i query records,this should display as

Record (1) of (10) ...it cursor is in first record and there are totally 10 records..

If i press arrow key,it should come as

Record (2) of (10)
Record (3) of (10)..so on..

If I Press F9 and add any record as 4th then
it should come as Record (4) of (11)

And if i press key to delete that record i say 5 th record(total is now 11)
then it should show as Record (5) of (10)..

I tried in two ways,,,

way 1
------

Record (IND1) of (IND2) where ind1 and ind are two fields i placed between text..
IND1 am getting from cursor_record and ind2 is count(*) am getting rom table...

If i add or delete corresponding ind1 and ind2 should change..am not able to do

way 2
------

Placing text item in which am concatinating values as

Record (X) of (IND2)

where x is trigger_record and and ind2 is count(*) am getting rom table...

This also works for query mode only..If i press f9 to enter records or press f10 to delete records it not chaging accordingly..

Am attaching screenshot of that form..

Any suggestion??pls experts

Attachment: screen.doc
(Size: 45.50KB, Downloaded 3162 times)

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151084 is a reply to message #151082]

Tue, 13 December 2005 01:01

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

By default,Oracle would show
Record 1/? or Record 2/? ..so on..if last record reached it will show like Record 7/7 again if we go back it wills how correctly..

Even if we delete or add it will change accordingly

Can we fetch those values from Oracle..How to do that?

In which trigger we have to write....

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151106 is a reply to message #151084]

Tue, 13 December 2005 03:21

ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai

Member

ashishmate@gmail.com

hi

oracle showing this

(Record 1/? or Record 2/? ..so on..)

coz he is not fetching all record.

you can set data_block property query all records to yes then u will get the total no of records fetched.

but this may reduce level of performance.

Upd-mod: Mod code tags

[Updated on: Thu, 15 December 2005 19:56] by Moderator

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151111 is a reply to message #151082]

Tue, 13 December 2005 05:19

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

Ash,

If i add a record or delete a record the number of records in record indicator should change...

U r telling set data_block property query all records to yes to get the total no of records fetched.I think it will give total number of records when it is queried.

But after If i add a record or delete a record,it would change and that is the issue.

It should happen in the form level..But if we select count from table it would give old value only becoz it is not saved yet.

So,what to do?

Is there any way to find number of rows in a block(block level) at

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151222 is a reply to message #151111]

Tue, 13 December 2005 17:27

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

Senior Member
Account Moderator

To get the total number of records without causing the Form to retrieve ALL the records, use the Count_Query command in the Pre-Query trigger of your block. If you do the Count_Query after the Execute-Query has been run Forms will retrieve ALL the records from the database. This could be painful!

Read the reference manual on Count_Query. It shows the use of Set_Block_Property(:System.Trigger_Block, QUERY_HITS, :control.hits). What you need to do is populate the :control.hits item in the Pre-Query trigger, then maintain it by incrementing 'on insert' and decrementing 'on delete'.

David

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151254 is a reply to message #151082]

Wed, 14 December 2005 00:15

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

Hi David,

I have seen this Count_Query. and Set_Block_Property(:System.Trigger_Block, QUERY_HITS, :control.hits) in the manual.But i didnt understand anything.

My form have only one block..IOn form level i wrote when-new-form-instance.

And in block level,i wrote already pre-query,post-query,pre-insert,key-down,when-create-record,when-new-record-instance..

The upper portion of the form should be autopopulated when form is loaded(Pls see the attached screen.)

where i have to write this one???

Now i tried the following code in pre-query and on-count


Set_Block_Property(:System.Trigger_Block,QUERY_HITS,:control.hits);

---Its giving error in :control hits as bad bin variable and must be declared.

i wrote the entire thing in on-count query taken from manual

BEGIN
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN 
    User_Exit('my_count');
    
   Set_Block_Property(:System.Trigger_Block,QUERY_HITS,:control.hits); 

  ELSE 
    Count_Query;
  END IF; 
END;

Its also giving same error.. I think :Control.hits gives count of records that hit in query..But,how to handle it..its only give error like bad variable and must be declare..what to do?

How to declare :control.hits?

i also tried


declare
hits number;
BEGIN
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN 
    User_Exit('my_count');
    
   Set_Block_Property(:System.Trigger_Block,QUERY_HITS,:control.hits); 

  ELSE 
    Count_Query;
  END IF; 
END;

same error..

but...

declare
hits number;
BEGIN
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN 
    User_Exit('my_count');
    
   Set_Block_Property(:System.Trigger_Block,QUERY_HITS,hits); 

  ELSE 
    Count_Query;
  END IF; 
END;

the above doest give error..but to get that :control.hits value and assign to other variable

becos we cant give like..

X:=Set_Block_Property(:System.Trigger_Block,QUERY_HITS,hits);

experts pls help....

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151257 is a reply to message #151222]

Wed, 14 December 2005 00:21

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

Dvid,

U said to populate :control.hits in pre-query..how to do it???

Then we have to increment or decrement that value in on-insert ot on-delete trigger???

I understand only that :control.hits gives count of records fetched in query...

Am getting nothing upon handling :control.hits ...

Again i have another doubt..in my form only one block..In some other forms multiple block is there and for each block of records record indicator is need..how to handle then...

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151450 is a reply to message #151257]

Wed, 14 December 2005 22:44

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

Senior Member
Account Moderator

Please read my previous post again. I haven't given you ALL the steps but just the basics.

You have to create a second block called 'control', add to it a non-database, non-canvas item 'hits', make it 'numeric'.

You populate 'control.hits' in the Pre-query trigger using the Count_Query and Set_Block_Property(:System.Trigger_Block, QUERY_HITS, :control.hits) commands.

Is this a student project or a work project? If student, then talk with your colleagues, if work, then talk with your colleagues. Forms development is a non-competitive activity.

David

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151471 is a reply to message #151082]

Thu, 15 December 2005 00:07

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

I asked my collegues they also didnt know..

One of my friend working in Oracle and one of my collegues said its not possible..

My TL also not helping me..I asked him logic he didnt tell me..
He told think logically and try something to find.
It seems to be he also doesnt know.

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151484 is a reply to message #151082]

Thu, 15 December 2005 01:39

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

David,

I have already one block DCIR.

U told populate to 'control.hits' in the Pre-query trigger using the Count_Query and Set_Block_Property(:System.Trigger_Block, QUERY_HITS, :control.hits) commands.

I created a control block namely control.

I believe we have to 'control.hits' in the Pre-query trigger using the Count_Query and Set_Block_Property(:System.Trigger_Block, QUERY_HITS, :control.hits) commands in the second block Control.Becoz,in DCIR block count_query gives illegal in pre-query..In post-query also its giving error..

(:System.Trigger_Block, QUERY_HITS, :control.hits) also gives some error in DCIR(both pre-query and post-query) and didnt retrieves rows..

So,I wrote


count_query;
(:System.Trigger_Block, QUERY_HITS, :control.hits);

It complied sucessfully..But not populating values into :control.hits..

Then how to populate the value..

How to assign counts to :control.hits fiels or to some other variable???

Becoz, some variable X,

X:=count_query; --- wont work.

( In Oracle Help, i saw two examples

example 1:
---------

begin
count_query;
end;

example 2:
----------

BEGIN
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN
User_Exit('my_count');
Set_Block_Property(:System.Trigger_Block,QUERY_HITS,:control.hits);
ELSE
Count_Query;
END IF;
END;

I tried both..examples)

even i wrote like..

begin
count_query;
Set_Block_Property('DCIR',QUERY_HITS,:hits); 
end;

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151612 is a reply to message #151484]

Thu, 15 December 2005 20:18

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

Senior Member
Account Moderator

Sorry, my error. I meant to say use the 'GET_Block_Property' in the Pre-Query, and use the 'SET_Block_Property' after you have changed the value in :count.hits.

BEGIN
  Message('pre-query - start');  pause;
  Message('pre-query - hits 1 =' || To_char(Nvl(:control.hits,
                                                 - 1)) || '<');  pause;
  
  count_query;
  
  Get_block_property(:System.trigger_block,
                     query_hits,
                     :control.hits);
  
  Message('pre-query - hits 2 =' || To_char(Nvl(:control.hits,
                                                 - 1)) || '<');  pause;
  Message('pre-query - end'); pause;
END;

David

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151663 is a reply to message #151612]

Fri, 16 December 2005 01:30

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

David,

U wrote

Get_block_property(:System.trigger_block,
query_hits,
:control.hits);

in the pre-query..

But,get_block_property have only 2 arguments..This is exactly set_block_property code.

Again as we know the block property anme why we need to use get_block_property?

we can use like set_block_property('DCIR',
query_hits,
:control.hits);

For which block we have to write this Pre-Query?

To see what value i spopulated in :control.hits i made it canvasable item.

I guess we should write in Pre-Query of Control Block.

Becoz,i wrote


BEGIN
  Message('pre-query - start');  pause;
  Message('pre-query - hits 1 =' || To_char(Nvl(:control.hits,
                                                 - 1)) || '<');  pause;
  
  count_query;
  
  
  set_block_property('dcir',
                     query_hits,
                     :control.hits);  */
  
  Message('pre-query - hits 2 =' || To_char(Nvl(:control.hits,
                                                 - 1)) || '<');  pause;
  Message('pre-query - end'); pause;
END;

It gave messages and acknowledges..But,also it gave error Count_query is illegal in Pre-query..Also it doesnt retrieve any records.

I tried it in Pre-Query of Control like.

Try -1
------


BEGIN
  Message('pre-query - start');  pause;
  Message('pre-query - hits 1 =' || To_char(Nvl(:control.hits,
                                                 - 1)) || '<');  pause;
  
  count_query;
  
  
  set_block_property('dcir',
                     query_hits,
                     :control.hits); 
  
  Message('pre-query - hits 2 =' || To_char(Nvl(:control.hits,
                                                 - 1)) || '<');  pause;
  Message('pre-query - end'); pause;
END;

Try-2
------


BEGIN
  Message('pre-query - start');  pause;
  Message('pre-query - hits 1 =' || To_char(Nvl(:control.hits,
                                                 - 1)) || '<');  pause;
  
  count_query;
  
  
  set_block_property('control',
                     query_hits,
                     :control.hits); 
  
  Message('pre-query - hits 2 =' || To_char(Nvl(:control.hits,
                                                 - 1)) || '<');  pause;
  Message('pre-query - end'); pause;
END;

When I press F12 to fetch records,Both codes are retrieving records.But no messages or acknowlegdes..

Or where to write this set_block_property???? We have to get_block_property of set_block_property for which block...

Sorry David..

I only understand Count_query gives count of records query fetches..BUt,how can we populate in a variable or item...i am not able to follow..

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151665 is a reply to message #151082]

Fri, 16 December 2005 01:35

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

I tried this code in Post-Query of Both DCIR and Control blocks


set_block_property(:System.trigger_block,
                     query_hits,
                     :control.hits);

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151734 is a reply to message #151663]

Sun, 18 December 2005 17:34

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

Senior Member
Account Moderator

Yes ... Get_Block_Property does only have two parameters. I was pushed for time on Friday and did not review my code properly, sorry.

Use the Get_Block_Property to GET the information you want in the Pre-Query and then you use the Set_Block_Property in the other triggers to change the value that is displayed at the bottom of the screen.

The command is therefore:
:control.hits := Get_block_property(:System.trigger_block,query_hits);

Do not put any of this code into the Post-Query as that trigger fires on every record and we KNOW that the first record that will be displayed is record number 1 of 'nn' and we got the 'nn' from the Pre-Query.

David

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151780 is a reply to message #151082]

Mon, 19 December 2005 03:43

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

David,

Count_query is not needed? Where to write it? I mean which trigger...Count_query in Pre-query trigger giving error..U r saying souldnot be written in Post-Query.Then where to use it...

Why we need to use get_block_property in Pre-query and what property we need to get? for changing value displayed at he botton of the screen?? AM confused..

Also where to write this,

:control.hits := Get_block_property(:System.trigger_block,query_hits);

becoz, u said dont write in post-query...

I wrote :control.hits := Get_block_property(:System.trigger_block,query_hits); in the when-button-pressed(OK) button to check the value of :control.hits..

After form loaded..I press <F12> to query records. Then I press button..

:control.hits having value 1..

Again am pressing down key to move next record and clicked the button then it gives 2..
There are 6 records,,As i do like this : Control.hits reaches maximum 6..

Actually what we needed is total records returned by the query i think..U only said like that..But his gives value like :system.trigger_Record...

Then u r saying use the Set_Block_Property in the other triggers to change the value that is displayed at the bottom of the screen.Am not understanding this..Can we change that?

Actually in my form there is a record indicator there i have to display what is the total record number and at which record cursor is in..That is both current record number and total record number..

Pls see the form in the attachment..in my first post..

Pls explain...

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151781 is a reply to message #151082]

Mon, 19 December 2005 03:48

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

In my form i have seperate ext item as record indicator where only i have to disply record count and totals.

Pls see the attachment

Attachment: screen.doc
(Size: 45.50KB, Downloaded 2335 times)

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #151913 is a reply to message #151781]

Mon, 19 December 2005 17:34

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

Senior Member
Account Moderator

Okay ... I reread the documentation this morning. Count_Query is a 'restricted' command, so we can't use it in either the Pre-Query or Post-Query commands. Concerning your observation that Count_Query is returning '1' for the first record, '2' for the second record, etc. This is happening because the Count_Query is being done AFTER the Execute-Query has have executed. I don't like this behaviour either but that is how Oracle Forms has been working for at least the last 10 years.

Also the item :control.hits should be Varchar2 not Numeric, and let's make it about 10 characters.

Thanks for the screen image. Would you be so kind as to tell me the names of the blocks that you are using, in order.

Now, let's try this approach. In the When-New-Form-Instance trigger put this code at the end of the existing code.

  Go_Block('blk');
  Do_Key('COUNT_QUERY');
  :control.hits := Get_block_property('blk',query_hits);

Also, please post the current contents of your WNFI trigger.

As you already have an item to store the total number of records please tell me on which block it exists and its name.

David

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #152659 is a reply to message #151082]

Mon, 26 December 2005 02:40

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

Hi David,

Am attaching all the triggers i used in the form.

There is only one block..DCIR..

Am sending triggers in attachment file.

When deleting records, key-delrec alters the record indicator accordingly.

But moving arrow-key up and down the total count not altereed.
Thats key-up trigger not working fine.

But exactly the same code working fine for other person(for other form).

Suppose am deleting one record..

the record indicator for current record changes

Record (5) of (6)

But,if i press up arrow it should come as

Record (4) of (6),Record (3) of (6),Record (2) of (6),Record (1) of (6)

But its coming as...

Record (4) of (7),Record (3) of (7),Record (2) of (7),Record (1) of (7)

Thats key-up not working....

I didnt use that count_query or query_hits here.....

Thanks,

sharan

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #152661 is a reply to message #151082]

Mon, 26 December 2005 02:45

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

hey last time i attached screen shot in screen.doc

Now its not allowing *.txt or *.doc why??????????

sorry for inconvenience..

WHEN-NEW-FORM-INSTANCE
----------------------------------------

SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW,WINDOW_STATE,MAXIMIZE);
SET_WINDOW_PROPERTY('ROOT_WINDOW',WINDOW_STATE,MAXIMIZE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.APPLICATION',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.DIRECTOR_DETAIL',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.PARTICIPANT_DETAIL',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.EXEMPT',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.LICENSE',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.COMPLAINTS',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.ADVERSE_ACTIONS',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.HISTORY',VISIBLE,PROPERTY_FALSE);

:SYSTEM.MESSAGE_LEVEL:=10;
GO_ITEM('DCIR_D');

PRE-INSERT
-----------

:DCIR.PROV_C:=:global.prov_c;
:DCIR.prov_c_ext:=:global.prov_c_ext;
select nvl(max(DCIR_C_SRNO),0)+1 into :DCIR.DCIR_C_SRNO from dcir where :DCIR.PROV_C=:global.prov_c and
:DCIR.prov_c_ext=:global.prov_c_ext;

PRE-QUERY
---------

:DCIR.PROV_C:=:global.prov_c;
:DCIR.prov_c_ext:=:global.prov_c_ext;

WHEN-NEW-RECORD-INSTANCE
-------------------------

go_item('dcir_d');

POST-QUERY
----------
BEGIN
SELECT COUNT(1) INTO :DCIR.IND2 FROM DCIR WHERE PROV_C =:GLOBAL.PROV_C AND PROV_C_EXT =:GLOBAL.PROV_C_EXT;
--:DCIR.IND1:=:SYSTEM.CURSOR_RECORD;
:IND1:=:system.trigger_Record;
SELECT PROV_N,PROV_L_PH,PROV_L_CITY,PROV_L_ST,PROV_L_ZIP INTO
:DCIR.NBT_PROV_N,:DCIR.NBT_PROV_L_PH,:DCIR.NBT_PROV_L_CITY,:DCIR.NBT_PROV_L_ST,:DCIR.NBT_PROV_L_ZIP FROM PROV
WHERE PROV_C =:GLOBAL.PROV_C AND PROV_C_EXT =:GLOBAL.PROV_C_EXT;
SELECT 'provider@ini.com','provider_work@ini.com' INTO :DCIR.NBT_PROV_L_EMAIL,:DCIR.NBT_PROV_L_WORKER_EMAIL FROM DUAL;

:NBT_DCIR_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

EXCEPTION
WHEN NO_DATA_FOUND THEN
:DCIR.NBT_PROV_N:='PROVIDER1';
:DCIR.NBT_PROV_L_PH:='(201)601-1234';
:DCIR.NBT_PROV_L_CITY:='JESPER';
:DCIR.NBT_PROV_L_ST:='AL';
:DCIR.NBT_PROV_L_ZIP:=12345;
:DCIR.NBT_PROV_L_EMAIL:='provider@ini.com';
:DCIR.NBT_PROV_L_WORKER_EMAIL:='provider_work@ini.com';

END;

WHEN-CREATE-RECORD
------------------

:global.prov_c:=1;
:global.prov_c_ext:=0;

BEGIN
:DCIR.PROV_C:=:GLOBAL.PROV_C;
:DCIR.PROV_C_EXT:=:GLOBAL.PROV_C_EXT;

SELECT NVL(MAX(DCIR_C_SRNO),0)+1 INTO :DCIR.DCIR_C_SRNO FROM DCIR WHERE :DCIR.PROV_C=:GLOBAL.PROV_C AND
:DCIR.PROV_C_EXT =:GLOBAL.PROV_C_EXT;

END;
:DCIR.IND1:=:SYSTEM.CURSOR_RECORD;

IF :IND1=0 THEN
:IND1:=1;
END IF;
SELECT COUNT(*)+1 INTO :IND2
FROM DCIR
WHERE
PROV_C=:global.PROV_C
AND PROV_C_EXT=:global.PROV_C_EXT;

/*

IF :IND2<:IND1 THEN
:IND2:=:IND1;
END IF;

*/

:NBT_DCIR_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

BEGIN
SELECT PROV_N,PROV_L_PH,PROV_L_EMAIL,PROV_L_WORKER_EMAIL,PROV_L_CITY,PROV_L_ST,PROV_L_ZIP,PROV_C_WORKER INTO
:DCIR.NBT_PROV_N,:DCIR.NBT_PROV_L_PH,:DCIR.NBT_PROV_L_EMAIL,:DCIR.NBT_PROV_L_WORKER_EMAIL,:DCIR.NBT_PROV_L_CITY,
:DCIR.NBT_PROV_L_ST,:DCIR.NBT_PROV_L_ZIP,:DCIR.NBT_DCIR_C_RECV_BY FROM PROV
WHERE PROV_C =:global.PROV_C AND PROV_C_EXT =:global.PROV_C_EXT;

--SELECT VRCD_X INTO :NBT_DCIR_N_RECV_BY FROM VRCD WHERE VRCD_C=:DCIR.NBT_DCIR_N_RECV_BY AND VRCD_C_TABLE='ROLE';

SELECT 'provider@ini.com','provider_work@ini.com' INTO :DCIR.NBT_PROV_L_EMAIL,:DCIR.NBT_PROV_L_WORKER_EMAIL FROM DUAL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
--:DCIR.NBT_PROV_N:='PROVIDER1';
--:DCIR.NBT_PROV_L_PH:='(201)601-1234';
--:DCIR.NBT_PROV_L_CITY:='JESPER';
--:DCIR.NBT_PROV_L_ST:='AL';
--:DCIR.NBT_PROV_L_ZIP:=12345;
--:DCIR.NBT_PROV_L_EMAIL:='provider@ini.com';
--:DCIR.NBT_PROV_L_WORKER_EMAIL:='provider_work@ini.com';
END;

key-delrec
----------

DECLARE
NO NUMBER;
BEGIN
NO:=SHOW_ALERT('ALERT_DELETE');

IF NO=ALERT_BUTTON1 THEN
DELETE_RECORD;
COMMIT_FORM;
CLEAR_MESSAGE;

SELECT COUNT(*) INTO :IND2 FROM DCIR
WHERE PROV_C =:DCIR.PROV_C AND PROV_C_EXT =:DCIR.PROV_C_EXT;
:IND1 := :IND2;

--:NBT_PVIV_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

key-up
-------

DECLARE
CNT1 NUMBER;
BEGIN

SELECT COUNT(*) INTO CNT1
FROM DCIR
WHERE PROV_C =:DCIR.PROV_C AND PROV_C_EXT =:DCIR.PROV_C_EXT;

IF CNT1=0 THEN
:IND2:=1;
ELSE
:IND2:=CNT1;
END IF;
:IND1:=:SYSTEM.CURSOR_RECORD;

IF :SYSTEM.CURSOR_RECORD =1 THEN

MESSAGE('First Record');
RAISE FORM_TRIGGER_FAILURE;
END IF;

UP;

END;

:NBT_DCIR_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

key-down
--------

DECLARE
CNT NUMBER;
BEGIN

SELECT COUNT(*) INTO CNT
FROM DCIR
WHERE PROV_C =:DCIR.PROV_C AND PROV_C_EXT =:DCIR.PROV_C_EXT;
IF CNT=0 THEN
:IND2:=1;
ELSE
:IND2:=CNT;
END IF;

IF :SYSTEM.LAST_RECORD <> 'TRUE' THEN
NEXT_RECORD;
:IND1:=:SYSTEM.CURSOR_RECORD;
ELSE
:IND1:=:SYSTEM.CURSOR_RECORD;
IF :IND1>:IND2 THEN
:IND2:=:IND1;
END IF;

MESSAGE('Last Record Has Been Reached');
raise form_trigger_failure;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

:NBT_DCIR_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #152774 is a reply to message #152659]

Tue, 27 December 2005 00:25

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

Senior Member
Account Moderator

If you only have one block then I assume that IND1 and IND2 are also in this block. Is this correct?

David

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #152782 is a reply to message #151082]

Tue, 27 December 2005 00:43

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

Yes.........

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #153482 is a reply to message #152782]

Mon, 02 January 2006 19:27

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

Senior Member
Account Moderator

I suggest that you put these two counters (IND1 and IND2) on their own block. Because you created them on each row, when you delete a row the remaining rows still have their initial information and unless you refresh each of the remaining rows then they will display incorrect information. Use the 'current_record' property to populate the 'IND1' field and after initially populating 'IND2' however you wish, then decrement or increment it as you delete and insert new entries.

David

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #153519 is a reply to message #151082]

Mon, 02 January 2006 23:50

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

Hi David,

Happy New year..

I added key-delrec trigger and i changed record_status in when-new-record instance..I did it last week and it was working fine...Again they are changing columns and functionality..They are working on requirement doc. again.

Am writting entire code here

When-new-form-instance
----------------------

SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW,WINDOW_STATE,MAXIMIZE);
SET_WINDOW_PROPERTY('ROOT_WINDOW',WINDOW_STATE,MAXIMIZE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.APPLICATION',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.DIRECTOR_DETAIL',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.PARTICIPANT_DETAIL',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.EXEMPT',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.LICENSE',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.COMPLAINTS',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.ADVERSE_ACTIONS',VISIBLE,PROPERTY_FALSE);
SET_MENU_ITEM_PROPERTY('PROVIDER_MENU.HISTORY',VISIBLE,PROPERTY_FALSE);

:SYSTEM.MESSAGE_LEVEL:=10;
GO_ITEM('DCIR_D');

Pre-query
---------

:DCIR.PROV_C:=:global.prov_c;
:DCIR.prov_c_ext:=:global.prov_c_ext;

Pre-Insert
----------

:DCIR.PROV_C:=:global.prov_c;
:DCIR.prov_c_ext:=:global.prov_c_ext;
select nvl(max(DCIR_C_SRNO),0)+1 into :DCIR.DCIR_C_SRNO from dcir where :DCIR.PROV_C=:global.prov_c and
:DCIR.prov_c_ext=:global.prov_c_ext;

key-down
--------

DECLARE
CNT NUMBER;
BEGIN

SELECT COUNT(*) INTO CNT
FROM DCIR
WHERE PROV_C =:DCIR.PROV_C AND PROV_C_EXT =:DCIR.PROV_C_EXT;
IF CNT=0 THEN
:IND2:=1;
ELSE
:IND2:=CNT;
END IF;

IF :SYSTEM.LAST_RECORD <> 'TRUE' THEN
NEXT_RECORD;
:IND1:=:SYSTEM.CURSOR_RECORD;
ELSE
MESSAGE('Last Record Has Been Reached');
raise form_trigger_failure;
:IND1:=:SYSTEM.CURSOR_RECORD;
IF :IND1>:IND2 THEN
:IND2:=:IND1;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

:NBT_DCIR_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

post-query
----------

BEGIN
SELECT COUNT(1) INTO :DCIR.IND2 FROM DCIR WHERE PROV_C =:GLOBAL.PROV_C AND PROV_C_EXT =:GLOBAL.PROV_C_EXT;
--:DCIR.IND1:=:SYSTEM.CURSOR_RECORD;
:IND1:=:system.trigger_Record;

SELECT PROV_N,PROV_L_PH,PROV_L_EMAIL,PROV_L_WORKER_EMAIL,PROV_L_CITY,PROV_L_ST,PROV_L_ZIP,PROV_C_WORKER INTO
:DCIR.NBT_PROV_N,:DCIR.NBT_PROV_L_PH,:DCIR.NBT_PROV_L_EMAIL,:DCIR.NBT_PROV_L_WORKER_EMAIL,:DCIR.NBT_PROV_L_CITY,
:DCIR.NBT_PROV_L_ST,:DCIR.NBT_PROV_L_ZIP,:DCIR.NBT_DCIR_C_RECV_BY FROM PROV
WHERE PROV_C =:global.PROV_C AND PROV_C_EXT =:global.PROV_C_EXT;

SELECT VRCD_X INTO :NBT_DCIR_N_RECV_BY FROM VRCD WHERE VRCD_C=:DCIR.NBT_DCIR_N_RECV_BY AND VRCD_C_TABLE='ROLE';

:NBT_DCIR_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;

END;

When-create-record
------------------

:global.prov_c:=1;
:global.prov_c_ext:=0;

BEGIN
:DCIR.PROV_C:=:GLOBAL.PROV_C;
:DCIR.PROV_C_EXT:=:GLOBAL.PROV_C_EXT;

SELECT NVL(MAX(DCIR_C_SRNO),0)+1 INTO :DCIR.DCIR_C_SRNO FROM DCIR WHERE :DCIR.PROV_C=:GLOBAL.PROV_C AND
:DCIR.PROV_C_EXT =:GLOBAL.PROV_C_EXT;

END;

:DCIR.IND1:=:SYSTEM.CURSOR_RECORD;

IF :IND1=0 THEN
:IND1:=1;
END IF;
SELECT COUNT(*)+1 INTO :IND2
FROM DCIR
WHERE
PROV_C=:global.PROV_C
AND PROV_C_EXT=:global.PROV_C_EXT;

:NBT_DCIR_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

BEGIN
SELECT PROV_N,PROV_L_PH,PROV_L_EMAIL,PROV_L_WORKER_EMAIL,PROV_L_CITY,PROV_L_ST,PROV_L_ZIP,PROV_C_WORKER INTO
:DCIR.NBT_PROV_N,:DCIR.NBT_PROV_L_PH,:DCIR.NBT_PROV_L_EMAIL,:DCIR.NBT_PROV_L_WORKER_EMAIL,:DCIR.NBT_PROV_L_CITY,
:DCIR.NBT_PROV_L_ST,:DCIR.NBT_PROV_L_ZIP,:DCIR.NBT_DCIR_C_RECV_BY FROM PROV
WHERE PROV_C =:global.PROV_C AND PROV_C_EXT =:global.PROV_C_EXT;

SELECT VRCD_X INTO :NBT_DCIR_N_RECV_BY FROM VRCD WHERE VRCD_C=:DCIR.NBT_DCIR_N_RECV_BY AND VRCD_C_TABLE='ROLE';

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;

END;

when-new-record-instance
------------------------
go_item('dcir_d');

if :system.form_status NOT IN ('NEW') then
SELECT COUNT(1) INTO :DCIR.IND2 FROM DCIR WHERE PROV_C =:GLOBAL.PROV_C AND PROV_C_EXT =:GLOBAL.PROV_C_EXT;
:ind1:=:system.cursor_Record;
end if;

:NBT_DCIR_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

if :system.RECORD_status IN ('NEW','INSERT') then
set_record_property(1,'DCIR',STATUS,QUERY_STATUS);
END IF;

key-delrec
----------

DECLARE
NO NUMBER;
BEGIN
NO:=SHOW_ALERT('ALERT_DELETE');
IF NO=ALERT_BUTTON1 THEN
DELETE_RECORD;
POST;
SELECT COUNT(*)-1 INTO :IND2
FROM DCIR
WHERE PROV_C =:DCIR.PROV_C AND PROV_C_EXT =:DCIR.PROV_C_EXT;

:IND1 := :IND2;
END IF;
END;

key-Up
------

DECLARE
CNT1 NUMBER;
BEGIN

SELECT COUNT(*)-1 INTO CNT1
FROM DCIR
WHERE PROV_C =:DCIR.PROV_C AND PROV_C_EXT =:DCIR.PROV_C_EXT;
IF CNT1=0 THEN
:IND2:=1;
ELSE
:IND2:=CNT1;
END IF;
:IND1:=:SYSTEM.CURSOR_RECORD;

IF :SYSTEM.CURSOR_RECORD =1 THEN

MESSAGE('First Record');
RAISE FORM_TRIGGER_FAILURE;
END IF;

UP;

END;

:NBT_DCIR_X_RECORD:='Record ( '||:IND1||' ) of ( '||:IND2||' )';

But,am not understanding ur suggession 'current_record' property to populate the 'IND1' field and after initially populating 'IND2'..Where to write it and how? Pls tell..

.....Sharan

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #153712 is a reply to message #153519]

Tue, 03 January 2006 19:57

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

Senior Member
Account Moderator

Please use the full name for all your items. They should be 'block.item', not just 'item'. That is,

GO_ITEM('DCIR_D');

should be

GO_ITEM('DCIR.DCIR_D');

and

:IND2:=1;

should be

:DCIR.IND2:=1;

Please read the documentation on the 'get_block_property' command's 'current_record' attribute. It is this function that you should be using to populate 'IND1'.

You are always populating 'IND2' via a 'select' statement. You should be able to populate it once at the beginning of your form and then just increment and decrement it in the various triggers.

David

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #153731 is a reply to message #151082]

Tue, 03 January 2006 22:07

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

Ya..am incrementing and decrementing 'IND1' in various triggers..There will be any issue or harm in using like that? If Yes, What is the alternative..Sugession Pls....

R U telling to increment 'IND2' like :DCIR.IND2:=:DICR.IND2+1 in the WHEN-CREATE-RECORD(iT SHOULD BE INCREMENTED IN WHEN-CREATE-RECORD OR WHEN-NEW-RECORD-INSTANCE??) and to decrement in the key-delrec like :DCIR.IND2:=:DICR.IND2-1

Am i rite????

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #153739 is a reply to message #153731]

Tue, 03 January 2006 22:21

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

Senior Member
Account Moderator

You are not incrementing or decrementing IND1, you are setting it to 'system.cursor_record'. I suggest that you place the IND1 and IND2 in a separate block and that you populate IND1 using the 'get_block_property' command's 'current_record' attribute.

Yes ... increment IND2, but on a separate block, NOT the 'DCIR' block, in the WHEN-CREATE-RECORD trigger and decrement it when you delete a record.

David

[Updated on: Tue, 03 January 2006 22:23]

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #153744 is a reply to message #151082]

Tue, 03 January 2006 22:40

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

Sorry..Typing mistake..Am incrementing and decrementing 'IND2' only.

U r telling to keep 'IND1' and 'IND2' in another block and.... to increment 'IND2' and decrement 'IND2' and populate IND1 using the 'get_block_property' command's 'current_record' attribute.

Ok..I will do..

Right now they are altering columns(Changing functional and reqrmt docs also) so its giving oracle error.After that job completes,they will give it and i do the changes..

But,Pls tell me...Now its working fine and u r sugessing changes..But,I like to know what problem or issue i would face if i have this type of codings..If u tell only, i can avoid in future forms..Without knowing the reason what for how can i change???..

....Sharan

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #153745 is a reply to message #153744]

Tue, 03 January 2006 22:49

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

Senior Member
Account Moderator

For me, the main problem is you have been handling IND1 and IND2 on EACH record. For me, they should be singularly occurring fields and as they are NOT related to the DATA of your main block. As such, place them in a SEPARATE block. This block could be the 'GLOBAL' block but I MUCH prefer to have a 'CTRL' block in my form, and this non-database block holds my various 'this form only' working variables.

The problem with your style of coding is that you are going to the database too often. Try to keep your database accesses to a minimum. Also, try to use the Form facilities as much as you can so that you don't have to write your own code. The smaller the code the better.

David

Report message to a moderator

Re: How to get record number and total record number in record indicator? [message #637773 is a reply to message #151082]

Sun, 24 May 2015 05:38

tofajjalhnipu66@gmail.com
Messages: 18
Registered: May 2015
Location: Chittagong,Bangladesh

Junior Member

How to make it ?

please ans me details ...

Regards

Report message to a moderator

Previous Topic:	Restrict number of record in Form6i
Next Topic:	Record has been updated by another user. Re-query to see change

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 17:51:46 CST 2025