Getting Started

 View Only
  • 1.  Working on Nested if Statement

    Posted 03-17-2022 12:25
    Hi Everyone,

    I was working on a currency formula, but it looks like my nested if statement does not work properly. The formula that I have now is 
    If([Reimburse Amount Override]>=0,[Reimburse Amount Override],
    If([Category Requires # of 1-Way Trips Field],
    If([Category - Multiplied by Mileage],
    ([# of 1-Way Trips] * [Category Rate]) * [Resource' Commuter Miles 1-way],
    ([# of 1-Way Trips] * [Category Rate])

    ),
    [Mileage Cost]+[Cost]
    ))

    The problem here is that Override works fine, but when I put "cost" and l leave "Reimburse Amount Override" blank , it does not calculate the cost any more!

    ------------------------------
    Michael Naser
    ------------------------------


  • 2.  RE: Working on Nested if Statement

    Posted 03-17-2022 12:36
    Can you say in plain english words what the logic is.  It's hard to help debug if we don't know your intent.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Working on Nested if Statement

    Posted 03-17-2022 13:46
    Sorry for the confusion! My goal is to being able to override this formula, but I would like to have the override argument an "optional" thing. Therefore, when I leave "[Reimburse amount override]" empty, I would like to have the formula work as it is.

    This is the original formula ( it works properly)
    If([Category Requires # of 1-Way Trips Field],
    If([Category - Multiplied by Mileage],
    ([# of 1-Way Trips] * [Category Rate]) * [Resource' Commuter Miles 1-way],
    ([# of 1-Way Trips] * [Category Rate])
    ),
    [Mileage Cost]+[Cost]
    )
    I created a new currency field called "Reimburse amount override" and then I added to the main form. And then I came up with the following formula;
    If([Reimburse Amount Override]>=0,[Reimburse Amount Override],
    If([Category Requires # of 1-Way Trips Field],
    If([Category - Multiplied by Mileage],
    ([# of 1-Way Trips] * [Category Rate]) * [Resource' Commuter Miles 1-way],
    ([# of 1-Way Trips] * [Category Rate])

    ),
    [Mileage Cost]+[Cost]
    ))
    Now the override works fine, but the problem is when I put so "cost", and I leave "Reimburse Amount Override" blank , the formula won't work anymore!

    ------------------------------
    Michael Naser
    ------------------------------



  • 4.  RE: Working on Nested if Statement

    Posted 03-17-2022 14:05
    Before I burn any brain cells can you tell me if on that override field you have deselected the checkbox to treat blank as zero?  ie we want to treat blank as nulls and not as zero.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Working on Nested if Statement

    Posted 03-18-2022 12:24
    Mark! Appreciate your help! I forgot to deselect the checkbox to treat blank as zero. Everything is working properly!

    ------------------------------
    Michael Naser
    ------------------------------