Complicated Relationship?

[derlandson]

I am working on a FM database and have been trying to make it do attendance electronically from each teacher.  There are 26 tables total, three of which I have created to make the calendar interface that will allow teachers to input the attendance.  All has gone well on that level, although I now need a way for the admin to look at a list of teachers and see if each teacher has done the attendance for each class they teach and to do this for each day that the school runs.  This is what I am having trouble with.  This is complicated by the fact that a teacher could mark a student absent and fulfill the duty of attendance or mark everyone as present and fulfill the task.  Either should result in attendance "Taken".  I have 26 total tables, but the most relevant ones to this issue are: Attendance incidents, Classes, Enrollment, Faculty and Calendar.  I had settled on a layout that is based on Faculty and shows the teacher name with a portal that shows the classes taught.  I had envisioned this to work in list view so an admin person can just scroll down the screen and see a green "Taken" or red "Not Taken" field in the portal for each teacher.  It ain't working!  I am using a global variable to step ahead through class days (this worked perfectly for individual faculty members), but the global variable ($$realdate) doesn't pull the info into the portal correctly.  It does in the first instance of an attendance incident, but not subsequent ones.  I am using {Case(Attendance_Enrolled::Attendance_Date=Classes_Current_Date;"Taken";"")} to try to map an attendance incident that occurs on a date that matches up with the class_date that is set by the  global variable of $$realdate.  I have been working on this awhile and am stuck!

 

Thanks for any help!  Dan

[comment]

This is very difficult to follow, because we don't know the meaning of your table names and what the relationships between them are. Is Classes a table where each record represents a meeting where attendance needs to be taken?

If it is, and if I understand correctly that a teacher either [a] creates a record in an Absences table for each student absent from that meeting, or enters "1" into an AllPresent field in the Classes table, then you can tell if attendance has been taken by defining a calculation field in the Classes table =
 

not IsEmpty ( Absences::ClassID ) or AllPresent

Make the result type a Number and format the field to display as Boolean. You can also use conditionally formatted text for the display.

[derlandson]

Thanks for the reply…  The database is complex, perhaps a little disorganized as well.  Classes is a table where each record represents a specific class that is offered at a given time of the day. They meet each day, so I created a calendar interface that allows a teacher to choose a specific day, see a listing of their students in a given class on that day and identify a student as absent.  That incident creates a record in the Attendance table and is related to the classes table via essentially the student ID number.   That  part works.  Your idea of having a table where each record represents a meeting makes sense, and I guess that is the part that I do not have.  I have the table of classes, I have the table of events which are days on which the classes meet and maybe I could join those two and make a field in classes with enough repetitions to represent all of the meeting times?  I guess I was hoping to make this work without the creation of more fields, and just come up with a creative relationship definition, but…  What do you think of the repetitions concept in a new field in classes?

[derlandson]

so after doing a little searching on this forum it seems to be that repeating fields and use of those repetitions is a poor choice to pursue.  I had only thought of that solution because the creator of the database I am using had chosen to use that concept in a couple of instances.  managed to make sense of those instances and learned a little about  the repetition concept.  Doesn't seem like I will be pursuing it.

[comment]

Okay. some of the fog lifts now, but a few issues remain:

 

They meet each day,

 

Surely not every day? Even if we disregard weekends and holidays, I am quite positive that they haven't met on March 24, 1563, for example. So some method needs to be devised to track the actual meeting dates.

 

If you only wanted to record absences, this wouldn't be a problem at all: the teacher would simply choose the class he/she is about to teach, receive a list of students enrolled in that class and tick off the absent ones. As a result, a record would be created in the Absences table for each absent student, in the form of {ClassID, StudentID, DateOfAbsence} - where DateOfAbsence would simply auto-enter creation date.

 

 

But you want something extra: a proof that the process above took place. For this, you need to provide a space for such proof to be recorded. If you had a table of all meeting dates of classes (either actual or projected), then this would be the correct place to have it.

 

If you don't want to track this aspect, then you could just add a table of AttendanceTaken and have a record created in it when no students are absent and the teacher confirms that attendance was taken. However, this approach has a potential flaw: if no students were absent, and the teacher did not click the "All Present" button, then there is no record whatsoever that this meeting took place at all - and you have no way (at least no convenient way) to determine that it did and that the teacher did not fulfill his/her duty.

 

So IMHO you need to either maintain a table of projected meeting dates, or look for a method to automatically create a meeting record for each actual meeting - for example, at that point when the teacher selects the class he/she is about to teach.

[eos]

I am quite positive that they haven't met on March 24, 1563, for example.

 

How do you just know all these things? It's downright uncanny … 

[comment]

It's downright uncanny … 

 

I do have some abilities:

http://fmforums.com/forum/topic/64800-psychic/

[eos]

I do have some abilities:

http://fmforums.com/forum/topic/64800-psychic/

 

I knew that, but I didn't realize they also extended into that realm.

 

Thanks for pointing out yet another interesting (and funny!) thread. 

[derlandson]

 

 

I am quite positive that they haven't met on March 24, 1563,

right!! point made.  I was referring to every day of the academic period when school is in session.  

 

 

 

If you only wanted to record absences, this wouldn't be a problem at all: the teacher would simply choose the class he/she is about to teach, receive a list of students enrolled in that class and tick off the absent ones. As a result, a record would be created in the Absences table for each absent student, in the form of {ClassID, StudentID, DateOfAbsence} - where DateOfAbsence would simply auto-enter creation date.

this part I already have working and  I already have a table of events that are a list of the dates when school is in session (this helps populate my calendar).

 

 

 

table of projected meeting dates

this makes total sense, and maybe what I have to do.  I was sort of hoping that I could use the Absences table to allow for an entry of either a absence or an all present on a particular date and then based on the date and the recorded class ID map that into a layout or portal that would allow an admin to see what teachers did not have an entry on a given day of either an absent or all present "code".  Again this would have to be keyed to a class ID and thus a faculty member via relationships and a portal…  at least that is what I had been trying to do.  

 

made a little progress by making a date field in the Faculty table a global in order to correctly match to the date of the attendance incident in the Absences table.  The global date field gets set by the admin as they step through the school days to see who has done attendance or not.  The class ID issue is still not working…Faculty potentially teach multiple classes in one day and one instance of an entry in the Absences table seems to be flagging "Taken" for all classes of that faculty member for that date.

 

Love the psychic thing!!

 

Thanks for the input! 

[comment]

I was sort of hoping that I could use the Absences table to allow for an entry of either a absence or an all present

 

That's a hack that might work for the stated purpose, but it will also have unwanted side effects (skewing the counts, for example).

 

 

 

The class ID issue is still not working…Faculty potentially teach multiple classes in one day and one instance of an entry in the Absences table seems to be flagging "Taken" for all classes of that faculty member for that date.

 

You need another global field in the Faculty table to select the class. Alternatively, put the global date field in the Classes table and look at a portal to Classes.

[derlandson]

So I am still having trouble with this issue.  I have set up a mock database to show the essence of the relationships.  The real database has many more relationships and tables, and quite a few instances of tables in it.  All for purposes beyond the scope of hat I am trying to do (I think).  

 

so the process that works is a faculty member sees a portal listing the classes they teach , and when they click on a class they see the students enrolled in a specific class they teach.  By entering that layout by a calendar interface they can indicate a student as being absent on a particular date.  That attendance record gets created in the attendance table and this all works for the teacher to see and work with.  I have a second instance of the attendance table (Attendance_Classes) that allows a teacher to enter in a "All Present" without having to make an entry into the parent (?) Attendance table that requires a student enrollment ID.  

 

What I am having trouble with is a layout for the admin to look at that tells them whether a faculty member has entered an attendance record for a given class on a given date. What I had hoped for was a layout based on Faculty that has a portal to classes where all the classes a Faculty member teaches are listed and a "Taken" or "Not Taken" field from the Attendance Classes (?) table gets shown based on the match of the Class ID.  The problem is that the "Taken" does not show up consistently for what I am thinking is a correct match.  It is quirky so that tells me something is incorrectly related or defined.  Hope that this is clearer than before.

 

Thanks for any input,

 

dan

[comment]

I am afraid this is confusing (again!) because you are trying to do two different thing at the same time.  First, solve the problem at the level of "the essence of the relationships" - which means an ERD. Only then (and not before) can you move to the problem of Filemaker implementation (RG and user interface).

 

Now, I see that you are determined for the Attendance table to have two types of records. I suggest you make sure that both types have a Date and a ClassID (even though you have drawn the table as a child of Enrollments, so a "real" absence record should not really need a ClassID).  This way you can construct an auxiliary relationship:

Classes::ClassID = Attendance X::ClassID
and
Classes::gDate = Attendance X::Date

and have each class record figure out what happened on the given date (i.e. are there any related records, of either type, in the Attendance table).

 

 

I have a second instance of the attendance table (Attendance_Classes) that allows a teacher to enter in a "All Present" without having to make an entry into the parent (?) Attendance table that requires a student enrollment ID.

 

I don't think you need this. Just use a script to create the record.