Yes, that is possible, but I'd take a closer look at putting groups and students in the same table. The fact that they "correlate in different ways to other tables" does not require that they be placed in different tables. By using more than one Tutorial: What are Table Occurrences? for your combined table of students and groups, you can set up both sets of relationships even though you have the data in the same table.
By putting the data in the same table, much of what you want here becomes very simple.
If you stick with separate tables, script steps such as GetField, Set field By Name and a few others can be set up to refer to different table occurrences (and thus differen tables), but it seems a needless complication here.
Thank you for your quick and helpful answer! I have been using the database for some time already… So if I change the table structure, I must know how to work in a safe way and how to import information back to the right places without too much work. I will work on a copy, or make a sample file, and try out putting groups and students in the same table.
(The correlations has to do with how one student, with it's own student-table, can have one or more type of lesson, and also be a member of a group. I use portals to have several students show up on one group record, with their starting/ending dates. I have tables for keeping my notes for each lesson that show up on the table for each lesson in a portal. For me as a "starter" this feels quite complicated))
My skills are not so good, and my time is limited, but I like the challenge of making the right system. I love to learn as I go along, and I hope to understand FileMaker more and more as time goes by. :)
FileMaker makes it easy to test and experiment. And using a copy for such a test is a very good idea. In fact, makcing lot's of back up copies while you develop is very good thing to do as it allows you to revert back if you try something new and then regret the change. See this thread for a way to automate the process: Saving Sequential Back Ups During Development
The correlations you describe are exactly what I thought that you meant. It sounds possible to use a combined table--or a linked in table with one record for each student or group for your "navigation" and then you manage the rest with relationships that use multiple Tutorial: What are Table Occurrences? to get everything set up the way that you need it.
Now I have been working a while on my project without getting anywhere…I found out how to make table occurences, but I couldn't find out how make use of it in my situation.Can you help me in the right direction - what is the right way to obtain my goals - wich are:One student can be a member of one (or several) group(s) and/or have single lesson(s)One lesson should then consist of one single student or a group of studentsIf it's a group, I want to see all the members of the same group on the same recordI want to organize these records (groups or single studens) based on weekday and start time for the lessonFor each lesson I make notes from week to week (what we did and homework, maybe a sound recording too).How many tables / table occurences, and what kind of relationships between them?
I suggest that you break this down and take it one part at a time adding tables/relationships as needed.
Taking on just the questions of students and groups, I suggest a slight change in what I recommended earlier now that I have a more complete picture of what you are doing.
Students::__pkStudentID = Student_Group::_fkStudentID
Groups::__pkGroupID = Student_Group::_fkGroupID
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
I suggest that if you have a single student, give that student a group of just one student and then you can have a group record for each group of students whether there be 1 student or many. The above "many to many" relationships are to facilitate being able to make a student a member of more than one group. You can place a portal to Student_Group on the students layout to assign that student to different groups. A portal to Student_Group can be placed on the Groups layout to list all students that are a member of that group.
Here's a demo file on many to many relationships that may help: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
I have created the tables and fields, with the relationships and portals, as you specified.
I try to understand the many to many demo file, but it seems a bit complicated to me at this point.
What is the simplest way to make the assignments of students and groups through the portals?
The simplest method is demonstrated on the "basic setup" layout in the demo file. You place a portal to the join table on a layout based on student. In th relationship between student and the Join table, select "allow creation of records via this relationship". Then you format the Join table match field that links to groups with a drop down list of groups. When you select a group for the student in this field in the portal, you create a new record in the join table that links the current student to the selected group.
Note the button with the + sign on the basic set up layout. It runs a script to create a new record for the table on the far side of the join table. In the example I have posted in this comment, that method could be used to create a new Group record and automatically link it to the current student by also creating a record in the join table with the needed ID values to correctly establish this link.
Thank you again, now I've got the basic setup with the "many to many" relationship and a working "+"-button for creating new related group-records.
Now I need a good way to distinguish between group-situation and singlestudent-situation in the group table. Could it be a radio-button choice placed in the join-table (and the portals)? If it was marked "Single student", then the group name automaticly would be the same as the student name?
Or maybe better: In the Student table's portal there is a drop-down list with values from the group-name field in the Groups table (to assign the student to group(s)). If I could have the first (or the last) line on the list to allways show "Single student" in addition to the groups, maybe separated from the groups by i line, it would be great. Chosing "Single student" from that list, creates a new record with the student name as the group name in the Groups table! (?)
I've seen a video on YouTube called something like "value list with a twist" - where they make use of table occurences to create something like this…
Am I going in the right direction??
Both are options you could use.
Your + button script could also copy over the student name as the group name as the default and then you'd edit the group's name after the fact in cases where you want to assign a different group name.
Hello it's me again - thank you for your help so far - and I hope you can help me a little bit more:…
OK; I have the two tables with the join table in between. On the students table I have one portal for single lessons and and another one for groups, using filter based on a radio button choice "Group" or "Individual" in the Groups table. I use a table occurence and a global field to help me get a value list with the group names and not the individual-names (If I want to assign a student to an existing group). I have a button for creating new individual lesson, and a button for creating new group. Things are starting to work the way I want it to work :)
Now, next problem: For each combination of student-to-group there is an instrument involved. One student may have a single lesson on one instrument, be a member of a group on another instrument, and possible yet another group on a third instrument. Though this is not likely to happen, I need that capability in the database. I thought this could be easy solved by having an instrument field in the join table so that each combination can have its own instrument involved. This looks fine in the students table, AND in the join table, but NOT in the groups table! If I have a group with one member, it's ok, but if there are several members, all get the same instrument as the first member! What am I doing wrong?
I mean: This looks fine in the students table portal, AND in the join table, but NOT in the groups table portal!
(But I guess you understood it anyhow…)
You haven't explained how you set this up so I can't suggest a way to change it.
A few questions to help focus your thoughts?
Is the instrument specific to the group? (All members of a group play the same instrument--you'r scheculing "sectionals" as we used to call them)
Is the instrument specific to a particular student_Group Pairing? (Johnny plays alto sax in the saxaphone group and tenor sax in the wind ensemble group...)
Thank you, that's right: The instrument is specific to a particular student_Group Pairing! Couldn't have said it better myself: (Johnny plays alto sax in the saxaphone group and tenor sax in the wind ensemble group...)
Then you can add a field to the join table where you link a student to a particular group and then you can show the correct instrument for that student when they attend that group. (I used to play Trombone in school bands myself and have a brother who teaches highschool band so this is familiar territory ;-)