search cancel

Parameterized lookup value is updated incorrectly via XOG when lookup query is missing @[email protected]


Article ID: 3953


Updated On:


Clarity PPM SaaS Clarity PPM On Premise


A parameterized lookup value is xogging in incorrectly. It always defaults to one value irrespective of the value in the XML for the XOG.

Steps to Reproduce:

  1. Create 2 static lookups:
    • LookupStc_FUNCTION

      Value     ID
       aaa     111
       bbb     222
    • LookupStc_SUBFUNCTION

      Value     ID
       www   111001
       xxx     111002
       yyy     222001
       zzz     222002
  2. Create a Dynamic Query lookup: Lookup_PARA
  3. Open the Benefit Plan Detail object
  4. Create 3 attributes:
    • MYFUNCTION. Associate it to LookupStc_FUNCTION
    • MYSUBFUNC. Associate it toLookupStc_SUBFUNCTION
    • MYPARA. Associated it to Lookup_PARA  (Parameterized)
    • Map the Lookup Parameter with Object Attribute ID=MYFUNCTION
  5. Add the attributes to the views
  6. Open a Project and Create a Benefit plan.
  7. In the Benefit Plan Detail enter:
    •  MYFUNCTION  = aaa (this is value ID 111)
    • MYPARA= xxx (this is value ID 111002)
  8. XOG out the Benefit plan. Output file shows:

            <Detail detailName="Detal1">
                <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                <ColumnValue name="myfunction">111</ColumnValue>
                <ColumnValue name="mypara">111002</ColumnValue>

  9. Modify the file with different values and use it as the input file:
    • bbb (this is value ID 222)
    • yyy (this is value ID 222001)
  10. XOG the file into Clarity
    Results: Output file successful: 1 record updated
  11. Open the project and check Benefit Plan Detail:
    •  MYFUNCTION  = bbb (this is value ID 222) -> correct
    • MYPARA= www (this is value ID 111001) -> Incorrect. This is not the value specified in the Xog input file. 

Expected Result: Parameterized lookup value should be updated with the value specified in the XOG input file.

Actual Result: Parameterized lookup value is not updated with the value specified in the XOG input file.

This issue is seen both in Classic PPM and Clarity (Modern User Experience (UX))


Release: All


This is working as designed. This is due to the where clause missing @[email protected], so the query is not able to apply the filter on the lookup value provided. Note: Te Query tab in Administration --> Data Administration --> Lookups mentions this:
"The WHERE clause must contain "@[email protected]" to work properly. To create a dependent lookup, enclose the lookup internal name in "@" symbols."

The problem is in the lookup definition ( dynamic NSQL in SubFunction_Parameterised(DYnamic).xml )

Example of a query missing this clause:

  @SELECT:lkp.lookup_enum:[email protected]
,[email protected]
FROM cmn_lookups lkp
INNER JOIN cmn_captions_nls nls ON nls.table_name = 'CMN_LOOKUPS' AND nls.pk_id =
WHERE lookup_type = 'CSC_SUB_FUNC_STATIC' AND lkp.is_active = 1
      AND nls.language_code = @WHERE:PARAM:[email protected]
      AND (@WHERE:PARAM:USER_DEF:INTEGER:[email protected] = LPAD(lkp.lookup_code,3) OR
          @WHERE:PARAM:USER_DEF:INTEGER:[email protected] IS NULL OR
          @WHERE:PARAM:USER_DEF:INTEGER:[email protected] = '')


Add AND @[email protected] in the NSQL WHERE clause of your dynamic lookup definition