Re: FORMS4.0 Question, relations between 3 blocks

Alan Lin <alanlin_at_thor.srl.caltech.edu> writes:

>I am trying to build a form for our department's paper publication data.
>I have created three tables,
 

>paper:
> paper_id, title, journal, volume, number, page, year, ...
 

>author:
> author_id, last_name, first_name, ...
 

>publication:
> paper_id, author_num, author_id
 

>A paper may have many co-authors, and the order of authors are important.
>So, one example may look like this,
 

> paper_id, author_num, author_id
> .. .. ..
> 23 1 45
> 23 2 3
> 23 3 57
> 23 4 18
> .. .. ..
>
>which means paper # 23 has 4 authors, their id are 45, 3, 57, and 18.
 

>I am trying to create a form with a layout like this,
 

> |======================================================================|
> | Paper Id ___________ |
> | Title __________________________________________ |
> | Journal ______________________ |
> | Volume ___________ Number _______ Page ______ Year _______ |
> | |
> |======================================================================|
> | |
> | Author Id Last Name First Name |
> | _________ _________ __________ |
> | _________ _________ __________ |
> | _________ _________ __________ |
> | _________ _________ __________ |
> | _________ _________ __________ |
> | _________ _________ __________ |
> | _________ _________ __________ |
> | _________ _________ __________ |
> | _________ _________ __________ |
> | _________ _________ __________ |
> | |
> |======================================================================|
>
>I have tried something out using Forms 4.0. I used three blocks,
 

> block_a (base table paper), displays 1 record,
> block_b (base table publication), displays 10 records,
NO: > block_c (base table author), display 10 records

Do NOT create the author block. Create two so-called "control items" in the publication block tha display the author first and last name. Have those fields filled by a post-change trigger on the author id item. This trigger will load the corresponding names from the author table when the form is displayed.

Allow a LOV on one of the author control items to select an author. When leaving the name field, add code to the when validate trigger to check if an existing author was selected. You could also put code in there to automaticvally generate an author id.

Good luck

>and following relations,
 

> block_b.paper_id=block_a.paper_id,
> block_c.author_id=block_b.author_id
 

>When I run query on a paper_id, the corresponding author_ids show up in
>block_b, but only the first author's last name and first name show up.
 

>Any suggestions to solve this problem, or any idea on how to design a form like
>this ?
 

>Your help will be greatly appreicated. Thanks!

>------------------------------------------------------
>Alan M. Lin
>Space Radiation Laboratory
>California Institute of Technology
 

>Tel: (818) 397-7072 Fax: (818) 397-7111
>Email: alanlin_at_thor.srl.caltech.edu
>Mail Stop: 220-22
>------------------------------------------------------
 

-- 
// ===========================================================================
// Roeland Loggen (rlogg_at_ctp.com)  After being built and asked "is there a god" 
// Cambridge Technology Partners   the supercomputer replied "well......
// Cambridge MA   (617)-374-8788   there wasn't one, but now there is"