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

  • 38
  • 1
  • Question
  • Updated 2 days ago
  • In Progress
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. 
Photo of Aylin Nazlim

Aylin Nazlim

  • 312 Points 250 badge 2x thumb

Posted 2 weeks ago

  • 38
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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.
Photo of Aylin Nazlim

Aylin Nazlim

  • 312 Points 250 badge 2x thumb
Hi QuickBase Coach,

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

Thanks,
Aylin
Photo of Aylin Nazlim

Aylin Nazlim

  • 312 Points 250 badge 2x thumb
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. 

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
The summary field should just be a Count if the number of records. Nothing to do with the Record ID.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Let me know when you have the summary fields working.
Photo of Aylin Nazlim

Aylin Nazlim

  • 312 Points 250 badge 2x thumb
I fixed the summary fields! I didn't change the first section to a specific field, that's where I messed up. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Actually, here isa format for a formula

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

Case($Max,
[#green], "Green",
[#yellow], "Yellow",
[#red], "Red")
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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.
Photo of Aylin Nazlim

Aylin Nazlim

  • 312 Points 250 badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Please post your code too as I cannot correct a screen shot.  It looks like the formula variable is missing the last semi colon.

We might have to rename the formula variable to call it "highest". Maybe Max is a reserved word.

The formula is using a formula variable just to increase the readability of the formula. https://help.quickbase.com/user-assistance/formula_variables.html

As for your second question, I  am not understanding your question.  Can you explain with an example.

 
Photo of Aylin Nazlim

Aylin Nazlim

  • 312 Points 250 badge 2x thumb

var Number Max = Max([Total Green Workstream Status], [Total Yellow Workstream Status], [Total Red Workstream Status])

 

Case($Max,

[Total Green Workstream Status], "Green",

[Total Yellow Workstream Status], "Yellow",

[Total Red Workstream Status], "Red")
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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]);
Photo of Aylin Nazlim

Aylin Nazlim

  • 312 Points 250 badge 2x thumb
Yay that worked! Also, how would I change the formula to keep the field blank if there are no related Workstream Status Records?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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.
Photo of Aylin Nazlim

Aylin Nazlim

  • 312 Points 250 badge 2x thumb

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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?