Forum Discussion

MikeKlausing's avatar
MikeKlausing
Qrew Assistant Captain
6 years ago

Formula to add numbers and display in a list

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
------------------------------
Formulas and functions
  • AdamKeever1's avatar
    AdamKeever1
    Qrew Commander
    6 years 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
    ------------------------------
    • MikeKlausing's avatar
      MikeKlausing
      Qrew Assistant Captain
      6 years 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

       



      ------Original Message------

      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
      ------------------------------
      • AdamKeever1's avatar
        AdamKeever1
        Qrew Commander
        6 years 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
        ------------------------------