Re: Forms 4.5 - Master Detail Problem

Three tables

Master -> detail -> subdetail
(one to many) and (one to one) relationship.

Master Populates detail.
Detail populates subdetail but only for the current detail record.

Easy fix... (keep dreaming :)

1. In the detail block, add an extra field(s) to store subdetail data.
2. Set the new field's "Database Item" property to "No"
3. Create a post-query trigger for the detail block <- this trigger fires once for each record fetched.

In the post-query trigger write

SELECT subdetail.column
INTO :detail.extra_field
WHERE subdetail.id = :detail.id;

4. Pour yourself cup of coffee, then watch see it run...

Subdetail block goes away...
updating the subdetail should be done elsewhere... but can be doen via when-validate-record and a good deal of work figuring that out.... :-o

David Donaldson <david.donaldson_at_capgemini.co.uk> wrote:

> I'm trying to create a from which has two master detail relationships
> but have hit a problem. The background to the form is as follows:
>
> This part of the database is used to track employees and their skills.
> The entity/relationship diagram looks like:
>
> Employee---<Skill_profile>---Skill
>
> The columns in the tables are:
>
> Employee:
> Employee_no
> First_name
> Surname
> Grade
>
> Skill_profile:
> Employee_no
> Skill_code
> Skill_level
>
> Skill:
> Skill_code
> Description
>
> What I'm trying to do is create a form which will show all the skills
> for one employee. I therefore set up relations under the employee and
> the skill_profile blocks with the following conditions:
>
> Employee:
> Master Employee
> Detail Skill_profile
> Join condition: Employee.employee_no = Skill_profile.employee_no
>
> Skill_profile:
> Master Skill_profile
> Detail Skill_code
> Join condition: Skill_profile.skill_code = Skill.skill_code
>
>
> The problem is that because I've set up an inverted relationship between
> the skill profile and skill, although multiple records are displayed for
> the skill_code and the skill_level, only one record is returned for the
> description. In order to display each description (which is taken from
> Skill) for each Skill_code(taken from skill_profile) the user has to
> click in each skill_code field.
>
> Is there a way that I can set this up so that all the descriptions are
> displayed for all the skill_codes?
>
> I'd really appreciate any advice as I'm a new user to Dev/2000. Thanks.

--
joebrown
_at_leading.net