Auto complete list with PPM users & surname changes (492 Views)
Reply
Occasional Visitor
gjmurphy
Posts: 3
Registered: ‎04-26-2013
Message 1 of 6 (492 Views)
Accepted Solution

Auto complete list with PPM users & surname changes

Hello,

 

In one of our requests there is a auto complete list of PPM users, performed via a user defined sql validation. The values of this selection are stored in a PARAMETERnn/VISIBLE_PARAMETERnn column pair.  So far so good: the users ID is stored in PARAMETERnn, the users full name in VISIBLE_PARAMETERnn.  Now, when where is a surname change of one of the PPM users these VISIBLE_PARAMETERnn values remain unchanged with the persons previous surname.

 

What is the best way to handle this type of scenario?  Re-implement the auto complete list in some other fashion, or a periodically run SQL script that would look for and correct such [no-longer accurate] auto complete values?

 

greetings,

gerry.

Trusted Contributor
kwood55
Posts: 131
Registered: ‎04-09-2012
Message 2 of 6 (475 Views)

Re: Auto complete list with PPM users & surname changes

- Is it really critical. New requests should have the correct Surname in the auto complete list.

 

- If OldSurname is not in many Requests then Open each request and select from the auto complete list again.

   You should be able to find OldSurname with search > requests or a SQL Runner Query.

 

- It may be possible to create a rule for the Request Type where "On Page Load" you can reset this field.

 

Occasional Visitor
gjmurphy
Posts: 3
Registered: ‎04-26-2013
Message 3 of 6 (465 Views)

Re: Auto complete list with PPM users & surname changes

Requests created after the name change have the correct (new) surname.  Searching works as well as you can only search with the current (new) name, which also picks up entries  containing the old surname.  Search results pages where the relevant column has been shown display the new surname, even when displaying the individual requests display the old surname.

 

Implementing a new rule to correct the displayed name sounds like the best option so far.

 

greetings,

gerry.

 

 

Trusted Contributor
kwood55
Posts: 131
Registered: ‎04-09-2012
Message 4 of 6 (460 Views)

Re: Auto complete list with PPM users & surname changes

Find out how many requests have OldSurname

- Open the workbench
- Select Sys Admin > Server Tools. Max out the SQL Runner window
- Cut and paste the query below into the SQL Statement window and press "Run SQL"

select * from kcrt_request_details

- Find which visible_parameterX contains the Full Name
- Modify your query to

select request_id, visible_parameterX  from kcrt_request_details where visible_parameterx like 'first OldSurname'

or

select request_id, visible_parameterX  from kcrt_request_details where visible_parameterx like '% OldSurname'

The percent is a wildcard.

- If your visible_parameterx is in the Request Header then start with this table

select * from kcrt_req_header_details

Honored Contributor
Jim Esler
Posts: 722
Registered: ‎05-09-2008
Message 5 of 6 (452 Views)

Re: Auto complete list with PPM users & surname changes

Given that surnames are probably not unique, you should also check for the desired id in the hidden parameter field if you use this database query to find the records you are interested in. Also, note that the parameter set used for this field could be different in different request types so a check for request type would also be prudent.

 

The on-page-load approach will probably not work on requests that are closed. If this is an issue, your only option would probably be a database query to update the request details entries directly.

Occasional Visitor
gjmurphy
Posts: 3
Registered: ‎04-26-2013
Message 6 of 6 (413 Views)

Re: Auto complete list with PPM users & surname changes

Thanks for the tips everyone.  Just for reference here is the solution that worked to resolve this:  iterate over each column containing the full names/user ids of ppm users and execute a query such as:

 

...

   execute immediate 'update ' || v_tablename || ' set VISIBLE_PARAMETER' || param_num || '=(select full_name from knta_users where user_id=PARAMETER' || param_num || ') where request_type_id=' || request_type_id || ' and PARAMETER' || param_num || ' not like ''%#@#%'' and VISIBLE_PARAMETER' || param_num || '!=(select full_name from knta_users where user_id=PARAMETER' || param_num || ')';
...

 

For fields that have the "Multi-Select Enabled" option in the workbench enabled the fix was a bit more complex: split the user_ids in PARAMETERx on '#@#', retrieve their full_names from knta_users, concatenate them with '#@#' and compare with VISIBLE_PARAMETERx.  When they differ update the record.

 

greetings,

gerry.

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.