Key-Exeqry Trigger and Population of Items [message #382939] |
Mon, 26 January 2009 08:33  |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
I have a form with some fields like "start_date", "company_id", "previous_start_date", "previous_company_id". Out of these, the "previous" fields are unbound and the others are bound to "my_companies" table. All are in the same block on the form. I need to select "previous" values based on the "start_date" and "company_id". More than one "previous" values is retrieved each time.
My first attempt was using a post-query trigger which runs a cursor. My problem is that my cursor writes back only one value into my form field. I ran the cursor as a pl/sql block in TOAD to test and it fetches all the records that I need. Then I realized that "next_record" cannot be used inside a post-query trigger.
The second option that someone suggested to me was to use the "key-exeqry" trigger on the module (block). I'm not sure how to write and call my cursor there. In the "key-exeqry" trigger for the module, there is some existing code with some lines of "set_item_property".
Can someone please guide me with writing my code in order to achieve the desired result?
The form items are of type "custom" and display type is "text" (I'm creating the form in Oracle Designer).
My cursor code is something like this:
declare
cursor previous_data_cur is
select company_id,start_date
from my_companies
where company_id = :myblock.company_id
and start_date < :myblock.start_date;
BEGIN
open previous_data_cur;
LOOP
fetch previous_data_cur into :myblock.prev_company_id, :myblock.prev_start_date;
EXIT WHEN previous_data_cur%NOTFOUND;
next_record;
END LOOP;
close previous_data_cur;
END;
Any help is greatly appreciated.
|
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #382949 is a reply to message #382947] |
Mon, 26 January 2009 10:58   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
As far as possible, my users want the "previous data" fields to be on the same block as the current data. That's the reason I'm trying to use the cursor approach. As I mentioned, my problem is about looping through the cursor.
Can you provide any suggestion to achieve that?
I do appreciate your reply.
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #382951 is a reply to message #382939] |
Mon, 26 January 2009 11:12   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If the users really want the previous records on the same datablock as the current records, and you can have multiple previous records per current record, then you've got a problem.
Because you're going to have to display the current records multiple times (once per corresponding previous record).
If you really need to do this then what you really need is a view that queries all the current data and joins it to all the previous data.
You can then base the datablock on that.
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #382953 is a reply to message #382951] |
Mon, 26 January 2009 11:48   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
Is there any way to display current data only once and previous data multiple times? I was thinking that using "text" items would put a default scrollbar if multiple values are retrieved.
Also, I'm not familiar how to create master-detail blocks in Designer. Can you please guide me through the steps? I'm a designer and forms newbie and am sort of stuck!
Thanks, once again, for your time.
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #382969 is a reply to message #382953] |
Mon, 26 January 2009 15:56   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote: | my users want the "previous data" fields to be on the same block as the current data
|
I wouldn't agree; your users want to be able to see all information they need. You, as a developer, should take care about providing these information in the most suitable way. What does an end-user know about forms' design? Most probably - nothing!
Users should know WHAT they want, you should do the HOW part of the job. Not vice versa. If you let users design your forms, oh boy, oh boy.
I still think that master-detail is a good approach. I don't use Designer so I can't navigate you through the process, but - as far as Forms Builder is concerned - master-detail form can be created in a matter of minutes. I suppose that Designer can do that as well, if you know how to do that.
Although, as you've said that you are not an experienced Designer nor Forms user, why do you insist on (a complicated) coding when these tools (Designer, Forms Builder) are here to help you do that job? Why do you think that you'll be able to code it more successfully than (Forms) Wizard can guide you through the master-detail design process?
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #382974 is a reply to message #382969] |
Mon, 26 January 2009 16:52   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
My users are not exactly naive..they are pretty savvy with the Forms side. If there is no way of achieving their requirement, then I'll have to convince them about the master-detail approach.
Also, using Designer is kinda mandatory at the client side. They need us to create forms in Designer and then generate the FMB out. Designer is not a friendly tool at all. I know it sounds strange considering that the use of Form Builder is much simpler. But as they say, customer is king!
Anyway, I guess I'll see if someone else responds to this post with some other ideas.
I really appreciate everyone's contribution so far.
|
|
|
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #383159 is a reply to message #382939] |
Tue, 27 January 2009 07:42   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Littlefoot didn't use master/detail in his example but if that layout is really what you want then master/detail is probably the simple way to do it.
Create two datablocks - one for current data and one for previous data.
Create a relationship between them.
Set the number of records displayed for the master block to 1.
Set the number of records displayed for the detail block to > 1.
Put the master items on the left of the canvas and the detail items on the right.
Job done
|
|
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #383172 is a reply to message #383163] |
Tue, 27 January 2009 08:50   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Littlefoot wrote on Tue, 27 January 2009 13:58 | Heh, cookiemonster, only if it was that simple! I'm talking about master-detail all the time, but - scorpio4frenz says that this is not an option because users want 1 block, not 2.
|
Sure, but that might well be based on a flawed understanding of how these things work.
scorpio4frenz - any particular reason why you can't try the steps suggested?
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #383186 is a reply to message #383172] |
Tue, 27 January 2009 10:33   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
My basic problem is that Designer is not a very user-friendly tool and I'm not sure how to go about implementing master-detail relationship in it. There aren't many helpful tutorials available! Also, in my case master and detail both get data from the same table (my_companies table). The data in the "detail" is restricted by start_date (start_date < start_date of current record). I'm not able to successfully create this in Designer.
That's why I'm looking for as much help here as possible.
Once again, thank you all for helping out.
|
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #383221 is a reply to message #383214] |
Tue, 27 January 2009 17:16   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
Littlefoot,
I have an idea about creating master-detail directly in Form Builder. I'm still in the learning phase with Designer. And there isn't much help available online! Also, I'm pretty new to Forms as well. I'm primarily an Apex developer and just gaining entry into the Forms area.
Anyways, I tried creating a master-detail scenario directly in Form builder to see if it works. First I created a view which pulls only company_id, start_Date columns from my_companies table. Then I added a block to my form based on this view, and created a relationship with the master block. The problem is that this detail block works fine (pulls all the company_id and start_date values matching current record). However, the other blocks on my form have stopped working!! The other blocks are all related to my master block by a "join" relationship. They are each based on a different table which has a foreign key in my master table. Again I'm not sure as to what is going on!! Also, while using the "data block" wizard to create master-detail relationship, I didn't see any place where I could add a "<" (less than) relationship...I need to use a condition "start_date< :my_block.start_date".
I greatly appreciate your time and effort. I'm definitely learning a lot of things about Forms from this forum. Thanks, you guys.
|
|
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #383841 is a reply to message #383474] |
Fri, 30 January 2009 11:09   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
Finally I decided to create a separate block on the form instead of placing the "previous" data on the same block.
I created a new block "prev_companies" based on the table "dual" and created unbound items on it for my data. I thought that it would be easier to simply write a query in some form of trigger to fetch data into this block. However, I'm not able to figure out which trigger to use! I tried "when-new-record-instance" but it fetches data into my "prev_companies" block only once.
Can anyone suggest what trigger can be used to achieve this? My query is something like:
select company_seq_num, start_date, status_code
into :PREV_companies.prev_seq_num, :PREV_companies.prev_start_date, :PREV_companies.prev_status
from companies
where start_date < :my_companies.start_date;
Once again, I greatly appreciate all the help that I'm receiving through this forum.
|
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #383872 is a reply to message #383859] |
Fri, 30 January 2009 17:13   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
Littlefoot,
As I have been saying before, master-detail is not working out for me..first of all because i'm using Designer. Also, in Form builder, when i run my form without the "previous" data block, all other blocks (based on various tables whose primary keys are foreign keys in my master table) work fine, but when i implement master-detail for "previous" data, all my other modules stop working...so something is definitely screwing it up.
Moreover, there is no master-detail relationship as such because all the data (current and previous is coming from the same table).
I know i will need a loop and a cursor to manually do what I'm trying to do. It doesn't seem like a major issue if I know where to place my code! As I said before, I tried putting it in "new-record-instance" but that's not working out.
If I decide to do this manually, which trigger would I need to place my code in?
Thanks for your feedback so far.
[Updated on: Fri, 30 January 2009 17:15] Report message to a moderator
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #383891 is a reply to message #383872] |
Sat, 31 January 2009 00:24   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
What a nightmare!
OK then. A block which is not based on a database table or a view is called a "control block". In Forms, you don't have to base it on any table (including DUAL which you have chosen now). But, if you are happy with that solution, fine with me.
In order to fill it with data, you have to know the WHERE clause of a SELECT statement which will join "current data" and "previous data". Master-detail for the last timeThe same column (or columns) would be used in "my" master-detail relationship - I *know* there are no two different tables, but nothing prevents you to create such a design by using the same "join" column(s) both in master and detail block. But OK, you don't want to do that as you don't know how, and it appears that Forum members can't help - so far - either. I'll try to give it a rest too (my master-detail idea, that is).
Once you have fetched data into the "current data" block (I believe that you'll do that by executing a query), I suppose there must be some kind of a "company ID". If so, I'd use the POST-QUERY trigger which is a perfect choice for such an operation. It might look like this:
begin
for cur_r in (select company_seq_num, start_date, status_code
from companies
where company_id = :current_data_block.company_id --> join condition
and start_date < :current_data_block.start_date
)
loop
:previous_data_block.company_seq_num := cur_r.company_seq_num;
:previous_data_block.start_date := cur_r.start_date;
:previous_data_block.status_code := cur_r.status_code;
next_record; --> go to the next record
end loop;
If you don't use NEXT_RECORD, you'll be overwriting data in the first "previous data" block record all over again, as long as there are records to be fetched from the cursor in the cursor FOR loop.
How does this look like?
[Updated on: Sat, 31 January 2009 00:27] Report message to a moderator
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #383897 is a reply to message #383891] |
Sat, 31 January 2009 00:33   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
Littlefoot,
I had implemented this even before I posted the question for the first time few days ago! Post-query trigger seemed like the best choice, but unfortunately "next_record" is restricted in the post-query trigger. That's where my entire complication (nightmare) started!!! Then another fellow programmer suggested using key-exeqry trigger but that didn't work out either and I finally posted my question on the forum.
Any other suggestions?
As I've been saying again and again, I really appreciate your help. This situation almost seems like a never ending nightmare!
[Updated on: Sat, 31 January 2009 00:36] Report message to a moderator
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #383899 is a reply to message #383897] |
Sat, 31 January 2009 00:52   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote: | unfortunately "next_record" is restricted in the post-query trigger
| Me, stupid. Me, very stupid. Sorry, I don't know what I was thinking of.
Hm, you have said that you have tried with the KEY-EXEQRY trigger as well. Could you, please, try to do that once again, but this time using code I have provided (the one from the POST-QUERY trigger):-- KEY-EXEQRY trigger
execute_qery;
go_block('previous_data_block'); --> otherwise, you'd do the NEXT_RECORD
--> in the current data block
for cur_r in (select ...
loop
:previous ...
next_record;
end loop;
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #384185 is a reply to message #383899] |
Mon, 02 February 2009 10:59   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
Littlefoot,
Congratulations! This is a huge step forward...this solution works but needs some fixing. If i just do an execute query when my form loads, this newly created block (based on view) remains empty. If I query by putting a specific company_id in my master block, then this new block grabs all the "previous" data but then my other blocks in the form remain blank. One reason I thought of for this issue is that foreign key relationship is not set properly in the view. I'm not sure how the FK should be since this is a one-to-ne relationship between "my_companies" and "view_my_companies" and not a typical one-to-many.
Any pointers?
Thanks again for the solution.
|
|
|
|
Re: Key-Exeqry Trigger and Population of Items [message #384658 is a reply to message #384185] |
Wed, 04 February 2009 12:25   |
scorpio4frenz
Messages: 48 Registered: October 2008
|
Member |
|
|
Everyone,
Thanks a lot for your help. I have finally solved my problem! I created a view for the "previous" data and then created a child block based on it. Finally figured out how to create the key link in Designer.
Once again, i greatly appreciate everybody's time.
|
|
|
|