Discussions

Expand all | Collapse all

If Formula Help with commas

  • 1.  If Formula Help with commas

    Posted 08-16-2021 09:53
    I've got an If formula that is working, but I need to only have the first comma before the next line to show only if there is text in the next field. Here's my formula:

    (If([Grant Acct.]<>"", [Grant Acct.]&"-"&If([GL Code]<>"", [GL Code]&"-"&If([Function Code]<>"", [Function Code]&"-"&If([Department Code]<>"", [Department Code]&"-"&If([Future Code]<>"", [Future Code]&",\n\n"&

    (If([Grant Acct. 1]<>"", [Grant Acct. 1]&"-"&If([GL Code 1]<>"", [GL Code 1]&"-"&If([Function Code 1]<>"", [Function Code 1]&"-"&If([Department Code 1]<>"", [Department Code 1]&"-"&If([Future Code 1]<>"", [Future Code 1]&",\n\n"&

    (If([Grant Acct. 2]<>"", [Grant Acct. 2]&"-"&If([GL Code 2]<>"", [GL Code 2]&"-"&If([Function Code 2]<>"", [Function Code 1]&"-"&If([Department Code 2]<>"", [Department Code 2]&"-"&If([Future Code 2]<>"", [Future Code 2]&",\n\n"&

    (If([Grant Acct. 3]<>"", [Grant Acct. 3]&"-"&If([GL Code 3]<>"", [GL Code 3]&"-"&If([Function Code 3]<>"", [Function Code 1]&"-"&If([Department Code 3]<>"", [Department Code 3]&"-"&If([Future Code 3]<>"", [Future Code 3])&",\n\n"&

    (If([Grant Acct. 4]<>"", [Grant Acct. 4]&"-"&If([GL Code 4]<>"", [GL Code 4]&"-"&If([Function Code 4]<>"", [Function Code 1]&"-"&If([Department Code 4]<>"", [Department Code 4]&"-"&If([Future Code 4]<>"", [Future Code 4])&",\n\n"&

    (If([Grant Acct. 5]<>"", [Grant Acct. 5]&"-"&If([GL Code 5]<>"", [GL Code 5]&"-"&If([Function Code 5]<>"", [Function Code 1]&"-"&If([Department Code 5]<>"", [Department Code 5]&"-"&If([Future Code 5]<>"", [Future Code 5])&",\n\n"&

    (If([Grant Acct. 6]<>"", [Grant Acct. 6]&"-"&If([GL Code 6]<>"", [GL Code 6]&"-"&If([Function Code 6]<>"", [Function Code 1]&"-"&If([Department Code 6]<>"", [Department Code 6]&"-"&If([Future Code 6]<>"", [Future Code 6])&""))))))))))))))))))))))))))))))))))))))



    ------------------------------
    Bethany Hudson
    Program Administrator
    Mobile Bay National Estuary Program
    Mobile AL
    2514598870
    ------------------------------


  • 2.  RE: If Formula Help with commas

    Posted 08-16-2021 11:13
    I suggest that you re-work your formula to use the List function.  This will separate non blank fields with a separator string that you specify.
    here is the help text on the List Function.
    https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=152&rl=fp

    I think that you want something like this.


    List(",\n\n",
    List("-",  [Grant Acct.], [GL Code],  [Function Code],  [Department Code], [Future Code]),
    List("-",[Grant Acct. 1], [GL Code 1], [Function Code 1], [Department Code 1], [Future Code 1]),
    List("-",[Grant Acct. 2], [GL Code 2], [Function Code 2], [Department Code 2], [Future Code 2]),

    etc
    List("-",[Grant Acct. 5], [GL Code 5], [Function Code 5], [Department Code 5], [Future Code 5])
    )



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: If Formula Help with commas

    Posted 08-16-2021 10:53
    I think you can do this with the List function. Also, using variables will help.

    var text ConditionOne = your first if statement;
    var text ConditionTwo = your second if statement;
    etc...
    etc...

    List(",\n\n", $ConditionOne, $ConditionTwo, $ConditionThree,.....)

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 4.  RE: If Formula Help with commas

    Posted 08-16-2021 11:01
    In fact, I think you can use the list function for your If Statements too. So the entire code would be,

    var text WordString = List("-", [Grant Acct], [GL Code], [Function Code], [Department Code], [Future Code];
    var text WordStringOne = List("-", [Grant Acct1], [GL Code1], [Function Code1], [Department Code1], [Future Code1];
    var text WordStringTwo = List("-", [Grant Acct2], [GL Code2], [Function Code2], [Department Code2], [Future Code2];
    ....

    List(",\n\n", $WordString, $WordStringOne, $WordString Two,.....)

    You can label the variable any name, as long as it is alpha characters only (no numbers). I just realized I used Condition in my first reply and Wordstring in my second....whatever you want to name it that is descriptive works.

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 5.  RE: If Formula Help with commas

    Posted 08-16-2021 11:16
    Perfect! Thanks!!

    --

    Bethany Hudson
    Program Administrator
    Mobile Bay National Estuary Program
    118 North Royal Street
    Suite 601
    Mobile, AL 36602
    (o) 251-459-8870
    (c) 251-367-1368