Oracle Forms - Checkboxes - When-Checkbox-Changed Trigger

same way do the coding for box5 also,

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

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?

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 !

The trigger you should create is When-checkbox-change trigger on both :box4 & :box5 item

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.  

 

see if 'Y' or 'N' are the values that corresponds to the checkbox checked versus checkbox unchecked. Please see the property of these checkbox.

In the properties, it has 'Value when checked' and I put Y and for 'Value when unchecked' I put N.

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.

Is there a way to get this code to work for all rows returned from the data block?

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.)

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 need for it to happen during the insert and update.

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.  

What triggers do you have on the block and on the two fields: MV_LIC_YN and DR_LIC_YN?

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.  

markgeer,
I have WHEN-CHECKBOX-CHANGED for both fields.

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.

I am just beginning to work with forms.  How can I use the WHEN-VALIDATE-RECORD trigger?

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;

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

I'm looking at it and will get back to you.

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).

Yeah, it's a data block and I used the wizard to query.

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.

That does the same thing.  Can I just email you the form and relevant table?

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

ok, you should have it.