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

# Calculation

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

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

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

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

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

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

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

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

Thanks erolst,

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