7 Replies Latest reply on May 25, 2011 11:00 PM by Shimanto

    Yet another countif question

    Shimanto

      Title

      Yet another countif question

      Post

      I have read a number of posts about how to implement an equivalent to a spreadsheet countif function in filemaker, but none of them seem to provide a solution for my current need.

      I am making a student database, and one thing I need to do is record class attendance. I have a field for each class with values of present, late or absent. I want to calculate a total number for each value for each record. In Excel or Numbers, I use the countif function. How can I do this in Filemaker Pro 10?

      Thank you.

        • 1. Re: Yet another countif question
          philmodjunk

          I want to calculate a total number for each value for each record.

          And what record is that? We'd need to know how you've structured your system, what tables, what relationships, before we can suggest a method that would work for you.

          A difference in table structure and/or relationships could explain why the posts you've read don't seem to work for you.

          If you are looking for a report that looks like this:

          John Smith:
               Present: 200
               Absent:  5
               Late:      1

          Jane Smith
                Present: 199
                absent:  3
                Late:      4

          It can be done quite easily with a summary report--provided that you have a table structure that will support it.

          • 2. Re: Yet another countif question
            Shimanto

            Thank you for posting Phil. I'm reading through some of your other posts and I think I may find some answers there Creating a student attendance register. I have 425 students in 10 classes, so it is rather complicated setting this up. I'm going to change my tables and relationships to the ones you suggested in that post. I'm missing two of the tables you mentioned that will help simplify things and also help me wrap my head around related tables. Here are some brief answers to your questions. I will update my posting after I have read your other postings on this topic so you won't have to explain anything twice.

            And what record is that?

            I  originally intended to have a record for each student in the attendance table. The records have a text field for each class with a pull down menu with values of Present, Absent and Late and calculation fields for the total of each in each record, like I would do in a spreadsheet.

            After thinking about it and trying to get my head out of spreadsheet mode and into relational database mode, perhaps the solutions mentioned in other posts would be appropriate if the attendance table had a record for each class instead of one for each student and a field for each student related to the students table.

            We'd need to know how you've structured your system, what tables, what relationships, before we can suggest a method that would work for you.

            Currently I have three tables: a table for students, with each student's, number, picture, contact info etc.; a table for grades, with a record for each student (perhaps this would be similarly better served with a record for each project/test with fields for each student); and the attendance table.

            Any advice on how to structure the tables and related fields would be greatly appreciated.

            I'm still getting my head around related tables and FMP in general. I appreciate your patience. Thank you.

            <<Edited this post to turn link text into hyperlink--PhilModJunk>>

            • 3. Re: Yet another countif question
              philmodjunk

              I think the advice in the link you found provides a good table structure for what you describe here. Feel free to respond to this link with any follow on questions you might have about how to set this up. The key here is that it's much easier to count records than it is to count values from a set of fields defined with in a single record.

              • 4. Re: Yet another countif question
                Shimanto

                I'm stuck on writing the script to create records in the Attendance table with dates for each student. I have a lot of dates and a lot of students, I want to write a script so I can copy each student record numerous times adding each date of each class to each student. I also need a script to add a records to Attendance for each student with each additional class, for dates not yet defined. I'm trying to figure it out, but I'm stuck.

                • 5. Re: Yet another countif question
                  aammondd

                  Structurally You would create a set of nested loops

                  The first (outside loop) would be based on your student table

                  You would go to the first record and loop through each student

                  you would set variables for the Student ID

                  Inside that loop you would start another loop that would loop through a set of dates  (you could do this from a table of dates such as school days)

                  You would set a variable to hold the date

                  Then you would move to the attendance table and create a new record

                  set the student id and date fields

                  Return to the inside loop and go to the next in loop

                  once you had completed that loop you go to the next in the outer loop which would repeat the process for the next student.

                  Now loops in and of themselves can be tricky to control

                  Usually in this method you need  to set a counter variable and advance it once during each loop then compare the counter to the found count

                  If the counter becomes greater than the found count then you exit the loop.

                   

                  I dont have a handy link to a loop tutorial but Phil or someone else might.

                  It wouldnt matter which table is the inner loop and which is the outer loop as both will create the same number of records

                   To add classes you might want to work from the date through the student to the student classes table

                  You can nest a number of these loops you just need to keep track of each loops exit independtly and realize that it gets exponentially longer the number of inner loops you add.

                  • 6. Re: Yet another countif question
                    aammondd

                    You can also use a begin and end dates entered at the time you run the script throgh an input box. For your date looping

                    And add  if logic around the inner loops to skip certain dates. There are a number of ways to write this logic

                    Something like if your input begin date was a monday

                    Set Variable (Sloopdate;Value = begin date from input box) [not sure of the syntax for this yet]

                    Set Variable (Sweekcounter; Value= 1)

                    Loop

                    If ($weekcounter; <= 5 )

                    [ Loop Stodent processing loop ]

                    End-if

                    Set Variable ($weekcountr; Value = $weekcounter +1)

                    If ($weekcounter; >5)

                    Set Variable ($loopdate;$loopdate + 3)

                    Set Variable (weekcounter; Value = 1) 

                    Else

                    Set Variable ($loopdate;$loopdate + 1)

                    Set Variable ($weekcounter; Value = $weekcounter + 1) 

                    Exit Loop If ($loopdate > $enddate)

                    Next

                    End Loop

                    Dont take the syntax as gospel at the moment I dont have FM handy this is just logical coding

                     

                    SOmeone else might have a better more syntax perfect example.

                    • 7. Re: Yet another countif question
                      Shimanto

                      Thank you very much. I'm working on it and hopefully unstuck.