validating non-qualifiers inside qualified table

[Former Member]

HI ,

we need to implement a validation for non-qualifiers in a qualified table.

The situation is the following - we have Qualified table "Additional units of measure" and we have as qualifier "Unit" (it can be KG, EA, PC, etc) and we have non-qualified field "EAN number" which can be assigned for each qualifier (Unit).

Now we need to make sure that no duplicat EANs are added for the same material - e.g. if we enter EAN number "123456" for unit KG, not to be able to enter the same EAN for different unit (e.g. PC).

Does anybody know how can we implement such a validation? The option unique field in the console for the EAN is not active as this is a lookup flat table against lookup table storing all EAN numbers.

Thanks&Regards,

ILIN

[Former Member · Accepted solution]

Hi LLin,

Firstly i dont think this is possible by making all non qualifier filed unique beacuse if you make it unique then it wont allow duplicates but allow different qualifiers for same non qualifier because they are mulivalued.

secondly why unique property is disabled .In my case it is enabled .

Thanks,

Sudhanshu

[Former Member · Accepted solution]

Hi Sudhanshu,

sorry i swapped the non-qualifiers with qualifiers in my post. I meant that the non qualifier is "Unit", but the EAN number is a qualifier and unique field is not active for the qualifiers, but only for non-qualifiers. Even if was active it wouldn't help us, as we have also additional condition - the EAN can be duplicated only if the conversion (qualifier fields: Numerator and Denominator) between different alternative units of measure (unit) is 1:1. In the cases when it is different from 1:1 the EAN number should not be duplicated.

I was thinking of many scenaris, but I couldn't find way to match for each non-qualifier (unit) what is its Numerator, Denominator and EAN number (qualifiers), and also how to check after that if this EAN is repeated for different unit(non-qualifier).

It looks like mission impossible

Kind Regards,

ILIN

[Former Member · Accepted solution]

Hi Ilin

This can be achieved via Java code, if you are using Portal as UI for capturing data.

Impossible to achieve this requirement in MDM client, as rightly mentioned by you.

thanks-Ravi

[Former Member · Accepted solution]

Hi IIin,

It looks like mission impossible

I don't think so, There is a workaround to achieve this thing in MDM. For this you need to change your Repository schema.

Create a new field of type Text in Main table say Check EAN

Create a new Qualifier field say Duplicate EAN in Qualified table of type Text/Integer which should be Calculated = Yes and Calculation Expression as given below using MDM Console:

IF (NUMERATOR=1 AND DENOMINATOR =1, u201C u201C, EAN NUMBER)

So your added new Qualifier field populated only if it meets the above condition. I mean populated Duplicate EAN only

when it is different from 1:1, for 1:1 it will populate blank(Empty value as per expression) " "

Scenario

Material(Main table field) Unit(NQ) NUM(Q) DEN(Q) EAN(Q) Duplicate EAN(Q)

1 EU 1 1 12345

1 MU 1 2 3456 3456

1 KU 1 1 678

2 EU 1 2 456 456

2 MU 1 1 12

2 KU 1 1 34

2 LU 3 1 456 456

So as per above example you have 2 material 1 and 2 and Material 1 has 3 Qualified links and Material 2 has 4 qualified links.

Now create an Assignment to populate newly added field in main table Check EAN as per expression given below:

XXX.Duplicate EAN

Where XXX is a field in main table which is look up to your qualified table.

After assignment your field for material 1 and material 2 will be populated as:

Material Check EAN

1 1:3456;

2 2:456;456;

Now you can run a validation on this field in main table to find out Duplicate values: Validation expression as:

IF (LEFT (Check EAN, 1) =2, MID(Check EAN, 3, FIND(Check EAN, u201C;u201D)-3)

= MID(Check EAN, FIND(Check EAN, u201C;u201D)+1),

FIND(Check EAN, u201C;u201D,FIND(Check EAN, u201C;u201D)+1)- FIND(Check EAN, u201C;u201D)-1)

So after executing this validation for material 1 and material 2 , you will come to know that Material 2 has duplicate records.

In field Check EAN, For Ist character = 2 , I mean for above expression It takes lot of time for me to write this expression.

Similarly for if 1st character = 3, one can write expression for it also, I know this would be brain storming to think and write expression for 3 but I think we can write it.

Note: I haven' t tried this solution at my end just strike in my mind so thought of sharing this workaround with you.

I hope it should work

Otherwise only option left using code JAVA APIs

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Dear Mandeep,

Thank you for your help and time. I tryed to implement it, but unfortunatelly the first thing which is bothering me is that according to your example:

Material(Main table field) Unit(NQ) NUM(Q) DEN(Q) EAN(Q) Duplicate EAN(Q)

1 EU 1 1 12345

1 MU 1 2 3456 3456

1 KU 1 1 678

2 EU 1 2 456 456

2 MU 1 1 12

2 KU 1 1 34

2 LU 3 1 456 456

After assignment your field for material 1 and material 2 is populated as:

Material Check EAN

1 3:;3456;;

2 4:456;;;456;

Instead:

1 1:3456;

2 2:456;456;

So it counts even the blank onse qualifiers. Maybe this is because Duplicate EAN is a calculated field. In such a case the validation will not work. Also we have so far up to 9 qualifier links, so the validation should cover all cases from 2 up to 9, which will be BIG brain storming

It looks like it should be done only with Java API, which we do not like much.

Thanks again for your help - really appreciated!

Kind Regards,

ILIN

[Former Member · Accepted solution]

Hi Ilin

We have implemented similar scenarios using Java code within the portal UI. It works very fine and independent of MDM validations.

Overall for data governance the validations can be grouped into multiple categories based on their implementation-

1. validations built on MDM repository.

2. Complex validations not supported by MDM in Portal UI

3. RFC calls for replicating existing validations from ECC.

in your case it makes sense to achieve the requirement via Portal code.

Please close thread if answered.

Thanks-Ravi

[Former Member · Accepted solution]

Hi Ilin,

As i suggested above in my post that if it does not work then only option left is using JAVA APIs.

So it counts even the blank onse qualifiers. Maybe this is because Duplicate EAN is a calculated field. In such a case the validation will not work.

As you are getting value as below:

Material Check EAN

1 3:;3456;;

2 4:456;;;456;

Here i mean its work well for case 2, if we can check that our 3rd character is Integer or not.

It will work for 2:456;456; , 2:;456; , 2:456;;, 2:456;678;

IF (LEFT (Check EAN, 1) = 2 AND HAS_ALL_CHARS(MID(Check EAN, 3,1),"0","9")

MID(Check EAN, 3, FIND(Check EAN, u201C;u201D)-3)

= MID(Check EAN, FIND(Check EAN, u201C;u201D)+1),

FIND(Check EAN, u201C;u201D,FIND(Check EAN, u201C;u201D)+1)- FIND(Check EAN, u201C;u201D)-1)

Similarly we need to think for all other cases too.

So, I only see drawback of creating validation for all cases from 2 to 9 which really need lot of thinking and may be possible that we can achieve it or not. I am not sure about this. So I think its good to go with JAVA APIs with Portal UI.

Hope it helps..

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Thank you, all for your help on this topic.

I also consider that the right approach for this is using Java API.

Really appreciate your input.

Kind Regards,

ILIN