Discussions

Expand all | Collapse all

SUBTRACT MULTIPLE FIELDS

  • 1.  SUBTRACT MULTIPLE FIELDS

    Posted 03-20-2018 18:00
    Hello I am trying to subtract multiple fields.  Here is what I am trying to do:

    ((payment amount) - (client fee) - (override) )/2

    The main problem I am having is the override field has blanks so it does't recognize the formula.  Perhaps the field types need to be changed?

    Payment amount - Currency Field
    Client Fee - Currency Field
    Override - Formula - Numeric

    Thanks for your help.


  • 2.  RE: SUBTRACT MULTIPLE FIELDS

    Posted 03-20-2018 18:02
    Try this:
    Sum([Payment amount],-[Client Fee],-[Override])/2


  • 3.  RE: SUBTRACT MULTIPLE FIELDS

    Posted 03-20-2018 18:05
    You have two choices.
    Set the properties of the [override] field to treat blank as zero.

    or else

    ([payment amount] - [client fee] - Nz([override]) )/2

    The Nz function will change a null numeric to a zero.


  • 4.  RE: SUBTRACT MULTIPLE FIELDS

    Posted 03-20-2018 18:07
    Awesome... that worked!  Thank you!