Forum Discussion

BethanyHudson's avatar
BethanyHudson
Qrew Trainee
4 years ago

If Formula Help with commas

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
------------------------------
Formulas and functions
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    4 years ago
    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
    ------------------------------
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    4 years ago
    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
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    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
    ------------------------------