Relationships and Join Tables

[hoyt202]

Surprise, surprise.  I am also a newbie but hopefully I have a softball question for someone just lurking around.

 

Okay, so I originally built a solution for our church directory with the following schema:

 

FAMILY 

_kp_family_id

_kf_member_id

last name

 

MEMBER

_kp_member_id

_kf_family_id

first name

 

It was structured like this so that we could do a printed directory page and make use of the portal to just list the first names and their personal cells, email, etc.

 

 

Now, I'd like to take that solution and also integrate an Attendance chart so that we can take attendance using FileMaker Go and an iPad.

 

This is where the extent of my knowledge begins to be stretched.

 

My goal is for every unique service (Sunday Morning, Sunday Night, Tuesday night, Saturday afternoon, etc.) to have a list that has every members' full name (last, first) and then a group of radio-buttoned statuses next to each of their names.

 

It will look, ultimately, like the attached pdf.  I color coded items that I'm guessing will come from a unique table.

 

Will you please help me sort this out?

 

Thank you,

 

Jason

Attendance.pdf

[doughemi]

Create a Services table.  It will contain a record for each service (_kp_Service_id, date, time, etc).

Create an Attendance table(_kp_attendance_id, _kf_service_id, _kf_member_id, status.  Status is your radio button field with the value list of reasons.  Each record will be one member attending one service.

 

Your attendance list layout will be based on the Services table with a portal to the Attendance table, but also showing the name fields from the Members table.  Most of your data entry will be on a layout  in the Attendance table.

[hoyt202]

doughemi,

 

Thank you for responding.  I will have some time later tonight to try to work on this (after real work stuff) and I'll report back on how well this works or what other issues I might have.

 

Thank you,

 

Jason

[hoyt202]

doughemi, I have been able to sit down and try this and I am stumped on this one.  I have attached a snapshot of my ERD and I will also attempt to explain the method to the madness.  I will list any key fields and important fields:

FAMILY NAME

_kp_family_id

name_last

 

MEMBER NAME

_kp_member_id

_kf_family_id

name_first

 

These two tables were created first so that the last name of Jones (and other general family info like 123 Main St.) lives in the FAMILY NAME table and then the first name of Tom (and any other family member and specific info about Tom like cell phone, email, etc.)

I can then have a directory page (with a Portal) like this:

Jones

-          Tom

-          Mary

-          Any kids, etc.

I now would like to take attendance from an iPad using FileMaker Go and I have setup and also been helped by a few on here with the following additional tables:

 

SERVICE

_kp_service_id

_kf_member_id

_kf_attendance_id

date_service

time_service

 

ATTENDANCE

_kp_attendance_id

status (value list driven (i.e. Present, Sick, Travelling, Working, School, Other, etc.)

 

I have joined the tables together, keeping the original two tables intact, like this:

 

MEMBER NAME                               SERVICE                               ATTENDANCE

_kp_member_id                              _kf_member_id                              

                                                       _kf_attendance_id                _kp_attendance_id

 

My design is to have a main layout from the SERVICE table (with date and time fields) with a portal from the MEMBER table (with MEMBER:_c_name_full and ATTENDANCE::status).

I have also tried to above suggestion of: "Your attendance list layout will be based on the Services table with a portal to the Attendance table, but also showing the name fields from the Members table.  Most of your data entry will be on a layout  in the Attendance table."  I have not successfully been able to get this to jive.

 

This is where I just don’t get why it’s not displaying what I want:

For EVERY new record in SERVICE, I want to list EVERY member (~ 150) with a blank status next to their name so that a radio button can be pressed, and it will record a new record for that member for that service.

 

All of the attempts at manipulating the relationship between the tables that I’ve done have resulted in the following:

1)       No names AND no status field show in the portal

2)      No names BUT ONE status field shows on the first line of the portal

3)      One name AND one status field show on the first line of the portal

4)      ALL names AND ALL status fields show in the portal BUT changing the status on the first record changes all of the rest of the statuses on the other lines to that same status.

 

I do have creation and deletion of records enabled on the "many" side between MEMBER and SERVICE and between SERVICE and ATTENDANCE.

 

This is because I’ve modified the relationships to be =, not equal, X, etc. but I can’t key upon the one that will get me the results I need.

 

I am new to FM and I have completed the available tutorials on VTC and other resources, too.  Would anyone be willing to help point me in the right direction?