Forum Discussion

MichaelNaser's avatar
MichaelNaser
Qrew Trainee
3 years ago

Working on Nested if Statement

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
------------------------------

4 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    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
    ------------------------------
    • MichaelNaser's avatar
      MichaelNaser
      Qrew Trainee
      3 years ago
      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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        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
        ------------------------------