12-18-2013 04:25 AM
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?
Solved! Go to Solution.
12-18-2013 09:45 AM
- 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.
12-19-2013 01:54 AM
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.
12-19-2013 02:45 AM
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'
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
12-19-2013 07:37 AM
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.
01-08-2014 01:20 AM
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.