6 Replies Latest reply: Jul 22, 2013 2:34 AM by MuthuKK RSS

    DEPENDENT LOOKUPS

    Nirav Dedhia
      I am workin with OIM 9.1.0.1.
      I have a process form with two lookup fields.
      1)Lookupfield name------>Country
      code decode
      India 1
      Australia 2

      2)Lookupfield name------>State
      code decode
      1 Maharashtra
      1 Delhi
      1 Mumbai
      2 Victoria
      2 Queensland

      I wanted state lookup to be dependent on the COUNTRY looup.
      ie)if user selects INDIA then only maharashtra,delhi,mumbai should appear when he/she double clicks on the State Lookup.

      For State lookupfiled on the form i had written a SQL query as follows:
      select lkv_decoded from lkv lkv,lku lku where lkv.lku_key=lku.lku_key and lku_type_string_key='Lookup.State' and lkv.lkv_encoded=(select lkv_decoded from lkv lkv,lku lku where lkv.lku_key=lku.lku_key and lku_type_string_key='Lookup.Country' and lkv.lkv_encoded = '$Form data.Country')

      But while saving the query I get error.
      INSERT FAILED
        • 1. Re: DEPENDENT LOOKUPS
          idamGod
          If the attribute name for COUNTRY in your process form is UD_FORM1_COUNTRY, then your sql query should be like below.

          select lkv_decoded from lkv lkv,lku lku where lkv.lku_key=lku.lku_key and lku_type_string_key='Lookup.State' and lkv.lkv_encoded=(select lkv_decoded from lkv lkv,lku lku
          where lkv.lku_key=lku.lku_key and lku_type_string_key='Lookup.Country' and lkv.lkv_encoded = '$Form data.UD_FORM1_COUNTRY$')
          • 2. Re: DEPENDENT LOOKUPS
            Rajiv Dewan
            METALINK ID *423425.1*
            • 3. Re: DEPENDENT LOOKUPS
              Nirav Dedhia
              Hi
              i changed my query and made the changes

              select lkv_decoded from lkv lkv,lku lku where lkv.lku_key=lku.lku_key and lku_type_string_key='Lookup.State' and lkv.lkv_encoded=(select lkv_decoded from lkv lkv,lku lku where lkv.lku_key=lku.lku_key and lku_type_string_key='Lookup.Country' and lkv.lkv_encoded = '$Form data.UD_FORM1_Country$')


              but still now i can save my query but values are not populated in the state lookup.
              i.e) if i select India as my country, and then when i double click on state field i get no values ,i get basically an empty table with no values.


              Regards,
              Nirav

              Edited by: 982433 on Mar 4, 2013 12:48 AM
              • 4. Re: DEPENDENT LOOKUPS
                Nirav Dedhia
                Hi Guys,

                Has anyone tried it on OIM 9.1.0.1 bcos its just not happening,i have visited all the links tried almost every solution.It does work for 11g.

                Please help...

                regards,
                nirav
                • 5. Re: DEPENDENT LOOKUPS
                  MuthuKK

                  Hi,

                  I am facing the same issue with OIM 9101. Lookup query works if I hardcode match criterion but not with $Form data.FIELD_NAME$.

                  I have the lookupfield in xlAdmin properties file as well.

                  Could you please provide more insight ?

                   

                  Many thanks,

                  MK

                  • 6. Re: DEPENDENT LOOKUPS
                    MuthuKK

                    Hi All,

                    Managed to fix the issue: Verified sql query in OIM DB and corrected lookup query accordingly