18 Replies Latest reply: Aug 11, 2011 3:13 PM by eva majarova RSS

How to Check a Value from a Lookup table??

eva majarova
Currently Being Moderated

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

  • Re: How to Check a Value from a Lookup table??
    Ravi Verma
    Currently Being Moderated

    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

  • Re: How to Check a Value from a Lookup table??
    Sowseel P
    Currently Being Moderated

    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

    • Re: How to Check a Value from a Lookup table??
      eva majarova
      Currently Being Moderated

      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

      • Re: How to Check a Value from a Lookup table??
        Sowseel P
        Currently Being Moderated

        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

        • Re: How to Check a Value from a Lookup table??
          eva majarova
          Currently Being Moderated

          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

          • Re: How to Check a Value from a Lookup table??
            Sowseel P
            Currently Being Moderated

            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.

            • Re: How to Check a Value from a Lookup table??
              eva majarova
              Currently Being Moderated

              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

              • Re: How to Check a Value from a Lookup table??
                Sowseel P
                Currently Being Moderated

                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

                • Re: How to Check a Value from a Lookup table??
                  eva majarova
                  Currently Being Moderated

                  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

                  • Re: How to Check a Value from a Lookup table??
                    Sowseel P
                    Currently Being Moderated

                    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

                    • Re: How to Check a Value from a Lookup table??
                      eva majarova
                      Currently Being Moderated

                      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

                      • Re: How to Check a Value from a Lookup table??
                        Sowseel P
                        Currently Being Moderated

                        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

                        • Re: How to Check a Value from a Lookup table??
                          eva majarova
                          Currently Being Moderated

                          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

                  • Re: How to Check a Value from a Lookup table??
                    Sowseel P
                    Currently Being Moderated

                    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

              • Re: How to Check a Value from a Lookup table??
                Ravi Verma
                Currently Being Moderated

                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

Actions