At-Most Validation for Qualified Records

[Former Member]

Dear Experts,

We have a qualified table (Product Map) associated to the Main Table (Product). There is a qualifier field "Y/N" which is a lookup-flat field to another flat table which stores the values Y & N.

Hence the values the Y/N field may have is Y, N or blank (no value).

We need to create a validation to check that only one of the qualified records in the Product Map has the Y/N field value as Y.

I can check if At-Least one record has Y value. If it can also be checked somehow that At-Most one record has Y value, the requirement can be fulfilled.

Please let me know if you have encountered this issue and how was it solved.

We are currently using SAP MDM 5.5 SP06 Patch 05 (5.5.65.98)

Thanks a lot in Advance.

Regards,

Uday

[Former Member · Accepted solution]

Hi Uday,

If you can modify yr lookup table that contain Y and N values then there is one work around.

Add one more non display field in that table of type integer and assign 1 to Y values and other values assign 0.

Now in validation check

if(SUM(lookupfieldofmaintable.lookupfiledinqualifiedtablethatcontainYandN.newfieldthatyouaddedinlookuptable)>1,false,true)

Its working fine at my end please try if modification of table is not an issue.

Thanks,

Sudhanshu

Edited by: Sudhanshu Agarwal on Jun 30, 2010 6:52 PM

[Former Member]

Hi Uday,

can u plz update abt yr status.Is yr prob solved or not.

Thanks,

Sudhanshu

[Former Member]

Hi Sudhanshu,

Thanks a lot for the workaround you have provided. I have tried this and it is working fine.

We are just considering if we should go ahead and change the schema. I do not see any real problem though.

Best Regards,

Uday

[Former Member]

Hi Uday,

As per my understanding your validation will work for only one qualified link to check whether that Qualifier has Y or N. So if your 1st Qualified link has Y then it will give success else will fail. As your requirement is to achieve check Y or N if any of the qualified link has Y, this would not be possible as Validation does not check Y/N for 2nd, 3rd or so-on Qualified link. So you will always get result on the based of 1st qualified link which will not solve your Purpose.

I can suggest you a workaround for this, this is working fine at my end

1stly, for this you need to create a one more new field of type Text say CheckQualifierValidation and its value you will populate using Assignment (Expression as XXX.DQ.Name )

where XXX = field in main table which is look up to Qualified table

DQ.Name= Desired Qualifier field which has value either as Y or N. I write it here. DQ. Name not DQ because as you said it is a Qualifer field which is lookup to Flat table.

Now after Populating this field with Assignment Expression you will have values like this (e.g. in case of 3 qualified links, 1 qualified link has value N, 2nd Qualified link has value Y and suppose third is blank has no value)

your new field CheckQualifierValidation will populated like this: 2:N;Y;

Now you have to run validation on this CheckQualifierValidation field (Expression as FIND(CheckQualifierValidation, "Y;") ) which will return Success if it has Y else will fail. This will give you desired result as you said and wanted check if At-Least one record has Y value. If it can also be checked somehow that At-Most one record has Y value, the requirement can be fulfilled..

Note: For testing this scenario if you have any empty field available just check with that field before creating new field

You can also do it through portal which obviously need effort and time to create custom application.

Regards,

Mandeep Saini

[Former Member]

Hi Uday

Unfortunately this is not possible in current version of MDM you are using.

Validations are not supported for Qualified tables fully.

If you want to check for Atmost 1 occurence of a qualifier value this can be done using custom coding in the portal UI.

Even promoting the Qualifier as NQ will also meet the requirement as again MDM does not support uniqueness for NQ at record level.

Best regards-Ravi