Validation of the fields required compulsory before saving

1008295

Hello friends,

Working on oracle forms 6i. I have a multi-data block. It has two columns - Bank No and Payment Date. My requirement is both the fields should not be left null or empty before saving. Also I am updating a third column called Status if the first 2 fields are not empty. I have designed a logic on when-validate-item-trigger. And it fires only once if any of the fields are empty. Also if I try saving the record,  my cursor should automatically move to the column which is empty or null and ask me to enter a value. And yes I know, go_item cannot be used in when-validate-item trigger and do not want to go for the timer. Any help with the when-validate-record or any other way out??

Please help as it is urgent.

Thanks in anticipation.

CraigB

I have a multi-data block. It has two columns - Bank No and Payment Date. My requirement is both the fields should not be left null or empty before saving. Also I am updating a third column called Status if the first 2 fields are not empty.

Are the BANK_NO and PAYMENT_DT block items marked as required?  Then the user must enter a value.

And yes I know, go_item cannot be used in when-validate-item trigger and do not want to go for the timer.

What's wrong with using a zero (0) millisecond non-repeating timer?  It executes immediately after the WVI completes and doesn't generate any network traffic (since it is a 0 millisecond).  Another option would be to set a GLOBAL or PARAMETER object value and evaluate this object in a Module (Form) level When-New-Item-Instance (WNII) trigger so your code would know to move the cursor to the desired field.  I personally don't like this method, because it fires everytime focus changes and you have to remember to reset your Global/Parameter object.  The non-repeating timer is much cleaner because you only create it when the situation demands it and the timer execute immediately after the WVI so there is no lag.

The best option, in all honesty, is simply to make the two fields required so the user can't leave the item or save the record if there is no value.

Craig...

1008295

Hello Craig,

That is cool..but if there are 2 or more records in the same block and say if I need to update only one then wouldn't it ask me to update all the records before saving?

Can you provide me with a snippet and the trigger in which I am suppose to write.

Also, never have I used a timer so have no idea about it. Could you please elaborate it or a link explaining in detail ?

CraigB

but if there are 2 or more records in the same block and say if I need to update only one then wouldn't it ask me to update all the records before saving?

Standard Forms functionality will prompt you to save changes in a record before allowing you to move to a new record.  Therefore, if your block is based on a database object (table, view, etc) you shouldn't have more than 1 row with unsaved changes.

never have I used a timer so have no idea about it. Could you please elaborate it or a link explaining in detail ?

The Oracle Forms Help system is your best friend!   It describes a Timer as "an internal time clock that you programatically create to perform an action each time the timer expires.  I've used timers to count the number of characters entered in a text item and mostly to execute Restricted Built-ins from triggers that don't allow the use of restricted built-ins; such as calling the GO_ITEM built-in from a When-Validate-Item trigger.  Triggers can repeat or execute only once and you can set the duration of the timer from 0 milliseconds up to a maximum of 24.85 days (2,147,482,647 milliseconds).  Why you would set one that large is beyond me, but you never know!

The problem with repeating timers is that if the user is doing something in your form when the timer expires, the focus temporarily leaves while the timer executes.  This can be an anoyance to your user if they are typing in a text item at the time the timer executes.

The most common use of Timers that I have seen is to execute a Restricted Builtin. For an example of how to use a timer to execute a Restricted Built-in take a look at this thread: https://forums.oracle.com/message/9781719#9781719

Hope this helps,

Craig...

Andreas Weiden

Hi Craig,

Standard Forms functionality will prompt you to save changes in a record before allowing you to move to a new record.  Therefore, if your block is based on a database object (table, view, etc) you shouldn't have more than 1 row with unsaved changes.

I'm not sure if i understand that correctly, but in general, if you do not have a masterdetail-block, you can add several records without saving.

Also, i see two more possible solutions:

1. Set the fields to required and set the property DEFER_REQUIRED_ENFORCEMENT  to TRUE

2. Set the form-property Navigation-unit to Form.

CraigB

Ah, yes, you are correct - I forgot this was only when you had a Master - Detail relationship.  Thanks for the clarification!

Craig...

1008295

Hey Craig ,

Sorry for the late reply ...see this is the code I am using on When-Validate-Trigger of the Bank reference item of the block.

As I said earlier I have two items Bank reference number and payment date.

begin

  if :BLOCK.PAYMENT_DATE is NULL then

  SET_ALERT_PROPERTY('AL_CAU',TITLE,'Error');

      SET_ALERT_PROPERTY('AL_CAU',ALERT_MESSAGE_TEXT,'Payment date cannot be null.') ;

      al_num := show_alert('AL_CAU');

      go_item('BLOCK.PAYMENT_DATE'); -- This is where I require it to go that item.

  else

  if :BLOCK.BANK_REF_NO is not NULL then

      if :BLOCK.STATUS = 'IN PROCESS' and :BLOCK.BANK_REF_NO is not NULL and :BLOCK.PAYMENT_DATE is not NULL then

  :BLOCK.STATUS := 'PAID';

      end if;

      else

  set_alert_property('AL_CAU',TITLE,'Error');

  set_alert_property('AL_CAU',ALERT_MESSAGE_TEXT,'Bank reference number cannot cannot be null');   

  al_num := show_alert('AL_CAU');

      end if;

  end if;

end;

Now I know that this(When-Validate-Item) will fire once when I leave the item. But my requirement is when the user will either press Ctrl+S(Save) or Form default Save button, I want the message 'Payment date cannot be null' to display as many times as the user uses the button to save or in this case fire the alert till that item is null.

So will a timer be helpful in this case. And how ? If you really give the snippet. Its a request.

The Learner

Hi,

Try

Key-CREREC trigger at Form Level.

It may help you.

Shankar

CraigB

Now I know that this(When-Validate-Item) will fire once when I leave the item. But my requirement is when the user will either press Ctrl+S(Save) or Form default Save button, I want the message 'Payment date cannot be null' to display as many times as the user uses the button to save or in this case fire the alert till that item is null.

So will a timer be helpful in this case. And how ? If you really give the snippet. Its a request. 

The When-Validate-Item (WVI) trigger will fire if the user presses Ctrl+S or clicks the Save button.  In this situation, yes a timer would be a good option for directing the cursor to the PAYMENT_DATE field.  If you take a look at the link I listed previously, there is a very good example of how to use a timer.  In that link, you would incorporate the code in the "On-Dispatch-Event trigger" into your WVI trigger.  In place of your call to the "go_item('BLOCK.PAYMENT_DATE');" you would call the "Timer_ID := Create_Timer('CALL_NEXT_RECORD',1,NO_REPEAT);" instead.  I recommend you name your timer something other than "CALL_NEXT_RECORD"; something like, "GOTO_PAYMENT_DATE" maybe.  Then, based on the example in the link, you would compare v_timer = 'GOTO_PAYMENT_DATE' instead of 'CALL_NEXT_RECORD' and then make your call to "go_item('BLOCK.PAYMENT_DATE');" instead of "NEXT_RECORD" in the When-Timer-Expired trigger.

Craig...

1008295

Hey Craig,

Thanx a lot for the help and making me understand the code.

Has paved  a way out for illrestricted procedures..

Well there was another small issue I had..

if :system.record_status = 'CHANGED' AND :BLOCK.STATUS = 'IN PROCESS' AND :BLOCK.PAYMENT_DATE is NULL then

  SET_ALERT_PROPERTY('AL_CAU',TITLE,'Error');

      SET_ALERT_PROPERTY('AL_CAU',ALERT_MESSAGE_TEXT,'Payment date cannot be null.') ;

      al_num := show_alert('AL_CAU');

  Timer_ID := Create_Timer('GOTO_PAYMENT_DATE',1,NO_REPEAT);

The timer is working fine. This is written on WVI(When-Validate-Item) Trigger.

The issue is if you look at the 'STATUS' field it is 'IN PROCESS' then only the trigger should fire.

Prior/Preceding to the 'IN PRocess' status there is status called 'SUBMITTED'. In this case if I click on the 'Payment Date' column and try coming out of the field,even then the trigger is fired an alert is given directing my cursor to the the 'PAYMENT DATE'. So it blocks the further actions.

Basically, I want the trigger to fire only when the record is 'IN PROCESS' status.

Also are there any limited cycles of this timer or is there any limitation after which it wont fire..??

CraigB

1008295 wrote: 
Basically, I want the trigger to fire only when the record is 'IN PROCESS' status. 

I'm a little confused by your statement.  Your code is testing for the combination of conditions (:SYSTEM.RECORD_STATUS, :BLOCK.STATUS and :BLOCK.PAYMENT_DATE).  Since your Timer is only created when the combination of these conditions are TRUE, the When-Timer-Expired (WTE) trigger will only fire in response the Timer you created expiring.  

1008295 wrote: 
Also are there any limited cycles of this timer or is there any limitation after which it wont fire..??

There are different values you can pass as parameters to the CREATE_TIMER built-in.  The way you are using it - as a 1 millisecond, non-repeating timer - there are no limited cycles.  The Timer "GOTO_PAYMENT_DATE" will expire only once causing the WTE trigger to execute only once.  Had you specified the ITERATE parameter as "REPEAT" then you could have created a timer that would expire over and over based on the time value specified in the MILLISECONDS parameter.  To fully understand the benefits and restrictions of using a TIMER, I suggest you look up the CREATE_TIMER built-in in the Forms Help system.

Craig...