OraFAQ Forum: Forms » Update database item when non-database item is manually updated

Home » Developer & Programmer » Forms » Update database item when non-database item is manually updated (oracle forms builder, 10.1.2, oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update database item when non-database item is manually updated [message #498639]

Thu, 10 March 2011 12:33

msdtjw
Messages: 25
Registered: March 2011

Junior Member

I am having trouble changing a non-displayed field when a displayed field is changed.

When a user wants to un-assign a territory they want to just null the ID field (non-database item). I then need to null the PIDM field(not visible, database item) for that one record.

I tried this, but I end up putting null on all the records instead of just the one record, which ends up un-assigning all the territories not just the one.

Is there a way to assign the null to the PIDM field associated to the ID field of that particular record.

Thanks

Report message to a moderator

Re: Update database item when non-database item is manually updated [message #498649 is a reply to message #498639]

Thu, 10 March 2011 12:55

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

What do you mean it put null in all the records?

Please show us the code and what trigger you put it on.

Report message to a moderator

Re: Update database item when non-database item is manually updated [message #498652 is a reply to message #498649]

Thu, 10 March 2011 13:04

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Adding the 2 lines in read assigned null to all the records on the form not just the current record. On this particular form a user can un-assign a territory by removing the ID#. Since the ID# is not a database item I need to also NULL the PIDM field( which is a database field) associated with the current record. I only put the change here because i saw this trigger fire when i tabbed to the next field. If there is a better trigger for this, please let me know.

DECLARE
cursor get_valid_recruiter IS
select sovarol_id, rtrim(spriden_first_name)||' '||rtrim(spriden_last_name)
from sovarol, spriden
where sovarol_id = :swrterr_terr_counselor
and sovarol_radm_code = 'EMRECR'
and spriden_id = :swrterr_terr_counselor
and spriden_change_ind is null;

BEGIN
message('IN POST CHANGE');
if :swrterr_terr_counselor > '' then
open get_valid_recruiter;
fetch get_valid_recruiter into :swrterr_terr_counselor,
:swrterr.counselor_name;
if get_valid_recruiter%notfound THEN
MESSAGE(G$_NLS.Get('SZATERR-005','FORM','*ERROR* Invalid Recruiter - Recruiter must exists on SOIAROL'));
message(' ',no_acknowledge);
:swrterr_terr_counselor := '';
:swrterr.counselor_name := '';
RAISE FORM_TRIGGER_FAILURE;
end if;
else
:swrterr_terr_pidm := '';
end if;

END;

Report message to a moderator

Re: Update database item when non-database item is manually updated [message #498658 is a reply to message #498652]

Thu, 10 March 2011 13:52

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) Can you please read and follow the orafaq forum guide - especially the part on how to format your post.
2) You haven't explicitly told us what trigger the code is in. I guess it's post-change from the message. Use when-validate-item instead.
3) This is never true:

if :swrterr_terr_counselor > '' then

Nothing is greater than null. Nothing is less than null, or equal to null. Any comparison involving null returns null.
4) Use null for null not ''.

Report message to a moderator

Re: Update database item when non-database item is manually updated [message #498662 is a reply to message #498639]

Thu, 10 March 2011 14:27

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Sorry First time using site.

I will use the WHEN-VALIDATE-ITEM to apply the change.

You are correct on the null comparison, and i will fix that.

I will try to explain the problem better.

For tabular format data block:

swrterr_terr_counselor - text item, enabled, navigable, not a
database item.

swrterr_terr_pidm - text item, enabled, un-navigable, database
item.

Problem:

When swrterr_terr_counselor of record #1 is changed to null by user by deleting the data in the swrterr_terr_counselor field, i need to change the swrterr_terr_pidm to null for the same record.

I tried using :swrterr_terr_pidm := ''(null) which put null in the swrterr_terr_pidm for all records.

I hope i explained the problem better.

Thanks

Report message to a moderator

Re: Update database item when non-database item is manually updated [message #498666 is a reply to message #498662]

Thu, 10 March 2011 14:51

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's doing it for all records because your if statement always evaluates to null so does the else everytime the trigger is fired.
Should be:

if :<block name>.swrterr_terr_counselor is not null then

Report message to a moderator

Re: Update database item when non-database item is manually updated [message #498668 is a reply to message #498666]

Thu, 10 March 2011 14:58

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Thank you for all your help. It is working now.

Report message to a moderator

Previous Topic:	Commit the data on change record
Next Topic:	Forms Did not fit to screen

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 17 16:20:19 CDT 2024