Black not working as a color

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?

1 Like

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:

1 Like

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-delivery-status-?appGuidString=61936e30-43a1-40ac-8fa6-ce3d3e11c4e3

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.

2 Likes

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.

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.

1 Like

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.

1 Like

@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
	)
)
2 Likes

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.

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

Expression set to Black
as%20black!

Expression set to Purple
as%20purple!

Expression/Code (Currently set to Purple):
codesnip!

Black isn’t a color…

Bahaha come on, that was funny!

2 Likes

Ya got me hoss. Haha

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

@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.

3 Likes

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.
ありがとうございました!

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

1 Like

どういたしまして @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.

2 Likes

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!

@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.

3 Likes

@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.