Getting Started

SOLUTION: Making your Conditional List pretty (with html)

  • 1.  SOLUTION: Making your Conditional List pretty (with html)

    Posted 02-20-2020 17:14

    So, I have seen it on the forum many a time "How do I make a list look like a list, but [insert complication/conditional here]...". Well today I'm gonna give you all some code that may be helpful to start down the path of making stuff look gooder without having to use scripting or hire Dan. (All the love to Dan, as he has helped every one of us at some point.)

    Ok here we go. All HTML, all the time...


    The Problem
    You have a Contact (named Nicole Samkange) and you want to submit her for a new job as a "Content Champion" on a client account. You need to know if she is already on the account and who else is on the account ("...She works well with Bob, but not with Sally" or whatever the reason). You also need to know if someone has already submitted her for the job, as there is nothing HR likes more than 20 submissions for the same thing.

    The lists we want

    You also have many different roles you can request for her (or anyone) but you only want to show what is relevant. So let's look at the fields we want to work from...

    The conditionals we have...

    The code we make...
    So, you need to make yourself a nice Rich Text Formula field and a case statement.
    But, let's break this down. What are we looking at?
    What it looks like broken down
    Case([Role], //(1)
    
    "Content Champion", //(2)
    If(ToText([Current Content Champion])<>"", //(3)
    "<h3 style='color:steelblue;'>Current Content Champion Contacts</h3>" //(4)
    &"\n" //(5)
    &SearchAndReplace(ToText([Current Content Champion])," ; "," <br>") //(6)
    ,""), //(7)
    

    (1) Start with a case statement. This is what you will be breaking all your lists up by. In this case the job [Role].

    (2) List all of your [Role] choices in "". You are literally looking for when the [Role] matches this text.

    (3) When your [Role] matches your text you must tell it what to show, but we want this to be conditional, so we will be using an if statement here. We also have pulled a Summary field from the Agency table. We use this Summary field to pull a list of all of the Resources in that [Role] currently approved in the [Approval] field at the Agency we are trying to place Nicole. But Summary fields can be a tricky thing. First we have to turn it into a string of text. We do that by adding ToText in front of the Summary field. Now if there is no one at all in the [Role] at this Agency, I don't want to show anything, but if the Summary field isn't blank (<>"") I want to show all the pretty.

    (4) Let's make a pretty header for our list. I like steelblue. I also like my words to be bold, so I used Header 3 (h3) size (google html header sizing and web colors for more info). Then I put my header title, making sure to close my header sizing after that.

    (5) I want to start my list on a new line so here I add a return by typing &"\n". & is how you add text and text elements to your code, and "\n" means return.

    (6) Now to make the list of names below the header. We now need to break down our long list of names in our Summary field. To do that, first convert the Summary field to text with the ToText. Next enclose the whole thing and do a SearchAndReplace. When you convert a Summary field to text it divides each name with a space, a ;, and another space. We need to get rid of all of this so we do a SearchAndRepalce where we take out " ; ", and replace it with a space and a line break " <br>". I know you are asking, "Why not just leave a space and not add another space?". The reason is that the space and break will now only show up when replacing a ;, keeping you from having extra spaces at the end of your list.

    (7) ...and the last part. Remember I said at the beginning, that even if they chose this role, if there were no names in the Summary field I didn't want anything to show? Yeah, this just says that. Just make sure to put a comma after this if you have more conditionals.

    So let's see this for real...

    What it looks like all-together

    Case([Role], 
    "Content Champion",
    If(ToText([Current Content Champion])<>"","<h3 style='color:steelblue;'>Current Content Champion Contacts:</h3>"&"\n"&SearchAndReplace(ToText([Current Content Champion])," ; "," <br>"),""),
    
    "Business Development",
    If(ToText([Current Business Development])<>"","<h3 style='color:steelblue;'>Current Business Development Contacts:</h3>"&"\n"&SearchAndReplace(ToText([Current Business Development])," ; "," <br>"),""),
    
    "eCommerce Senior Manager",
    If(ToText([Current eCommerce Senior Manager])<>"","<h3 style='color:steelblue;'>Current eCommerce Senior Manager Contacts:</h3>"&"\n"&SearchAndReplace(ToText([Current eCommerce Senior Manager])," ; "," <br>"),""),
    
    "eCommerce Manager",
    If(ToText([Current eCommerce Manager])<>"","<h3 style='color:steelblue;'>Current eCommerce Manager Contacts:</h3>"&"\n"&SearchAndReplace(ToText([Current eCommerce Manager])," ; "," <br>"),""))
    As you can see, this is what makes the conditional lists. To make the Pending list, do the same thing, but pull another Summary field from the Agency table that just shows the Resources in the [Role] that are still Pending in the [Approval] field. 
    I hope this helps a few of you, and if you have a better simple html way, please feel free to share. I love new ideas and solutions. :)


    ------------------------------
    Meredith Moore
    aka Quick Base Girl
    ------------------------------