Conditional Formatting One Column Based on a Different Column's Value

user01099
user01099 Member
in Charting

Hello,

 

Working with a table and attempting to change the color/format of "Column A" based on the value of "Column B" .  I understand that I am able to change the format of the entire row or only Column B based on Column B's value, but not one specified column.  Are there any settings in "Chart Properties" that I am missing?  Or, perhaps, there is a Beast Mode to work around this?  Thanks in advance for any help. 

Best Answer

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    I think that you have to go with the old method of HTML injection in this case.

     

    Try this beast mode for Red BackGround:

     

    CASE WHEN `ColumnA`='ColumnB' THEN
    CONCAT('<div style="background-color:#fcbcb7; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `ColumnA`, '</a></div>')
    END

     

    DOJO_Table_HTML_Cond_Format.png

     

    Change the background-color: to the desired colors, you can also use other HTML attributes. 

     

    You can get your HTML color codes from this site:

    https://html-color-codes.info

     

    Hope this helps. Don't hesitate to ask in you need further help.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    I think that you have to go with the old method of HTML injection in this case.

     

    Try this beast mode for Red BackGround:

     

    CASE WHEN `ColumnA`='ColumnB' THEN
    CONCAT('<div style="background-color:#fcbcb7; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `ColumnA`, '</a></div>')
    END

     

    DOJO_Table_HTML_Cond_Format.png

     

    Change the background-color: to the desired colors, you can also use other HTML attributes. 

     

    You can get your HTML color codes from this site:

    https://html-color-codes.info

     

    Hope this helps. Don't hesitate to ask in you need further help.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • ST_-Superman-_
    ST_-Superman-_ Coach

    @RGranada

    I have a new wrinkle to add to this problem.  I would like to create a card that has one row for each account.  Then I want to have a separate column for each of several different products.  The trick is that I then want to be able to format the color of the values in each column based on the competitor name.  

     

    I have tried this by using the following beastmode:

    CASE
    WHEN `Product Group`='Product A' THEN


    CASE
    WHEN `competitor_name__c`='Competitor A' THEN
    CONCAT('<div style="background-color:#7EAEFD; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `Product Group TAM`,'</div>')
    WHEN `competitor_name__c`='Competitor B' THEN
    CONCAT('<div style="background-color:#00FF00; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `Product Group TAM`,'</div>')

    (... continued for a number of competitors, but you get the idea)
    ELSE '0'
    END

    END

     

    I then repeat this beast mode for `Product B`

     

    The issue is that when I put these beastmodes on the table card, I am getting multiple rows for each account name.The orange box is for one account, I want this on one lineThe orange box is for one account, I want this on one line

     Any thoughts on how to accomplish this?


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • AS
    AS Coach

    @ST_-Superman-_

    Can you wrap each of your product columns in a single MAX() function (ie MAX( CASE WHEN `Product.... END) ?  That's what I've done in SQL in this kind of circumstance before and it's worked.

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • PacoTaco
    PacoTaco Member

    Nice solution, however the code will show if you try exporting the data.

     

    I wish there was a conditional formatting solution built into the card design that would allow this natively so the data in the card itself wouldn't need to be manipulated.

     

    We have a use case where we need the output to remain valid/exactly as it is but would like to alert/call out potential issues with the data by highlighting them on the card before someone tries to export it.

     

    Thanks,

    --Nick

This discussion has been closed.

Categories