8 Replies Latest reply on Feb 26, 2014 11:32 AM by MallieWilliams

    Calculation

    MallieWilliams

      Hello guys,

       

      If I have a field called Status and I have a drop down list (ex) Stage 1, Stage 2, Stage 3, Stage 4 and Stage 5 how can I get a count for each stage? If that make sense. I know that there will be another field called count that I need to write a calculation on.

        • 1. Re: Calculation
          Mike_Mitchell

          Hello, Mallie.

           

          One way to do this (the bad way, IMHO) would be to insert calculation fields that always evaluate to the value of each status code. Then define self-joining relationships from those fields to your status field. You can then use the Count ( ) function to determine the total.

           

          A better way would be a separate table that contains records for the various status codes. Use a relationship from the status in that table to the status in your main table. Do the Count ( ) field in the status codes table. As a bonus, you can define your drop-down based on the values in the status code table, so if you ever need to add a new status code, everything is already in place to do your metrics.

           

          HTH

           

          Mike

          • 2. Re: Calculation
            MallieWilliams

            Hey Mike,

             

            I'll try it the second way. I don't understand this part though..As a bonus, you can define your drop-down based on the values in the status code table, so if you ever need to add a new status code, everything is already in place to do your metrics. Shouldn't there still be a calculation done in the Count field? Should a Case () function be done in this field? Due to the different Values. Stage 1, Stage 2 ect.  So say if I'm in the portal and I use  stage 1 in the dropdown list for 3 records I want the count to say 3. If I go to the next portal row and put stage 3 and there are 5 records or rows with Stage 3 the count should be 5.

            • 3. Re: Calculation
              erolst

              Mike is absolutely right: using a Stages table saves you from having to use (and maybe later adapt) any lengthy Case()s, by giving you the natural place for the count: each Stages record simply counts itself.

               

              1. Your value list will consist of the primaryKey and the statusName fields from the Status table. Should you add another stage, the value list will automatically grow. (And should you decide to rename your stages, then that's no problem, either.)

               

              2. In your other table, instead of using a text value for the status, define a the field as foreign key field for the status and format it with your value list. (If you use a popup, the status name is displayed automatically, while you're actually inserting a key.)

               

              3. Define a relationship from your Status table to the other table, where Status::primaryKey = OtherTable::statusForeignKey, and define a calculation field in Status as Count ( OtherTable::statusForeignKey ) (or another non-empty field, e.g. the primary key field).

              • 4. Re: Calculation
                Mike_Mitchell

                Mallie -

                 

                Try the attached for an example.

                 

                Edit: erolst is correct about using keys. The sample doesn't, because key management is a little more complex. Once you get the basic concept down, follow his advice for using keys instead of the names of the status codes.

                • 5. Re: Calculation
                  erolst

                  So you don't want a total count of Stages, you want to count the different stages that are used in a child/join table records for a record in that other table?

                  1 of 1 people found this helpful
                  • 6. Re: Calculation
                    MallieWilliams

                    For each stage I want the total count. So if stage 1 has 3 records in the portal, then the total would be 3. And if stage 2 have 5 records in the portal then the total count would be 5 and so on.

                    • 7. Re: Calculation
                      MallieWilliams

                      Hey Mike, this file got me on the right track..I will play with what I have now..Thanks!

                      1 of 1 people found this helpful
                      • 8. Re: Calculation
                        MallieWilliams

                        Thanks erolst,

                         

                        I'm on the right track..Thanks for your help again.