12 Replies Latest reply on Jun 16, 2011 4:55 AM by DonaldBennett

    Attendance Reports

    DonaldBennett

      Title

      Attendance Reports

      Post

      I'm having a problem. I have the following table relationships. Some events are weeklong and away and don't require attendance. Some events are local occur regularly and do require attendance. I've got portals working fairly well. What I want to do is get a report printed out for attendance at the regularly occurring events. An example: An event, mission trip, will have to persons attending. It's a one time, week long event. Another event, Lunch for Kids, recurs weekly with some different people attending each week. I'd like to get a report of who attends the Lunch for Kids, with weekly subtotals, names, etc... I cannot figure out how to do that using portals. There's a sample file at http://tinyurl.com/3bv97mb

      Any help will be appreciated. 

      relationships.gif

        • 1. Re: Attendance Reports
          philmodjunk

          Instead of using a portal, try setting up a list of attendees from a layout based on Attendance. You can perform a find or use Go To Related Records to pull up just the related records for a specific event and date. Fields from Person and Event can be added to this layout to fill in needed details.

          • 2. Re: Attendance Reports
            DonaldBennett

            I guess I'm not sure how to do that without using portals from person and event.

            • 3. Re: Attendance Reports
              philmodjunk

              If there is just a single related record in the other table, you can add a field from the related table directly to the layout without using a portal. A portal is only needed when you have multiple related records involved for a given record in your layout's table.

              Step by Step. On you Attendance layout, enter layout mode and use the field tool to add a new field to the body of the attendance layout. When you release the mouse button you'll get a dialog box from which to select the field you want to add to the layout. Select Person from the drop down list of tables at the top of this layout, then click First to select the Person::First field for your new field.

              When you return to browse mode, you should now see the first name of the person to whom each attendance record is related. You can repeat this with the other name fields to add the full name to each attendance record.

              In similar fashion, you can add fields from the Event table to a header or sub summary part to display the name of the event.

              • 4. Re: Attendance Reports
                DonaldBennett

                Thanks for your help, but I can't make it work. I get the message: "This action cannot be performed because this field is not modifiable." I'm going to rethink how to do events and attendance from the top.

                Thanks again.

                • 5. Re: Attendance Reports
                  philmodjunk

                  What exactly were you doing when you got this message?

                  This error message indicates that you were somehow trying to change the value of a calculation field by editing it. This could be through trying to create a related record where the key field is a calculation field it could happen if you enter a calculation field and try to edit it or a script attempts to use a step such as Set Field to modify the value in a calculation field.

                  • 6. Re: Attendance Reports
                    DonaldBennett

                    I added the full name field which was a calculation field. I switched to the first name field as you suggested and that works, but it doesn't actually relate. I need to use the EventPersonJoin::PersonID field and then I can add other fields.

                    So now that that's working, I'm assuming that I'll have to add a new record for every event-person instead of having one event record with every person on it?

                    • 7. Re: Attendance Reports
                      philmodjunk

                      I hope you aren't using names for the ID fields in your relationships. That's not a good way to go here at all. They should be auto-entered serial numbers.

                      "I'm assuming that I'll have to add a new record for every event-person instead of having one event record with every person on it?"

                      That is indeed how such a join record is intended to be structured. That's what I assumed that you were already doing here. Wink

                      • 8. Re: Attendance Reports
                        DonaldBennett

                        Well, what I was hoping to have was an Event table with records of events, i.e. Mission trip, VBS, Sunday school, etc... Then I want a separate attendance table where I can have VBS day 1, VBS day 2, VBS day 3, etc... That's why I started using portals. I could have one record showing lots of names. Then I wanted to be able to run reports of who attends VBS and on what days. I think I'm getting too convoluted for my newbie skill level.

                        Yes, I am using serial numbers for ID fields. Smile

                        • 9. Re: Attendance Reports
                          philmodjunk

                          Well, what I was hoping to have was an Event table with records of events, i.e. Mission trip, VBS, Sunday school, etc...

                          Which is what you would use your Event table to record.

                          Then I want a separate attendance table where I can have VBS day 1, VBS day 2, VBS day 3, etc

                          Which would be the purpose of the Attendance table where you can generate one record for each participant for specific combination of Event ID and date.

                          Your EventPersonJoin table has a different purpose, it links specific records in Person to specific records in Events. A single record would link one person to one event. The link from EventPersonJoin, would then link one such record to all the attendance records for that person for that recurring event (You'd see if johnny smith attended day 1, day 2... of VBS for example.)

                          By performing a find in your attendance table, you see all the attendance records matching your search criteria. If you find all attendance records for a given event such as vbs, you can get a list of all participants over all the dates of the event. Sorting can then group these records by day and arrange them in alphabetical order.

                          There are two ways to use this table to track attendance, BTW, you can use a script to generate one attendance record for each participant for each day and use a field in the table to record them as present or absent or you can only create records in the attendance table for people who are present for that day. Either way works, but the first option makes it easy to produce and use check lists for recording who's present/absent on a given day.

                          None of this precludes the use of portals, by the way. Portals would remain very useful for data entry and for browsing the data. What I'm suggesting just takes advantage of the structure you already have to list your attendance records in a flexible format that avoids issues you can encounter when trying to print such a report using portals.

                          • 10. Re: Attendance Reports
                            DonaldBennett

                            I'm feeling a little thick this week, but I see the logic here finally. I'm not up to scripting yet so we won't go there.

                            If I add people to VBS via a portal, so it shows everyone attending, can I then somehow filter my results in a value table to show only those people? That way when I select a PersonID for a specific day, it doesn't show me everyone in the whole database, only those attending the main event.

                            • 11. Re: Attendance Reports
                              philmodjunk

                              But of course, such "filtered" value lists are called conditional value lists.

                              Forum Tutorial: Custom Value List?

                              Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                              Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

                              • 12. Re: Attendance Reports
                                DonaldBennett

                                Thanks, I now have conditional value lists working. What is happening, however, is when I add some one to the attendance table, it also adds them to the Events table. So now, I have events with people listed multiple times, once for every session they've attended. I may just completely dump the portal on the Event layout. People at a one time event could be entered into the attendance table, just like for multiple occurrences. This may then screw up the conditional value lists. We shall see.

                                Thanks for all your help, PhilModJunk.