Home » Developer & Programmer » Forms » how to check record status?? (Oracle Form Builder 10g, Win XP)
how to check record status?? [message #522199] Wed, 07 September 2011 04:53 Go to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
I am manually committing the form by writing commit statement in key - commit trigger for some reason.

Everything is working fine. I want to display a message if some necessary fields are not filled properly, like if a user filled half table and mistakenly he press the commit button. Here I want to show him a message that you can't saved the data because the table is not completely filled. Is this possible?
Re: how to check record status?? [message #522202 is a reply to message #522199] Wed, 07 September 2011 04:58 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Necessary fields" should have the "Required" property set to "Yes". In that case, you couldn't leave them empty. Also, if they are required, underlying table should have these columns defined as NOT NULL (which would also prevent users from committing incomplete data).

If you want to check it manually, I'd do that in WHEN-VALIDATE-RECORD trigger (though, you can do it in KEY-COMMIT as well, I suppose). Code would be the same in both cases, something like
if :block.item1 is null or :block.item4 is null or :block.itemx is null then
   message('Incomplete data');
   raise form_trigger_failure;
else
   commit;
end if;


Re: how to check record status?? [message #522206 is a reply to message #522202] Wed, 07 September 2011 05:10 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
it will be a very lengthy way to check. is there any other way to see that whether the record is saved or not after executing commit manually??
Re: how to check record status?? [message #522209 is a reply to message #522206] Wed, 07 September 2011 05:20 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's the problem with using the required property exactly?

And don't use key-commit. For anything. Ever. It can be bypassed.
Re: how to check record status?? [message #522210 is a reply to message #522209] Wed, 07 September 2011 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
@LF - since when does commit work in validate triggers?
Re: how to check record status?? [message #522214 is a reply to message #522210] Wed, 07 September 2011 05:27 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Shame on me ...

I guess that it should have been
if ...
   message
   raise
end if
Re: how to check record status?? [message #522216 is a reply to message #522214] Wed, 07 September 2011 05:36 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
Let me tell you the whole stuff. I am clearing the form behind key-commit trigger as i cant able to use clear_form statement in POST-commit trigger. And I am writing a commit statement with a message that the data is saved. but when the mandatory fields are not filled the data is not saved and it should be like this. but the problem is I am doing some updates in the form and if i write commit statement and the table is not complete, the table data will not saved but other updates will become permanent. Is there any way to check that the record entered is valid or not so that i can put it in IF condition and execute commit statement?
Re: how to check record status?? [message #522217 is a reply to message #522216] Wed, 07 September 2011 05:39 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Is there any way to check that the record entered is valid or not

I suppose it is that huge
if :block.item1 is null or :block.item4 is null or :block.itemx is null ...
kind of IF statement. I don't know if you can avoid it (i.e. make it simpler), if you really want to write your own code.
Re: how to check record status?? [message #522220 is a reply to message #522217] Wed, 07 September 2011 06:07 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
Again - what's the problem with using the required property?
That would be the simple wau to do this.
Re: how to check record status?? [message #522229 is a reply to message #522220] Wed, 07 September 2011 07:42 Go to previous messageGo to next message
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
Member
Well you could do a check that loops through the block, but it will require some kind of indicator that fields are required. It'll look something like this (mind you, this is for our own system, and I expect you use your imagination to fill in the blanks, and this piece of code is better placed in a library so you can acces it from every form, not just a single one).

declare
  t_item  varchar2(64);
  t_mandatory boolean;
  t_string varchar2(256);
  t_display_message varchar2(1);
  t_block varchar2(64);
begin
  t_item    := get_block_property(t_block,first_item);
--loop through all items in the current block of a form
  while t_item is not null
  loop
  	  if get_item_property(t_block||'.'||t_item, item_type)
  	  in ('TEXT ITEM','LIST')
      then 
            t_manadatory := get_item_property(t_block||'.'||t_item, required);
      else t_manadatory := 'FALSE';
      end if;
      if t_manadatory = 'TRUE'
      then
            if name_in(t_block||'.'||t_item) is null
            then
                  t_display_message := 'J';
                  t_string := t_string ||' '||get_item_property(t_block||'.'||t_item , prompt_text);
            end if;
      end if;
      t_item      := get_item_property(t_block||'.'||t_item, nextitem);
  end loop;
  if t_display_message = 'J'
  then
        message('Please fill these:'||t_string );
        message('Please fill these:'||t_string );
      
  else
    --all the mandatory fields have been filled, do something else. 
  end if;

end;


I wrote this particular code to enforce user-defined checks which were ignored if a call-form was used to merely look at data generated from a different form, which might not have been complete for the called-form but needed to be filled in. Since upon opening and quering the record status is valid, required fields are not triggered. Hitting exit_form at that point won't trigger the "required" messages. It's hooked beneath an OK-button and the commit-trigger to prevent going around it.

The other way is to manually define which records you need filled, and to check those, or to define them at database level and let the database send you back an error thus causing a trigger failure, stopping the execution of the code.

Hope you get something out of this. Good luck!

[Updated on: Wed, 07 September 2011 07:43]

Report message to a moderator

Re: how to check record status?? [message #522231 is a reply to message #522229] Wed, 07 September 2011 08:04 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
I can't remember off hand but I'm pretty sure the required property is checked before when-validate-record fires. And since you can't use key-commit for this as it can be bypassed (well you can but it means that sometimes you'll get the standard forms required message instead of what you coded) then I don't see this code being much help.

Set the required properties and let them do their job.
Re: how to check record status?? [message #522235 is a reply to message #522231] Wed, 07 September 2011 09:28 Go to previous messageGo to next message
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
Member
*nods* Like I said, its specific for when you open a form and query data, and not all the required fields are filled but you do want your user to fill them before he exits... if he simply closes the form after the query, the data will stay invalidated.

It is however also a block of code that goes through all the items in the current block and checks whether they are required, and that might be of help. Of course, its just a suggestion... I gather though I am supposed to give a 100% solution?
Re: how to check record status?? [message #522236 is a reply to message #522235] Wed, 07 September 2011 09:35 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
No, hints are fine generally, I just can't see any way of applying your suggestion consistently to this case.
Re: how to check record status?? [message #522237 is a reply to message #522236] Wed, 07 September 2011 09:42 Go to previous messageGo to next message
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
Member
If he doesnt want to set required fields for some unknown reason, and he does want to check if all fields in the current block are filled, and doesnt want to type
if :block.item1 is null or :block.item4 is null or :block.itemx is null ...

because he has 50 fields he can use the above-stated loop to loop through everything Wink Of course, small modifications of the loop are required, such as removing the check on property_required and replacing it with an "is null".

But setting required is a lot better, I agree Smile
Re: how to check record status?? [message #522241 is a reply to message #522237] Wed, 07 September 2011 10:03 Go to previous messageGo to next message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
According to the original post it's not all fields. If it was that'd work, as it isn't you'd have to store a list somewhere. At which point it probably becomes more work than just coding it the hard way.
Re: how to check record status?? [message #522243 is a reply to message #522241] Wed, 07 September 2011 10:09 Go to previous messageGo to next message
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
Member
Well, theoretically one could have user- or application-manager defined required fields who's requirement property are stored in a table, add in a cursor to check and voilla. Though its a lot easier to simply query that table at form creation and set property_required = true.
Re: how to check record status?? [message #522294 is a reply to message #522243] Thu, 08 September 2011 01:28 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
Thanks guys for the help. I have an idea of checking the item one by one. Loop makes the life easier for larger tables. I was thinking the other way around, like if there is any way to check whether the record recently added to form fulfills the committing criteria or not. Now, I understand that I have to check manually the fields either using loop or by going through them manually.

Cookiemonster: I might be wrong but to the best of my knowledge if we make the field as required in the property palette of that field or put a not null constraint in the table then the cursor will never move forward until the necessary field is filled by the user. And if user intentionally presses the commit button without filling necessary fields, the commit will never happen. So, for sure this record will not save in the DB but I am executing a manual commit behind KEY COMMIT trigger on form level. I just want not to execute this manual commit if that record is not good for commit. Now I have only one option to check fields manually. Thanks for help.
Re: how to check record status?? [message #522306 is a reply to message #522294] Thu, 08 September 2011 03:48 Go to previous message
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
Senior Member
new_oracle2011 wrote on Thu, 08 September 2011 07:28

Cookiemonster: I might be wrong but to the best of my knowledge if we make the field as required in the property palette of that field or put a not null constraint in the table then the cursor will never move forward until the necessary field is filled by the user.

That's the default behaviour - it can be changed. Have a look at the defer required enforcement property at form level. Not null isn't checked until the record is submitted to the DB.

And I'll say again - key-commit can be bypassed. If the used makes changes and then exits the form (or presses enter query or exit query or clear block) the form will display a pop-up message asking if they want to save changes. If they click yes then the changes are saved without key-commit firing. So if you want to do validation you can't put it in key-commit.
Previous Topic: Text Item with long data type
Next Topic: Forms 11g: Multiple applets same oracle session??
Goto Forum:
  


Current Time: Sat Sep 07 13:07:32 CDT 2024