3 Replies Latest reply on Feb 9, 2010 8:07 AM by CeesPrins

    New here, having problems with grouping based on checkbox

    CeesPrins

      Title

      New here, having problems with grouping based on checkbox

      Post

      Hi all,

       

      I'm new to FM and running into problems, would appreciate any help after digging into this forum without result :-(

       

      Let me describe the problem in english which is not my native language

       

      We have a childhood here and I'm trying to setup a simple database with the children

       

      For the moment I want an automated list for all kids who are scheduled on a certain day.

       

      (assume) I only have the fiields:

      'First' (firstname/text)

      'Group' (group/text) and

      'Days' (days present/checkbox with Mo, Tu, We, Th, Fr)

       

      Let's say today Jan 28 (Thu) I want to print a list who should be present and who is not scheduled (just in case they visit us an extra day they are on the list) and that per group 

       

      On 1 page I want the following grouping based on the 'Th' from checkbox list because it is a thursday

       

      "Some text' + day of the week (actual) + date (actual) 

       

      Group: 1

      scheduled: (total scheduled group 1)

      Alley

      Bert

        Zack

       

      not scheduled: (total not scheduled group 1)

       

        Amy

        William

       

      Group: 2

        scheduled: (total scheduled group 2)

      Allison

      Jeffrey

       

      not scheduled: (total not scheduled group 2)

        Ben

      Sam 

       

      Total scheduled group 1 + 2

       

      Total unscheduled group 1 + 2

       

      Total scheduled + unscheduled for group 1 + 2

       

      --------------------------------------------------------------

       

      I want to do this on a daily basis considering the values in the checkbox list. 

       

      I'm completely in the blind, so hoping somebody can help me 

       

      Many thanks in advance

       

      Beste regards,

      Cees 

       

       

       

       

       

       

       

        • 1. Re: New here, having problems with grouping based on checkbox
          philmodjunk
            

          You only show one field for marking children "present" on don't show any field for recording a child's schedule. You'll need both items in order to create your report. You'll also need at least two additional tables with which to track attendance and schedules.

           

          Your Children table should have at least:

          ChildID (auto-entered serial number)

          Name

          Group

           

          Your Schedule table: (1 record for each day of the week that a child is scheduled).

          ChildID (number)

          DayName (text, use with drop down list to select day of week by name)

           

          Your Attendance table:
          ChildID (number)

          AttDate (date field)

           

          You can one portal on your Children layout to record the child's schedule and another to record their attendance or you can record attendance on a separate layout based on your attendance table.

           

          Now a summary report can be created, based on the Attendance table, to list children present on a given day (or the week, or other time period...) and you can group your children by Group and whether or not they were scheduled for that day.

           

          Here's a link to a simple tutorial on setting up summary reports that you may find useful:

          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: New here, having problems with grouping based on checkbox
            mmccarty
              

            1) Create the following calculation fields:

            numDays=PatternCount(Days;¶)

            DayofWeek=DayName(Get(CurrentDate))  *make this global

              These two fields tell you how many days a kids is scheduled to come and what is the current day of the week.

             

            2) Create this custom function

            CheckSchedule(s,field)=If(s>1; If(GetValue(field;s)=DayOfWeek;1;0)+CheckSchedule(s-1;field);CheckSchedule(1;field))

             *For simplicity, make sure Days field is Monday, Tuesday, etc instead of Mo, Tue

            Since you are using a checkbox, the Days field is really a ¶ delimited list of which days a child comes.  This scripts looks through that list and if one of those days matches the current date it will return 1 if not 0

             

            3) Create the calculation field, Scheduled=If(CheckSchedule(numDays;Days)=1;"Scheduled";"Not Scheduled")

            This field uses the custom function to see if a child is scheduled to come today or not.

             

            4) Create a new layout.  Go to Layouts>Part Setup and create a subsummary report for Group and a subsummary report by Scheduled.  In the First field in the body part. 

             

            5) Sort by Group then Scheduled

             

            Let me know if you have any questions,  I will elaborate or include pictures.

             

            • 3. Re: New here, having problems with grouping based on checkbox
              CeesPrins
                

              Thank you mmccarty and Phil for your advice.

              I'm sorry I have been ill for some time, so read your replies today.

              Will dig in it, because I'm not only new to this forum but also to Filemaker Pro so have to learn a lot.

              Thanks again.

               

              Regards,

              Cees