3 Replies Latest reply on Jan 15, 2010 1:11 PM by comment_1

    How to make a field that is a summary of unique values for multiple fields.

    jyajj

      Title

      How to make a field that is a summary of unique values for multiple fields.

      Post

      I have one table that is called "Evaluations" Each Evaluations record has 19 eval fields (evalA, EvalB, EvalC). Each field has one evaluation result from a value list of 7 options.

       

      ****The reason for keeping all the evaluations as one record is because it relates to a certain item that has those evaluations and certain groups (2 other tables)****

       

      My problem is that I want to count how many of each value list option is in each field for each evaluation in a "dyamic" report without having to make 60 calc fields.

       

      Example:

       

      EvalA I need to know how many of  Value list option1, option2, option3, option4, option 5 for Eval A

       

      for Eval B I need to know how many of Value list Option1, option2. option3, option 4, option 5 for Eval B

       

      this would repeat for all evals.

       

      The table should look something like this.

       

                                        EvalA  EvalB EvalC ......

       

      valuelistchoice1total       2        4        6

      valuelistchoice 2 total     4        0        3

      valuelistchoice 3 total     1        1        2

       

       

      Is this possible????   Any help would be great! Thank You

       

        • 1. Re: How to make a field that is a summary of unique values for multiple fields.
          comment_1
            

          jyajj wrote:

          ****The reason for keeping all the evaluations as one record is because it relates to a certain item that has those evaluations and certain groups (2 other tables)****


          That's not a good reason. You should have an idividual record for each evaluation in a related table. Then it's very easy to produce a summary report like:

           

          Item 001

           Eval A

           • Choice1: 2

           • Choice2: 4

           • Choice3: 1  

           

           Eval B

           • Choice1: 4

           • Choice2: 0

           • Choice3: 1  

           

           Eval C 

          ...

           


          • 2. Re: How to make a field that is a summary of unique values for multiple fields.
            jyajj
              

            Your absolutely correct. 

             

             

            ..... but I work at a Huge company and I am new to FileMaker and need to create this report ASAP. 

             

            Just to reassure you that I wasn't just being lazy.....My logic was that  a user reports on the same evaluations for every item and multiple users can share the same item and I needed a calc to calc all evals connected to each user independently and separately based on an algorithm ....so it seemed logical to say "all evaluations for this particular part" dumped into one table...at first.

             

            Any work around help for now would be great. I plan to break the evaluations up in the next version of the database!

            • 3. Re: How to make a field that is a summary of unique values for multiple fields.
              comment_1
                

              IMHO, it would be easier to fix this right now than to try to patch it. I don't see how you can achieve your goal with your current structure without performing 133 calculations (19 evals * 7 choices) and the same number of summaries.

               

              You could perhaps reduce the number of required fields by using repeating fields, or run a script that compiles the summaries into a global field - but none of these are something I would suggest for someone new to Filemaker to try.