Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a list of items with different fields displaying their informations
I have an issue when I want to color a cell depending on their status (Straight Table)
Status are: ACTIVE, OBSOLETE, BOMOBS (basically it says if it is usable, not usable or obsolete to put in a nomenclature)
ITEM NAME (dimension) ITEM CODE (dimension) STATUS (expression)
Wire 23x24 W12234 ACTIVE
Wire 23x25 W12235 OBSOLETE
Wire 25x26 W12236 ?
In this example, Wire 25x26 has 2 status: OBSOLETE and BOMOBS
I would like to display this item in red
=if(STATUS = 'ACTIVE' , Green()),
if(STATUS = 'OBSOLETE' , Red(),
if(STATUS = 'BOMOBS', Red(),
Gray()))
If I add the status as a dimension:
ITEM NAME (dimension) STATUS (dimension) ITEM CODE (dimension) STATUS (expression)
Wire 23x24 ACTIVE W12234 ACTIVE
Wire 23x25 OBSOLETE W12235 OBSOLETE
Wire 25x26 OBSOLETE W12236 OBSOLETE
Wire 25x26 BOMOBS W12236 BOMOBS
Obviously I don't want 2 lines for ITEMS that have both status
I tried this
=if(STATUS = 'ACTIVE' , Green()),
if(STATUS = 'OBSOLETE' or STATUS = 'BOMOBS', Red(),
Gray())
Now working...
Any idea how I could flag item with these 2 status so it display in red ?
Thank you !
if you use STATUS as Dimension it will Display two lines
if your desired Output is like the first table I would try
concat(STATUS,';') as expression
fo rthe colour you Need something like if match(STATUS,'OBSOLETE','BOMOBS'),Red())
This is, at its core, a data consistency issue - an item shouldn't have more than one status. I'm assuming this is what you're presented with, though, so let's work with that.
Probably the easiest approach given you only have three statuses is to use CONCAT() in a simple IF statement.
If(WildMatch(STATUS,'*ACTIVE*'),Green(), Red())
This will color the text green if the status list includes "ACTIVE", and will color it red in any other case.
Note that this will still not display anything in the "STATUS" field in your table - there's more than one value, so you will have to either split it into two rows or aggregate it (e.g. by using CONCAT()) in order to display something.
Hello Fabien,
PFA.
1) Added Item Code and Item Name in Dimension List
2) Added =Concat(STATUS ,' ') in Expression
Let me know if it works for you.
Thanks all for your input !
I like the wildmatch option for color but in my case, as I forgot to mention, if the item has no status, it should be grey
So using
If(WildMatch(STATUS,'*ACTIVE*'),Green(), Red())
Items without status will be in red instead of grey
Is there any possibility to use if(concat(STATUS,'OBSOLETE' or 'BOMOBS'),red())) ?
Thanks again
should be Expression like
If(WildMatch(STATUS,'*ACTIVE*'),
Green(),
if (wildmatch(concat(STATUS,'OBSOLETE', 'BOMOBS'), Red(), Grey())
hello
did you try
pick(WildMatch(STATUS,'*ACTIVE*')+1,grey(),Green(), Red())
?
Hi
This one does not work
When I have Items with multiple status, it does not color the text at all
Hi
Sadly, this expression does not work
Maybe like
Item | Concat(Status,',') | =Pick(Max(WildMatch(Status,'ACTIVE','OBSOLETE','BOMOBS'))+1,LightGray(),LightGreen(),Lightred(),Lightred()) |
---|---|---|
ACTIVE,BOMOBS,OBSOLETE,OBSOLETE,unknown | RGB(255,0,0) | |
Wire 23x24 | ACTIVE | RGB(0,255,0) |
Wire 23x25 | OBSOLETE | RGB(255,0,0) |
Wire 25x26 | BOMOBS,OBSOLETE | RGB(255,0,0) |
Wire 28x2 | unknown | RGB(192,192,192) |