OraFAQ Forum: Forms » help of forms 6i

Home » Developer & Programmer » Forms » help of forms 6i (oracle developer forms 6i Forms [32 Bit] Version 6.0.8.27.0 )

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

help of forms 6i [message #622824]

Sun, 31 August 2014 09:04

kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA

Junior Member

Hello All,

I am trying to achieve a simple thing but feeling complex in terms of making it actually happen in forms.

1. User when clicks on a form, a query find window appears
2. User provides value of column1 (required column in the query find window) and other 3 column values (optional)
3. Find button should open up a multi record form (another block in the same form) with the query results
4. The multi record block has 2 check boxes also which are blank initially for most the ones. But for some (depending on the database column value of another item, these 2 check boxes should be shown checked). Eg. If table.column3 is not null, then the two check boxes should be shown checked.
I have written this logic in the post-query of the multi-record block, a cursor from the table, looping the cursor and comparing the value of the block record items. If table record value = block record value, then make the check box checked.
5. The control block has a button 'generate' when clicked should show the number of records having the checkboxes checked.
If the step4 has 20 records checked, the step5 generate should show the count as 20.
I am again looping from first_record till :system.last_record = 'TRUE', if the block.checkbox1 = y and :block.checkbox2 = y then count:= count+1; finally end of the loop, I am displaying the count.

For some reason, the count always shows incorrectly and always less than the expected numbers.
If the table has 20 rows satisfying the condition, the count in the 'generate' shows only 3. Not sure what is going wrong. I am resetting the value of the count in the beginning of the 'generate' code. Any clues are much much appreciated.

Thanks & Regards,
Kiran

Report message to a moderator

Re: help of forms 6i [message #622832 is a reply to message #622824]

Sun, 31 August 2014 10:12

kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA

Junior Member

This seems the post-query is setting check box checked only for the first 5 or 6 records, in spite of having 535 records matching the criteria.
What logic should go in to post-query ? I only have the two statements - :block.checkbox1 = y and :block.checkbox2 = y
This seems to be setting only 5 or 6 ?

Report message to a moderator

Re: help of forms 6i [message #622835 is a reply to message #622832]

Sun, 31 August 2014 10:31

kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA

Junior Member

post-query trigger at the multi-record block

CURSOR my_cur IS
SELECT col1,col2
FROM table1
WHERE col3 is null;

begin

FOR default_my_cur in my_cur
LOOP

IF (default_my_cur.col1 = :block.col1 AND default_my_cur.col2 = :block.col2)
THEN
:block.checkbox1 = 'Y';
:block.checkbox2 = 'Y';

v_count := v_count + 1;
END IF;
END LOOP;
end;

Will this logic in the post-query loop for only few records and not for all the queried records of this block ?

Report message to a moderator

Re: help of forms 6i [message #622836 is a reply to message #622832]

Sun, 31 August 2014 10:37

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

Senior Member
Account Moderator

Step 4 is wrong. A loop makes things slow - mind you, it fires once per every record you fetch, so you are performing 500 loops - the one looping through 1 record, the second one through 2 records, ... All you need (in POST-QUERY trigger) is this:

if :block.column3 is not null then
   :block.checkbox1 := 1;
   :block.checkbox2 := 1;

   :control_block.generate_count := :control_block.generate_count + 1;
end if;

(Reset :control_block.generate_count to 0 in PRE-QUERY trigger).

Report message to a moderator

Re: help of forms 6i [message #622840 is a reply to message #622836]

Sun, 31 August 2014 11:00

kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA

Junior Member

Really ?? Ohhh... Let me try that now..Few min

Report message to a moderator

Re: help of forms 6i [message #622843 is a reply to message #622840]

Sun, 31 August 2014 11:28

kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA

Junior Member

Yep...Awesome, its working. Nothing needed in the post-query, just a IF statement and setting the checkboxes to Y when the condition satisfied. Great.
Please advice one last thing..

So, as per the above changes, the multi-record block shows up with the few records checkboxes as checked. Later the user reviews and may be he will check few more records or he may uncheck few records.
The Generate button when clicked, should basically tell us the number of checked records and then call a concurrent program.

Currently I am doing in the generate button -
While :system.last_record <> 'TRUE' LOOP
IF (:block.checkbox1 = 'Y' AND :block.checkbox2 = 'Y')
THEN
v_count:= v_count+1;
END IF
next_record;
END LOOP;

If v_count > 0 THEN
call Conc.Prog;
END IF;

If this makes sense, the 'generate' button is taking long as it has to loop thru all the queried records and also has to check for the checked records. Do you have any suggestion on this to improve the efficiency ?

Thank you so much...

Report message to a moderator

Re: help of forms 6i [message #622845 is a reply to message #622843]

Sun, 31 August 2014 11:37

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

Senior Member
Account Moderator

If you implemented ":control_block.generate_count" in POST-QUERY, you already have the initial count of checkboxes after EXECUTE_QUERY.

In order to adjust that number as user sets checkboxes ON or OFF, use WHEN-CHECKBOX-CHANGED trigger and put the same equation (I suggested previously) into that trigger.

Report message to a moderator

Re: help of forms 6i [message #622846 is a reply to message #622845]

Sun, 31 August 2014 11:44

kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA

Junior Member

Thank you so much again.. Let me try these changes and will keep you posted. Thanks a lot!!

Report message to a moderator

Re: help of forms 6i [message #622848 is a reply to message #622846]

Sun, 31 August 2014 12:17

kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA

Junior Member

Sorry to bug you again.. On the same form, I had to implement the 'SELECT ALL' and 'DE-SELECT ALL' buttons also. So, my 'SELECT ALL' button logic goes like
the same WHILE LOOP till end of the block, make both the checkboxes CHECKED. For 'DE-SELECT ALL' again, loop from first-record till last, if the checkboxes are checked, make them unchecked.

Given this as the criteria, do you suggest having the count increase or decrease in the 'WHEN-CHECKBOX-CHANGED' trigger of the checkboxes or perform the same LOOP in the generate button also ?
If the user selects 'SELECT ALL' then all the records in the queried block should be selected and our :control_block.generate_count might be not correct ?
Please advice.

Report message to a moderator

Re: help of forms 6i [message #622856 is a reply to message #622848]

Sun, 31 August 2014 15:09

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

Senior Member
Account Moderator

DE-SELECT ALL button is simple: in that case, :generate_count = 0.

For SELECT ALL you have several options. Looping through all record is probably the worst of them.

A better one is to include such a code into SELECT ALL button's WHEN-BUTTON-PRESSED trigger:

go_block('your_block');
last_record;
:generate_count := :system.cursor_record;

Another one uses GET_BLOCK_PROPERTY built-in and its QUERY_HITS property (read about it in Forms Help). Shortly, you have to run COUNT_QUERY built-in procedure and count query hits, such as

go_block('your_block');
count_query;
:generate_count := get_block_property('your_block', query_hits);

The third option uses GET_BLOCK_PROPERTY with LAST_QUERY; you'd extract WHERE clause out of it, compose the full SELECT COUNT(*) statement (using the WHERE clause, as it depends on search criteria), dynamically create record group query and find the result (obviously, that's the most complex solution; I wouldn't use it).

Report message to a moderator

Re: help of forms 6i [message #622884 is a reply to message #622856]

Mon, 01 September 2014 06:40

kkesari
Messages: 12
Registered: June 2007
Location: MCLEAN, VA

Junior Member

But on the 'SELECT ALL' button and 'DE-SELECT ALL' button apart from getting the counts by block properties,how to actually make the checkboxes unchecked ? I was looping through the first record to last record just to make the checkboxes of each of the record unchecked. Any better way to make the entire block checkboxes to make it check or uncheck ? Kindly advice.

Report message to a moderator

Re: help of forms 6i [message #622885 is a reply to message #622884]

Mon, 01 September 2014 06:46

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

Senior Member
Account Moderator

Right; I didn't comment that part of your question (i.e. "how to (un)check all checkboxes") as there was nothing I'd like to add to your solution - I'd do that through the loop as well.

You could, though, re-query the block and set the checkbox value in POST-QUERY trigger (using some IFs), but I suppose that a loop is easier to implement.

Report message to a moderator