Home » Developer & Programmer » Forms » changing status of records in detail db (Forms 6i)
changing status of records in detail db [message #361684] Thu, 27 November 2008 07:46 Go to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
Hi everyone,

I am new to Forms, so my questions are related to specific situations which I was unable to find in a book.

Here is the story.

I created two data blocks based on two tables: db1 and db2.

Record in the first data block consists of two columns: id and status.

Record in the second data block consists of three columns: id, number, status.

db1 and db2 are connected via id.

In the first data block I have situation like this:

ID STATUS
1 YES

In the second data block I have this:

ID NUMBER STATUS
1 1 YES
1 2 YES
1 3 YES

I am trying to change status of the records in db2 related to db1, when changing status of the record in db1.

I mean, when I change in a list (on canvas) status of the db1 record like this:

ID STATUS
1 NO

I would like this to be changed in the second block like this:

ID NUMBER STATUS
1 1 NO
1 2 NO
1 3 NO

I tried a lot of stuff, searched through triggers, but didn't make it.

[Updated on: Thu, 27 November 2008 07:50]

Report message to a moderator

Re: changing status of records in detail db [message #361689 is a reply to message #361684] Thu, 27 November 2008 08:44 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
If the status in db1 is a poplist, then you can use the WHEN-LIST-CHANGED trigger to change the status in db2 to the value of the status in db1 by using a loop.

Rajy
Re: changing status of records in detail db [message #361742 is a reply to message #361689] Thu, 27 November 2008 19:25 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Instead of a loop, consider performing an 'update', with a 'standard.commit', and then going to the block and doing an 'execute_query'. If you wish you can do a further 'go_block' to return to the master block.

David
Re: changing status of records in detail db [message #362703 is a reply to message #361742] Wed, 03 December 2008 16:05 Go to previous messageGo to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
djmartin wrote on Thu, 27 November 2008 19:25
Instead of a loop, consider performing an 'update', with a 'standard.commit', and then going to the block and doing an 'execute_query'. If you wish you can do a further 'go_block' to return to the master block.

David


It works, but this message appears "frm-40654: record has been updated by another user. re-query to see change." after I change status of master record and go to the detail records on other canvas. I am aware the solution is not to hide this message, but to fix the cause. Any suggestion is appreciated.
Re: changing status of records in detail db [message #362732 is a reply to message #362703] Thu, 04 December 2008 00:32 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Do a 'commit_form' before the manual 'update'.

David
Re: changing status of records in detail db [message #363026 is a reply to message #362732] Thu, 04 December 2008 16:39 Go to previous messageGo to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
djmartin wrote on Thu, 04 December 2008 00:32
Do a 'commit_form' before the manual 'update'.

David


I tried it all, in POST-UPDATE, in WHEN-LIST-CHANGED, in POST-FORMS-COMMIT.

Here is my code in a trigger:

---

COMMIT_FORM;
IF :db1.status='YES' THEN
UPDATE table2 SET table2.status='YES'
WHERE table2.id=:db1.id;
STANDARD.COMMIT;
END IF;

---

Just to mention that I have two data blocks: db1 and db2 based on two tables table1 and table2 and they are master-detail related through id field.
Re: changing status of records in detail db [message #364320 is a reply to message #363026] Mon, 08 December 2008 00:30 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Do it in the 'pre-update' at the block level of block1 (but without the 'commit_form').

David
Re: changing status of records in detail db [message #365227 is a reply to message #364320] Thu, 11 December 2008 02:59 Go to previous messageGo to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
djmartin wrote on Mon, 08 December 2008 00:30
Do it in the 'pre-update' at the block level of block1 (but without the 'commit_form').

David


I did that, but then just one record was updated in block2.
Re: changing status of records in detail db [message #375812 is a reply to message #365227] Mon, 15 December 2008 00:36 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Then your 'update' statement only selected one record. You did put the 'pre-update' with the manual update on the master record didn't you?

Post your code and please use code tags.

david
Re: changing status of records in detail db [message #375825 is a reply to message #375812] Mon, 15 December 2008 01:10 Go to previous messageGo to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
djmartin wrote on Mon, 15 December 2008 00:36
Then your 'update' statement only selected one record. You did put the 'pre-update' with the manual update on the master record didn't you?

Post your code and please use code tags.

david



IF :db1.status='YES' THEN

UPDATE table2 SET table2.status='YES' WHERE table2.id=:db1.id;

STANDARD.COMMIT;

END IF;



table1 related to db1
table2 related to db2
Re: changing status of records in detail db [message #376094 is a reply to message #375825] Tue, 16 December 2008 00:42 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Where have you used 'db2'? In which trigger do you have this code? What answer do you get when you run the following in SQL*Plus?
select count (*)
  from table2
 where table2.id = :db1.id;

David

Upd: You will have to evaluate ':db1.id', obviously.

[Updated on: Tue, 16 December 2008 00:44]

Report message to a moderator

Re: changing status of records in detail db [message #376206 is a reply to message #376094] Tue, 16 December 2008 08:27 Go to previous messageGo to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
djmartin wrote on Tue, 16 December 2008 00:42
Where have you used 'db2'? In which trigger do you have this code? What answer do you get when you run the following in SQL*Plus?
select count (*)
  from table2
 where table2.id = :db1.id;

David

Upd: You will have to evaluate ':db1.id', obviously.


The result is 3.

I managed to update all 3 records in db2, but still when I move from canvas1 to canvas2 "frm-40654" appeares: "record has been updated by another user. re-query to see change." Then I have to query it again in order to see the change.

I placed the code in PRE-UPDATE trigger in db1.

db1 is based on table1, the items of db1 are placed on canvas1.
db2 is based on table2, the items of db2 are placed on canvas2 (different window).

On canvas1, I change the db1.status into YES and do commit_form (I have button to do that). Then I open canvas2 to see whether
statuses in db2 had been changed. Then I get the frm-40654 message.
Re: changing status of records in detail db [message #376300 is a reply to message #376206] Tue, 16 December 2008 20:34 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Do you have a 'relations' defined between these two blocks?

Somehow you need to do an 'execute_query' on 'db2'. The problem is that 'execute_query' is a 'restricted' built-in.

David
Re: changing status of records in detail db [message #380560 is a reply to message #376300] Mon, 12 January 2009 05:54 Go to previous messageGo to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
djmartin wrote on Tue, 16 December 2008 20:34
Do you have a 'relations' defined between these two blocks?



I have.

Quote:

Somehow you need to do an 'execute_query' on 'db2'. The problem is that 'execute_query' is a 'restricted' built-in.

David


I've realized that Smile
Re: changing status of records in detail db [message #380567 is a reply to message #361684] Mon, 12 January 2009 06:12 Go to previous messageGo to next message
tipyyt
Messages: 11
Registered: March 2007
Location: Karachi
Junior Member

'post change'

GO_BLOCK 'BLOCK2'
update block2
set BLOCK2.status =:BLOCK1.STATUS
WHERE ID=:BLOCK1.ID;

Re: changing status of records in detail db [message #380569 is a reply to message #380567] Mon, 12 January 2009 06:20 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@tipyyt,

GO_BLOCK is a restricted procedure and can not be used in the POST-CHANGE trigger.
Re: changing status of records in detail db [message #380586 is a reply to message #380569] Mon, 12 January 2009 07:36 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

If you are getting desired execution and only tyhat message is a problem then
Try
if(err_type = 'FRM' )AND ERR_CODE IN ('YOUR FRM ERROR CODE')Then NULL;
end if;

on ON_ERROR Trigger on Form Level
Javed

[Updated on: Mon, 12 January 2009 07:39]

Report message to a moderator

Re: changing status of records in detail db [message #380669 is a reply to message #380586] Mon, 12 January 2009 23:41 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please stop quoting the whole of a post, we are able to read previous entries.

Please answer my previous questions.

David
Previous Topic: oracle forms
Next Topic: get developer suite's working directory
Goto Forum:
  


Current Time: Sat Dec 03 03:38:03 CST 2016

Total time taken to generate the page: 0.05445 seconds