Project and Portfolio Management Practitioners Forum
cancel

Creating a drop down list using data from an external DB.

Highlighted
Sevent3
New Member.

Creating a drop down list using data from an external DB.

Got thrown into the world of PPM so a noob question here. What I'm tasked to do is create a drop down list validation with data that is to be retrieved from an external database. Is this possible? If so, a pointer or a sample would be appreciated. DBlinks are frowned upon(no matter how brief it may exist) and the validation SQL doesn't want to see something like that in there to create and destroy the link on the fly it seems.

 

Thanks in advance....

4 REPLIES
dirkf
Acclaimed Contributor.

Re: Creating a drop down list using data from an external DB.

Hi Sevent,

 

I fear that this seems not possible to do out of the box. I have found a knowledge-base article which refers to this issue:

 

"I would like to make an enhancement request to enable validations to connect to external databases via JDBC. The business case for this enhancement request is that without this ability, the Kintana application is causing
a.) Increased Complexity in the environment with both other options (DB Links or New Internal Tables)
b.) Duplication of data as well as scheduling issues with the ETL process.

This request is critical moving forward with implementation of our first Kintana workflow "

The issue is documented in QCCR1L31603 visible using the following link:

http://support.openview.hp.com/selfsolve/document/FID/DOCUMENTUM_QCCR1L31603

 

As you will see, the document is unfortunately in a poor state (probs due to migration issues from a different tool)  - I will edit this later during the day to present the information above. You should submit your email-address to the link nonetheless to be informed if and when the status changes for this ER - in parallel, you might consider logging a new ER nonetheless to submit your own business requirements and make a statement for the version that you are using. Use the following questionaire to log the ER:

 

1) Please provide a short problem statement:

2) When does the situation occur? Please describe an actual scenario from the
customer's current environment.

3) How is the situation currently handled or resolved? What work around is used
today?

4) What is unsatisfactory about the current situation and why?

5) What is the required functionality to meet that will enable the described
scenario?

6) What and how many users, administrators and non-PPM systems play a role in this use case?

 

Hope this helps. Sorry I don't have better news.

Have a great day nonetheless

 

Best regards,

Dirk

dirkf
Acclaimed Contributor.

Re: Creating a drop down list using data from an external DB.

Hi Sevent,

 

I found that this document is internally visible only, thus the ER is not customer-editable. Since it was created for ITG 2.0 (!!!) it's probably better that way. There's not much use pointing a customer-visible document at this ER since no customer will therefore be able to find it. Best thing to do would really be to create a new ticket with PPM-Support and have them log a new incident with Rnd.

If you want, either refer to the existing CR or have them assign the case to me (Dirk Foeste).

 

Have a great day.

Best regards,

Dirk

Jim Esler
Acclaimed Contributor.

Re: Creating a drop down list using data from an external DB.

We have used the Description field in dropdown entries to specify filters for a custom validation. The validation then uses the token for the controlling field to pick entries from the second field's validation. The sql for the second validation looks like:

 

select lookup_code, meaning
FROM knta_lookups
where description like '%[REQD.P.FIRST_FIELD]%'
and lookup_type = '<second list name>'

and enabled_flag = 'Y'
order by seq

erik_c
Respected Contributor.

Re: Creating a drop down list using data from an external DB.

You could create an Auto Complete List, validated by Command. This will allow you to do anything you want with a shell script that will return a delimited list. You could, for example, log on to the other database via sqlplus and execute your query.