Display Second Owner Name in a Portal

[Michael Crawford]

Hello Fellow DBer's:

I need your help with an issue displaying the seconded owner name of an account that associates 2 names within a portal. I want to display the owner's names on a billing statement. Through the relationship it will display the first name (owner1) but will not allow me to display the second name (owner2).

Any ideas on how to best do this? Thanks Mike

Tables:

Units (where the portal resides)

Owners (where the owner information resides)

Accounting (where I want the 1st & 2nd owner names to appear, btw, the first

name appears but not the second)

Relationship:

Units (pkunit#) = Owners (fkunit#) allow creation

Units (uacct#) = Accounting (xacct#)

Portal includes:

field: ofname (first name)

field: olname (last name)

field: ostatus (menu select owner1, owner2, etc.)

Layouts:

Units displays the owner information (as a portal) and the unit information.

Owners displays the owner information entered in the portal.

Accounting information displays the billing statement with the owner names,

address, account number, etc.

[Ender]

Can an owner have more than one unit? How is Accounting related to the other tables?

[Michael Crawford]

Yes, but the primary key is unit (one unit, many owners). - Mike

[Michael Crawford]

Accounting is related to units by acct #.

[Ender]

Since each Unit can have more than one owner, and each Owner can have more than one Unit, this describes a many-to-many. The usual way to do a many-to-many is to create a join table between the two tables, let's call it Owner_Unit. This Owner_Unit table would have match fields for the UnitID and the OwnerID.

The relationships I see are:

Accounting <=> Unit:

Accounting::Acct# = Unit::Acct#

Unit <=> Owner_Unit:

Unit::pkunit# = Owner_Unit::fkunit#

Owner_Unit <=> Owner:

Owner_Unit::fkownerID = Owner::pkownerID

Assuming the relationship between Accounting and Unit is a Many-to-One, you should be able to insert a portal on an Accounting layout that shows related records from Owner (or Owner_Unit,) that shows all owners of the related Unit.

[Michael Crawford]

Thanks Ender, is this solution using FM6?

[Ender]

No, this is for FM7. This could be done in FM6, though it would require some lookups.