2 Replies Latest reply on Apr 19, 2011 9:38 AM by BillyWilliams

    Youth Club, Courses and Sessions

    BillyWilliams

      Title

      Youth Club, Courses and Sessions

      Post

      Hello,

      I have volunteered to help a local youth club keep track of its activity. (This was on the very thin basis that I am the only person they know who has a little computer knowledge, as I happen to use a bit of MS Excel in my day job.)

      I thought the activity could be kept on an Excel spreadsheet, which I could do. However, its now obvious that Excel is not enough, and the only other option is to use FileMaker Pro 11.

      This leaves me in a bit of pickle, as I have offered my help, but am a bit out of my depth. 

      The youth club runs Courses, such as Football, Music and Judo.

      Each course usually consists of about 8 Sessions.

      Most Sessions are free, but for some there is a £1 charge.

      All the club want is to keep track of which kids are booked on each Course and their Session attendance.

      As I am a complete novice at this I am trying to keep the database as simple as possible for my own benefit.

      I am trying to keep the number of Tables and Relationships as small as possible.

      I know I will need tables for Youths, Courses, Sessions and some sort of table which is a blend of all three.

      Before I dive in I would appreciate any advice as what to aim for.

      I am thinking of having two main layouts for viewing and editing the data.
      (I will try and attach pictures of the these to this post)

      1) Layout showing Youth and related Courses/Sessions

      2) Layout showing Course and related Sessions/Youths

      In both of these layouts I would like to be able to update the related information.

      My questions are:

      (a) Are my layouts the best way to approach this task?

      (b) Would there be a better layout taking into account this is my first attempt at FileMaker?

      I would prefer to use the built in features of FileMaker rather than get into lots of programming scripts. (Even if that means the database is ‘clunky’ in parts.)

      Any suggestions would be really appreciated.

      Thank you,

      Billy

      Layouts.jpg

        • 1. Re: Youth Club, Courses and Sessions
          philmodjunk

          You've done an excellent job of analyzing the basic needs of this organization. You've listed: Youth,Courses, Sessions and these will make three different tables in your database.

          Before you an design layouts, you need relationships that properly link your tables so that you can look up a Youth record and see courses, sessions and attendance records for that Youth as well as looking at these records from the point of view of a particular course and/or session.

          Much of what I am about to post may be unfamiliar to you. Be prepared to look things up in FileMaker help and to ask follow up questions as needed.

          You'll need to open up Manage | Database and define the above 4 tables on the tables tab, define some basic fields on the fields tab and then link them in relationships on the Relationships tab.

          You'll need these relationships:

          Youth---<Sessions>----Courses

          Youth::YouthID = Sessions::YouthID

          Courses::CourseID = Sessions::CourseID

          Youth::YouthID and Courses::CourseID should be defined as auto-entered serial number fields of type number. Their matching fields in these two relationships should be simple number fields (no auto-enter options).

          Double click the relationship line linking Yout to Sessions and select "Allow creation of record via this relationship" for Sessions.

          Now you can place a portal to Sessions on your Youth layout and you can use it to record attendance and payments for each session. You'd select the CourseID from a drop down list or pop up menu of courseID's (With course names listed in a second column of values). The session date can be a date field with a drop down calendar or it can automatically enter today's date,  or both. Attendance fields and payment fields in the sessions table can be placed in this portal to record attendance and payment.

          Once you get those basics working, there are more things you can do with this, such as a report based on Sessions that shows attendance and payment for each course, broken down by sessions. A similar report can pull up the attendance record for a specific youth and show what payments have been made.

          There are ways to use a fourth table to list who has entrolled in each course. There are ways to automate things so that you can pull up a set of session records that list all enrolled youth so that you can just click a check box to mark each youth that is present, but you'll want to get the basics working first.

          What you have here is called a "many to many" relationship. This demo file uses different table names to match "Contracts to Companies" instead of matching "Youth" to "Courses", but you may find it useful to examine:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          • 2. Re: Youth Club, Courses and Sessions
            BillyWilliams

            Thank you PhilModJunk,

            Before making my post I read through the Forum trying to find a solution, and at the same time getting a feel as how to list my needs clearly, so hopefully it was easy to understand.

            From a quick scan of your reply I can see the basics that I need to get together.

            Once the basic structure is up and running I will find it much easier to build upon that foundation.

            It looks like I now have quite a few hours of work to put in.

            When it is finished I will post a version of it somewhere as an aid to other people, (but that will be good while away yet!)

            Thank you.

            Billy