OraFAQ Forum: Forms » Record 1/? "Counter"

Home » Developer & Programmer » Forms » Record 1/? "Counter" (Oracle Forms 10.1.2.3)

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

Record 1/? "Counter" [message #440541]

Mon, 25 January 2010 08:34

Kevin58
Messages: 79
Registered: January 2009

Member

I been looking high and low for a way to get the default record counter, at the bottom of the window, to provide how many records have been returned by any query.

You know the record 1/? or record 1/10 at the bottom.

I don't want to do an:
execute_query;
last_record;
first_record;

Nor do I want to set the "query All Records" property of the data block to "Yes".

Both result in the form opening up way too slow. I've a table with a total of 29500+ rows.

Any ideas?

[MERGED by LF]

[Updated on: Thu, 22 April 2010 01:02] by Moderator

Report message to a moderator

Re: Record 1/? "Counter" [message #440547 is a reply to message #440541]

Mon, 25 January 2010 09:05

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

I don't believe there is any way to get forms to fill in that particular number until all records have been retrieved.
Is the count_query builtin of any use?

Report message to a moderator

Re: Record 1/? "Counter" [message #440550 is a reply to message #440547]

Mon, 25 January 2010 09:11

Kevin58
Messages: 79
Registered: January 2009

Member

I have another form that only deals with 48 row table.
I put in a non-database control block with an item called hits.

Then I created a pre-query trigger with this code:
:control.hits := get_block_property('matl_intended_use', query_hits);

Along with a WNFI trigger that has:
--count_query;
execute_query;

Notice that I commented out the "count_query" built-in.

This seems to work just fine with the built in "record counter".

I'm confused why it works with tables with fewer rows.

Other ideas? Smile

Smile

Report message to a moderator

Re: Record 1/? "Counter" [message #445449 is a reply to message #440550]

Mon, 01 March 2010 23:38

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

Senior Member
Account Moderator

Sorry that I have been so long in replying. Have you solved your problem?

In the 'pre-query' trigger execute 'count_query'.

David

Report message to a moderator

Re: Record 1/? "Counter" [message #445528 is a reply to message #440541]

Tue, 02 March 2010 06:08

Kevin58
Messages: 79
Registered: January 2009

Member

Hi Dave,

Sorry, I should have updated this thread.

According to the Oracle Tech Support, there is no way to
get or set the record counter.

Except by doing a last_record call or setting one of the data block properties to retrieve all records.

Both of those solutions make my form load extremely slow.

I will have to come up with another solution and just ignore
the record counter.

Thanks for checking back!

Kevin

Report message to a moderator

Re: Record 1/? "Counter" [message #447394 is a reply to message #445528]

Mon, 15 March 2010 00:59

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

Senior Member
Account Moderator

Did you TRY running the 'count_query' command in the 'Pre-Query' trigger?

I don't give specific information in a reply just to waste my and your time!

David

Report message to a moderator

Re: Record 1/? "Counter" [message #447937 is a reply to message #447394]

Thu, 18 March 2010 10:19

Kevin58
Messages: 79
Registered: January 2009

Member

Hi Dave,

Sorry I missed your suggestion earlier.
I do appreciate your time and expertise.

I tried to place count_query in the pre-query trigger.
It give an error: FRM-40737: Illegal restricted procedure
COUNT_QUERY in the pre-query trigger.

I get the same error whether the pre-query trigger is at the form level or at my main data block level.

Any other ideas?
Thanks,

Kevin

Report message to a moderator

Re: Record 1/? "Counter" [message #448937 is a reply to message #447937]

Thu, 25 March 2010 22:37

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

Senior Member
Account Moderator

My bad. I keep on making this error time and time again. Go to http://www.orafaq.com/forum/mv/msg/56366/151913/67467/#msg_151913 where I believe I got this right.

"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);

Does this work for you? Also have a good look at the example for 'count_query' in the documentation.

David

Report message to a moderator

Re: Record 1/? "Counter" [message #450175 is a reply to message #448937]

Mon, 05 April 2010 11:07

Kevin58
Messages: 79
Registered: January 2009

Member

Hi David,

I tried your count_query suggestion.

What happens is that the form will show:
"FRM-40355: Query will retrieve 29528 records."

But then I have to press the execute query button.

I added the execute_query built-in just after your code.

So now the form still shows the number of records it will retrieve
and display the first record.

However, the counter at the bottom still shows:
"Record: 1/?"

Here is the last lines of my WNFI trigger:
=====================
Go_Block('sample');
Do_Key('COUNT_QUERY');
:control.hits := Get_Block_Property('sample', query_hits);

do_key('EXECUTE_QUERY');
=====================

It appears that we cannot get this to work. Unfortunate.

The users are counting on being able to know how many records they have selected when they query with or without a filter.
Thanks for your help.

Kevin

Report message to a moderator

Re: Record 1/? "Counter" [message #450180 is a reply to message #450175]

Mon, 05 April 2010 11:58

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

Senior Member
Account Moderator

Quote:

the counter at the bottom still shows: "Record: 1/?"

So what? Number of records to be fetched is stored and displayed in a form item called ":control.hits".

Report message to a moderator

Re: Record 1/? "Counter" [message #450184 is a reply to message #450180]

Mon, 05 April 2010 12:17

Kevin58
Messages: 79
Registered: January 2009

Member

The point is that the "Record 1/?" indicator at the bottom
never gets updated until one goes to the last record in the query.

So as far as our users are concerned the "Record 1/?" is of no use.

There is always the possibility to program our own record counter.
That should not be a problem.

However, we were trying to use what the system already could display. It does not take up any real estate on the form, and is in a standard location.
Thanks,

Kevin

Report message to a moderator

Re: Record 1/? "Counter" [message #450222 is a reply to message #450184]

Mon, 05 April 2010 15:10

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

Senior Member
Account Moderator

Well, perhaps your users could do that in two steps: first step would be entering query mode, entering search criteria, going to form menu and counting query hits. Next step would be to actually perform query in order to fetch records.

Report message to a moderator

Re: Record 1/? "Counter" [message #451330 is a reply to message #450222]

Wed, 14 April 2010 01:32

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

Senior Member
Account Moderator

Put the following into the 'KEY-EXEQRY' trigger.

begin
  count_query;
  execute_query;
end;

David

Report message to a moderator

Re: Record 1/? "Counter" [message #451367 is a reply to message #440541]

Wed, 14 April 2010 03:35

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

I thought we'ed established that this particular problem wasn't fixable?
OP knows there are other methods of displaying the count and he can use them if he wants.

Report message to a moderator

Re: Record 1/? "Counter" [message #451374 is a reply to message #451367]

Wed, 14 April 2010 04:13

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

Senior Member
Account Moderator

David's suggestion looks pretty as far as the first record is concerned:

/forum/fa/7702/0/

/forum/fa/7702/0/

However, the second (and any other, up to the last record) is useless as there's (again) "2 / ?" there, and no "total number of records".

/forum/fa/7703/0/

/forum/fa/7703/0/

Attachment: q1.PNG
(Size: 1.91KB, Downloaded 2431 times)
Attachment: q2.PNG
(Size: 1.29KB, Downloaded 2470 times)

Report message to a moderator

Re: Record 1/? "Counter" [message #451403 is a reply to message #440541]

Wed, 14 April 2010 07:21

Kevin58
Messages: 79
Registered: January 2009

Member

I like David's suggestion.
I also get the same result as littlefoot.

Kind of makes you wonder why have the "counter" at all.
I hope 11g has methods to make it show the total count.

Thanks for checking out more ideas!

Kevin

Report message to a moderator

Re: Record 1/? "Counter" [message #451474 is a reply to message #451403]

Thu, 15 April 2010 00:56

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

Senior Member
Account Moderator

My apologies for dragging this thread on and on. The group I was working with in 1996-1997 also was tasked with trying to solve this problem. I can't remember the final solution but considering that Oracle hasn't changed forms that much since then, that we either built our own status line or re-educated the user and left the total somewhere else on the screen.

Also, have a look at the behaviour of the number initially represented by '?' as you get to the last row and then go down to the next (new) row, and then move back up and down and up - the value changes. This could also be confusing to your users.

I just did a test on Forms 4.5 and there is only a single number which represents the number of records so far retrieved. Not much help but at least things have changed a bit since then.

David

Report message to a moderator

Counting Records [message #452345 is a reply to message #440541]

Wed, 21 April 2010 08:44

Kevin58
Messages: 79
Registered: January 2009

Member

Hi All,

I'm working on creating our own record counter for display
on a form.

I am using this technique with WNFI, KEY-ENTQRY, and
KEY-EXEQRY.

Code fragment end of WNFI trigger:
Go_Block('sample');
--Do_Key('COUNT_QUERY');
--:control.hits := Get_Block_Property('sample', query_hits);

do_key('EXECUTE_QUERY');

KEY-EXEQRY:
Declare
blk_id Block;

begin
blk_id := Find_Block('SAMPLE');
count_query;
:control.hits := Get_Block_Property(blk_id, QUERY_HITS);
execute_query;
end;

KEY-ENTQRY:
:CONTROL.HITS := NULL;
ENTER_QUERY;

When the form launches it displays the correct record count
in the display item named :control.hits.

The issues is that when I try to query anything afterwards,
The :count.hits becomes 0 (zero). Even though the message at the bottom shows "FRM-40355: Query will retrieve 2550 records."

Why does this work on startup but not during a query?
Thanks,

Kevin

Report message to a moderator

Re: Counting Records [message #452389 is a reply to message #452345]

Wed, 21 April 2010 15:39

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

Senior Member
Account Moderator

What happens if you put "synchronize" here?

:control.hits := Get_Block_Property(blk_id, QUERY_HITS);
synchronize;

Report message to a moderator

Re: Counting Records [message #452500 is a reply to message #452389]

Thu, 22 April 2010 07:16

Kevin58
Messages: 79
Registered: January 2009

Member

Still behaves the same as before.

Curious why my new subject "Counting Records" has been placed at the end of the previous subject "Record 1/? Counter"?

This subject has to do with creating my own record counter and nothing to do with the internal "Forms Counter".

Wouldn't this make it harder for others to respond. Especially if they seen that a thread may have been closed?
Thanks,

Kevin

[Updated on: Thu, 22 April 2010 07:25]

Report message to a moderator

Re: Record 1/? "Counter" [message #452502 is a reply to message #440541]

Thu, 22 April 2010 07:26

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Stick a message to display the result of the get_block_property call where LF told you to put the synchronize. That we we'll know if that's giving the wrong answer or something else is resetting your item.

Report message to a moderator

Re: Counting Records [message #452504 is a reply to message #452500]

Thu, 22 April 2010 07:31

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Kevin58 wrote on Thu, 22 April 2010 13:16

Curious why my new subject "Counting Records" has been placed at the end of the previous subject "Record 1/? Counter"?

This subject has to do with creating my own record counter and nothing to do with the internal "Forms Counter".

Because someone decided it logically followed on from the other thread, and if someone's looking for a solution to this problem - forms counter not doing what you want, so come up with an alternative - it'll make sense to have it in the same place.

Kevin58 wrote on Thu, 22 April 2010 13:16

Wouldn't this make it harder for others to respond. Especially if they seen that a thread may have been closed?

I'd be surprised if it makes any difference.

Report message to a moderator

Re: Record 1/? "Counter" [message #452510 is a reply to message #440541]

Thu, 22 April 2010 07:48

Kevin58
Messages: 79
Registered: January 2009

Member

Here is my updated Key-EXEQRY trigger code:

Declare
	blk_id Block;

begin
	blk_id := Find_Block('SAMPLE');
	count_query;
	:control.hits := Get_Block_Property(blk_id, QUERY_HITS);
	message('Value of :control.hits: ' || :control.hits);
	synchronize;
	execute_query;
end;

On the initial launch I get an message box that has the same
info as was placed at the bottom of the form telling me how many records will be retrieved. "FRM-40355: Query will retrieve 29528 records."

After I press OK, then the message line shows my message
"Value of :control.hits: 29528".

Any subsequent queries will show the "FRM-40355" message with the number of records to be retrieved. But the control.hits value changes to 0 (zero). Odd.

CM: Added [code] tags.

[Updated on: Thu, 22 April 2010 08:25]

Report message to a moderator

Re: Record 1/? "Counter" [message #452520 is a reply to message #440541]

Thu, 22 April 2010 07:59

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Is the message showing that control_hits is zero, or is that from looking at the item on the screen?
It's not clear from your post.

And can you please start using code tags for code - see the forum guide for details.

Report message to a moderator

Re: Record 1/? "Counter" [message #452534 is a reply to message #440541]

Thu, 22 April 2010 08:35

Kevin58
Messages: 79
Registered: January 2009

Member

Hi CM,

Thanks for the info about the code tags.
That will make it easier for all to see/read.

The popup message box is the normal response from
forms that says "FRM-40355: Query will return 2550 records.".
After I press the "OK" button then I get my message.

Then my message appears in the status line at the bottom of the form. "Value of :control.hits: 0".

The "control" block is not a database data block.

The item :control.hits is defined with:
Data Type: Number
Data Length Semantics: Null
Initial Value: is blank
Format Mask: 999,990

Report message to a moderator

Re: Record 1/? "Counter" [message #452535 is a reply to message #440541]

Thu, 22 April 2010 08:44

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Looks like the problem is get_block_propery but lets be sure.
Replace this:

	:control.hits := Get_Block_Property(blk_id, QUERY_HITS);
	message('Value of :control.hits: ' || :control.hits);

With:

	message('Query hits result: ' || Get_Block_Property(blk_id, QUERY_HITS));

Report message to a moderator

Re: Record 1/? "Counter" [message #452542 is a reply to message #440541]

Thu, 22 April 2010 09:14

Kevin58
Messages: 79
Registered: January 2009

Member

Here is the code for KEY-EXEQRY:

Declare
  blk_id Block;
begin
  blk_id := Find_Block('SAMPLE');
  count_query;
  --:control.hits := Get_Block_Property(blk_id, QUERY_HITS);
  message('Value of :control.hits: ' || Get_Block_Property(blk_id, QUERY_HITS));
  synchronize;
  execute_query;
end;

Again, I get the message box and status line on startup with
the correct number of records.

However, any subsequent queries shows the message box with
the correct number of records, the FRM-40355 info, however
the status line will show only "Value of :control.hits: 0".

It is like the built-in function Get_Block_Property has a bug.

Kevin

Report message to a moderator

Re: Record 1/? "Counter" [message #452550 is a reply to message #440541]

Thu, 22 April 2010 09:52

Kevin58
Messages: 79
Registered: January 2009

Member

I have found a solution on Metalink.

It says to create a ON-COUNT trigger with this code:

DECLARE 
   the_hits  VARCHAR2(10); 
   blk_id Block;
BEGIN 
   IF :SYSTEM.MODE = 'ENTER-QUERY' THEN 
      blk_id := Find_Block('SAMPLE');
      --MESSAGE(:SYSTEM.MODE); 
      COUNT_QUERY; 
      :CONTROL.HITS := GET_BLOCK_PROPERTY(blk_id, QUERY_HITS);
      --MESSAGE('retrieved ' || the_hits || ' query hits.'); 
   END IF; 
END;

The KEY-EXEQRY trigger is now:

begin
  count_query;
  execute_query;
end;

Now I am getting accurate record counts to display!
Thanks for sharing your debugging techniques!

Kevin

Report message to a moderator

Re: Record 1/? "Counter" [message #452552 is a reply to message #440541]

Thu, 22 April 2010 10:03

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Thanks for the feedback.

Report message to a moderator

Previous Topic:	Print an Oracle Report 10g using UNIX
Next Topic:	Delete detail block single record

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 02:21:09 CDT 2024