What an interesting challenge!
Try a new occurrence of Students with this relationship
Sessions::anyField X AllStudents::anyField
AllStudents::StudentID = AttendanceAllStudents::StudentID
with this filter expression:
IsEmpty ( List ( AttendanceAllStudents::SessionID ) ; Sessions::SessionID ) )
Phil helped me with something similar for a scout troop and events. On my event layout I had a portal of all scouts in the troop and a portal of scouts that attended that particular event. I set up the name field of the All scouts portal to create the joint table record which caused them to then show up in the Attended portal. A filter expressing Phil came up with would then remove them from the All scout portal. This way it can be used to take roll call at the event. Effectively it moved them from one portal to the other leaving me with two lists, Attended and Did Not Attend.
Thank you for you replies Phil and Mark.
Unfortunately, despite experimenting with the x relationship and with the ‘List’ function, I still can’t seem to put your explanations into practice.
I assume that the layout should be showing records from ‘Sessions’, but I am not sure about the portal. Also, I don’t fully understand the alternate table structure that Phil suggested.
Thank you very much for the help.
I don’t fully understand the alternate table structure that Phil suggested.
Do you know how to make table occurrences in Manage | Database | relationships?
Click the Students "box" (what we call a table occurrence) and make a new copy by clicking the duplicate button (two green plus signs). This does not make a duplicate of the table, just creates a new box on the relaltionships chart that refers to the same Students table. You can double click this new box to open up a dialog where you can rename it "AllStudents".
Drag from any field in AllStudents to any field in sessions to create a relationship. This will have the = operator as the default so we need to change it to the cartesian join operator X. Double click the relationship line to open a dialog where you can change the operator from = to X. This operator means that any record in Sessions will match to all records in Students.
Now make a new occurrence of Attendance and link it to All students by the StudentID fields. YOu'll use this added occurrence with a portal to AllStudents so that student names can be listed in the portal.
From here, you use a Layout based on Sessions and a Portal to AllStudents to set this up.
Can you get that far with this set up?
If so, report back and we'll take the next step. If not, let me know where things get hung up and I'll clarify.
Phil: so far, so good.
I have created the two duplicate tables 'AllStudents' and 'AttendanceAllStudents' and I have linked them to the original 'Sessions' table.
Sessions >---X---< AllStudents ---=---< AttendanceAllStudents
I then created a layout (let's call it 'Absence chart') based on 'Sessions'. In this layout, I put a portal showing related records from 'AllStudents'.
As I understand it, 'Absence chart' will let browse through all the sessions while viewing in the portal all the students who did not attend the session on display. This is where I block...
Thank you for all your help.
I hate to interject late but I would approach this a bit differently. There should only be need for one additional table occurrence of Students and one calculation in Sessions called cAttending (result is text) which would be:
Case ( IsEmpty ( List ( Attendance::StudentID ) ) ; 0 ; List ( Attendance::StudentID ) )
Then join: Sessions::cAttending ≠ Students~notAttending::StudentID
The same 'not equal' relationship is then also the basis for the dwindling value list. It refreshes without the dreaded Refresh Window[flush cache].
It is just something else to consider which might be simpler overall. The file can be downloaded from here:
BTW, I would not use filtered portals on this situation because I would suspect there will be a large number of Attendance records AND because dwindling value lists do not operate via filtered portals.
I loved the solution example you demonstrated at (http://www.directlinesolutions.com/downloads/Students.zip) and had a question. Is there a way, once you are showing Attendance and "Not Attending" where you can simply click on the name for anyone on the "Not attending" list, and trigger off a script or something that would move them over into the "Attending" area? Obviously, it would have to build the right related records to represent this relationship correctly...