11 Replies Latest reply on Jun 14, 2012 1:04 PM by mactitioner

    Count if

    mactitioner

      The Situation:

       

      Once a week a report is generated automatically and sent to me in a CSV format with the names of employees and the deals they closed that week.

       

      Every week my job is to manually count how many times an employee's name shows up on this report and present this information to my higher-ups to show how many deals an employee closed that week.

       

      To avoid manually counting and potentially miss counting I wanted to deploy a small FileMaker database solution to handle this process for me.

       

      Where I'm currently at:

       

      I have created the first table in my DB that contains all the employees on file and their relevant data.

       

      Since I only need to calculate this once per week over a 13 week quarter I have created 13 separate tables each to represent one week in the quarter.

       

      The question:

       

      What is the best way to automate this process so that I can click a button and import the weekly report so that it then counts all the times an employees name shows up on a report?

       

      I was thinking a "count if" statement would do the trick but I don't know how to create a calculation that wouldn't require me going into the report each week and defining what fields should be counted.

       

      Thank you in advance for everyone's support on this project. As I'm relatively new to FileMaker I have been taking online training with VTC and Lynda but I find that some of their materials come up short where I really need them.

        • 1. Re: Count if
          comment

          mactitioner wrote:

           

          I have created 13 separate tables each to represent one week in the quarter.

          That's hardly a good idea. All your imported reports can - and should - live in the same table.

           

           

          It would help seeing the format of the two reports (received and intended), but it seem s that all you have to do is find the records in the date range of interest and summarize them by employee.

          • 2. Re: Count if
            mactitioner

            Thats a great suggestion and I can certainly keep them all in one table. But that still doesn't answer my question: what is the best way to import this report so I can assign it to a specific field inside the table (week 1 week 2 ...) and get it to count the names?

            • 3. Re: Count if
              comment

              mactitioner wrote:

               

              But that still doesn't answer my question: what is the best way to import this report

              You don't want specific fields for week 1, week 2, etc. You want ONE field with the week number, and another field with the quantity (or perhaps the amount?).  As I mentioned earlier, it's difficult to be specific without seeing what actually comes in and what needs to come out.

              • 4. Re: Count if
                mactitioner

                I have attached to images. This is where I want the information to be populated and how I want it to be displayed for the end results. The goal is so I can carry this information around on my iPad and have coaching conversations based on how they preformed in specific weeks.

                • 5. Re: Count if
                  mactitioner

                  I have attached the only field that I care about in the report that is generated too. So you can get an idea of what I get.

                  • 6. Re: Count if
                    comment

                    mactitioner wrote:

                     

                    I have attached the only field that I care about in the report that is generated too. So you can get an idea of what I get.

                     

                    All I see is a list of names. I presume each occurrence of a name is a "sale" that needs to be counted - but how does one know the week number of the sale? Is there alaso a date or a week column?

                     

                     

                    BTW, don't you have a unique EmployeeID? What will you do if you get two employees with the same name?

                    • 7. Re: Count if
                      mactitioner

                      The report comes out weekly which includes the data from the week prior, so each week the names change. Jane doe could show up there one week but not the next. Also yes, each time the name shows up represents one completed transaction. Im just attempting to count how many times the name appears which would represent the total transaction then get that information into the other side without having to type it b/c there is in realty 108 employees.

                      • 8. Re: Count if
                        comment

                        mactitioner wrote:

                         

                        The report comes out weekly

                         

                        I understand that, but if you will be importing the reports into a single table, then you need a way to tell them apart. If there is no date or week number in the source file itself, you could add one as part of the importing process - but then it is difficult to keep track of which reports were already imported.

                        • 9. Re: Count if
                          mactitioner

                          Cool, assuming I have the files named by the date they were produced and there is a time stamp inside how would I accomplish the remaining task?

                          • 10. Re: Count if
                            comment

                            See if the atached sketch can get you started.

                            • 11. Re: Count if
                              mactitioner

                              That looks perfect. Thank you.