How to Check a Value from a Lookup table??

[former_member283645]

Hi Experts,

I have 2 lookup tables.

If a Value in Lk1 is selected, i need to check if that selected value is present in Lk2 table.

Ex:

MF table (Look up 1)

0001

0002

0003

0004

Ecc Table (Look up 2).

0001

0003

0004

0005

Note: User will not have access to Look up table 2.

So, When user selects any value in MF table (Look up 1) then the validation should check if that value is present in Look uptable 2 (Ecc table).

If present then true Else False.

Did someboday face a similar situation!!!

Can anyone help??

Kind Reagrds

Eva

[former_member207367 · Accepted solution]

Hi Eva,

I think we can not achieve your requirement through MDM Validations..... as i know there is no function for checking one lookup value data with another lookup table value data.

I have a question to your requirement...

What is MF table ? is it a table in one of your remote systems and you want synidcate the MF record to ECC before doing this do you want to check the lookup data exists in ECC or not?

regards

Sowseel

[former_member283645 · Accepted solution]

Hi,

My requirement is..

I have 2 Lookup tables.

1) MF table

2) Ecc table.

Both Lookup tables are Filled By individual Interfaces from Mf and Ecc.

So, Mf-> MDM interface will fill the MF table

Ecc-> MDM interface will fill the ECC table.

I have my main table with 2 lookup fields looking in to both the tables.

Now If user selects a Value from MF Lookup table then when i run a validation it should say whether that value i sPresent in ECC look up values List.

If the Validation is passed then i will send that record to ECC Else No.

Please Help..

Kind Regards

Eva

[former_member207367 · Accepted solution]

Hi Eva,

I see your requirement this way...

1) I will add two fields to your current Lookup table and link to one field in the main table, say the field name is LK

2) Create a Lookup Flat Multi Valued field in the main table which will be linked to a lookup table name systems which has different remote system names used in the landscape.By this field i will be identifying whether the record is for remote system MF or Remote System ECC or both

Say the your current Lookup table structure is

Code

Name

What i do is add two fields to the current structure

Code

Name

ECC

MF

And the sample data will be like this

Code Name ECC MF

0001 xx Yes Yes

0002 xx No Yes

0003 xx Yes Yes

0004 xx Yes Yes

0005 xx Yes No

By this way i can easily validate the record which is selected in the main table exists in ECC and MF Systems based on the systems field in the main table.

Validaiton Code for checking the record exists in ECC

If( HAS_ANY_VALUES(systems,ECC)=TRUE,LK1.ECC=Yes)

Validaiton Code for checking the record exists in MF

If( HAS_ANY_VALUES(systems,MF)=TRUE,LK1.MF=Yes)

Regards

Sowseel

[former_member283645 · Accepted solution]

I am not sure if you understand my requirement.

If the User selects a Value from MF Lookup table from main table, Then the validation Should check IF that Value is available in the list of Lookup Values of ECC Lookup table.

If yes then True, Else False.

Kind Regards

Eva

[former_member207367 · Accepted solution]

Hi Eva,

I am not sure of such validations like validating a lookup value from another lookup table value.... can be achived through MDM Data manager....

But the solution which i preposed in my last post is an workaround for your requirement i feel.. Lets wait if some one replies with better solution.

Regards

Sowseel.

[former_member283645 · Accepted solution]

Thanks for the Reply.

The work around that you mentioned , If i understand Correctly..

User need to Select a value from Systems Lookup table!!!

But we want the MDM to validate based on User selection of Mainframe Lookup table.

Did i understand Right!!!

Can You please Let me know how and in what sequence your validations Work/React.

Kind Regards

Eva

[former_member207367 · Accepted solution]

Hi Eva,

User needs to select the system and the Main Frame lookup field.

A question to you again , How do you identify a record in your MDM System whether it is an Main Frame record or ECC Record? Do you have any identifier for it ?

System field in my scenario holds the identification like a remote system in the Key mapping section..

Based on the selected System value and the customized Lookup table value we will validate whether the lookup value exists in corresponding remote system or not (based on Yes/No values in the lookup table).

sample Lookup table data :

Code Name ECC MF

0001 xx Yes Yes

0002 xx No Yes

0003 xx Yes Yes

0004 xx Yes Yes

0005 xx Yes No

Example:

User selects System as MF and MainFrame lookup field value as 0005

As per the Lookup table data 0005 does not exist in MF and it is marked No so the validation will trigger and wont allow the record to save ...

If( HAS_ANY_VALUES(systems,MF)=TRUE,LK1.MF=Yes)

Where as the user selects the System MF and Main Frame lookup field as 0004, then validation will not trigger as the record will be saved.

Let me know if any questions.

Regards

Sowseel

Regards

Sowseel

Edited by: sowseel pasumarthi on Aug 8, 2011 5:13 PM

[Former Member · Accepted solution]

Hi Eva,

Looking at problem from 2 different angles-

1.Can you create only 1 lookup with both values and may be flags for each system,pls check the feasibility.

2.I guess PI can handle this better,let me know your finding on these.

Thanks,

Ravi

[former_member283645 · Accepted solution]

Hi,

So You mean to say..

There will be One lookup table called Systems.

which has ECC and MF values (Only 2 drop down values.)

My MF lookup table.

Filled By MF interface, will have Codes in it.

Ex: 0001, 0002, 0003 etc..

Now i need to extend this Table with fields like ECC and MF (Marked in Bold Below

Code Name ECC MF

0001 xx Yes Yes

0002 xx No Yes

0003 xx Yes Yes

0004 xx Yes Yes

0005 xx Yes No

Now, Firstly When the MF interface is Filling the Code and Name field How will it knows to Fill the ECC and MF fields in this Table??

I can put Yes for all MF field here But how will it know whether this value is present in ECC ref table??

Also user will be keying in DM/Portal, So there is nothing like Record Belongs to ECC or MF, Its a Materail record thats it.

Ref values to create this material is filled by MF and ECC.

Can you please Suggest..

@ Ravi

There Must be 2 different Lookup values.

1 will be filled by ongoing interface from MF

and 1 will be filled by Ongoiong interface from ECC.

Kinf Regards

Eva

[former_member207367 · Accepted solution]

Hi Eva,

For just validating the Lookup table record you can use this way

Lookup table Structure

Code

Name

ECC

First interface MF->MDM will run and creates the code and Name which exists in MF

Second interface ECC->MDM :

In this Map add a new field ECC and Set to Yes in the value mapping under converted value as YES and map that field to ECC of the look up table with your ECC code and Name

In matching startegy take code as a matching value and in the import action

Skip the creates

Update All Mapped Fields for existing recrds

By this way your lookup table will have only MF data with ECC Status Yes or Blank

sample data

Code Name ECC

0001 XXX Yes

0002 XXX

Then you can use the below validation

*LK1.ECC=Yes*

for validating the lookup table record exists in ECC or not.

LK1 is the main table field which is used by the user for selecting the lookup values of MF

regards

Sowseel

[former_member207367 · Accepted solution]

Hi Eva,

For just validating the Lookup table record you can use this way

Lookup table Structure

Code

Name

ECC

First interface MF->MDM will run and creates the code and Name which exists in MF

Second interface ECC->MDM :

In this Map add a new field ECC and Set to Yes in the value mapping under converted value as YES and map that field to ECC of the look up table with your ECC code and Name

In matching startegy take code as a matching value and in the import action

Skip the creates

Update All Mapped Fields for existing recrds

By this way your lookup table will have only MF data with ECC Status Yes or Blank

sample data

Code Name ECC

0001 XXX Yes

0002 XXX

Then you can use the below validation

*LK1.ECC=Yes*

for validating the lookup table record exists in ECC or not.

LK1 is the main table field which is used by the user for selecting the lookup values of MF

regards

Sowseel

[former_member283645 · Accepted solution]

Hi Sowseel,

Thanks for the reply.

If i understand you correctly.

MF Lookup- > will have 2 fields, Name and Code which are filled By ongoing MF to MDM interface.

ECC Lookup -> will have 3 fields, Name,Code and ECC , Name and code will be populated by ECC system and ECC Field is populated by default value as Yes.

By doing this way , when the ECC --> MDM interface is active, the ECCref table will be filled with ..

Name Code ECC

-

---

-

---

-

---

xyz 001 YES

GHJ 002 YES

So, You mean to say the ECC ref table will be updated with MF codes with setting as Skip on create (So that something which is not in ECC table is Created from MF) and Update All Mapped Fields (So that Ecc field will be updated With Yes if MF Reference Value has the same Value).

Now the ECC table has the Codes and Indicator says that YEs it is available in MF also and Blank which means it is not available in Mainframe.

When my user selects a Code from the MF Lookup table How will we write the validation like MF lookup .[Record] = ECC (Yes) in ECC reference table !!

Firstly is it possible to write it??

Kind Regards

Eva

[former_member207367 · Accepted solution]

Hi Eva,

Let me explain this way...

Step 1: MF Table will be updated with MF->MDM Data interface - MF Table Codes will be created in the MF table

Step 2 : Same MF Table will be updated with ECC->MDM interface data by Just updating all mapped fields and skipping the creates by this ECC Value will be updated to the MF Table records (ECC Field which has the value YES can be added as a new field in the import map or you can get the same from the interface from ECC as a constant)

Step3 : ECC Table will be updated by the ECC-MDM interface by mapping code and name against the ECC table - ECC Table data also gets created

With the above activities in the MF table we got the MF codes relationship with ECC by Yes or blank

Now in the main table under Validations create a validation and in the expression editor select your Main table Field which is linked to the look up table MF table .

say LK1 is your main table field wihch is linked to look up table MF Table.

When you click on the Fields of the validation expression editor you will be able to see LK1 in your fields section with an arrow when you move the cursor on the arrow it will give you the MF lookup Table Field names and also Record, there select the field ECC and equate that to YES

LK1.ECC = YES

regards

sowseel

[former_member283645 · Accepted solution]

Thanks for the Detiailed Explanation.

That Helped.

After the LK1 lookup table (Which is mf data table) is filled with ECC indicator values.

when i write a validation ..

In My LK1 table the Code is the Display field.

So user will have an option of viewing the Codes in the Drop down list.

So, ECC field is Invisible for the user in the Drop down list.

When User select a Value from the drop down list we need to chk if that value has YES in ECC column.

In validation Editor..

LK1.[Record] = LK1.ECC = YES

How do we write this ??

Currently the System is Unavaialbale but will this work!!!

Kind Regards

Eva

[former_member207367 · Accepted solution]

Hi Eva,

User will not be having access to select the ECC field but under the expression editor of validation you can see the field...

In the last post of mine given in detail how to select the field in expression editor... And use the same code in the last post it works... Please give a try and let know the results

Regards

Sowseel

[former_member283645 · Accepted solution]

Hi,

Thanks soweel.

That Worked.

Thanks again for your help.

Kind Regards

Eva

[former_member283645]

Thanks

[Former Member]

HI Eva,

Let me confirm my understanding,you have 2 lookup tables and user selects one value for one of the lookups and that value is needs to be searched and checked for in the other lookup.

So you try to achieve this using a validation comparing the values of 2 values simply by equating the two.

But in case you want to validate this that other value is in 1st lookup or not then you can equate value of 1stlookup.record to FIND(Secondlookup.record).

Let me know if it works.

Thanks,

Ravi