Sorting validation rows (661 Views)
Reply
Regular Advisor
bzdafro
Posts: 154
Registered: ‎07-30-2009
Message 1 of 10 (661 Views)

Sorting validation rows

Hi,

 

We use validations to store a lot of information in PPM such as server names, application names, etc.  The problem is validations do not sort alphabetically.   You have to manually move the rows up or down to sort.      We are running ver 7.5sp1.   Does anyone know if ver 8 or 9 added enhancements to the validations to sort alphabetically?   If not, has anyone run into this issue and have some suggestions on work arounds?   We were thinking about sorting the rows (as in the query below)  and then updating the table with the results.   However, I am not sure how difficult this is.  Any suggestions?

 

 

select seq,lookup_code,description from knta_lookups
where lookup_type like 'WINDOWS Prod%'
order by 3,2

Please use plain text.
Honored Contributor
Jim Esler
Posts: 715
Registered: ‎05-09-2008
Message 2 of 10 (659 Views)

Re: Sorting validation rows

We do something similar to what you are considering but we place it in its own validation with Component Type of Auto Complete List validated by SQL - Custom. This way the list is populated real time from the contents of the dropdown validation.

Please use plain text.
Honored Contributor
Erik Cole
Posts: 861
Registered: ‎02-11-2008
Message 3 of 10 (656 Views)

Re: Sorting validation rows

I second Jim: create a new Auto Complete List and plop your SQL in that, ordered however you like. Maintain the list of values in the original validation.

 

You also would typically do this so that you could use one validation that has a "enabled = true" clause for form field input, and a second validation for lookups that will show even the disabled values for reference so you can pick up requests that have field values that might not be currently enabled.

 

Incidentally, 9.1 includes a new tree view validation capability as well.

Please use plain text.
Honored Contributor
Utkarsh_Mishra
Posts: 695
Registered: ‎07-13-2010
Message 4 of 10 (650 Views)

Re: Sorting validation rows

Hi Bzdafro,

 

I think you are talking about the Drop Down list validation, in which the sequence is maintianed in the order of value entry.

 

In version 8.0 there is not such utility for sort, but you can acheive it by creating a new SQL list validation.

 

and then can select the sorted list by

 

SELECT lookup_code, meaning, default_flag FROM knta_lookups WHERE lookup_type = <List Validatio Name>' and enabled_flag = 'Y' ORDER BY Meaning

 

Hope this helps you.....

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Please use plain text.
Advisor
EricLEdberg
Posts: 19
Registered: ‎05-16-2011
Message 5 of 10 (636 Views)

Re: Sorting validation rows

I posted a similar question just a few days ago.  One of our local HP PPM contractors, Francois Schneider,  provided this procedure example which is supposed to reorder the sequence of the rsc_location table. 

  • I have not tested this code so you may want to TEST it first and modify it to meet your needs

Eric Edberg

 

 

PROCEDURE refresh_rsc_location(p_lookup_type IN VARCHAR2) IS
  l_count      NUMBER;
  TYPE listCodes IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
  l_list listCodes;
BEGIN
  -- Reorder the SEQ field
   SELECT lookup_code BULK COLLECT INTO l_list
     FROM knta_lookups_nls
    WHERE lookup_type = p_lookup_type
ORDER BY meaning;
  IF l_list.FIRST IS NOT NULL THEN
    l_count       := 0;
    FOR i         IN l_list.FIRST..l_list.LAST
    LOOP
      l_count := l_count + 1;
       UPDATE knta_lookups_nls
      SET last_update_date   = SYSDATE,
        seq                  = l_count
        WHERE lookup_type = p_lookup_type and lookup_code = l_list(i);
    END LOOP;
    COMMIT;
  END IF;
END refresh_table_component;

This is called with the code:
refresh_rsc_location('RSC – Location');

 

 



Please use plain text.
Occasional Advisor
leewil1
Posts: 8
Registered: ‎11-15-2011
Message 6 of 10 (561 Views)

Re: Sorting validation rows

With minor changes I was able to add the stored procedure listed here, refresh_table_component.  Needed to make a couple of changes for the procedure  to be successfully added:

1) PROCEDURE name needed to match END name. So PROCEDURE refresh_rsc_location became PROCEDURE refresh_table_component. 

2) knta_lookups_nls  does not exist in our database. References to table knta_lookups_nls were changed to knta_lookups.

 

Next when I ran the procedure, it did what I expected - the column seq was set according to the order by clause. However, in PPM, under validations, the order did not match.  The row with seq=1 showed seq=5 in the validation list. 

 

Am I missing something here?

Please use plain text.
Honored Contributor
Jim Esler
Posts: 715
Registered: ‎05-09-2008
Message 7 of 10 (557 Views)

Re: Sorting validation rows

Whenever you change something directly in the database tables, you need to be concerned about cached values. You can use the script kRunCacheManager.sh to clear one or all caches.

Please use plain text.
Honored Contributor
AlexSavencu
Posts: 593
Registered: ‎04-30-2010
Message 8 of 10 (554 Views)

Re: Sorting validation rows

Hi,

 

I would not recommend altering the rows in the database only for sorting purposes.

 

We always do it like this: we have a drop-down validation that contains the values, but we create extra auto-complete validations based on custom SQL in order to access the values.

 

cheers

alex


--remember to kudos people who helped solve your problem
Please use plain text.
Regular Advisor
bzdafro
Posts: 154
Registered: ‎07-30-2009
Message 9 of 10 (547 Views)

Re: Sorting validation rows

Alex,

 

I agree that auto complete is the best way to sort validations when creating requests or packages.  However the issue we face is how to manage the data in the validations.  Example.  We rely heavily on validations to store data - server names, application names, windows domain group names, etc.  Some of the validations contain hundreds of entries.  We will use one of the fields (code / meaning / description) to store the application name.   We then sort that entry in the list alphabetically using the up/down arrows in the validation (a VERY painful process).   That way all the entries for HR, as example, are grouped together   This way when HR contacts us to add/modify or delete something for HR, we can find that entry in the validation and make the change.  Otherwise, we would have to scroll thru all 200+ entries in the validation list.   We could write a SQL query to find the rows, but that requires time and the skill set to do that.   Part of our team is constantly making changes to the validations manually.  They are not comfortable with SQL nor would I want them running updates to the tables.  

 

How does everyone manage large lists of data required to fill in requests or packages?   Example, we have a request form that requires the user to select an oracle role and database.  We have about 75 different application teams each with their own role names & db names.   We use the auto complete to sort & provide the user with an appropriate list of roles and DBs to choose from, depending upon their security groups.  Does anyone have similar processes on their requests or packages?  If so, how are you managing the data being used on the fields?   It seems silly but I can use an external file and store all this information.  Then using unix commands like grep, I can return the roles and DB information.  However, I would prefer to use the build in tables in PPM to store this information.   It doesnt seem right that I would need to store this information in a source external to PPM.  By using external source, I also loose the ability to use tokens to resolve fields from the validations.    

Thanks   

Please use plain text.
Honored Contributor
AlexSavencu
Posts: 593
Registered: ‎04-30-2010
Message 10 of 10 (537 Views)

Re: Sorting validation rows

Hello, bzdafro,

 

I haven't seen such an interesting post in a long time!

 

First of all: there is a best practice which says that specific data within the organization should have a single source of truth.

 

Therefore, it might be a good idea to integrate PPM with the IT Asset Management system you have (e.g. for servers, domain users, groups etc - you can even use the AD as a data source), and/or with the system that has the source data for the organizational chart (an HRMS, or some companies rely on AD to do this).

 

In this manner, you can integrate PPM with these systems in two ways:

1. offline integration - you can create batch jobs to synchronize data in PPM from the external system(s) at specified intervals.

2. online integration - you can have the field validations select directly from the external system using views over db links.

 

We have PPM implementations integrated as follows:

- using Oracle Identity Management, we bring in user information, we assign licenses and security groups;

- using Oracle Identity Management, we bring in organizational structure information and assign the appropriate users in order to match the company's organizational structure;

- we have integrated using the online method with Service Desk systems to be able to select and report based on data related to services, HW & SW systems and so on.

 

Having these approaches, we have:

- avoided the burden to maintain data for which PPM is not the core system;

- isolated PPM, in terms that we are using only the data that is published by the external system.

 

Please let me know if this helps.

 

cheers

alex


--remember to kudos people who helped solve your problem
Please use plain text.
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