Use of Portal to select related contacts

[TWillson]

I have a question regarding the way I am using portals in my database. I am new to Filemaker and have just begun to learn about scripts and calculations.

My database is used for patient records. I would like to keep track of various other service providers that these patients see, such as orthotists, medical clinics, doctors etc. I have a contacts database which lists all the other service providers and need to associate them with the patient record.

Originally I just used a lookup field to pick the contacts unique ID number, used relationship fields to show some of their basic info and a button which would go to the related record. However this seemed complicated and limited the number of contacts displayed in the patient record.

Alternatively, (based on an example from the book "Advanced FileMaker Pro 5.5 Techniques for Developer") I have set up a portal in the main patient database which lists all the contacts from the contacts database (relationship based on a calculated field in both databases with a value equal to one). When I select a contact a new record is created in a third database "paientscontacts", which copies the patients unique ID and the contacts uniqe ID into a new record. I than have a second portal setup to show all the related contacts for that patient.

Am I on the right track with this, or is there a better way to set this up?

Thank you,

Thomas Willson

[LiveOak]

This is a classic technique. What you have is a many-to-many relationship between Contacts and Patients. A Patient can have many contacts and a Contact may relate to many patients. Since FM doesn't really handle many-to-many relationships (yes, I know the exceptions, so don't write!), this situation is resolved into two one-to-many relationship using a "join" file (patientcontacts).

Similar needs exist in many common database situations. Another common one is between Invoices and Payments. One payment can be applied to many invoices and one invoice may have many payments.

Sounds fine to me.

-bd

[TWillson]

Thanks for the advice, just wanted to make sure. I actually took a few days off from working on this database (only part-time), and have been reading through a lot of the posts here. These forums have been a huge help to me.

Thanks,

Tom Willson

[LiveOak]

Always happy to hear that. I'd like to say that advertising covers the cost of the forums, but it's been more of a labor or love for Steve (OceanWest), Kurt (Database Resources) and myself (Live Oak Systems).

-bd

[Greenhill]

Sorry to resurrect a seemingly dead thread, but this is a perfect representation of my situation, but stops just short of the full help I need.

My structure is exactly the same as TWillson's, but for Patients, read Books, and for Contacts, read reviewers. Thus each individual book might be reviewed by any number of reviewers, and each reviewer might review any number of books.

As below, I have a portal with all my reviewers in on the book layout, and I now want to select a reviewer(s) to review a book. I have created a third database (booksreviews), but have no idea how to copy the Book and Reviewer ID detail into that database.

I can manually input into the third database and happily display the information in another portal on the Book layout (to show who has reviewed this particular book), but am stuck on automating it.

Many thanks

David Palmer

[Ugo DI LUCA]

Let see...

So you established :

1. Constant relationships from Books to Reviewers, and vice-versa, so that :

a. while being in Books.fp5, you have a portal listing all reviewers from Reviewers.fp5 through relationship "ReviewersByConstantInBooks"

b. while being in Reviewers.fp5, you have a portal listing all books from Books.fp5 through relationship "BooksByConstantInReviewers"

2. Constant Self-relationships in both files, so that you can have a portal listing :

a. all Books while being in Books.fp5 through relationship "SjConstantBooks"

b. all Reviewers while being in Reviewers.fp5 through relationship "SjConstantReviewers".

Here's one way of creating an affiliation and generate a new join record in BookReviewersJoin.fp5 by a few clicks (3), say from the Books.fp5 file.

Settings :

1. Create a new Constant relationship to the BookReviewersJoin.fp5, "JoinByConstant"

2. Create a new layout and draw 2 portals, one at Left side, the other at Right side of your Layout.

Left : "SjConstantBooks" portal

Right : "ReviewersByConstantInBooks"

3. Create 2 global fields gBookID and gReviewerID.

4. Create 2 new relationships (for display purpose only)

- "BookBygBookID" with gBookID matching BookID

- "ReviewerBygReviewerID" with gReviewerID matching ReviewerID

and Drop in the center of your layout 2 related fields

- BookBygBookID::BookName

-ReviewerBygReviewerID::Reviewer Name

5. Place a button "Affiliate", just next or down these 2 related fields.

6. Create a new global field gNewJoinID and a relationship "JoinByGlobal" with gNewJoinID matching ID in the BooksReviewJoin.fp5

7. Add a global gNewID in the BookReviewersJoin.fp5

Now, you're ready for the last step...

External Script (In BookReviewersJoin.fp5)

'Create New Record'

SetField[gNewID, ID]

Button in the"SjConstantBooks" portal

'Set gBookID'

SetField["gBookID", "SjConstantBooks::BookID"]

Button in the "ReviewersByConstantInBooks" portal

'Set gReviewerID'

SetField["gReviewerID", "ReviewersByConstantInBooks::ReviewerID"]

Button "Affiliate"

'AffiliateInNewJoinRecord'

PerformExternal[bookReviewersJoin.fp5,'Create New Record']

SetField["gNewJoinID","JoinByConstant::gNewID"]

SetField["JoinByGlobal::BookID","gBookID"]

SetField["JoinByGlobal::ReviewerID","gReviewerID"]

The 3 buttons are your 3 clicks...

Hope I wasn't too descriptive.