Hierarchical portals?

[Wes Jones]

Hello,

I am developing a database for a non-profit spay/neuter clinic for our local humane society. We are working in FM6 because it was very affordable. Among many things, I am trying to create an appointment scheduling page that pulls information from a Client (people) database and a Patient (animal) database. I want to be able to display, add, and modify appointments showing related information from both databases with one Client/Patient per line, showing only those appointments for that date. I have a third database with Appointment dates, Client ID, and Patient ID.

Clients (with unique ID) can have many Patients (with unique ID)

Appointments can have many Clients

Two ways I am trying to make this work are as follows:

1. Make a portal in a separate database (sort of a Master) that would reference the Appointment date to show those Clients with their Patients for those dates. This works, but it only lists the actual information from the Appointment database (Client ID & Patient ID). I need more information than just the ID’s, so I would like to refer back to the individual databases. I think what I need to have the portal look at Date -> Client -> Patient, but I cannot have a portal within another portal. Or can I? Lookup fields only look “back” one level. Another thought is to have two portals (one for Clients, one for Patients), side-by-side, but I need the lines to be linked somehow.

2. Or I can try to do a self-join in my Appointments database, but when I try to make a portal for the date, I just get a the same values repeating the same number of times that appointment date is in the database

I keep trying small variations on these same two tracks and failing. I am obviously missing some (hopefully elegant) solution that creates a one line entry consisting of multiple fields from two different databases (Client & Patient) that are linked but are grouped by a third variable (Appointment).

Any insight would be greatly appreciated and apologies if I have not conveyed my project and problem correctly. As I am a newbie, step-by-step detail will not be wasted on me!

Thanks in advance,

Wes Jones

Napa Humane

[Fenton]

You have to use a technique called "tunneling," which we no longer often need in FileMaker 7/8. Create a calculation field in Patients, = Client::PersonsName (based on a ClientID).

Then you can show that in the Appts. file, pointing at the Patients file, via PatientID. BTW, it is:

Date -->Patient -->Client

not

Date -->Client -->Patient

Actually it is:

Appt::PatientID = Patient::PatientID

Patient::ClientID = Client::ClientID

These are 3 separate files. It can't be done correctly in less. There is only a Date in the Appts. file. Any given Patient could have multiple Appts., at different dates.

Or you can upgrade to 7/8 and this problem goes away; because in 7/8 you can view data from several relationships away. It makes relational databases so much easier. (It also allows you to put all the data in 1 file, in separate tables.)

Edited April 11, 2006 by Guest

[Zero Tolerence]

There used to be a great whitepaper on this. I believe it was posted somewhere on the the forums. Anyhow, it explained this stuff very well. Anyone remeber the post?

[Wes Jones]

Thank you both for the information. I was able to create pipelines to retrieve the records. I was also able to locate the .pdf on creating data matrices (http://www.nightwing.com.au/FileMaker/resources/MatrixModelling.pdf ). This has intrigued me because I do have many fields that I am trying to retrieve up through multiple tables. Creating a pipeline for each field seems pretty cumbersome and repetitive.

However, when I try to retrieve data from the matrix, I only get the first matching record, so I am continuing with multiple pipelines.

Any tips on using portals and a data matrix to access data that is several relationships away?

Again, thanks for getting me to this place!

WJ

[Wes Jones]

Hi,

As an update, I am still trying to use the matrix structure to bring many fields up through a series of tables into a portal in FM6. I've made a small mock-up to try different solutions. So far, I am still stuck, getting just the first record repeating in my top level portal. I am probably doing something that is obviously bone-headed, but I keep going round and round. Any guidance is greatly appreciated. Thanks,

WSJ

TestMatrix.zip