Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle Forms - Checkboxes - When-Checkbox-Changed Trigger

Posted on 2007-07-24
40
Medium Priority
?
23,703 Views
Last Modified: 2013-12-19
I have a form that has a five checkboxes.  Two of the checkboxes (let's call them box4 and box5) cannot both be checked.  For example, if box4 is checked, box5 will uncheck and vice versa.  I am new to SQL/PL and do not know what to code in the trigger to get this done.  
0
Comment
Question by:VibertH
  • 17
  • 9
  • 7
  • +1
37 Comments
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 19559972
What is the value you have set for these checkboxes? Is it '0' and '1' OR 'Y' and 'N' or anything else?

Letz say it is 'Y' or 'N' then use the following.

For box4 do the following,
if :box4 = 'Y' then
:box5 := 'N';
else
:box5 := 'Y';
end if;
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 19559979
same way do the coding for box5 also,

if :box5 = 'Y' then
:box4 := 'N';
else
:box4 := 'Y';
end if;

0
 

Author Comment

by:VibertH
ID: 19560066
Yes, it uses Y and N.

So in the When-checkbox-change trigger I should put (syntax and code):
BEGIN
     IF CHECKBOX_CHECKED(:BLOCK.BOX4)
                :BLOCK.BOX5 := 'N'
    END IF;
END;

Will this work?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 11

Accepted Solution

by:
sujit_kumar earned 1000 total points
ID: 19560100
i think you can do like this. i am unable to test it as i don't have forms developer with me now.

BEGIN
     IF CHECKBOX_CHECKED(:BLOCK.BOX4)
                :BLOCK.BOX5 := 'N'
     ELSE
                  :BLOCK.BOX5 := 'Y'
    END IF;
END;
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19561966
If you change checkboxes 4 and 5 to radio buttons that are in the same group, Oracle Forms will automatically make sure that only one gets selected.

Or, if you prefer to keep them as checkboxes, you need to have code like sujit_kumar suggested on each of them making sure that the opposite one gets unchecked whenever the user checks either one.
0
 
LVL 4

Expert Comment

by:prast1007
ID: 19562166
You can create a program unit, let's say SPR_CHECK_VALUES(nmbVal number)  like below:

PROCEDURE SPR_CHECK_VALUES (nmbVal number) is
BEGIN
    if nmbVal = 0 and :BOX4 = 'Y' then
       :BOX5 := 'N';
    elsif nmbVal = 0 and :BOX4 = 'N' then
       :BOX5 := 'Y';
    elsif nmbVal = 1 and :BOX5 = 'N' then
       :BOX4 := 'N';
    elsif nmbVal = 1 and :BOX5 = 'N' then
       :BOX4 := 'Y';
   end if;
end;

and then create a WHEN_CHECK_BOX_CHANGE trigger on both box4 & box5 item,
In box4 trigger call the above program unit
SPR_CHECK_VALUES(0);
and in box5 trigger call the above program unit using:
SPR_CHECK_VALUES(1);

I wish it will help !
0
 
LVL 4

Expert Comment

by:prast1007
ID: 19562199
The trigger you should create is When-checkbox-change trigger on both :box4 & :box5 item
0
 

Author Comment

by:VibertH
ID: 19565198
I don't want radio buttons because I want the page to look uniformed.  I want everything to be checkboxes. But thanks for the suggestion.  

This is what I did:  

I went to the Data Block --> <Block Name> --> Item --> <Checkbox Name> --> Triggers ->> WHEN-CHECKBOX-CHANGED.  I then went into the PL/SQL for WHEN-CHECKBOX-CHANGED.  And I typed

BEGIN
      IF CHECKBOX_CHECKED(:TAX_TYP.MV_LIC_YN)
      THEN
            :TAX_TYP.DR_LIC_YN := 'N';
      END IF;

END;

I didn't use the else because I don't want it to do anyting if it is unchecked.  When I check the box, I get this error:

Unable to resolve reference to item Y.  The other box is not unchecked either.

When I uncheck it I get:

Unable to resolve reference to item N.  

 
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 19565578
see if 'Y' or 'N' are the values that corresponds to the checkbox checked versus checkbox unchecked. Please see the property of these checkbox.
0
 

Author Comment

by:VibertH
ID: 19565773
In the properties, it has 'Value when checked' and I put Y and for 'Value when unchecked' I put N.
0
 

Author Comment

by:VibertH
ID: 19566382
Ok, I got it.  I removed the Checkbox_Checked function and used the below code.  

BEGIN
      IF (:TAX_TYP.MV_LIC_YN = 'Y')
      THEN
            :TAX_TYP.DR_LIC_YN := 'N';
      END IF;

END;

This works fine except it only works on the first row returned.  How can I get it to work on all rows returned from the data block.
0
 

Author Comment

by:VibertH
ID: 19567251
Is there a way to get this code to work for all rows returned from the data block?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19567539
When do you need this to happen?  Do you really want (or need) this at query time for every row retrieved? (That would surprise me.)   Or, do you only need this at data entry time when the record is being added or changed?  (This is what I thought you were asking for.)
0
 

Author Comment

by:VibertH
ID: 19568424
Let me explain a little more.  There is a data block that returns multiple rows.  I am not concerned with adding and deleting the rows.  That part of the code is is completed.  I am adding two additional columns of checkboxes, hence five columns of checkboxes.  The other three columns are not concerned with which other columns are checked.  But for the last two it matters.  Only one of the two can be checked at one time.  

So each row has five cloumns of checkboxes.  The trigger is only working on the first row.  The other rows do not raise the trigger and I don't know why not.
0
 

Author Comment

by:VibertH
ID: 19568434
I need for it to happen during the insert and update.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 1000 total points
ID: 19568855
"I am not concerned with adding and deleting the rows."
and:
"I need for it to happen during the insert and update."

Which of those statements do you want us to believe?  They cannot both be true, since "adding" = an "insert".

You also said you have: "a data block that returns multiple rows" and: "The trigger is only working on the first row.  The other rows do not raise the trigger."  If you want this trigger to fire on every row retrieved by a query in the block, then it must be a POST-QUERY trigger.  But, do these two checkboxes correspond to two columns in the table?  If yes, then using a POST-QUERY trigger to modify the values of the checkboxes is usually *NOT* a good idea.  That will prompt the user then with the message: "Do you want to save your changes?" after every query, but the user may think: "I didn't change anything! Why am I getting this message?"

I think we do not understand the business problem that you are trying to solve with these checkboxes, and maybe using checkboxes in an Oracle form is not the best or easiest way to solve the problem.  Can you describe more of the data problem you are trying to solve with these checkboxes?
0
 

Author Comment

by:VibertH
ID: 19569671
OK.  Let me try again.  (I wish there was a way to show you the form. )  I am taking an existing form that the update, delete, and insert already worked for.  That's why I said I wasn't concerned with that.  (I know, it was a little misleading.)  So, what I need to do to the form is add two more columns of checkboxes.  I have added the columns to the table using the alter table command and it retuns to the form all of the required info, including the Y and N for the checkboxes.  The problem I am having is on the first row returned, the triggers works.  For examlple, if box4 is checked when I check box5, it will uncheck box4 and vice versa.   But for the second row (second record returned), it's now working.  I can check both columns and it chouldn't allow that.  Of the two columns, only one column can be checked for any row.
0
 
LVL 4

Expert Comment

by:prast1007
ID: 19571267
Is there a way to get this code to work for all rows returned from the data block?

VibertH,
The trigger (WHEN-CHECKBOX-CHANGED) should works on item levels, not the item_instance level, and I'm sorry that I write the wrong procedure.

Just try to create a program unit like below :
PROCEDURE spr_check_values (nmbVal int) IS
BEGIN
  if nmbVal = 0 and :TAX_TYP.DR_LIC_YN = 'Y' then
     :TAX_TYP.MV_LIC_YN := 'N';
  elsif nmbVal = 0 and :TAX_TYP.DR_LIC_YN = 'N' then
     :TAX_TYP.MV_LIC_YN := 'Y';
  elsif nmbVal = 1 and :TAX_TYP.MV_LIC_YN = 'N' then
     :TAX_TYP.DR_LIC_YN := 'Y';
  elsif nmbVal = 1 and :TAX_TYP.MV_LIC_YN = 'Y' then
     :TAX_TYP.DR_LIC_YN := 'N';
  end if;
END;

- Under :TAX_TYP.DR_LIC_YN item, create a WHEN-CHECKBOX-CHANGED trigger with a single line statement below:
SPR_CHECK_VALUES(0);

- And under :TAX_TYP.MV_LIC_YN item, create a WHEN-CHECKBOX-CHANGED trigger with a single line statement below:
SPR_CHECK_VALUES(1);

It should works fine.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19574002
A "WHEN-CHECKBOX-CHANGED" trigger (by default at least) will work *ONLY* when the user intentionally checks or unchecks the field (like when they create a new record or change an existing record).  This trigger *WILL NOT* be fired automatically though when existing records are queried in the block *UNLESS* you add a POST-QUERY trigger that forces a value into one of these fields.  But, I would not consider that option (a POST-QUERY trigger) the best way to solve the problem, since you probably do not want to force the users to query every record in the table, and unless you add a check in the POST-QUERY trigger to see if one of these is already checked, this trigger may cause more problems (or at least more user annoyance) than it is worth.

I think you first need to do an update of the existing records to set of these two values, then the  "WHEN-CHECKBOX-CHANGED" triggers like prast1007 suggested can take care of every new or changed record from now on.
0
 

Author Comment

by:VibertH
ID: 19574309
This does the same thing the other code does.  It's applied to only the first row.  Let me try to diagram this with the data (where 'X' is a checkmark).

ID     Description    Basis   Checkbox1   Checkbox2   Checkbox3   MV_LIC_YN     DR_LIC_YN
--     --------------    -------    ------------     ------------    -------------    --------------     ---------------
1      Some desc    None            X                                         X                                            X    
2      Some desc    None            X                                         X                     X                     X
3      Some desc    None            X                                         X                     X                     X
4      Some desc    None                                   X                                         X                     X
5      Some desc    None            X                     X                  X                    X                      X    

This is an exmple of the form when it is loaded.  You can update, insert, and delete from here.  That all works perfectly.  For ID 1 (or row 1), the triggers work fine.  For IDs 2-5, it's not working at all.  It allows for both columns to be checked and it shouldn't.  All rows should behave as row 1.  
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19574382
What triggers do you have on the block and on the two fields: MV_LIC_YN and DR_LIC_YN?
0
 

Author Comment

by:VibertH
ID: 19574430
markgeer,
I am not trying to get it to fire on load.  I am trying to get it to fire when I check a box.  

ID     Description    Basis   Checkbox1   Checkbox2   Checkbox3   MV_LIC_YN     DR_LIC_YN
--     --------------    -------    ------------     ------------    -------------    --------------     ---------------
1      Some desc    None            X                                         X                                            X    
2      Some desc    None            X                                         X                     X                     X
3      Some desc    None            X                                         X                     X                     X
4      Some desc    None                                   X                                         X                     X
5      Some desc    None            X                     X                  X                    X                      X    

If this loads with both rows checked, that's cool.  But since I just added these two columns to the table, that won't be the case they will be null for every record.  

So let's work with a real exmple.  This is how the table loads.

ID     Description    Basis   Checkbox1   Checkbox2   Checkbox3   MV_LIC_YN     DR_LIC_YN
--     --------------    -------    ------------     ------------    -------------    --------------     ---------------
1      Some desc    None            X                                         X                                                  
2      Some desc    None            X                                         X                                            
3      Some desc    None            X                                         X                                            
4      Some desc    None                                   X                                                                
5      Some desc    None            X                     X                  X                                                

This is what I need for it to do after checking the boxes.  

ID     Description    Basis   Checkbox1   Checkbox2   Checkbox3   MV_LIC_YN     DR_LIC_YN
--     --------------    -------    ------------     ------------    -------------    --------------     ---------------
1      Some desc    None            X                                         X                                            X    
2      Some desc    None            X                                         X                     X                      
3      Some desc    None            X                                         X                                            X
4      Some desc    None                                   X                                         X                      
5      Some desc    None            X                     X                  X                    X                

This is what it actually does.

ID     Description    Basis   Checkbox1   Checkbox2   Checkbox3   MV_LIC_YN     DR_LIC_YN
--     --------------    -------    ------------     ------------    -------------    --------------     ---------------
1      Some desc    None            X                                         X                                            X    
2      Some desc    None            X                                         X                     X                     X
3      Some desc    None            X                                         X                     X                     X
4      Some desc    None                                   X                                         X                     X
5      Some desc    None            X                     X                  X                    X                      X    


THe trigger works only for the first row.  
0
 

Author Comment

by:VibertH
ID: 19574455
markgeer,
I have WHEN-CHECKBOX-CHANGED for both fields.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19574538
I should confess that even though I've worked with Oracle Forms for 15 years (since version 2) I rarely use checkboxes, so I don't have much experience with WHEN-CHECKBOX-CHANGED triggers, and I was assuming they would be excuted with the same frequency as a WHEN-RADIO-CHANGED trigger.

Another option that should be very effective, but not quite as user-friendly is a WHEN-VALIDATE-RECORD trigger.  That could easily ensure that only one of this is checked.
0
 

Author Comment

by:VibertH
ID: 19574673
I am just beginning to work with forms.  How can I use the WHEN-VALIDATE-RECORD trigger?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19576246
The WHEN-VALIDATE-RECORD trigger is not my favorite since I don't find it as user-friendly as a WHEN-VALIDATE-ITEM trigger (which is what I try use for most validation in Oracle Forms) but the WHEN-VALIDATE-RECORD trigger is very reliable.  It is executed when the user tries to navigate away from or save a new or changed record.  That could easily check to confirm that at least one but not both of the two new checkboxes are checked.

This code in a WHEN-VALIDATE-RECORD trigger should do the job:
 IF :TAX_TYP.MV_LIC_YN = 'Y' THEN
  IF :TAX_TYP.DR_LIC_YN := 'Y' THEN;
    -- Display an alert here with a message like: "Please check either MV_LIC or DR_LIC, but not both."
      raise form_trigger_failure;
  END IF;
ELSE
  IF :TAX_TYP.DR_LIC_YN := 'N' THEN;
    -- Display an alert here with a message like: "Please check either MV_LIC or DR_LIC.."
      raise form_trigger_failure;
  END IF:
END IF:

Note: I usually use a procedure in an attached PLL library to display an alert, and to "raise form_trigger_failure;".  

Here is the text of the procedure "msg_alert" that we put in a PLL library that we attach to all of our forms, so we can call this from any Forms trigger or program unit:

(This does require that you define three alerts with names matching the names in the three "FIND_ALERT..." commands and copy these alerts into all of your forms.)

procedure msg_alert(
errm in char,           /* message */
errt in char,           /* message type */
rftf in boolean         /* raise form_trigger_failure ? */
) is
--
  alert_is alert;
  alert_button number;
--
BEGIN
--        
  IF (errt = 'E')
    THEN alert_is := FIND_ALERT('ALERT_ERROR');
  ELSIF (errt = 'W')
    THEN alert_is := FIND_ALERT('ALERT_WARN');
  ELSIF (errt = 'I')
    THEN alert_is := FIND_ALERT('ALERT_INFO');
  ELSE
    MESSAGE(errm);
  END IF;
--    
  IF errt IN ('E','W','I') THEN
    SET_ALERT_PROPERTY(alert_is,ALERT_MESSAGE_TEXT,errm);
    alert_button := SHOW_ALERT(alert_is);
  END IF;
--
  IF (rftf) THEN
    RAISE FORM_TRIGGER_FAILURE;
  END IF;
--
END;
0
 
LVL 4

Expert Comment

by:prast1007
ID: 19579727
VibertH, it's a little bit ackward that the procedure above only works on the first row. Because I never got the same issue with that, for I have used it on many forms.

If you don't mind, could you just send me the form source code (fmb), so I can take a look at it closer.

Maybe there's something else in it that make the procedure not work/override.

Wish can help you out.
prast@fif.astra.co.id
0
 

Author Comment

by:VibertH
ID: 19581687
I'm looking at it and will get back to you.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19582204
You do have the block defined as a database block (not a control block) right?  And you use the default query functionality of Oracle Forms to query that database table (or view) to put multiple records on the screen, right?

Like prast1007, I've never seen a Forms trigger work on only the first record in a block (unless it had logic in it that checked the record number or something).
0
 

Author Comment

by:VibertH
ID: 19593479
Yeah, it's a data block and I used the wizard to query.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19594590
OK, the WHEN-CHECKBOX-CHANGED trigger only fires when the user adds or changes a record, *NOT* when a query populates records in the block.  A POST_QUERY trigger would fire at query time for every record retrieved by a query.

But which problem are you trying to solve here?  Are you trying to get one of these two columns selected for all existing records, or do you want the screen to make sure that for new (or changed) records, at least one of these columns gets selected?  Those are two different problems, and will require two different triggers *IF* you want an Oracle Forms to help solve both problems.
0
 

Author Comment

by:VibertH
ID: 19594836
Ok, what I am trying to do is after the form loads, it will load rows.  At this point, you can check or uncheck boxes for the rows.  For any given row, you can select one of the two columns.  If you add a new row, you can select one of the two columns.  So if I check/uncheck the boxes for the rows, then I am changing the record.  So the WHEN-CHECKBOX-CHANGED should fire.  But it is only firing when I I check/uncheck for the first row.  For the others, it does nothing.
0
 
LVL 4

Expert Comment

by:prast1007
ID: 19599043
Well VibertH,

I'm not cery sure about what is really going on with your form since I don't see it, but maybe it happens because the columns (MV_LIC_YN, DR_LIC_YN) has no default values. Just tried to add something in POST_QUERY trigger in the related data block like this :

IF :MV_LIC_YN is null then
   IF :DR_LIC_YN  is not null then
       if :DR_LIC_YN is in ('Y', 'N') then
           spr_check_values (0);
       else
          /* Define an error message because of :DR_LIC_YN has invalid value ! */
       end if;
   ELSE
       /* just initiate  the proposed value */
      :DR_LIC_YN := 'Y';
      :MV_LIC_YN := 'N';
   END IF;
END IF;
IF :DR_LIC_YN is null then
   IF :MV_LIC_YN  is not null then
       if :MV_LIC_YN is in ('Y', 'N') then
           spr_check_values (1);
       else
          /* Define an error message because of :MV_LIC_YN has invalid value ! */
       end if;
   ELSE
       /* just initiate  the proposed value */
      :DR_LIC_YN := 'Y';
      :MV_LIC_YN := 'N';
   END IF;
END IF;

I assumed that you still have the procedure spr_check_value in your program unit.
0
 

Author Comment

by:VibertH
ID: 19599827
That does the same thing.  Can I just email you the form and relevant table?
0
 
LVL 4

Expert Comment

by:prast1007
ID: 19599858
Okay then,
Sent me the form along with the table's structure relted with it, And I will try to look at it closely...

Regards,
prast@fif.astra.co.id
0
 

Author Comment

by:VibertH
ID: 19599939
ok, you should have it.
0
 
LVL 4

Expert Comment

by:prast1007
ID: 22311093
Should I get the credit for this also ?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

810 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