Need to flag Item with 2 status

[fgirardin]

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 !

[Anonymous]

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())

[Or]

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. 

[gawalimegha]

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.

[fgirardin]

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

[Anonymous]

should be Expression like

If(WildMatch(STATUS,'*ACTIVE*'),

Green(),

  if (wildmatch(concat(STATUS,'OBSOLETE', 'BOMOBS'), Red(), Grey())

[olivierrobin]

hello

did you try

pick(WildMatch(STATUS,'*ACTIVE*')+1,grey(),Green(), Red())

?

[fgirardin]

Hi

This one does not work

When I have Items with multiple status, it does not color the text at all

[fgirardin]

Hi

Sadly, this expression does not work

[swuehl]

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)