Black not working as a color

Jojo_Stella · 11-15-2019 10:57 PM

I have a color type column I’m using as a category for a calendar. For some reason Black won’t work? Every other color works except black? If I set the Result “Cancelled” As “Black” It turns blue and I don’t understand. If I tell it to be “Pink”, it works flawlessly.
My Color Column Expression is as follows:

IF([Type]=“Gig”,SWITCH(
[Status],
“Hold”, “Yellow”,
“Hold 2”, “Yellow”,
“Dead-Hold”, “Orange”,
“Confirmed (Need Details)”, “Red”,
“Confirmed”, “Red”,
“Cancelled”, “Black”,""),
SWITCH(
[Type],
“Request”,“Green”,
“Campaign”,“Blue”,"")
)

Any have thoughts?

Kirk_Masden

I’m sorry to say that I don’t know the answer to your question but I’d like to report on a related problem.

I wasn’t aware of the color column type so I went to the following page to learn more:

It has a link to a sample app called “Project Plan sample” with the following address but either the address of the app itself seems to be broken:

Kirk_Masden

I found another app to practice with:

https://www.appsheet.com/samples/An-app-for-a-delivery-driver-to-keep-track-of-deliveries-based-on-d...

I had no problem getting black to work in this app. I wonder if the difficulty might not with your SWITCH() expression. If you change the “Black” part to another color, does it work?

Or, perhaps there’s something unique about the context of a calendar that is causing some sort of problem.

jcadence

@Kirk_Masden thanks for finding the broken link and an alternate solution. I’ve updated the support article so future app creators can easily access the appropriate information.

Kirk_Masden

In regard to the Switch() expression, I’ve looked at

and

but am still having trouble understanding Switch(). It would seem to me that an IFS expression could do the same work. If anyone could help me understand it, I’d appreciate it.

Jojo_Stella

So SWITCH() is actually a variation of IFS (from what I understand).
I did try other colors in place of black and they worked flawlessly and as expected. Just for whatever reason black won’t work. Pink works, purple works. All as expected in the expression.

FURTHERMORE, in the actual forms when I fill them out, it shows the correct colors as well next to the color Column (INCLUDING BLACK).

I did think as another user mentioned in this post, that it’s possible there’s something unique about the Calendar that could make black not work. The Calendar is a newer feature, albeit not SO new… But new enough.

LeventK

@Kirk_Masden
@Jojo_Stella
SWITCH(…) and IFS(…) are two expressions that work slightly differently. Let’s look at the syntax:

With SWITCH(…), you basically evaluate a certain lookup value against some possible TRUE evaluation&result pairs and assign a default value if all your evaluation criteria resulted to FALSE.

SWITCH(
	LookUp_Value,
	"IF_TRUE_Value1", "Result1",
	"IF_TRUE_Value2", "Result2",
	.
	.
	.
	"Default_Value_IF_All_False"
)

With IFS(…), you basically evaluate multiple conditional statements and assign a TRUE value to each of one them.

IFS(
	"IF_Value1", "Result1",
	"IF_Value2", "Result2",
	.
	.
	.
)

The major difference with these 2 expressions is:
The SWITCH expression continues to evaluate all the inner statements till the end even-though any one of the inner statements evaluates to TRUE and does not stop its execution. On the contrary, the IFS expression stops execution with the first conditional statement evaluating to TRUE and the rest is not evaluated. If every conditional statement evaluates to FALSE, than IFS return an empty string or void response.

Kirk_Masden

Thanks so much! Super helpful. This was a gap in my knowledge about ordinary spreadsheets (Google sheets, etc.). I can see how SWITCH can be useful but your explanation also helped me see that it may not as efficient as IFS in some situations.
ありがとうございました！

LeventK

どういたしまして @Kirk_Masden
Provided you have only one condition to check which might have multiple outcome values, than SWITCH is more efficient. However provided you have multiple conditions to check where each of those conditions might have one or more outcome, than use of IFS is more efficient. For example:

SWITCH(
	USEREMAIL(), 
	"user1@mydomain.com", "UPDATES_ONLY", 
	"user2@mydomain.com", "ALL_CHANGES", 
	"READ_ONLY"
)

IFS(
	USEREMAIL()="user1@mydomain.com", "UPDATES_ONLY", 
	USEREMAIL()="user2@mydomain.com", "ALL_CHANGES", 
	AND(
		NOT(USEREMAIL()="user1@mydomain.com"),
		NOT(USEREMAIL()="user2@mydomain.com")
	),"READ_ONLY"
)

Both expressions do the same thing actually but regarding the use case in above example, SWITCH is more efficient to use and construct as well.

WillowMobileSys

@Kirk_Masden
Hey guys! While I recognize this is not related to @Jojo_Stella original post, I wanted to impart an important consideration when using IFS. You can, and in most cases SHOULD, implement a default.

Consider what would happen if NONE of the conditions in the IFS evaluated to true…the result is a blank value. This becomes difficult to debug - Did the expression even fire??

For this reason, I strongly recommend including a default value. To do so you would simply add a explicit true condition as the last expression, following the below pattern:

IFS( 
    "IF_Value1", "Result1",
    "IF_Value2", "Result2",
     .
     .
     . 
     true, "Default value"
 )

If there ever is a problem, you’ll know right away if it is because the expression fired or not or if the default was assigned when not expected that there was something wrong with the conditions themselves.

LeventK

@WillowMobileSystems
You are correct, you can include a default value provided you don’t want the IFS to return a void response with a TRUE parameter at the end.

Bahbus

I have a quick suggestion. Rather than pulling your hair out about it, I suggest leaving it as is (for now), and setting up a seperate format rule to do something else on the view. Perhaps format the text to be Strikethrough when “Cancelled”. Then when they fix the black with the calendar, you can just delete the format rule. Or keep it. But it should be visibly different enough, and provide at least some context.

LeventK

@Jojo_Stella
Unfortunately you are using the SWITCH() expression incorrectly. A SWITCH expression shall always have a default value as an end statement, where the expression will evaluate to the default value if all the inner-statements evaluate to FALSE. In your SWITCH statements, your default value is set as a blank value, where presumably you are trying to create a color value. With the color value, you cannot refer to a “blank” value. The correct expression shall be set like this:

IF(
	[Type]="Gig",
	SWITCH(
		[Status],
		"Hold", "Yellow",
		"Hold 2", "Yellow",
		"Dead-Hold", "Orange",
		"Confirmed (Need Details)", "Red",
		"Confirmed", "Red",
		"Cancelled", "Black",
		"White" //or whatever default color you want if all conditions are FALSE
	),
	SWITCH(
		[Type],
		"Request","Green",
		"Campaign","Blue",
		"White" //or whatever default color you want if all conditions are FALSE
	)
)

Jojo_Stella

I have a default value. The Default vaule is “”. As in nothing. None of the data entries/rows will use the last entry so the final default being “” is merely to complete the expression.

Jojo_Stella

In fact I even added a color as the default expression as you mentioned. I copied your expression right over, and it’s still doing the same thing… It’s very odd. lol

Jojo_Stella

Expression set to Black
!

Expression set to Purple
!

Expression/Code (Currently set to Purple):
!

LeventK

@Jojo_Stella
Assignment of a black color will return the same color as your set theme color.

Jojo_Stella

what do you mean by “Theme Color”? The default result?

What can I do to have the calendar show black? lol

appreciate all your time and knowledge!

LeventK

@Jojo_Stella
I mean this by “Theme color” where (in the example) it refers to the background color is Medium Blue and the text color is white

Jojo_Stella

INTERESTING! So Black will output the theme color of the second color in that palette (ex. theme is grey/ blue, but black will show blue)? Is there a reason for that? So there is no possible means to get black as a category color in calendar view?

Really enjoyed reading all of this. These little community lessons are great and go very far in regards to helping us new cats. Thanks @LeventK and @WillowMobileSystems for your explanations and knowledge.

LeventK

@Jojo_Stella
Learning is theoretically a never ending process throughout life. We are learning from each other and we develop and keen our skills whilst trying to solve or reply others questions, queries or issues. Welcome to the community

Bahbus

I would consider making a new Feature Request or Bug Report. Not sure which it fits better in, because either way “Black” should not default to a setting on the backend no matter what. Black should be black. “Default”, “Theme”, or some other keyword should be used to reference the theme setting.

pravse

Wrt the original question, seems like a bug, that’s all.

“Black” is not intended to mean “Default” or any other color.

@nico could you take a look please? Thanks

Kirk_Masden

Thanks @LeventK! So SWITCH() is similar to IFS() with the addition of the default value. Thanks! That helps me understand it.

Grant_Stead

Black isn’t a color…

Bahaha come on, that was funny!

Jojo_Stella

Ya got me hoss. Haha

nico

Black and White were missing from the theme colors, I’ve added them, this should behave as it should starting from tomorrow afternoon. Thanks for the report.

Note: white color is still effectively unusable because it will be white text on a white background but black should be fine. I’ll eventually fix the white color by changing the text color to black if demand is high enough.