OK, I tried to come up with a way to explain my problem, but without showing you it seems complicated to verbalize... here goes a semi-short version...

I have created a Filemaker db for an educational program tracking student info including contact, grades, surveys, etc. I also have a db tracking events/activities offered by this program including student events, parents events, and pro development events.

These are two different dbs, each with their own sets of related db files.

Now my client wants me to modify the student db to list all the events each student participates in. Currently the Events db just tracks the total # of students per event ( a 1 time data entry per record).

[color:"red"]My question is, how do I relate these two dbs that already have key IDs so they can relate to their original related records? Or do I use a repeating field in the Student db and get the values from the Event Name field in the Activities db?

I know this probably isn't enough information, but any help insights are greatly appreciated! [color:"red"] I was hired on as a consultant and I think I'm already in over my head! I am attaching an example of my dbs so that hopefully the problem will be more clear.

Thanks in advance for your help!

Student-Events Problem.zip

First,

Check your messages, I sent you a PM that you should read.

Next, I think you definitely do not want to use a repeating field.

How about creating an StudentActivities line item file that just stores activites per student. This would be related to the Student file and the activity file and would hold the Unique ID for each, as well as its own UniqueID.

Portal in student data entry would be related by StudentDB StudentID::StudentID ActivityLineItems with allow creation of related records checked.

Portal in activities would be Activities ActivityID::ActivityID ActivityLineItems

You could then enter students for an activity either individually from the student records, or through activities records (enter all students for a single activity in one layout.)

Let me know if it is not clear.

-Raz

How about creating an StudentActivities line item file that just stores activites per student.

Which db would I define this field in? Students or Activities or both?

This would be related to the Student file and the activity file and would hold the Unique ID for each, as well as its own UniqueID.

How would I define these new fields? How many new fields does this require?

Portal in student data entry would be related by StudentDB StudentID::StudentID ActivityLineItems with allow creation of related records checked. Portal in activities would be Activities ActivityID::ActivityID ActivityLineItems

What db is this related to? It seems you have suggested creating a new db called ActivitiesLineItems and related both Students db and Activities db to it. Is that right?

You could then enter students for an activity either individually from the student records, or through activities records (enter all students for a single activity in one layout.)

That would be awesome to enter all students for a single activity in one layout! I can't wait to see how it works. Thanks again!

-Sol Rodriguez

Sorry for being unclear, let me elaborate a bit:

What db is this related to? It seems you have suggested creating a new db called ActivitiesLineItems and related both Students db and Activities db to it. Is that right?

Yes.

your new database (lets call it LineItems) is what stores the records of individual students participation in single activities.

you would want a minimum of 5 fields and 4 relationships:

studentID (Lookup, StudentName::StudentID)

activityID (Lookup, ActivityName::ActivityID)

LineItemID (AutoEnter unique value)

StudentName (Lookup, StudentID::StudentName)

ActivityName (Lookup, ActivityID::ActivityName)

LineItemID would be the only field that contained unique data.

your relationships from LineItems would be

StudentName::StudentName (to StudentDB)

StudentID::StudentID (to StudentDB)

ActivityName::ActivityName (to ActivityDB)

ActivityID::ActivityID (to activityDB)

LineItemID would be unique for each record in LineItemDB

your relationship from ActivityDB would be

ActivityID::ActivityID (to LineItemDB- allow creation of related records)

ActivityID and ActivityName would be unique for each record in ActivityDB

your relationship from StudentDB would be

StudentID::StudentID (to LineItemDB- allow creation of related records)

StudentID and StudentName would be unique for each record in StudentDB

In ActivityDB, you would create a portal using LineItem relationship.

In the portal, you would place the LineItem::StudentName field.

Format the field to be a pop up list displaying all values from StudentName field in StudentDB.

Click on the StudentName field in the first portal row, and select "Little Jimmy" from the pop up list- your relationships and lookups should automatically fill in the other 4 fields in LineItems.

In StudentDB, you would create a portal using LineItem relationship.

In the portal, you would place the LineItem::ActivityName field.

Format the field to be a pop up list displaying all values from ActivityName field in ActivityDB.

Click on the ActivityName field in the first portal row, and select "Volleyball" from the pop up list- your relationships and lookups should automatically fill in the other 4 fields in LineItems.

This should let you view and enter all students for an activity in activity DB and view and enter all activities for a student in studentDB.

In activitiesDB, you could create a calc field count(LineItem::LineItemID) to display a total of all students for that activity.

You could also use the LineIems DB as a hop point to get from student to activity:

place a button on each portal row "go to activity" that runs script:

Go to related record "StudentID::StudentID"

performscript (external)

external scriptwould be in LineItems:

go to related record "ActivityID::ActivityID"

There are plenty of other ways to use these relationships as well.

Have fun!

-Raz

Here are the attached files per the message I sent to you. You're help is greatly appreciated!

LineItemshelp.zip