Hi all

 

One of my use case is to monitor a number of fields being left empty at the sales order creation. In order to keep track of these issues and make sure the root cause are being resolved permanently, I want to create a "status" for each item (in the example below, we are monitoring the Plant field).

 

Status:

1. NOK = plant field is missing
2. OK (fixed) = plant field was changed from NULL to any value
3. OK = all the other cases

 

it is the second option 'OK (fixed)' that is causing me some headaches and I would appreciate your help. I tried several options but for now, none of them are working.

 

Option 1:

CASE

WHEN "VBAP"."WERKS" IS NULL THEN 'NOK'

WHEN ("_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN ('Change Plant')

AND "_CEL_O2C_ACTIVITIES"."CHANGED_FROM" IS NULL)

THEN 'OK (fixed)'

ELSE 'OK' END

 

the result of this option gives me a table that will display 2 rows for one item, one with the 'status OK' and the other with the status 'OK (fixed)', instead of one row per item with the 'OK (fixed)' status.

 

Option 2:

CASE

WHEN "VBAP"."WERKS" IS NULL THEN 'NOK'

WHEN (MATCH_ACTIVITIES ("_CEL_O2C_ACTIVITIES"."ACTIVITY_EN", NODE 'Change Plant' ]) = 1

AND MATCH_ACTIVITIES ( "_CEL_O2C_ACTIVITIES"."CHANGED_FROM", NODE Â''] ) = 1)

THEN 'OK (fixed)'

ELSE 'OK' END

 

the result of this option : the status will only show OK or NOK. I am not quite sure we cannot capture NULL values with the Match_Activities formula, probably the reason it is not giving me the right outcome.

 

 

Any clue on how to handle that ?

thanks