many many to many relationships = headache

[MikeKD]

Hi folks,

I'm in the early stages of doing a database for my music dept at school.

I ran out of ideas when planning and started to write, hoping inspiration would come to me, but it hasn't.

 

I've done the easy bit first and have a table of pupils and one of classes, with a join table and a portal in each. I can so who's in each class and which classes pupils are in.

 

It's adding assessments that has stumped me.

I have a third table of assessments with a join table to classes and another join table to pupils.

What I can't work out is how to create a layout where I can create a new assessment (or add an existing one) to a class so I can give the pupils grades.

I've been trying to do this with a portal, but there's no records in the join table, so no list of pupils names to add grades to.

Should I use a script to create a set of records for the class when I create a new assessment or am I going up the wrong street entirely!!

 

Cheers,

Mike

[GisMo]

Hi,

 

If I understand correctly, You create a class, you create students, and then you add students to the class.

 

To create an assesment table(many-to-many), you need two foreign keys in the Assessment table ClassID and StudentID. Then whatever fields/columns you need to do the actual assessment.

 

In your relationship graph, Connect the Class and Student to the Assesment via those keys. 

 

When you create an assessment from the class layout, create a new record and put BOTH foreign keys into the new assessment record. Put the "Class. Assessment" table into a portal on the class layout.

 

This should get you started.

 

Let me know how it goes for you....

[MikeKD]

Many thanks GisMo,

Yes, you understand correctly!

I think I've sorted out the relationships; I had extra joins which was confusing me.

 

I'm now stuck on creating the new assessment record for the class. I think I need to have a button on the class layout to run a script to go to the assessment layout and create a new assessment record.  Am I on the right lines?!

 

Cheers!

Mike

[Fitch]

How many assessments does a student get per class?

[Matthew F]

I'm now stuck on creating the new assessment record for the class.

 

In your relationship graph between Class and Assessment do you have the connection set to "allow creation of records in this table via this relationship" for the Assessment table?  If so you ought to be able to create new rows in the Assessment portal (presumably by entering the student ID).

[GisMo]

Hi Mike,

From the context of a Class layout and within a portal containing students enrolled in that class, you can create a button/script to set the ClassID and StudentID as variables and then create a new assessment record and write those variables to the foreign keys in the assessment table record. That would be individual basis. You could also loop through all the students in a class and creat their assesments at once. Or Like MatthewF said, and enable record creation via the graph relationship and you can enter assesments directly from the enrolled students portal.

This all begs the question Fitch asked...If you have multiple assesments per class per student you may need to introduce yet another table with many-to-many to handle this more properly.

Have a screenshot of your relationship graph?

[MikeKD]

Hi folks,

the amount of assessments per class could vary; they might do one big topic or several little ones. That's why I wanted to use a separate assessments table, rather than added a fixed number of fields to the pupil table.

I was also whether to use the assessment table to mark attendance...

 

I woke up this morning with an understanding in principle of how to do it. It's just putting that in to practice now!

Here's what I think I want:

 

1. A class layout with a portal of assessments and the ability to create new ones.
2. A second portal with pupils names and relative fields from an assessment.
3. A button on the assessment portal row to select that particular assessment and use those records in portal 2.

 

 

Many thanks!!

Mike

 

[Matthew F]

Looks like you're getting there.  

 

I'd just like to point out that it may not be necessary for all the Table Occurrences to be linked together in one big map.  You can replicate TOs (like you did with Classes) and break your graph into smaller components.  Do you need to be able to reach all the way from Auditions to Assessments, for example?  If not, then they could be separated into smaller maps (you may need to replicate Pupils table). This is not to say that your map is a problem, but at some point large maps can get unwieldy and confusing. 

[MikeKD]

Thanks Matthew!  My map's quite small, but is already confusing!

 

I'm thinking that because each class can have many assessments and each assesment can have many classes (and the same for both with pupils) I do actually need to use a join table - is that correct?

 

Cheers,

Mike

[Fitch]

Sounds right. If I were you, I'd step back and forget about the graph for a minute. Think about how you want the process to flow. You pretty much described this a couple of posts ago.

 

What you might do is:

- create two global fields in the class table, let's call them assessment_g and student_g

- when user clicks an assessment in the portal, it takes that ID and puts it into assessments_g

-  the user then clicks a student in the other portal, it puts that ID into student_g

- make a third portal (one line) that is driven by a relationship from both globals to the assessments table

 

Can you take it from there?

 

Since the student and assessment tables would be on the class layout, the graph you have won't work. You need a table occurrence of Class that relates directly to both the pupil join and assessment join (at least for what I'm proposing).

[MikeKD]

Hi Matthew,

Auditions are entirely separate from classes. It's handy to see who's been auditioning for things.

 

Fitch,

I've tried to follow your suggestions but have failed:-)  My knowledge of scripting is so low that I've not managed to pull the ID's, having tried quite a few functions.

 

I haven't got to sorting out the relationships yet.

 

Ideally, when an assessment is selected everyone in the class would show records so they could be given marks - so I probably need a step to create new records if they don't exist.

 

I've read through the various chapters on scripting but haven't struck lucky so far!

 

Cheers,

Mike

[Matthew F]

It would help to be specific about what you trying to accomplish.  What do you want to see on your layouts and how should they function?  

 

 

I'm thinking that because each class can have many assessments and each assesment can have many classes (and the same for both with pupils) I do actually need to use a join table - is that correct?

Actually this confuses me a little.  How can an assessment have many classes? Are these standardized tests that are being given in more than one classroom? 

 

Something that may need to be kept straight is the distinction between a table describing an assessment and the record of the application of the assessment.  For example maybe you give 10 exams throughout the year and you want a record describing each one.  That would not be the same as a record of each students performance on an exam.  You can link the two by an exam identifier, to pull in the title, subject, etc.    The relationship graph would go:   Pupils <--> Assessment <--> Assessment Description. Classes could link to any of these tables depending on what you want to display/manipulate on a "classroom" layout.  (Or conversely, whether you want classroom info displayed or manipulated from a Pupils or Assessment layout.)

 

[Fenton]

So, it seems to me that a Pupil could have one or more Assessments, while in a particular Class. So, on your graph, Pupil_Class_Assessment should be attached to the Pupll_Class_Join table (on the graph), not directly to the Pupil table; as ever Assessment is connected to a specific Pupil's Class (?). It would have BOTH the Pupil_ID AND the Class_ID in the relationship. I would name it like: pupll_class_join_Assessment

 

If you wanted to create this from the Class table's layout, you would need BOTH choices. Which, as Fitch said, clicking to set the IDs to two globals is the easiest way to tell a script to go to a layout of the Pupil_Class_Assessment table, create a new record, set both IDs, and return to the Class table's layout. I'd want to show the "global Assessment" field on the layout (like over the portal of the Pupils), so you know what you're going to get; as it would be a little tricky (but not impossible, but a different story) to see the result there.

 

I would NOT put the real Class table's main graph (table occurrence) as part of the group connected to the Pupil table's main table occurrence! I would brake that link, and move it to its OWN "table occurrence group". 

 

As Pupil and Class are the two main tables, they should have their OWN group area, NOT connected to the other "main table occurrence" of the other. This make seem like a lot of "extra" table occurrences; but it will save you're mind from must trouble, now and later.

 

Yes, some of the other table occurrences will be in each, some of like dupes. But the relationships may be slighter different, and some will only be one or the other, especially when you create special ones for "fancy" ( : - ) things later.

 

P.S. Hopefully this makes some sense. Yes, there is more than can be done...

[MikeKD]

Thanks guys,

reading, digesting and trying again......!!

 

Oh Matthew, yes, often an assessment will be used by more than one class. When this happens, it would be really useful to see how other classes have done to try and make it as consistent as possible.  Also, occasionally a pupil changes class and it would be handy for them to be able to take their grades with them! The current commercial software I use doesn't let this happen and (in theory!) I should be able to come up with something that improves on it.

 

I can get to grips with the relationship structure I think - when I think about your comments they make sense.

 

Thanks again!

Mike

[MikeKD]

Hi guys,

Here's my first go a re-doing the relationships.

Have I done what you thought I should?!!

Cheers,

Mike

 

 

[Fenton]

Yes, that's looking pretty good. I know it seems kind of irrelevant to separate them into two "table occurrence groups". But when you want to add more, or use for scripts, it helps to know clearly where to go. 

 

I created a simple file like yours (just the tables and IDs needed), so I could show how I would show the Relationship Graph. I got a little carried away, and changed the names as I might do them. Each of use does this a little differently, so don't see it as "the way." My method is solving what I consider the biggest problem with the relationship graph. That is that you do not SEE the graph, when you really use it, scripts, calculations, etc.. You see them all, as text. 

 

The separation into "groups" lets you at least see those separately. But within each group they are in alphabetical order. So, I name them on the relationship graph that way. Which means adding ALL the table occurrences before each. Basically, the "parent" shows first in the list (on the left in the graph), the "childs" show after, in order, the farthest at the last. Yes, some of them might end up kind of long. However, I don't find this too bad. Whether the name is short of long actually tells me much about it.

 

Another thing. While I think you're "join" may be good to see, I have (over the years) decided to use the version "one|two" to show "join" tables on the graph; I see it a "join", FileMaker seems to be OK with "|", and it's shorter (which my method needs). It makes it clear that it's 1 table, as many "_" in long names is sometimes unclear. You're simple file is not so complex (so far), so this is mostly just 'my way'. [ I kept the "join" word in the actual table's (as they are not actually on the graph, and only seen if you go look at Tables.]

 

Also, I changed "Pupil|Class" to "Class|Pupil" to match it's parent's group. I assume I'll know it's the same table. I also use the same color in all instances of a table.

 

Notice that I added one, "pu_Pupil|Class|Assess" to the "pupils" group. So you could have a portal on a Pupil layout, to see all Assessments for a pupil (in some way).

 

Since I started this "group" thing, let me say another (good) thing. FileMaker is smart about going from one group to another. For example: If you wanted to go from a Pupil, to all their Classes, in a list view (to print or whatever), you could. (I also added a "pu_pupil|class_Class" table occurrence.) With a single step you could use that relationship from a Pupil, via Go To Related Record, and specify the Class layout. 

 

The Class layout is in the "CL__Classes" group; it is NOT actually linked to the Pupils group. But FileMaker is smart enough to produce the correct found set for you. Hence the "groups" is not as bad as it seems. [ Don't however think you can "set field" (or even see correctly) until you get into the correct group, using its relationships. Oh, another good thing; a field calculation, after specifying a group's relationship, will also be valid in another group (as you've already told it what to use. This can be used to show a calculation field of "last, first name," for example, in another group, without a link for it.] 

 

OK, I guess I better stop there. Too much info for either of us :-/ By the way, I have serious trouble reading, even my own writing. I have to make the computer say it to me ( I can understand what I hear, more or less). I had a stroke 2 years ago, which damaged that part of my brain. Hence I a retired. However I seem to know FileMaker relationships actually better than most English words. Our brain remembers what it did the last few years. But I cannot be sure that what I write is 100% correct.

[MikeKD]

Hi Fenton,

many thanks for this.

I've worked through it and think I've reproduced it in my language.

I like your wording though, and may well come back to it when I'm a bit more confident.

I'll sleep on it tonight and come back to it tomorrow; I've made decent progress today.

 

Many thanks for your help :-)

 

Cheers,

Mike

[MikeKD]

Hi folks,

Sigh...

I've made some progress today (the en suite is almost finished for a start!).

• I've got the script working to write current assessment to a global field.
• I've worked out that I just need to be able to filter the portal to get the right records there.

 

I still have quite a few issues though:

• I can't work out how to create the needed new records for an assessment with a script.
• I can't seem to get the relationships working properly. -

I've attached by (pathetic) effort for you to laugh at!

 

Best wishes,

Mike

 

PupilGrades.fmp12.zip

[eos]

I'm a great friend of the arts, so here is some reading material plus multimedia: “Comical variables! Colorful portals! New-fangled SQL, romantic relationships and thrilling loops! Plus: The Mysterious Case of the Missing Pupils! Get your copy today!”

 

Anyway, I hope this gives you some pointers and inspiration, especially with scripting.

 

EDIT1: The assessment creation script mistakenly checks on all existing pupils, instead of only those in the class; here's a corrected version 02.

 

EDIT2: Just as a reminder – the SQL statement within ExecuteSQL() is just a string, so the referred field and TO names are not covered by FM's internal object name update mechanism. There are methods and tools to guard against this. Here's an article outlining the problem and providing solutions: http://www.filemakerhacks.com/?p=4924

 

Added the “native” way of doing this to the script.

PupilGrade_TheSQL_eos_v02.fmp12.zip

[MikeKD]

Hi EOS,

this looks great - so many thanks!!

I'm going to have a proper look and see if I can understand your magic now. :-)

Cheers,

Mike

[Fenton]

1. In the "Class_Assess_Join" table occurrences in the relationship graph; do not turn on "Allow creation of records" for the (left) Class table. Its main layout is of the Class table; you'd just create the record the normal way. Yes, allow it on the (right) Class_Assess_Join.

 

2. In the "Pupil_Class_Assess_Join" turn off "Allow creation", for two reasons: 1. You're going to use a script.

 

3. In the Class layout, in the Pupil Info tab, you are using Pupil 2 as the relationship for the info; it should be Pupil 3 (which is going thru the Student_g relationship). Pupil 2 is going thru the Pupil_Class_Join relationship. (Lots of Pupil relationships.)

 

[ One reason I write longish precise names for relationships; it may look long, but it tells you were and what it is. Another reason is to use names which keeps it in alphabetical order, so I can locate it in a drop-down text list. ]

[MikeKD]

Many thanks for this!

Can I change the table names 1/2 through a project; does that break the relationships and scripts or do they automatically update?

I've made a fair bit of progress; I'll try to upload what I've done later on so you can see.

Cheers,

Mike

[eos]

Can I change the table names 1/2 through a project; does that break the relationships and scripts or do they automatically update?

 

That's probably OK, but I'd be careful around the 2/3 mark … 

 

OK, just kidding: http://fmhelp.filemaker.com/fmphelp_12/en/html/create_db.8.7.html#1028466

[Fenton]

I know little about SQL, and I like the fact that it can do things which would require another relationship to do; get a list of the pupils which already have the Assessment. 

 

But there is one weakness of SQL, which is that it is just text. So, if you change the name of a relationship, it will brake. Since I have been known to do that, I would want it to be safer. Fields names would also be vulnerable, but in many cases, such as IDs, you'd likely never change them.

 

These small changes to the script would make it safe to a relationship name change. It seems a little odd, because it goes to a layout, then returns. But since it makes no data change, I think it's fast enough. Add these steps before you run the SQL, the step to set the variable: $pupilsInClass

 

Go to Layout ["Pupil_Class_Assess_Join" (Pupil_Class_Assess_Join)]

Set Variable [$table_name; Value: Get ( LayoutTableName )

Go to Layout [original layout]

 

Notice that the second name of the 1st step is the name of the relationship (of that layout), which the 2nd step, with Get ( LayoutTableName ), captures.

 

You now how the exact current name of the relationship of that layout.

Then make a change to the SQL that sets the Variable for: $pupilsInClass, in the FROM line:

 

FROM " & $table_name & "

 

Notice that has adds 2 ", to get out of the text, add the $variable, then back again into text.

 

It will no longer vulnerable to changes to the relationship name. You could do such with each of the fields specified also (as they are just text here); but really, you'd need more of the above tweak.

 

I've tested this, and it works. Please (anyone) let me know if it would brake.

 

Caution: If you have to get data from a portal row, you'd want to get it into variables before using a Go to Layout step, as you'd be out of the portal.

 

P.S. It is much easier to do this with FileMaker Pro Advanced, so you can watch it one step at a time, and see what the Variables are. Or, if not, copy/paste them to a "test" script, with only them.

[MikeKD]

Hi Chaps,

Here's where I've got to so far.  You've helped me past the problems that seemed insurmountable a couple of days ago :-)

 

 

I'm now starting to get my head round a few more issues:

• Ideally I'd like to be able to sort pupils in portals by name and by grade, but there's no obvious way to do that unless I change their ID's to do it which seems highly dodgy!
• I have some join tables with no IDs of their own.  I've started to think that "join tables" is a slightly misleading term - one of mine is the record of a kids work in that unit. Fenton's naming convention looks more sensible than mine.  I'm plucking up courage to re-think the table names...
• I'm starting to have fun using conditional formatting.
• I'm still struggling at a really low level when it comes to scripting, but am making progress.

Once again, thanks so much for your help guys; it's hugely appreciated!

 

Cheers,

Mike

PupilGradeV2.fmp12.zip

[Fenton]

P.S. I discovered (the hard way) that ExecuteSQL() does not like certain characters. It does not like "|" or "~", in the name of a Relationship Graph table occurrence; there are likely a few others also.

[Mary Butterfelder]

Hi Mike! I was looking this over and tried to glean some ideas from the thread but your database is quite complicated. I have a thread a few down about relationships in a recipe database. How did you discover how to get all those portals to work? Trial and error, forum help, or just a quick study? Was there somewhere in particular that you went to learn about relationships other than the FM Support Forum or the FMPro missing manual? I have been to both and didn't help in a direct, quick sense of the word help. Does trial and error help when trying to get these things to work when one doesn't quite follow the way relationships work? thanks if you have any feedback and for sharing your stuff!

Mary

[MikeKD]

Hi Mary,

To be honest, I've got no real help from the FMPro docs.

The guys in this forum have been EXTREMELY helpful though.  Very often there's something very simple stopping things working, though the database in this thread has been very hard for me to get my head round until I was helped out! I'll have a look at your thread now.

Cheers,

Mike

[Mary Butterfelder]

Yes, I hear you. Fran just did what I've been fiddling with for days probably in an hour or so! It does speak volumes to the forum and to the skills of the expert members. It also speaks to the possibilities in fm that go far beyond what documentation describes. I would imagine most of the expert members here are consultants. So, I would feel very safe hiring someone to do more on my database when my little club hits high gear. Right now, it's kinda in 2nd gear...good luck to you Mike! 

[MikeKD]

Hi folks,

I'm stuck again I'm afraid :-(

 

• I've not changed file names I'm afraid - I started and then bottled out, not wanting to break what was working so far!
• I've added a nice simple 1 to many yeargroup relationship. (I thought it was a scripting problem, so started a new thread in there, but a simple relationship seemed better.
• I've found a nice trick to sort portals by whatever I want (though it's not changeable by the user)
• I've made some slight progress in scripting, though nothing worth writing home about!

But....

 

I just can't work out how to get percentages to work at all!

Percentage in Pupil_Grade_Class_Join should be worked out from the mark in Pupil_Grade_Class_Join and the maximum mark in Assessments.

I just can't get the lookup to Assessments to work. I think I've got the relationship wrong somehow, but can't see how.

 

I'd be grateful if someone could see what I'm doing wrong!!

 

Cheers,

Mike

PupilGradeV2.fmp12.zip

[Fenton]

From what I can see, you've specified that wrong Assessments table occurrence. The one you specified may have "some" connection, but not the direct one you need. I think you need: Assessments 3. [This is one reason why I add the full lenth to names. It is just too hard to remember which is the one you want otherwise; as there may be several connected. But, at my age, I cannot rely on memory. If you can remember 'em all, you'll be fine :-]

[MikeKD]

Thanks Fenton!

You're right I had the wrong table occurunce.

I've fixed that (and have started to cautiously rename things as well!) Unfortunately, I think the problems are deeper than that one mistake.

There seems to be something wrong with the relationships around the Assessments table, but I can't work it out at the moment.

 

I think the problems are:

• A new assessment record isn't always created because of a relationship issue, but I don't know what that issue is! This means that the maximum score isn't always available.
• It's possible my formula for working out the percentage is wrong - it hasn't given the correct answer yet, despite many alterations!

I'd be really grateful for more help, I'd like to input real data tomorrow if possible!

 

Many thanks,

Mike

[MikeKD]

I think I've got the above issues cracked now.

The formula needed to be in brackets.

Not all records had a UID,

 

I've now got real data in it.

 

What's the best way to proceed with development?

I assume I should develop a separate database & then import current data when I'm happy with the next step. Is that easy to do?

 

Cheers!

Mike