Home » Developer & Programmer » Forms » Key-Exeqry Trigger and Population of Items (Oracle Designer 9.0.4.4.8, Windows XP Professional)
Key-Exeqry Trigger and Population of Items [message #382939] Mon, 26 January 2009 08:33 Go to next message
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 #382947 is a reply to message #382939] Mon, 26 January 2009 10:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, cursor-style solution is a complicated one. A simple one would be creating two blocks: master would display current data (such as "start_date" and "company_id"), while detail would display history ("previous_...") data. Relation with the master would, probably, be the "company_id" column.

Such a design might do the job in a few mouse clicks; Forms is capable of taking care about all the details (OK, most of the details).

You'd use the POST-QUERY trigger to populate non-database items (such as "company_name" through "company_id" etc.).
Re: Key-Exeqry Trigger and Population of Items [message #382949 is a reply to message #382947] Mon, 26 January 2009 10:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 Go to previous messageGo to next message
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 #383016 is a reply to message #382974] Tue, 27 January 2009 00:04 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If your users are 'pretty savvy' then they should be able to draw a screen layout for you that illustrates how they would handle the issue of the single 'current' record having multiple 'past' records. Once they have drawn it out for you come back to us.

David
Re: Key-Exeqry Trigger and Population of Items [message #383024 is a reply to message #382974] Tue, 27 January 2009 00:20 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you think about @cookiemonster's idea? Creating a view and basing a data block on this view? Also, if there is one master (current data) record which can have more than one detail (history data) records, by default you'll display equal number of master and detail records UNLESS! you set the "Number of items displayed" item property to 1. Doing so, it is possible to simulate master-detail design on the same block.

To illustrate it, here's an example based on Scott's EMP table. I have displayed 3 columns: JOB (which will be used as a "master" column - its number of items displayed is 1), employee name and salary. In this example, I have queried employees by job = 'CLERK'. Perhaps something like this might help?

./fa/5636/0/
Re: Key-Exeqry Trigger and Population of Items [message #383154 is a reply to message #383024] Tue, 27 January 2009 07:29 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Littlefoot,

Your diagram illustrates precisely what I need! 1 master and multiple details. I still haven't found a way in Designer.

Did you do this example using the master-detail approach in form builder?
Re: Key-Exeqry Trigger and Population of Items [message #383159 is a reply to message #382939] Tue, 27 January 2009 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #383163 is a reply to message #383159] Tue, 27 January 2009 07:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
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.
Re: Key-Exeqry Trigger and Population of Items [message #383165 is a reply to message #383163] Tue, 27 January 2009 08:01 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Littlefoot,

Could you please walk me through the steps of how you achieved this? This is a ray of hope after struggling with this requirement for so long!!

Greatly appreciate everyone's efforts
Re: Key-Exeqry Trigger and Population of Items [message #383172 is a reply to message #383163] Tue, 27 January 2009 08:50 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 #383214 is a reply to message #383165] Tue, 27 January 2009 15:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, I don't speak Designer.

The key point in Forms Builder is the highlighted part of the screenshot - setting the "Number of items displayed" to 1. I really wouldn't know how and where it can be set in Designer.

Just being curious: someone has told you to do the job. How come they expect you to do something if you are NOT educated and do NOT know how to use a tool? "Master-detail" isn't really high technology, so ...
Re: Key-Exeqry Trigger and Population of Items [message #383221 is a reply to message #383214] Tue, 27 January 2009 17:16 Go to previous messageGo to next message
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 #383251 is a reply to message #383221] Wed, 28 January 2009 00:13 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
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".

The fact is: you should join blocks on a common column; it is usually done by joining primary key of one table with a foreign key from another table (but doesn't necessarily have to be that way).

Condition you are talking about (start_date < :my_block.start_date) should probably be entered into the WHERE clause block property. In words: "Master block contains departments. Detail block contains employees. Join them on department ID so that I could fetch all employees from a certain department. Set the WHERE clause of the detail block so that it doesn't display all employees, but only those whose hire date is less than some other date (for example, 1st of December last year)".

I wouldn't know what has happened with other blocks; what do they do, anyway? Are they also details of the master block? You might review triggers and procedures created by Forms (or Designer) (Wizard); perhaps they got screwed up.
Re: Key-Exeqry Trigger and Population of Items [message #383474 is a reply to message #383251] Thu, 29 January 2009 00:18 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I suggest creating a view based on your table in your Designer database definition. Then create a link between the view and table. Use the view and the table in your Form in the Designer and I think that the Designer will automatically create the master-detail for you.

Actually, point your designer at the demonstration database and get it to create a master-detail form based on 'emp' and 'dept'. Use this simple linked pair and then move on to a Form that points to the same table twice.

Try searching the 'Designer' forum for 'master detail'.

David
Re: Key-Exeqry Trigger and Population of Items [message #383841 is a reply to message #383474] Fri, 30 January 2009 11:09 Go to previous messageGo to next message
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 #383859 is a reply to message #383841] Fri, 30 January 2009 14:02 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why making things complicated? Base your "previous data" block on this table (where "previous data" columns are) and master-detail relationship will take care about everything; you don't have to code it (more you code, more chances to screw something up).

If you insist on manually selecting data into forms' items, you'll need to do that in a loop because you have to explicitly tell Forms to insert data, move to the next record, insert data, move to the next record, etc.
Re: Key-Exeqry Trigger and Population of Items [message #383872 is a reply to message #383859] Fri, 30 January 2009 17:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 time

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 Go to previous messageGo to next message
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. Sad 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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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. ./fa/1620/0/ 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 Go to previous messageGo to next message
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 #384266 is a reply to message #384185] Tue, 03 February 2009 01:04 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Master-detail can be done in Designer. Please learn how.

David
Re: Key-Exeqry Trigger and Population of Items [message #384658 is a reply to message #384185] Wed, 04 February 2009 12:25 Go to previous messageGo to next message
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.
Re: Key-Exeqry Trigger and Population of Items [message #384672 is a reply to message #384658] Wed, 04 February 2009 14:49 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/2115/0/
Previous Topic: tabpage icons problem
Next Topic: how to set push button iconic
Goto Forum:
  


Current Time: Fri Dec 09 06:05:44 CST 2016

Total time taken to generate the page: 0.08738 seconds