Validation expression with lookup field

[former_member1052991]

Hi all,

My issue is this, i want to make sure that my field in a main table should contain entries in a lookup table.

I tried with the following expression

FIELD1 = LOOKUPTABLE.FIELD1 (where FIELD1 is maintable field and LOOKUPTABLE.FIELD1 is display field in lookup table).

But its not working, plz help me to solve this issue.

Thanks,

Guna

[Former Member · Accepted solution]

Hi Guna,

from my understanding, this is not possible and not necessary. You need to create your main table field as lookup(flat) instead of text. Then the values are automatically restricted to only the plant master table values. There is not need to create a validation anymore.

Best regards

Christian

[former_member1052991 · Accepted solution]

Hi Christian,

Thanks for your reply.

Yes, as you said we can do that, but this is my client requirement.

Are your sure this is not possible in MDM?..

Could you plz confirm it?

Thanks,

Guna

[Former Member · Accepted solution]

Hi Guna,

yes, this is not possible with standard functionality, maybe with the use of the API in the portal.

I would strongly question the requirement, because this is exactly the intended use of lookup tables, i.e. to validate the content of a field by providing a value list. Is there a reason for this requirement?

Best regards

Christian

[former_member1052991 · Accepted solution]

Hi Christian,

As per our client requirement, validation against the existing table entries(like check table validation).

If we specify that field as lookup, it wont allow you to enter the data which is not in lookup table right?

Thanks,

Guna

[Former Member · Accepted solution]

Hi Guna,

Yes, If you specify a field of type look up flat, it wont allow you to enter the data which is not in look-up table. However, if you import data through Import Server, make sure that you have set properties Automap Unmapped Value = No and Unmapped Value Handling = Fail . So even when you import data for main table using Import Server, it wont allow you to add values if that values does not exist in our look-up table.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Guna,

that is correct. From my understanding, your client wants to check against a set of possible values but at the same time add values whenever needed. This seems a bit contradictory to me and will not foster data quality. Usually, if a new value is required, it should be created in the lookup table. If you maintain the data somewhere else, but only import into MDM, then you can add values to lookup tables through import server as mentioned above (just set to Add missing values). If you maintain the values in MDM directly, then maybe consider my above points.

This is only my opinion without more detailed understanding. You may want to challenge the requirement depending on your scenario.

Best regards

Christian

[former_member1052991 · Accepted solution]

Hi Christian,

Thanks a lot for reply again...

No you are wrong, sorry if i confused you..

ultimate aim for this requirement is whenever the data is going to be load in to MDM..

they want to do validation against existing table.. Say for example.. if my lookup table has plant value as 1000 and 2000.

If I load 3000, it needs to tell that 3000 does not exist in MDM.. it should not be created in lookup as you said..

I hope now you are clear about my requirement..

Thanks,

Guna..

[former_member1052991 · Accepted solution]

Hi all,

It seems that it is not possible to validate value with table.

So i am closing this thread......

[Former Member · Accepted solution]

Hi,

Say for example.. if my lookup table has plant value as 1000 and 2000.

If I load 3000, it needs to tell that 3000 does not exist in MDM.. it should not be created in lookup as you said..

See, if you are using middleware PI/XI, then you can achieve this requirement where you can validate the transactional data against Master data before sending it.

So in this way we can come to know that data is not valid according to requirements and thus can reject this message or raise some alerts etc very early in processing cycle.

For complete details, please refer below blog:

/people/raj.sharma5/blog/2009/03/09/lookup-in-to-the-mdm-from-sap-pi

Regards,

Mandeep Saini