Forum Discussion

DanaDana's avatar
DanaDana
Qrew Cadet
8 years ago

IF Statement in a Calculated Column in a Summary Report

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? 

  • MichaelBarrow's avatar
    MichaelBarrow
    Qrew Cadet
    8 years ago
    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.
  • DanaDana's avatar
    DanaDana
    Qrew Cadet
    8 years ago
    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. 
    • MichaelBarrow's avatar
      MichaelBarrow
      Qrew Cadet
      8 years ago
      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.
    • DanaDana's avatar
      DanaDana
      Qrew Cadet
      8 years ago
      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! 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.
  • DanaDana's avatar
    DanaDana
    Qrew Cadet
    8 years ago
    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! 
  • MichaelBarrow's avatar
    MichaelBarrow
    Qrew Cadet
    8 years ago
    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?