AnsweredAssumed Answered

Lookup from Secondary Database

Question asked by e_martin on Mar 24, 2016
Latest reply on Mar 24, 2016 by nick_darlington

We're on premise, CA PPM 13.3

 

I'm trying to make a lookup that's similar to Resource Browse but using a table we use on our Integration Bridge Database, as it has all Resources in our company, not just the ones that use CA PPM.

 

I'm able to use the drop down when creating the NSQL Query to change from our NIKU schema to the iBRIDGE schema, however it doesn't seem to function the same.

 

Normally, I would query and return the various different variables, including the ID and would be able to configure the Parent Window tab in the Lookup Studio pages to set the hidden key to the ID (so it stores that on the database) and then the display attribute I can select a different field form the NSQL that it will populate on the page.

 

Once I've done this and created the attribute on the custom object with the new lookup, the look up works fine, however when I use it, it doesn't listen to the configuration. When I lookup I can see the full name for the resource (like the Display Attribute selected) but when I save, it then changes the Displayed value to what's selected as the Hidden Key. So it changes from their First and Last Name, to the ID number. Which is fine at the database level, however visually, it becomes confusing for the user to see the person represented by a random database ID and not their name.

 

The roughly same NSQL query works fine if it's the NIKU Schema, and only doesn't work when I use the drop down for the iBRIDGE Schema.

 

Any help would be appreciated.

 

Here's the NSQL that I'm using from our iBRIDGE schema:

 

SELECT    @SELECT:RESOURCES.PERSON_ID:ID@,

        @SELECT:RESOURCES.FULL_NAME:DISPLAY@,

        @SELECT:RESOURCES.LAST_NAME||', '||RESOURCES.FIRST_NAME||' - N'||RESOURCES.EMPLOYEE_NUMBER:LOOKUP@,

        @SELECT:RESOURCES.LAST_NAME:LAST_NAME@,

        @SELECT:RESOURCES.FIRST_NAME:FIRST_NAME@,

        @SELECT:RESOURCES.EMAIL_ADDRESS:EMAIL@,

        @SELECT:RESOURCES.EMPLOYEE_NUMBER:EMPLOYEE_NUMBER@

FROM     HR_DATA_RAW_T RESOURCES              

WHERE 1=1

AND @FILTER@              

AND RESOURCES.PERSON_ID != 0                            

@BROWSE-ONLY:

AND     ASSIGNMENT_STATUS_IND = 'A'

:BROWSE-ONLY@

Outcomes