?
Solved

Changing Record Color in Oracle Form

Posted on 2008-10-30
18
Medium Priority
?
10,471 Views
Last Modified: 2013-12-12
Hi Experts..I am encountering a problem while trying to Highlight the record with any specific color.
My requirement is When new form is opened, the record that are satisfying the conditions should get highlighted with some color.Its an Tabular form I know I need to use set_item_instance_property and I am using some thing like this:

I wrote the below code in WHEN_NEW_FORM_INSTANCE Trigger.I even tested it to know whether the query is getting executed or not.It is working fine..i am able to display simple messages.

I think i need to write the code in some other trigger, but not sure.Could some one help me out??

EXECUTE_QUERY;
LOOP
--message('statemnt1');
v_lot_cnt := 0;
SELECT count(1) INTO v_lot_cnt 
FROM SMC_IRRS
WHERE lot_number = :WIP_OPERATIONS_V.lot_number
AND department = '656'
AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';
--message('statement2 count:' || v_lot_cnt);
IF v_lot_cnt <> 0 
THEN 
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 
end if;
   IF :SYSTEM.last_record='TRUE' THEN
       EXIT;
   ELSE
       NEXT_RECORD;
    END IF; 
   END LOOP;

Open in new window

0
Comment
Question by:sum_got
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
18 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 22848143
Hi!

Did you try to put the code in the POST-QUERY-trigger of the queried block?

So you won't have to go through all the records twice. Having quite the same code in one of my forms in the post-query-trigger worked fine.
0
 

Expert Comment

by:mahmoud_76
ID: 22849298
The most efficient way to do that, is to put that logic in WHEN_NEW_ITEM_INSTANCE trigger on block level, so with every record displayed in the grid the calculation will b done and if condition satisfied then visual attribute changes accordingly.
0
 

Expert Comment

by:mahmoud_76
ID: 22849478
Sorry, i meant WHEN_NEW_RECORD_INSTANCE
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sum_got
ID: 22851877
Hi MarusId...I tried to execute the above code in POST_QUERY Trigger at block level (wip_operations_v)
I am getting the below error msg :
40737-Illegal restricted procedure
NEXT_RECORD in POST_QUERY trigger

If i commented the Next_Record Code..How could i go to the next record in the grid (tabular form)

When i keep on pressing ok, i am getting some other err msgs like Unable to Fetch the next record and so on. Could some one help me out??

0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22851916
Hi sum_got,

You don't need the loop in this case (as Forms is going through all the records anyway), only the following part of code:
SELECT count(1) INTO v_lot_cnt 
FROM SMC_IRRS
WHERE lot_number = :WIP_OPERATIONS_V.lot_number
AND department = '656'
AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';
--message('statement2 count:' || v_lot_cnt);
IF v_lot_cnt <> 0 
THEN 
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 
end if;

Open in new window

0
 

Author Comment

by:sum_got
ID: 22851976
Hi mahmoud_76 and All,
I thk you din't get me properly.Let me be more clear.
 When i opened a new form, i am able to populate the data in the tabular block.My requirement is to
highlight the records which are satisfying the conditions i specified.
When i am trying in When_New_Record_Instance, nothing is happening.I am attaching the code for reference.
::Code written in POST_QUERY at Block Level::
Note : I tried the same code in When_New_Record_Instance Trigger
 
DECLARE
  V_ERROR_COUNT NUMBER;
  v_lot_cnt number := 0;
BEGIN
-- Additional Code for Highlighting Jobs 
--EXECUTE_QUERY;
LOOP
--message('statemnt1');
v_lot_cnt := 0;
SELECT count(1) INTO v_lot_cnt 
FROM SMC_IRRS
WHERE lot_number = :WIP_OPERATIONS_V.lot_number
AND department = '656'
AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';
 
--message('statement2 count:' || v_lot_cnt);
 
IF v_lot_cnt <> 0 
THEN 
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 
end if;
   IF :SYSTEM.last_record='TRUE' THEN
       EXIT;
   ELSE
       NEXT_RECORD;
   END IF; 
END LOOP; 

Open in new window

0
 

Author Comment

by:sum_got
ID: 22852147
Hi MarkusId,

I tried as you said, but nothing happened.Just the data is populating in the grid as before,  the records that are satisfying the condition are not getting highlighted.Wat might be the reason behind that?
I am really not understanding, i am fresher to the job.Could you pls help?
If you need any more details, i can give you.
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22852209
Hi,

Have you already tried to put a message-command inside the if-block and tried if it is shown? Please try also with "!=" instead of "<>" as unequal-sign (or just with greater than ">").

If a message inside the if-block shows up and the records haven't changed their colour you could try another visual attribute or change the visual attribute to another colour?
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22852295
Please also try with the Background/font-parameters instead of the visual_attribute (didn't work for me in Forms 6i, but that doesn't necessarily mean that it wouldn't work on another environment)
0
 

Author Comment

by:sum_got
ID: 22852390
Hi,

I tried putting the message-command before the Select Statement and at the End of the Select Statement. I am getting the msg populated.Attached the screen shot and code.
Am i trying in the right way ? How you want me to try?? Could you pls send the code for background color
FYI : I am working on Forms 5

message('statemnt1');
--v_lot_cnt := 0;
SELECT count(1) INTO v_lot_cnt 
FROM SMC_IRRS
WHERE lot_number = :WIP_OPERATIONS_V.lot_number
AND department = '656'
AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';
 
message('statement2 count:' || v_lot_cnt);
 
IF v_lot_cnt <> 0 
THEN 
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 
end if;

Open in new window

Snap.doc
0
 
LVL 9

Accepted Solution

by:
MarkusId earned 2000 total points
ID: 22852419
In the attached document the v_lot_cnt = 0, so it won't change the record, as it doesn't enter the if-block (because the program gets there only if v_lot_cnt does not equal 0)
0
 

Author Comment

by:sum_got
ID: 22852575
Hi...If i am not wrong wat you are saying is The value for v_lot_cnt is 0 and thats the reason its not entering the IF-Condition.
I think, when the select statment is checking the LOT_NUMBER aganist the condition, the count is getting changed to other than Zero (i checked the query  in DB)
Could i test the code putting the message command inside the IF Condition.  
0
 

Author Comment

by:sum_got
ID: 22852779
Hi MarkusId,
I tried putting the message command in side the IF-Condition and the message is not getting displayed.That means Is it not entering into the IF statement at all.
If that is the Case..How should i change my code.Could you pls modify the code.

message('statemnt1');
--v_lot_cnt := 0;
SELECT count(1) INTO v_lot_cnt 
FROM SMC_IRRS
WHERE lot_number = :WIP_OPERATIONS_V.lot_number
AND department = '656'
AND MATERIAL_REMOVAL_REQUIRED = 'IS Required';
 
message('statement2 count:' || v_lot_cnt);
 
IF v_lot_cnt <> 0 
THEN 
     message('statement3 count:' || v_lot_cnt);
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.mfg_order',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.item',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.lot_number',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.description',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN');
     SET_ITEM_INSTANCE_PROPERTY('WIP_OPERATIONS_V.location',CURRENT_RECORD,VISUAL_ATTRIBUTE ,'VISATT_CYAN'); 
end if;

Open in new window

0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22856337
Hi,

Sorry I couldn't answer earlier - well, you have the select-statement above that isn't fetching any records. So could you please also message-output the :WIP_OPERATIONS_V.lot_number and show select and the output of a database query where you have  replaced :WIP_OPERATIONS_V.lot_number with the value given by the Forms?
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 23129602
I suppose the initial question has turned out not to be the real problem. I'm quite sure the SET_ITEM_INSTANCE-property would have worked if the program had not been prevented
from going there through the if-clause.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question