How to implement link tables

[patatparis]

Could someone please help with how to do link tables and multiple table joins?

The need is to have a 1-to-infinity relationship between tables, by using the link table.

The desire is to achieve an application supported by a fully normalized database behind it.

Table Client.fp5 serves solely as a GUI, containing singular field "person_id".

Other fields will be "dragged" onto the Client.fp5 layout from related tables using the layout tools.

Table Person.fp5 serves to contain the client's "person_id", "firstname", "lastname" fields (i.e. things which most people have only one of);

as well as several address_id fields.

There is a relationship Client::person_id<->Person::id.

Table AddressInfo.fp5 contains an "id" field, and address information such as street, city, etc.

There is a relationship AddressInfo::id<->Person::address_id1.

A relationship AddressInfo::id<->Person::address_id2.

A relationship AddressIndo::id<->Person::address_id3.

What I wish to do is show the address information from AddressInfo.fp5 on the layout of Client.fp5 when

their sole relatioship is through Person.fp5.

Thanks for your help.

-- Pat

[LiveOak]

Simple, although maybe not obvious. You need to add calculated fields to your Person.fp5 file to "relay" information from the address file and make it available in the person file.

create relationships from Person to Address and use these to display information in calculated fields native to the Person.fp5 file. For example, create in the Person file:

cState (calculation, text) = PersonToAddressRelationship::State

cZip (calculation, text) = PersonToAddressRelationship::Zip

etc.

It is now possible to access these fields from the Client file as related fields in the Person file.

-bd