same way do the coding for box5 also,
if :box5 = 'Y' then
:box4 := 'N';
else
:box4 := 'Y';
end if;
Main Topics
Browse All TopicsI 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.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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.
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 !
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.
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.
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.
"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?
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.
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.
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.
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.
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.
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.
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_i
alert_button := SHOW_ALERT(alert_is);
END IF;
--
IF (rftf) THEN
RAISE FORM_TRIGGER_FAILURE;
END IF;
--
END;
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
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).
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.
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.
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.
Business Accounts
Answer for Membership
by: sujit_kumarPosted on 2007-07-24 at 13:52:54ID: 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;