7 Replies Latest reply on Apr 14, 2010 4:06 PM by philmodjunk

    day camp/membership database setup



      day camp/membership database setup


      I've been using filemaker since 4.1 (a wonderful program), but I'm not using it anywhere near its potential.

      I currently have 8.5, am thinking about upgrading to 10 advanced (I would like to have parents being able to sign up their kids online (how exciting)

      I have a day camp with 150 kids that go into 9 different groups based on age and sign up for camp from 1 to 10 weeks.

      (including other things like t-shirt size, allergies, day notes...)

      I need to make team lists each day of which kids are signed up for which weeks grouped according to their group (age).

      Now all my fields are text fields (week1, week2, week3... & group1, group2...) and then I have value lists of week1 list = "june 5-june 10" and so on and group1ist1 =  "pinecones" (the name of one of the groups)

      In my layout, when I enter a new camper, I have the fields set up as checkbox sets. So I have all 10 weeks as seperate fields(week1, week2...), showing checkboxes with the weeklist being diplayed ("june5-10", "june13-jun19"...), and all 9 groups displayed the same way & the various other ifo all as text fields.

      So, whenever I need to print a team list, signout sheet or whatever, I goto a layout showing the 9 fields of group names & the 10 fields of weeks & the user needs to check 1st the group then the week, then continue. So if they want a team list for the whole camp, they have to do that 9 times (for each group for that particular week).

      sounds crazy & stupid

      If I set up a text field called groups with a value list of all 9 groups & a text field called weeks with a value list of all the weeks, I can't figure out how to go thru both them fields for summaries for team list, (need each week), sign out/in sheets (need each day) and the other things (t-shirt size and what not)

      thanks for any input


        • 1. Re: day camp/membership database setup

          It'll be much easier to do what you want if you re-design your database so that you have tables of related records instead of:


          all 10 weeks as seperate fields(week1, week2...)




          group1, group2...


          A table for weeks and a table for groups will be many times more flexible and it will be much easier to set something up to print out all groups for a given week.


          If you aren't familiar with portals, you might look that tool up in help as it is a very useful way to work with such groups of related records.

          • 2. Re: day camp/membership database setup


            I got into relational databases & using portals, a bit confusing at first, but wonderful stuff for my call log file


            For my camper file, I still kept it as 1 table (name, address, age, a field for the weeks, named allweeks, using a value list (june10-june15, june18-june22...aug20-aug25) and a field for the kids, named group (using a value list-group1, group2...group7). So each kid can only be in 1 group (a radio button) but can attend any number different of camp weeks (a checkbox).


            So, now my huge problem is, I need a summary report, showing each group, with how many kids are showing up each week, like so


            group1                    # of kids

                 June10-june15       23

                 june18-june22        33


                 aug20-aug25           21



                june10-june15        21

                june18-june22        45


             and so on


            But because the weeks are a value list, when kids sign up for different combinations of weeks, each combination shows up separately (do you know  what I'm trying to say?).


            Now, I can do a find on a certain week, then do a summary report for each group, that works, but it only shows me 1 week per search, where I really need to see the attendance of a particular group for the whole summer (allweeks).


            I can't think how making allweeks a relational table would help.


            Thanks for any advice.



            • 3. Re: day camp/membership database setup

              I can't think how making allweeks a relational table would help.


              Without making it a relational table, it will be very difficult to do.


              The example you post would be very straight forward to create if you based your report on just such a table.


              You would do this with two more tables: A "weeks" table where one record = 1 week of the year and a "Schedule" table where one record represents a single camper attending camp for a specific week. The Schedule table functions as a "Join Table" to enable the many to many relationship you have here.




              WIth that structure, you could create your report by basing it on either the Weeks (using the count function) or the Schedule table (using a "count of" summary field).

              • 4. Re: day camp/membership database setup

                So now I have 3 tables


                campers                 schedule            weeks

                camper_ID ------<sched_ID>------week_ID               (both camper_ID & week_ID are indexed, auto-enter serial, sched_ID is just a number field)

                fname                   camperwk            wk1

                lname                                                wk2

                address                                             ...

                age                                                     wk10

                group                                                wk1sum    - summary = count of wk1

                                                                          wk2sum   - summary=count of wk1



                I created 10 value lists; wk1 list = "june10-15", wk2 list = "june 18-23"...wk10 list = "aug22-27"


                On my camper layout page, where I enter the campers name, age & stuff, I created a portal showing weeks, with all 10 weeks showing their value lists thru a checkbox set.


                I made a report using the table-weeks, with a sub-summary sorted by campers::group with wk1sum1, wksum2, ... also on that layout part,

                and it all seems to work. I'm amazed.


                So, if you think that all looks good, then I'll continue on with it.


                Could you answer these 3 questions.


                1. Why do I need the table shedule?, and camperwk seems totally pointless.


                2. To keep my filemaker form looking like my paper form, the weeks need to look like so:

                      june10-15        july 2-8       aug 2-7

                      june16-20        july 10-15   aug 9-15

                      june22-26        july 17-22   aug 18-22

                so, instead of 1 long thin portal, could I just as well have 9 small portals & place them where I like.


                3. Will this all work in IWP?


                Thanks so much, you've been a great help with your guidance.




                • 5. Re: day camp/membership database setup

                  What I intended was this:


                  camper_ID ------<sched_ID

                  fname                   camperwk>------week (just one week field, not 10!)

                  lname                                                Week

                  address                                             Calculation fields using Sum()



                  • 6. Re: day camp/membership database setup

                    ok, so I changed my relations.

                    In my weeks table, I have 2 fields

                    week         text

                    weeksum  calc = sum(week)    You said calculation fields - should I have more?


                    On my camper layout, I opened a portal to my weeks table, but now what?

                    What field(s) do I place my value list for the actual dates (june15-june20, june23-june28...)


                    I know there are many ways to solve each problem. I would like to do it the right way, as it is important the data is correct.

                    Do you see an inherit flaw with the last way I had it setup. And even if you don't, I would still like to understand what you are trying to get me to do.




                    • 7. Re: day camp/membership database setup



                      You're wrestling with a database concept that isn't the easiest thing to understand nor to explain on the forum. It requres a pretty complete design change from what you are trying to do. Unfortunately, I've been dealing with major and urgent issues that have nothing to do with this forum. These have left me with very little time to respond to forum posts.


                      Anyone else out there want to chime in and guide pve through the steps to set up this many to many system?