I have child "Task" records that each have a status assigned to the record. I want to calculate the overall status of the Parent "Deliverable" to give an idea of where all the sub tasks level in an overall status. For example out of 5 tasks 2 are yellow, 1 is red and 3 are green, so the overall status of the deliverable to describe the tasks would equal GREEN.

npYou would make a summary field on the Parent to total up the # of Greens, # of Yellows and # of red, in three separate fields.Then make a formula to calculate the average Task Status.Post back if you need help with the formula.

Hi QuickBase Coach,Could you please provide me with the formula as well? I will work on the summary fields first. Thanks,Aylin

I actually think I did the Summary field incorrectly because of what I choose for the "Total of" part of the summary field. I think it's wrong because the total of the records on the screen shot says 6 and not 3. Please advise.

The summary field should just be a Count if the number of records. Nothing to do with the Record ID.

Let me know when you have the summary fields working.

Calculating the ""most"" used text answer or average of all text-multiple choice answers

18 Replies

QuickBaseCoachD
Qrew Captain
6 years ago
np
You would make a summary field on the Parent to total up the # of Greens, # of Yellows and # of red, in three separate fields.

Then make a formula to calculate the average Task Status.

Post back if you need help with the formula.
AylinNazlim
Qrew Cadet
6 years ago
Hi QuickBase Coach,

Could you please provide me with the formula as well? I will work on the summary fields first.

Thanks,
Aylin
AylinNazlim
Qrew Cadet
6 years ago
I actually think I did the Summary field incorrectly because of what I choose for the "Total of" part of the summary field. I think it's wrong because the total of the records on the screen shot says 6 and not 3. Please advise.
QuickBaseCoachD
Qrew Captain
6 years ago
The summary field should just be a Count if the number of records. Nothing to do with the Record ID.
QuickBaseCoachD
Qrew Captain
6 years ago
Let me know when you have the summary fields working.
- AylinNazlim
  Qrew Cadet
  6 years ago
  I fixed the summary fields! I didn't change the first section to a specific field, that's where I messed up.
QuickBaseCoachD
Qrew Captain
6 years ago
Actually, here isa format for a formula

var number Max = Max([# green], [# yellow], [#red])

Case($Max,
[#green], "Green",
[#yellow], "Yellow",
[#red], "Red")
- AylinNazlim
  Qrew Cadet
  6 years ago
  Hi,
  
  I have just a couple questions on this:
  
  1. Is this all one formula?
  2. What does the "var number" mean above?
  3. #green, does this need to be the #FieldNameofTotalGreen?
  4. Field Type is: Formula - Rich Text, correct?
  
  Thanks!
  Aylin
- QuickBaseCoachD
  Qrew Captain
  6 years ago
  1. Yes this is all one formula.
  
  2. The var refers to a formula variable. it is a useful technique to make formulas more readable. Here is some help text. https://help.quickbase.com/user-assistance/formula_variables.html
  
  3. Yes, you would put in the name of your field for the total # of green.
  
  4. The formula is formula text , not formula rich test.
- AylinNazlim
  Qrew Cadet
  6 years ago
  Hi I'm almost there!
  1. I'm getting this error message for the first "MAX" shown below. What does MAX refer to?
  
  2. Just to take this one step ahead while we are talking about it. I will also like to average the formula of the Average Task Statuses to the related parent record, would I make a formula-text field on the parent table for the Averaged child records of the Grand child records?
QuickBaseCoachD
Qrew Captain
6 years ago
It needs a semi colin on his first line

var Number Max = Max([Total Green Workstream Status], [Total Yellow Workstream Status], [Total Red Workstream Status]);
- AylinNazlim
  Qrew Cadet
  6 years ago
  Yay that worked! Also, how would I change the formula to keep the field blank if there are no related Workstream Status Records?
- QuickBaseCoachD
  Qrew Captain
  6 years ago
  Make this change to blank the result if all zero.
  
  IF($Max > 0,
  Case($Max,
  [Total Green Workstream Status], "Green",
  [Total Yellow Workstream Status], "Yellow",
  [Total Red Workstream Status], "Red")
  
  If([PicExistsFull], "<a target='_blank' href=" & $URL & ">" & $Words & "</a>", " ")
  )
  
  be sure that the field properties of the the three Totals fields are set to treat blank as zero.
AylinNazlim
Qrew Cadet
6 years ago
QuickBaseCoachD
Qrew Captain
6 years ago
OK, now I see your relationship but you will need to explain this with an example

I will also like to average the formula of the Average Task Statuses to the related parent record, would I make a formula-text field on the parent table for the Averaged child records of the Grand child records?