displaying data from multiple tables in one portal

[rkass068]

Hello I have created a database for a hospital clinic that contains 3 tables:

 

1. Main- unique key=pk_main

2. Lab- foreign key=fk_main

3. treatment- unique key=fk_main

 

The lab table and the treatment table both contain a date field and a description field (text)

 

I want to be able to have one portal that will display the records of both the lab and treatment tables on the layout linked to "main"

 

for example,

 

date                Lab description

date                 Treatment description

date                 Treatment description

date                  Lab

date                   Lab

date                   etc

 

Is this possible to do? If so how?

[doughemi]

You need a Visits table which joins Main (which I assume contains patient data, and should be named Patients) to Labs and Treatments.

 

Each Visits record will contain  PatientID, LabID, TreatmentID foreign key fields, which are related to the primary keys of the  appropriate tables.  The Visits table is the only one that needs a date field.

 

A portal to Visits in a Patients layout can then show the fields Visits::Date, Lab::Description, and Treatment::Description.

[rkass068]

Thanks, I will try that

 

so what you are saying is I should have main:

 

pk_main=visits:fk_main

 

visits:pk_visits=treatment:fk_visits

                       =lab:fk_visits

 

Or should it be

 

main:pk_main=visits:fk_main

                      =lab:fk_main

                       =treatment:fk_main

and, treatment:pk_treat=visits:fk_treat

         lab:pk_lab=visits:fk_lab

 

which way would it be? wouldnt this also make many table occurrences as well?

thanks for the help I really appreciate it

[doughemi]

Like this

 

A portal in Main can show fields from Labs and Treatments because of the chained relationships through Visits.

 

Do a search for Join Tables in Filemaker help for more information.

[rkass068]

Like this

RD.png

A portal in Main can show fields from Labs and Treatments because of the chained relationships through Visits.

Do a search for Join Tables in Filemaker help for more information.

Thanks. I am trying that but FileMaker wants to make additional table occurrences for visits. Is there a way to shut that off?

Like this

RD.png

A portal in Main can show fields from Labs and Treatments because of the chained relationships through Visits.

Do a search for Join Tables in Filemaker help for more information.

Thanks. I am trying that but FileMaker wants to make additional table occurrences for visits. Is there a way to shut that off?

[doughemi]

What are you doing that requires additional Visits TOs? You need only one portal to Visits.

 

rkass.fmp12.zip

[rkass068]

I do not need additional TOs but when I set up the relationships with treatment and lab pointing to visits FM asks me to make another TO. I don't know why it is doing that. It then defaults the name to visits 2.

[doughemi]

Do you have relationships between Main and Labs or Main and Treatments?  If so, get rid of them.  See the example I posted.

[rkass068]

It worked!!! Omg thank you so much!!