Discussions

Expand all | Collapse all

creating specific formula (if, then, else?)

  • 1.  creating specific formula (if, then, else?)

    Posted 22 days ago
    Hello,

    I want to create a numeric formula where if one cell is empty, then i want the the formula to give back 0, else, if it has a number inserted, then i want the outcome to be another cell - the number inserted cell

    1st Cell) Inventory par (always going to be 9)
    2nd Cell) Inventory on hand (insert a number)
    3rd Cell) Inventory to order (0 if nothing is inserted, otherwise ([inventory Par] - [inventory on hand]))

    Please advise,
    Thank you

    ------------------------------
    Brandon Hermez
    ------------------------------


  • 2.  RE: creating specific formula (if, then, else?)

    Posted 22 days ago
    The fields generally have a setting where null values are treated as 0, but you can probably also have it where you can set them as variables and go from there:

    var number inventoryHand = if([Inventory on Hand] > 0, [Inventory on Hand], 0);

    [Inventory Par] - $inventoryHand

    -----
    Hope this helps

    ------------------------------
    Ryan Stanford
    ------------------------------



  • 3.  RE: creating specific formula (if, then, else?)

    Posted 22 days ago
    This formula could lead to a negative number being entered if the inventory on hand is greater than the par value.

    To cover all bases, 
    var number inventoryHand = if([Inventory on Hand] > 0 and [Inventory on Hand] < [Inventory Par], [Inventory on Hand], 0);

    ------------------------------
    Paul Peterson
    ------------------------------



  • 4.  RE: creating specific formula (if, then, else?)

    Posted 21 days ago
    So I tried this and It asked me to put a number after the semicolon, so i put the [Par] - [inventory on hand] and i'm still getting the same result. The issue is if the on hand is null then i want the order amount to be 0, and it only does the equation (par - on hand) when someone enters in a number for the on hand inventory. The par is always static so right now in the order it is always showing that static number when on hand inventory is null

    ------------------------------
    Brandon Hermez
    ------------------------------



  • 5.  RE: creating specific formula (if, then, else?)

    Posted 21 days ago
    Try this:

    If(IsNull([inventory on hand]), 0,  [Par] - [inventory on hand])

    ------------------------------
    Paul Peterson
    ------------------------------



  • 6.  RE: creating specific formula (if, then, else?)

    Posted 21 days ago
    Hi Paul,

    Thank you so much so far! I tried your formula: If(IsNull([inventory on hand]), 0,  [Par] - [inventory on hand])

    i'm still getting the par as my result to order instead of 0 when my on hand inventory is null
    Any other idea? Thank you so much!


    ------------------------------
    Brandon Hermez
    ------------------------------



  • 7.  RE: creating specific formula (if, then, else?)

    Posted 21 days ago
    Try this instead:

     If([inventory on hand]="", 0,  [Par] - [inventory on hand])

    ------------------------------
    Paul Peterson
    ------------------------------