4 Replies Latest reply on Feb 24, 2014 3:34 PM by RandyWood

    Trying to find the best calculation to count names in a list, then output to another field.

    RandyWood

      Title

      Trying to find the best calculation to count names in a list, then output to another field.

      Post

           Hello,

            

           I have a table of a list of names that will have many names in it. I need a calculation that will count each name, hopefully all at once, and then output the quantity of each unique name to a field that I have created.

           I have tried PatternCount, Count, and have not had the success I wanted so far.

           Also, each one of these name entries has a dollar value associated with it, I would also like to know if there is a way to total those associated entries together into a field that I have created.

            

           Thanks!

           Randy

       

        • 1. Re: Trying to find the best calculation to count names in a list, then output to another field.
          philmodjunk

               What problem do you solve by outputting this data to another field. Are you outputting just the count or also the value--to the same field or two fields?

               Their are two basic approaches that can get your counts and compute your dollar values--

               One is a summary report based on your records where you sort records by the name field and place the name field and a summary field that computes the dollar sub-total in a sub summary layout part.  If you remove the body layout part, you can get a list that lists each name once with it's associated sub total.

               another method is to use a related table where each name appears only once and you use a relationship to access your original table and compute subtotals.

               And other methods beyond that are also possible such as using ExecuteSQL to pull all of this info into a single field.

          • 2. Re: Trying to find the best calculation to count names in a list, then output to another field.
            RandyWood

                 Thank you for this wonderful information!

                 Here is the problem I am trying to solve. I work in a business where my employees get business leads that are forecasted to generate a certain amount of revenue. I want to be able to see each quarter how many business leads they got, and then from the $ amount forecasted from that business lead, total up each of those leads into a field.

                 Also, each of my employees are on individual teams and I want to be able to track the number of business leads and revenue they have among the teams. I have the list of total employees and a separate table, and another table with all of the teams broken down.

                 I hope this explanation helps. Also, I am relatively new to FileMaker, but am fairly familiar with excel and formulas.

             

            • 3. Re: Trying to find the best calculation to count names in a list, then output to another field.
              philmodjunk
                   

                        Here is the problem I am trying to solve. I work in a business where my employees get business leads that are forecasted to generate a certain amount of revenue. I want to be able to see each quarter how many business leads they got, and then from the $ amount forecasted from that business lead, total up each of those leads into a field.

                   This is easily done with the summary report that I briefly described. There is no need to copy this data to another field in another table to get that report to work. Here's a tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

                   

                        Also, each of my employees are on individual teams and I want to be able to track the number of business leads and revenue they have among the teams. I have the list of total employees and a separate table, and another table with all of the teams broken down.

                   That also sounds possible with a straight forward summary report, but there is not enough detail in what you have described so far about how your tables and relationships are structured to be sure of that. I'd need to see tables/relationships and also more about what numbers you need to see: (otals for each team, with a break down for each team member or something else?

              • 4. Re: Trying to find the best calculation to count names in a list, then output to another field.
                RandyWood

                     I was able to use this calculation to total the number of leads which is currently working, but now I need a way to find the number of leads on each team.

                      

                     http://help.filemaker.com/app/answers/detail/a_id/2907/~/how-to-show-the-number-of-related-records