Self-join trouble

[lolitafontaine]

Hi!

I have two types of "meetings" I am tracking: Faculty Meetings and Council Meetings. I have created Table:Meetings, and each record has field:MeetingType which indicates which type of meeting it is. There is a record for each scheduled meeting, and each meeting is further qualifed by field:Academic Year.

I need to track attendance to each of these meetings. Table:People tracks everyone in my DB, and any subset of those people can attend both types of meetings. I need 2 layouts -- one for Faculty Meeting attendance records, and one for Council Meeting attendance records. Each layout script will generate a list of only those people eligible to attend the meeting, and a field driven by a value list should list only those meetings that are appropriate for the layout as checkboxes. So, as we enter the attendance records off the physical rosters, we can do so by just scrolling down a list and checking each attendee off for the date of the meeting.

I'm pretty sure I need some self-join relationships here, but I'm stuck at on the match fields. I want a field, say "2006_07FacultyAttendance" on Layout:Faculty_Meeting_Attendance to show, via value list, only the Faculty Meetings for that academic year, just as a similar field on Layout:Council_Meeting_Attendance will show only that year's Council Meetings.

Do I need two seperate Meeting Tables? I thought I could get away with two seperate self-joined Table Occurances, but I'm stuck. Any help?

Thanks!

[Dr. Evil]

No you do not need two separate tables, only one table: FacailtyMembers.

Yes you need to create a self join relationship.

You will need at least two layouts. (Three if you want a print/view page)

LAYOUT ONE (List View): with full list of facility members so you can label: Not Attending, Facility or Council.

LAYOUT TWO (Form View): With global sorting key and self joined portal on layout. This layout is where you will view what member is doing what, as well as print from.

LAYOUT THREE: This layout would be set up for your printing needs, as your roster.

To make this work you will need these three fields:

glb_sort (this field is global storage, and is used to filter self relation)

m_attendance_type (this field is used to label member, not attending, facility, or council)

clc_attendance_key (this field is used as a multi key, combines m_attendance values as well as "All".

Your self join table occurrence will be joined by the fields glb_sort and clc_attendance_key

I have built a demo file of what I am talking about to help illustrate my instructions.

I noticed you do not have the latest copy of FileMaker. I suggest you to to filemaker.com and download a free trial so you can view my file.

Let me know if you have any questions, hope I can help!

attendance.fp7.zip

[lolitafontaine]

Sorry so late in replying, but thanks so much for this! I'll give it a try...

I appreciate your help (and your moniker). :-)

Lisa

PS - I am on FM8, I just need to adjust my settings. Thanks for pointing that out.