IF Statement in a Calculated Column in a Summary Report

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
I have a table named "Validations". Within that table, I have two record types, "Budget" and "Validated". My form has a "Budget Units" field and a "Validated Units" field. I am trying to create a Summary report that roles up the Budgeted Units and Validated Units for one project on one row. Then, I am trying to use the formula If([Validated]>[Budget],[Budget],[Validated]) but I'm not getting any results. Any pointers on where I'm going wrong? 

Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
When I have run into trouble with this kind of thing in the past, it has always been around the difference between 0 and null in the comparison. Go into your [Validated] and [Budget] fields and make sure that the checkbox field property for "Treat blank values as "0" in calculations" is checked on. See if that might work.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
I checked my fields and they were both selected to "Treat blank values as "0" in calculations". I've tried different formulas and it seems like only straight calculations work, addition/subtraction/etc. 
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
Make sure you check that setting both on the original source fields in the child table and also in your summary fields at the parent level. I would check them on in all 4 locations. Is your formula an actual formula field in the parent table or are you doing it on the fly in the report? As Mark says, don't try to do the latter in a summary report. Create the actual formula numeric field in the parent table and then reference that in the report.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
This is my problem- I don't have the data separated into a parent/child relationship. I have everything rolling up to one table. It looks like I'll need to separate it into two tables to do what I need. Thank you Michael! 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Are you trying to do calculations on the detail records or the actually summary columns on the report.  You cannot do calcs on the actually summary columns.  If that is what you are trying to do, you cannot do that.  Those columns just exist on the report and are not fields available to do math on.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
I was hoping that wasn't the answer. That's exactly what I was trying to do. Looks like I'll need to separate the budget and validations into two separate tables and use summary fields instead. Thank you! 
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
I guess I'm not fully understanding your business logic and how you are representing it within QuickBase. When you use the phrase "rolling up", that to me in QuickBase-speak is a parent-child relationship using summary fields within the relationship. How do the Budgets and Validations records relate to a Project?