Forum Discussion

JessicaMatenga's avatar
JessicaMatenga
Qrew Trainee
5 years ago

Formula to return correct subtotals

HI
I have two numeric subtotal fields from on table to another.  I have tried to create a single formula field using the If and Isnull statement, but it is only returning data from the isnull field.
Result 1 - If(IsNull([Purchase Order Subtotal]),[PO Subtotal.],[Purchase Order Subtotal])

If the [Purchase Order Subtotal] is populated, it would return the Purchase Order Subtotal for jobs that had a value in this field.  But for jobs where the [PO Subtotal.] is populated, it returns a zero result.

Result 2 - If(IsNull([PO Subtotal.]),[Purchase Order Subtotal],[PO Subtotal.])

If the PO Subtotal. is populated, it will return the value of the PO Subtotal. value.  But for jobs where the [Purchase Order Subtotal] is populated it returns it as the same as the PO Subtotal. value.

Note:
- the [PO Subtotal.] field is also an If and Isnull formula field
- the [Purchase Order Subtotal] field is an * formula field

I think I have figured it out but don't know how to write the formula.  Essentially, in either case, the Result 1 returns as Zero because in all these cases, if the Purchase Order Subtotal contains data, then the PO Subtotal. will not be populated anyway.  It is either one or the other so the ISNULL is incorrect.  I need a formula that returns either one or the other, whichever one is populated.

------------------------------
Jessica Matenga
------------------------------

5 Replies

  • Can you look at the field properties for the summary fields and make sure that the box is unchecked that says to "treat blank as zero".   Then the IsNull should work as you expect.   Post back if that does not fix the problem.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
  • Hi Mark
    It is not working but I think is it probably from the look up fields rather than the formula itself.

    I have also tried - 
    If([PO Subtotal.]=0,[Purchase Order Subtotal],[PO Subtotal.])
    If([Purchase Order Subtotal]=0,[PO Subtotal.],[Purchase Order Subtotal])


    Partially works but doesn't calculate any changes to the if I use the second formula.

    ------------------------------
    Jessica Matenga
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      Can you say in words the logic that you want?

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------
  • 1. Old Field with old data
    2. New Field with new data

    I want to return to be, if data exists in the old field, show this data.  However, if data exists in the new field, show that data. 
    But the old field is no longer visible, so if data is in both the old and the new, the new field does not update with the new data.  
    The only time the new data is displayed is when I change the field in the formula.

    I tried to combine the two fields in the table where the totals come from the create the relationship across to the table I want to display the answer.  The results are still the same.

    ------------------------------
    Jessica Matenga
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      If there is data in both fields which on do you want to show?

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------