I need to pull from an ALM project a very large drop down project list of value choices for a field, Its quite a few values so having to do it manually would be quite annoying. Anyway to easily do this from database or some export in the UI?
Answer by MrWhite · Jan 09 at 03:47 PM
First, we need to identify from the DB the project list via its name, what its LS_PATH reference is. The path is how ALM in the DB links what uses the list and its values.
Site admin can be used to do this or directly in the DB. I like using site admin as the hierarchy of projects is easier to see and navigation is quicker for those who might not be overly familiar with the actual database layout that is seen in a tool such as SQL Management Studio.
Example from site admin is below. For this example, I am going to pull the value for a project list named “Browser”.
I ran this query then in site admin, under the project where my lists is located. My project was demo, and I expanded the tree view and then clicked on the LISTS table. Then I placed the query as seen below.
From this result, we need the LS_PATH value, which is AAAABO in my example.
Using that value, I jump over to my SQL Server Management Studio (for me located on same server as my database).
Here I chose a New Query and use the following query to pull my results then.
SELECT LS_NAME FROM default_demo_db.td.LISTS Where LS_PATH LIKE '%AAAAABO%'
This then will show me all the values from the project list “Browser” from my project Demo.
Results can be saved as a CSV or similar file for use in Excel or other application.
See my comment to this answer for a solution on how to try and distinguish subitems from a list.