Discussions

Expand all | Collapse all

Formula to add numbers and display in a list

  • 1.  Formula to add numbers and display in a list

    Posted 20 days ago

    I have 4 numeric fields that I need to 1, add together and 2, display in a vertical list only if they exist. If they dont exist I want it to be collapsed. 

    The formula I have got to complete the second part is: 
    "<span style=\"font-size:11px;\">" &
    "<b>" &

    var text VerticalList =
    (List
    ("<br />$",
    ToText(([Cost Notes])),
    ToText([Note 2 Cost]),
    ToText([Note 3 Cost]),
    ToText([Note 4 Cost])));

    If($VerticalList <> "",
    "$"
    & $VerticalList)

    & "</b>"
    & "</span>"

    To get a value of 0 to not show up though in this list I have to uncheck " treat as 0" in those fields. 

    The problem im running into is then I cant add them into one total cost because ive unchecked treat as 0. 
    But if I checkmark that my list displays, lets say Note 4's costs as $0 which I dont want.



    ------------------------------
    Mike Klausing
    ------------------------------


  • 2.  RE: Formula to add numbers and display in a list

    Posted 20 days ago
    Edited by Adam Keever 20 days ago
    Try adding some if statements:
    "<span style=\"font-size:11px;\">" &
    "<b>" &
    
    var text VerticalList =
    (List
    ("<br />$",
    IF(ToText([Cost Notes])=0,"",ToText([Cost Notes]),
    IF(ToText([Note 2 Cost])=0,"",ToText([Note 2 Cost]),
    IF(ToText([Note 3 Cost])=0,"",ToText([Note 3 Cost]),
    IF(ToText([Note 4 Cost])=0,"",ToText([Note 4 Cost])));
    
    If($VerticalList <> "",
    "$"
    & $VerticalList)
    
    & "</b>"
    & "</span>"​

    If the "" syntax doesn't work, try to replace that with null.

    ------------------------------
    Adam Keever
    ------------------------------



  • 3.  RE: Formula to add numbers and display in a list

    Posted 20 days ago

    This doesn't seem to work. I have to remove the "totext" parts of this equation, but even at that I cant get a formula to work that way.

     

     

     

    Thank you,
    Mike Klausing

     






  • 4.  RE: Formula to add numbers and display in a list

    Posted 20 days ago
    Edited by Adam Keever 20 days ago
    This syntax should work for you Mike:

    "<span style=\"font-size:11px;\">" &
    "<b>" &
    
    var text VerticalList =
    (List
    ("<br />$",
    If([Cost Notes]=0,"",ToText([Cost Notes])),
    If([Note 2 Cost]=0,"",ToText([Note 2 Cost])),
    If([Note 3 Cost]=0,"",ToText([Note 3 Cost])),
    If([Note 4 Cost]=0,"",ToText([Note 4 Cost]))));
    
    If($VerticalList <> "",
    "$"
    & $VerticalList)
    
    & "</b>"
    & "</span>"​



    ------------------------------
    Adam Keever
    ------------------------------



  • 5.  RE: Formula to add numbers and display in a list

    Posted 20 days ago

    Got it to work. Thanks Adam!

     

    Thank you,
    Mike Klausing