4 Replies Latest reply on May 18, 2010 3:26 PM by fitch

    Counting items based on 2 parameters



      Counting items based on 2 parameters


      I'm testing out Filemaker 11.0v1 to make a CRM database and I want to see how many leads a sales person has closed.  I have 2 tables set up: a leads and sales person table.  The leads table has categories for the name of the lead, the sales person working the lead, and whether it is closed or still prospective ("status").  The sales table has the sales person's name, the # of leads still open, and the # of leads closed.


      I want the # of closed leads category to be a count of the number of leads that a sales person has closed.  In other words, if there were a countif() function, I would countif(Leads::lead;leads::salesperson=salesperson and leads::status= "closed").  I've tried searching through other posts and wasn't able to figure this out.  Any suggestions? 

        • 1. Re: Counting items based on 2 parameters

          One way to do it is with a global calculated field in the sales person table that is equal to "closed" -- let's call it globalStatusClosed.


          Now make a relationship with two predicates:




          Then make a calculated field in sales, using that relationship and a field that always has a value such as an auto-entered ID:


          • 2. Re: Counting items based on 2 parameters

            Thanks, that helped me understand things a little better.  It does leave me with another question though...


            What if I want to have another column in my Sales table which counts the total # leads (open or closed) each sales person has?  Using relationships like this seems like it will limit me pretty quickly...

            • 3. Re: Counting items based on 2 parameters

              You can do everything with only one relationship:


              Total number of leads =

              Count ( Leads::LeadID )


              Closed leads =

              ValueCount ( FilterValues ( List ( Leads::Status ) ; "Closed" ) )



              P.S. Don't use names for relationships.

              • 4. Re: Counting items based on 2 parameters

                Michael's solution is better than mine. And I completely agree, using names (i.e. any modifiable data) for relationships is not a good idea. I only used that in my example to stay in line with the question as asked, but never mind that -- use unique IDs for relationships, not names!


                I was going to suggest yet another way to do it: in the leads file, make separate fields for each status. Make each status a number field that you set up as a checkbox (1 vs 0 or empty). You could make trigger scripts so that when one status is set, the others are cleared. You could then use simple sum or count functions from the salesperson.


                However, if you anticipate many different status options, this might not be the best way. It all depends on what kinds of entry and reporting you'll be doing.