Associate/display mult records from related file

[hambone]

I am trying to modify the database for a small non-profit. I am not a database guy and to date have only done simple modifications to our system. So here is what I am trying to do and how I am trying to do it.

We have a members database. I want updateable list for each member record showing which activities they attended. I want to be able to search for members who attended activities based on date, number of events, sponsor of event etc. I want to be able to add new activities. I also want to do a number of calculations across this data including a count of activities each member attended, total number of members that attended more than x number of activities etc. Until now, we have stored this information in a big text box list. Searches are extremely limited. So I am looking for a way to accomplish this and thought that building a related Activities file would accomplish this. But maybe I am barking up the wrong tree and there is a better way to accomplish this

[QuinTech]

Hi, Hambone, and welcome to FM Forums!

You are asking a lot here (as you probably know). It sounds like, if you're going to do this yourself, you're wise to think about picking up a book. I can't give any recommendations on that. But i know from experience that developing databases for small non-profits is one of the most challenging jobs you can take on. (As are most jobs in the non-profit world, it seems!) You will have no budget, your goals will continuously shift, and if your database does everything you want it to, most users will complain that it is too complicated to use.

I'd encourage you to look into already-developed systems that might meet your needs. I haven't used ebase myself, but i've taken a look at it and it looks like a good system. You might want to try their free download and see if it's a package that could meet your needs.

I don't mean to discourage, but you might save yourself 2,000 grey hairs by going with an established software package instead of trying to build your own.

HTH,

Jerry

[transpower]

Hambone: Pick up Learn FileMaker Pro by Stars, et. al., and work through the end-of-the-chapter problems. When you're done, you'll be in better shape to tackle your problem.

[hambone]

Thank you both for your response. I have looked at ebase on and off again over the last couple of years. The upfront investment would be substantial, both in terms of customizing it for our application and software/hardware infrastructure. Moreover, our existing database system has evolved over 10+ years with several people working on it and moving it to another system would be a non-trivial exercise. I have also considered open-source Linux solutions but I have heard of a number horror stories of substantial investment in roll-your-own solutions that are difficult to use at best. So I am resigned to learn what I can about filemaker...

So thanks for the book recommendation. I've ordered it and a couple of otherrs.

But can someone tell me whether I can create a single list of Activites and then associtate/view/query some subset of those Activities with each Member record ala

Activity File

Activity 1

Activity 2

Activity 3

Activity 4

Activity 5

Member File

Member 1

Activity 3

Activity 4

Member 2

Activity 1

Activity 3

Activity 5

Member 3

Activity 4

Activity 5

.

.

.

Member N

Seems like this would map to a product ordering or invoicing system. Thanks again for any reponse.

[QuinTech]

The only way that I have been able to display more than one Activity record in the portal is to give multiple records the same id/key which defeats what I am trying to do.

This is perfectly appropriate given the right conditions. You will probably want to use a join table from the Activity file to the Member file. There's a wealth of information on this site about join tables; just do a search for "+join +table" (without the quotes) and you should have more than enough information to get started.

HTH,

Jerry

[hambone]

OK I mapped the "Creating many-many relationships" from Filemaker 5.0 help to my application. I have a Member file, an Activity file and the join file MemberActivity. I placed a MemberActivity portal on the Member file layout. I am able to enter 1 MemberId and 1 ActivityID per MemberActivity record and it appears on the appropriate Member record. I can create many MemberActivity records, each with a single MemberId and ActivityId and these are displayed on the appropriate Member record. But what I really want to do is either have one MemberActivity record per activity with multiple MemberIds (and associated fields) or 1 MemberActivity record per member with multiple ActivityIds (and associated fields).

I thought that multi-key fields were the way to go but I had two problems. One is I couldn't get the other fields associated with 2nd, 3rd... key instance to display in the MemberActivity record. Second, from reading this forum, I get the impression that multi-key in a join table is redundant...that I should be able to do the job with a join table.

Thanks for any help!

[QuinTech]

I get the impression that multi-key in a join table is redundant

Yep, you're right about that. In fact, a multi-key in this scenario is downright hazardous, because

I couldn't get the other fields associated with 2nd, 3rd... key instance to display in the MemberActivity record

Why do you want one MemberActivity record per activity or per member? I feel like you are applying multikey logic to a join table. If you are trying to have, say, the activity name display in the portal, you can do this by adding a calc to MemberActivity that pulls the activity name into this join table as a field in the join table. This is an important concept for Filemaker 5 and 6 which is (blessedly) moot in version 7, but i digress.

J

[hambone]

I want to avoid NxM records in the MemberActivity file (see below). Also I am using several calc fields in MemberActivity and they work fine.

MemberActivity Records

memberID 1 ActivityID 2

memberID 1 ActivityID 3

memberID 1 ActivityID 7

memberID 2 ActivityID 1

memberID 2 ActivityID 3

memberID 2 ActivityID 4

Member Records

member 1

ActivityID 2

ActivityID 3

ActivityID 7

member 2

ActivityID 1

ActivityID 3

ActivityID 4

Thanks...

[QuinTech]

I want to avoid NxM records in the MemberActivity file

But why?

[carlyle]

I may be missing something here, but I think that there may be a simpler solution for your problem (unless I am mis-understanding your problem).

Here is how our non-profit database is set up. Each member has a unique member ID (auto entered as a serial number). In our activities database, I have a field called member # also. I use the member number field as the link field for both databases. When I define the relationship in the members file, I clidk all applicable buttons (delete related records when I delete the main record, allow creation of related records, and sort related records by date).

THen, in a portal layout within my membership database, I put all of the fields from the activities file in the same row (date, activity name, # hours, etc).

Now here is the key that I think you are missing. I never enter data about the activities from within the activities file. I only enter data about the activities from within the membership file (which automatically puts the correct member ID number in the record in the activity file). In other words, if Joe Schmoe volunteered for our river cleanup, I find him in our member database, go to the portal, and enter his volunteer hours, date and othe information related to his activity. THEN to analyze the data (i.e. to find out how many volunteer hours people put in at the river cleanup) I go to the activities file, do my find, and create a summary layout to display the totals. In my analysis, I always ahve to ask the question "What am I trying to find out"...if I want to find information about people (i.e. how many people came to the river cleanup), then I do the find from within the member database. If I want to find informaiton about the activity (i.e. how many people hours went into the rivercleanup), then I go to the activity database.

This same thing works for tracking our members' donations. I have a separate file that has all donation information (member ID, amount, date, commments). I display this information in a portal, and do all data entry from within the members file (through the portal). When I want to know how many people donated this year, I do the find through the membership database. but if I want to know how much money we made this month, I find it in the donations database.

I don't think that you need a many to many file for this--just create a field in your activity file called "Activity type" and then if you want to find all information about a River Cleanup or whatever, you can do a find for activity type=river cleanup and the date that you want.

[QuinTech]

Hi, carlyle. That's certainly a valid way of doing it, and if it works for you, you should go with it. However, you've identified one of the issues with this approach -- you have to create multiple records for each activity (that is, your activities file will have x number of records for the same River Cleanup activity).

I would find such an approach to be more standard for a Donations file, where each donation is truly a unique event. But Activities are common events, and thus the usual treatment would be with a join table.

J

[carlyle]

Yeah, I think it depends on how hambone is using the data. For us it works just fine to have a database that has one record for each "volunteer event" (or "activity" as it may be)...meaning one person on a given date volunteers x hours doing xyz. Sometimes, there are multiple volunteers all helping with the same activity (i.e. a river cleanup), and sometimes not (i.e. someone comes in to do data entry). Having one record for each volunteer event works best for us since this way, each volunteer event doesn't have to be associated with a larger "activity." And if I want summary info for the larger activities, I just find those records... Your solution is definitely more streamlined for people who are doing a lot of work within each activity.

[hambone]

Hi QuinTech: This is in response to your post above...(haven't figured out how to use the quote feature yet.) I think what you are describing is exactly what I want to do. Just to be sure, do I need a join record for each individual member-activity association? Thats what I have now but what I would like is a single join record for each activity with all members who participated in the activity listed in that record. I am trying to avoid creating a separate join record for each single member-activity association. I just haven't figured out how to do it. What am I missing?

[QuinTech]

Hi hambone. To quote, you would type a [ and then the word quote and then a ], then paste the quote, and at the end type a [ then a /quote then a ]. Like so:

this is your quote

Yes, you do need a join record for each individual member-activity association. That is the essence of a join file. If you are going to have one record, then it is not a join file. If you are worried about users having to navigate a bunch of mumbo-jumbo join records, then just don't let them navigate that-- they should only be able to access the join records through your nice, clean portal.

You are still applying multi-key logic to a join table structure. Just forget about that for the time being, follow this model, and i think it will make sense eventually.

Carlyle has a good idea, though-- if you're willing to create a new record for each activity, this will certainly work. It will make reporting in the traditional sense more difficult, but it is a quicker setup.

J

[hambone]

So is there a way to make the join record creation transparent to the person doing data entry? We have new activities weekly so it would be nice if the data entry person could go to the Activities file, create a new record for the latest activity and fill in the fields. Then go to the Members file, search for each person attending the activity and add the activity to the activity list...the appropriate join records being created as that entry is made. It seems cumbersome to require the data entry to manually create each join file.

[Fenton]

The data entry people never "go to" the join file (table) really. You may want to script the choice of Activity for the person; at least filter the choices to future activities (don't need past ones). Unfortunately this is easy enough in FileMaker 7, but kind of a pain in earlier versions. Is 5 the version you have to write for? It does not even have related value lists; if I remember that came in 5.5. But I'd use a portal anyway, which would work in 5.

To deal with date ranges; there was/is a free plug-in, by David McKee (FileMaker engineer), Ranges, at:

http://www.geocities.com/SiliconValley/Network/9327/plPLUGs.html

It's an oldie but a goodie, and would work with your system. There's also the Smart Ranges technique; it doesn't need a plug-in, but has a bit of a learning curve.

Either could let you make a multi-line key from today's date a little ways into the future. You could then present the choices in a portal, either on the layout, or in another little window. (There's a free somewhat limited window-handling plug-in on that page also.)

Basically, if you want a nice user interface you should let them click on a portal of future activities, then handle the creation of the join record, and the setting of the two IDs (Member and Activity) with a script. It's a fairly simple operation. The IDs are passed with global fields between the files/tables.

There are example files in the Sample section that I've done using version 7, Conditional Values Lists; but none in version 5 format. It would be more or less the same; but more tedious, using separate files instead of tables.

[hambone]

Fenton: thanks for the response. It seems that this solution requires periodic intervention of a database administrator. In our case, the activities we are logging are unpredictable and sometimes come with less than a week's notice. So the choices would constantly require updating by a database administrator...which we do not have...just volunteers who are willing to do database entry.

Right now, activities are logged in a multilined text box in each member record. All anyone has to do is enter a line that includes date and name of activity and hit carriage return. Nothing prior has to be done by a database admin to make this happen. It may not be possible but I would like a solution that is as simple as this.

Thanks

[hambone]

Thanks to all who replied. After a little more investigation, I found that the solution to my problem was actually quite simple. For the relationship between the Members file and the Join file, I checked the "Allow creation of related records" box. Now when a new entry is made in the MemberActivity portal in the Members file, the associated join record is automatically created.

Great! Then I thought, how can I get these activity records sorted by date per member in the portal and of course found that there is a sort feature in the relationship dialog.

I suspect this is obvious to non-newbies so I am writing this for somebody like myself who is trying to figure out how to do what I wanted to do and happens to stumble upon this thread.

Finally, browsing this forum was helpful and going through the tutorials in the Getting Started manual...at least read them...was also helpful...in some ways, more than studying the Filemaker manual.