Forum Discussion

AshleySolomon's avatar
AshleySolomon
Qrew Trainee
2 years ago

Numeric Formula with multiple conditions

HELP! I've racked my brain over this one. 

I have a Numeric Total Field (Currency) that gets it's value via serval different possible options.

1st it looks at a Manual Entry Field. If the Manual Entry field is populated then value is multiplied by the Fee Rate to provide a Total.

If the Manual Entry Field is blank...

2nd it looks at a Snapshot field and multiplies that value by the Fee Rate to provide a Total. 

If both are blank then the Total is $0.00.

My is issue is if I entry 0 into the Manual Entry Field so there is no charge (treating it like an override) the formula isn't calculating. How do I account for this in the formula?

Current Formula:

If([Domestic Wires (#) - Manual]<>0, Sum([Domestic Wires (#) - Manual] * [Fee Schedule - Wire Fee (Domestic)]), Sum([# of Domestic Wires (Snapshot)]*[Fee Schedule - Wire Fee (Domestic)],0))

I have tried this one too...

If([Domestic Wires (#) - Manual]<>0, Sum(If([Domestic Wires (#) - Manual]=0,0), [Domestic Wires (#) - Manual] * [Fee Schedule - Wire Fee (Domestic)]), Sum([# of Domestic Wires (Snapshot)]*[Fee Schedule - Wire Fee (Domestic)],0))

Snap Shot for Reference...



------------------------------
Ashley Solomon
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago

    In Quickbase, there is a difference between a zero and a null.  I suggest that you go to the field properties for

    [Domestic Wires (#) - Manual]

    and you will probably see that these checkbox is enabled to treat blank as zero.  So deselect that.  That will be that blank is null and not zero. Then you can test with IsNull( ....)

    If(not IsNull([Domestic Wires (#) - Manual]), 

    [Domestic Wires (#) - Manual] * [Fee Schedule - Wire Fee (Domestic)],

    [# of Domestic Wires (Snapshot)]*[Fee Schedule - Wire Fee (Domestic)],0)

    Note that you don't need the clutter of those SUM(   ) functions.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------