4 Replies Latest reply on May 10, 2011 3:27 PM by rjserrano




      COUNT IF


      I've been searching for help on this and am not really liking anything I'm seeing...There's got to be an easier way???  I have a field made up of about 7 unique values.  I want to set up 7 different (new) fields...each one would return a count of the number of each unique value...the field is an attendance type field.  It is a description of a reason for an absence...i.e. illness, dr. appt., etc.  I want to set up another field(s)...one that will calculate how many were due to "illness"...another field that will calculate how many due to "dr. appt.", etc...A COUNT IF (for those who've worked with Excel) type function...Please, someone give me an easy way to accomplish this task?!  Thanks!


        • 1. Re: COUNT IF

          I can't promise "easy". There are several different approaches and you indicate you 'didn't like what you saw'. That makes me cautious about suggesting anything as I may well be suggesting the same thing you already don't like.Wink

          Let's run through some questions to get a clearer picture of what you want to accomplish here...

          How likely are these "7 different values" to change in the future? (we can set things up so that changing a value is a data entry operation or we can use an approach that "hard wires" things to the 7 values.)

          How is your data structure? (Describe your table from which you want your counts) Is there a related table involved? (say an employee table and you want to see counts for that one employee...)

          How do you want this to look on the screen or on the printed page?

          • 2. Re: COUNT IF

            ok...so you're going to make me really show my ignorance by describing how my tables work...ha ha!  Understand I am relatively new to this and am completely self taught...that's my disclaimer...anyway, on to the description...first off, I did think about the "changing a value in a data entry operation", but I don't think for what I'm trying to accomplish makes the most sense...Now let me describe what's going on here...

            I'm a basketball coach and I'm tracking a number of things (more stat related)...however, I'm wanting to incorporate keeping track of my players and their ability/desire to make practice.  I want to know how many practices we have, if they're at practice or if not (what reason)...so the main table/layout is by player and includes roster info, address, as well as parent and parent address info...a 2nd table is practice info (practice #, date, a value list that houses the info i was talking about - at practice, illness, injury, dr. appt., etc., as well as free throw stats - makes, misses, %...and a few other assorted bits of info)...i also have a table for game stats...so back to the original question.  I'm wanting to show on each players layout how many practices they were at and how many they missed based on reason...so I do want (hard wired) to know how many were due to illness, due to injury, etc.  I hope this makes sense???

            Oh, and also...the practice info exists in a portal for each player...in other words each line of the portal is one practice...I hope my design is correct?...again, I'm new to this.

            Finally, I hope my terminology is somewhat coherent?  And thanks for the help.


            • 3. Re: COUNT IF

              Sounds like you've got a reasonable structure to your tables, though the devil can really be in the details when it comes to database design.

              I'll assume you have this relationship to make your portal happen. (Don't need the portal for this, but knonwing you have it tells me that your relationship works for this approach):

              Players::PlayerID = Practices::PlayerID

              If you have FileMaker 11, you can set up a series of filtered portals that give you your counts for each category. I'll do this for "At Practice" and "Ill" and let you extrapolate to all the counts that you want.

              Define a summary field that computes the "count of" PlayerID (any field that's never empty will serve for the count here)

              Add another portal to practices on your Players layout. Make it just one row in size. Select the "Filter Portal Records" option and use this expression:  Practices::Status = "At Practice". Put just the summary field you have just defined inside this portal. Select the other options and formatting you want for this portal. You can make the portal boundaries invisible so that it looks like it is just a field. Now make a copy of the portal, open Portal setup... for the copy and change the filter expression to:  Practices::Status = "Ill"

              You can continue this method to add as many selective "counts" of your practice records that you need. It's main advantage is that you do not have to define a series of different relationships and calculation fields to implement each count. It's main disadvantage is that the counts in these portals will not update "smoothly" each time you add or edit a record in the practices portal you have already put on this layout. To get the totals to update, you'll likely need to use an OnObjectExit or OnObjectSave trigger on the Status field to perform a script that does this:

              Commit Record
              Refresh Window [Flush cached join results]

              An option you may want to also use is to create a summary report on the practices table. This report would use the same summary and status fields, but could provide a "break down" of all the practice records for one, a group, or all players on your team roster. You can further restrict the records to a specific date range if that is useful.

              The report would look like this:

              Magic Johnson

              Ill:  2
              At Practice: 20
              Unexcused: 1

              Wilt Chamberline

              Ill : 1
              At Practice : 22

              and so forth.

              Similar reports can be used to summarize other data such as computing a free throw percentage for each player.

              • 4. Re: COUNT IF
                Thanks for the help!...and I'm at least a little relieved to know I'm actually doing things correctly...and yes, my database is set up as you described...whew! And although, unfortunately I don't have the time at the moment to give this a try...I must say in concept and visualization...it sounds brilliant!!!...I'm just a little upset that I didn't figure it out on my own...feeling a little intellectually inferior at the moment!  ha ha! Anyway, thanks again and I can't wait to give this a shot...I'm confident this is going to work perfectly!  Will let you know! Randy