Discussions

 View Only
  • 1.  IF Statement in a Calculated Column in a Summary Report

    Posted 06-19-2017 16:23
    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? 



  • 2.  RE: IF Statement in a Calculated Column in a Summary Report

    Posted 06-19-2017 22:48
    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.


  • 3.  RE: IF Statement in a Calculated Column in a Summary Report

    Posted 06-20-2017 14:23
    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. 


  • 4.  RE: IF Statement in a Calculated Column in a Summary Report

    Posted 06-20-2017 14:49
    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.


  • 5.  RE: IF Statement in a Calculated Column in a Summary Report

    Posted 06-20-2017 14:56
    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! 


  • 6.  RE: IF Statement in a Calculated Column in a Summary Report

    Posted 06-20-2017 14:35
    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.


  • 7.  RE: IF Statement in a Calculated Column in a Summary Report

    Posted 06-20-2017 14:49
    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! 


  • 8.  RE: IF Statement in a Calculated Column in a Summary Report

    Posted 06-20-2017 15:05
    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?