Newbie needs help - combine many tables in portal?

[BrownFedora]

Hey, I'm developing a Contact Management System for a school team project and we're a little new to filemaker pro. Very new actually. We're working with FileMaker Pro 7 and need to if and how to mix different fields from related tables in a single portal.

Here's the deal:

The client wants a Contact Activity Log, tracking communication and donation activity. The Communication and Donation are related back to Contacts via 1-to-M relationship

Contact Table (ContactID, Name)

Communication Table (ContactID, MessageID, Message, Date)

Donation Table (ContactID, DonationID, Amount, Date)

For a given contact, I can get a portal to show either the related communication or donation info. But what we need is to combine the two, put them in ONE portal, and then sort by Date (I've seen plenty of posts on sorting portals so no need to pass on how to there).

Ex. portal should look like below for ContactID - 3

MessageID - 3 | Phone call from Bob | 04/04/2004

MessageID - 2 | email from Mike | 04/02/2004

DonationID - 3 | $50 cash | 03/29/2004

MessageID - 1 | talked with Jane | 02/16/2004

DonationID - 1 | gave painting | 02/14/2004

Is this possible? Can a script do it and if yes, how?

Any help greatly appreciated

FileMaker Version: 7

Platform: Windows XP

[Vaughan]

This is a data design issue. Portals display related records from only one table at a time. (You can have more than one portal on alayout of course.)

What is the relationship between messages and conations? Do you want to get donation totals out of the system or are they really just messages as well?

To get the display that you want you need to enter the donations into the messages (or the messages into the donations). In other words, a record in this related file could either be a message or a donation.

At this point I'd be re-evaluating the requirements of the interface and finding out why the users want to see donations and messages mixed up together. Maybe they could have them in separate portals side-by-side?

[BrownFedora]

The client wants to able to look at a compilated activity history that they have for a given contact. It is important to see these activities together in order to see trends or coordinate the next move (EX. you can see Donation was given but no Thank You Letter has been sent yet).

There are more activities (volunteering and event attendence) to track but I just listed the two in the example to simplified the explination. I had considered merging the tables but they figured that's be 2 steps back as far as normalization goes (original data was just a giant excel sheet, dozens of fields, no relational structure).

We have separate portal views for each individual activity on given layouts, but the portal on the main page should display all them together in relation to their date.

Would this be possible with some kind of script? :

1) create an table with generic fields, zero records (Generic Table)

2) for given contact, copy related records from activity tables into Generic Table

3) sort Generic Table by Date

4) display Generic Table in portal

5) empty Generic Table when you move to next contact (back to step 2) and repeat)

FileMaker Version: 7

Platform: Windows XP

[Ender]

If the different tables have similar types of information, then I think they should be in the same table. You can always access different subsets of that table through filtered portals (one for Contacts, one for donations).

If the different tables have different types of information, or you have other compelling reasons to keep the tables separate, you could try one of these two methods to generate an Activity Log:

(1. Every time a Contact Note, Donation, or other type of activity is done for a contact, create an Activity Log entry in an Activity Log table.

(2. When your users go to the Activity Log screen, a script imports the related Contact Notes, Donations, and other types of activity to an Activity Log table.

The first method would require a script to "process" the new records in each of your activity files and would add a bit of overhead to the record creation. The second method could be slow when your users go to the Activity Log screen.