Multi User Select Field Colorization

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I am wondering if it is possible to colorize a multi-select field gracefully on a report. For example, I would like to assign each option in the field a color, but if two or more are selected I want all of their colors to show. 

So I have this list:

Jack (blue)
Carol (red)
Barbara (purple)
Janet (yellow)

I know that on my custom report I can colorize if there is only one selected. However, I am wondering if I, for instance, select both Jack and Barbara on this list can I have both blue and purple show? So on the report, it lists them both separated into different "bubbles". Can I colorize this report so each bubble shows the correct color? I have attached a couple screenshots for reference. The first is what the report looks like, the second is a mock up of what I am looking for. Thanks in advance!
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
What you will want to do is create a formula text field that converts those entries into colorized text.

It will need to evaluate each option that is there, and assign a color.

It will be a List function, with some if statements.

List(", ",
If(Contains([Name], "Jack"), "Blue Text", ""),
If(Contains([Name], "Carol"), "Red Text", ""),
If(Contains([Name], "Barbara"), "Purple Text", ""),
If(Contains([Name], "Janet"), "Yellow Text", "")
)

I'll need to circle back to this to make that text become colorized.  
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,266 Points 2k badge 2x thumb
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
I don't see where this answers my questions. It just appears to be a list of colors. 
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,266 Points 2k badge 2x thumb
Sorry - it's a tool that allows you to see hundreds of different color options which can be used in QuickBase - just thought it's helpful when picking colors to pick ones which are readable in a standard QuickBase environment (instead of "red","blue" etc.).
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
It is a cool tool. I currently use the HTML color picker on the w3 schools website. This may be a resource I can use in the future. Thanks
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
instead of having "Blue Color" you can add this

"<div style=\"font-size: 10pt; color:white;background-color:blue; width:100%;  text-align:center; \">Jack</div>"
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
So this would need to be done for all combinations? So Jack would be blue, Carol would be red, and the combination of Jack and Carol would be a third color?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Instructions to setup the IOL technique are in every pastie:

There is a good chance that this code uses the image onlaod techique which you can read about here:

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=294

These may help - especially the second one which inlines the above instructions with screenshots:

What is the Simplest IOL Example?
https://community.quickbase.com/quickbase/topics/what-is-the-simplest-iol-example

How Do I Setup the Image Onload Technique (IOL)?https://community.quickbase.com/quickbase/topics/how-do-i-setup-the-image-onload-technique-iol
(Edited)
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
Once I figured out what I was doing I was able to get this to work. Thanks so much for the help!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Lesson to others: Took less than an hour for Andreonna to become an IOL master.
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
To be fair, I placed a catering order in the middle of that hour. So more like 20-30 min to set it up. Understanding it in depth will take a bit longer. 

Next question. When I filter the reports using dynamic filters the colorization disappears. Is there a reason for this? So in your sample table if you filter it to show only jack it removes the colors. However, if you create a custom report filtered to only show jack, the colors are there. It's only when filtering using the dynamic filters on the left side of the report page. 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
When dynamic filters execute it overwrites the changes made by the IOL so you have to add additional JavaScript to again clobber the changes made by the dynamic filter or use another technique call BOL (Branding OnLoad).

It is sort of an arms race between native QuickBase and your script but luckily your script can always prevail. In either case one way to solve the problem is to write a mutation observer in script to detect when the dynamic filters code changes the display and then reinvoke the color changing portion of your script.

You can search this forum for mutation observer  and find some examples but I don't have time to provide an example:

https://community.quickbase.com/quickbase/topics/resize-record-picker-popup-window

I have tradition of submitting presentations for Empower that never get approved because they are so stunningly advanced and creative that QuickBase staff just faint and collapse in reviewing them. In 2015 I prepared a presentation entitled "Let Me Mutation Observe That For You". I will see if I can find the demo I created.
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Correct (At least going this route),  If you have a ton of options there are a few other ways, but multi select fields are tricky.

Here is a more complete formula for your example

List(", ",
If(Contains([Name], "Jack"),
"<div style=\"font-size: 12pt; color:white;background-color:blue; width:100%;  text-align:center; \">Jack</div>", ""),
If(Contains([Name], "Carol"),
"<div style=\"font-size: 12pt; color:white;background-color:red; width:100%;  text-align:center; \">Carol</div>", ""),
If(Contains([Name], "Barbara"),
"<div style=\"font-size: 12pt; color:white;background-color:purple; width:100%;  text-align:center; \">Carol</div>", ""),
If(Contains([Name], "Janet"),
"<div style=\"font-size: 12pt; color:white;background-color:yellow; width:100%;  text-align:center; \">Carol</div>", "")
)
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
I am finding that multi select fields are a pain. This seems to be the only option in terms of assigning multiple people to one record though. 
Photo of Trinity_QB_Ninja

Trinity_QB_Ninja, Champion

  • 10 Points
There is no way to run a loop in QuickBase formula.  Therefore, there is a lot of repeating syntax.  I built it out for 4 users.  This would need to be built out for 20 users total.  Also, users are hard coded which is going to be a pain to maintain over time.  Good Luck!


var text jackstyle = "background: purple; color: white; border-color: purple'>Jack";
var text janetstyle = "background: yellow; color: black; border-color: yellow'>Janet";
var text carolstyle = "background: red; color: white; border-color: red'>Carol";
var text barbarastyle = "background: blue; color: white; border-color: blue'>Barbara";

var text css = "display: inline-block; margin: 1px; background: white; max-width: 98%; "&
"text-overflow: ellipsis;word-wrap: break-word; background: red; border: 1px solid rgb(204, 213, 240);" &
"border-radius: 5px; padding: 2px; -moz-border-radius: 5px; -webkit-border-radius: 5px;";

var text usera = Part(ToText([List User]), 1, ";");
var text userb = Part(ToText([List User]), 2, ";");
var text userc = Part(ToText([List User]), 3, ";");
var text userd = Part(ToText([List User]), 4, ";");

var text useraf = If(
Contains($usera, "jack")=true, $jackstyle,
Contains($usera, "janet")=true, $janetstyle,
Contains($usera, "carol")=true, $carolstyle,
Contains($usera, "barbara")=true, $barbarastyle 
);

var text userbf = If(
Contains($userb, "jack")=true, $jackstyle,
Contains($userb, "janet")=true, $janetstyle,
Contains($userb, "carol")=true, $carolstyle,
Contains($userb, "barbara")=true, $barbarastyle
);

var text usercf = If(
Contains($userc, "jack")=true, $jackstyle,
Contains($userc, "janet")=true, $janetstyle,
Contains($userc, "carol")=true, $carolstyle,
Contains($userc, "barbara")=true, $barbarastyle
);

var text userdf = If(
Contains($userd, "jack")=true, $jackstyle,
Contains($userd, "janet")=true, $janetstyle,
Contains($userd, "carol")=true, $carolstyle,
Contains($userd, "barbara")=true, $barbarastyle
);

"<span style='" & $css &
List("</span><span style='" & $css,
$useraf,
$userbf,
$usercf,
$userdf,
""
)&
"</span>"
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
>Also, users are hard coded which is going to be a pain to maintain over time.  Good Luck!

You could just write a short off-line JavaScript function that took an array of user names and styles and spit out the QuickBase formula as text. Whatever repeating pattern is in the QuickBase formula can easily be iterated over in script.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
UPDATE: I made a simple  change to detect dynamic filters through the hashchange event:

https://haversineconsulting.quickbase.com/db/bmm3bpupr?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=563

No need to use Mutation Observers with this change.

I also unchecked the Searchable property of the [-] field so remnants of this field would not display in the dynamic filter panel:

     Include this field when searching/filtering this table

To be honest I can't believe QuickBase implemented this dynbamic fitler option through using the URL hash - the change state should have been persisted in local storage.
(Edited)
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
I may not understand everything you are saying here, but it certainly works like a charm! Thanks so much!