#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

How To: Create Custom Picklists within ProjectWise Using PowerShell

In this post, I’m going to demonstrate how to create and use custom picklists within ProjectWise using PowerShell. These custom picklists can be used to limit the values available for document attributes.

The PowerShell cmdlets we will look at are Add-PWPicklist, Get-PWPicklist, and Get-PWPicklistItems. There are a few other cmdlets for working with custom picklists.

# The following will list all cmdlets pertaining to the Custom Picklists.
Get-Help *-pwpicklist
# The following will show all of the help information for the Add-PWPicklist cmdlet.
Get-Help Add-PWPicklist -Full

The Add-PWPicklist requires two parameter values, a picklist name, and the corresponding picklist values.

In the following example, I create an array variable containing the name of 3 states. I use the values in the array as the picklist values and “States” for the picklist name.

# Array of State names
$States = "New York", "Colorado", "South Carolina"
# Create a new ProjectWise picklist.
Add-PWPicklist -PicklistName "States" -PicklistValues $States

After running, a new database table has been created within the current ProjectWise datasource with the Table name CustomPicklists. The table contains two columns, PicklistName and PicklistValue.

If we run a simple query within our database now, we should see the new table, and the columns and data.

sql

Next, I am going to create 3 new arrays with cities corresponding to each of the states added. Then create 3 new picklist for each state using the corresponding array of cities as the values.

# The following are 3 arrays with cities that correspond to the states in the states array.
$NYCities = "New York", "Albany", "Saratoga", "Buffalo", "Rochester", "Syracuse"
$ColoradoCities = "Denver", "Boulder", "Golden", "Durango", "Colorado Springs"
$SCCities = "Greenville", "Spartanburg", "Charleston", "Columbia", "Myrtle Beach"

# Creating 3 new picklists for each state and corresponding cities.
Add-PWPicklist -PicklistName "New York" -PicklistValues $NYCities
Add-PWPicklist -PicklistName "Colorado" -PicklistValues $ColoradoCities
Add-PWPicklist -PicklistName "South Carolina" -PicklistValues $SCCities

Again, if you look in the database, you will see the new picklists and corresponding values. In the following you will see that I am only querying for the cities within Colorado.

sql2

To get a list of Custom Picklists or Values within the current datasource using PowerShell, use the following cmdlets.

# Will return a list of all available picklists. Or false if none exists.
Get-PWPicklist
# Will return the values corresponding to the provided PicklistName.
Get-PWPicklistItems -PicklistName States

 

Now that we have the new Custom Picklists created in our datasource, we can add them to document attribute definitions. In the following you will see that I have added two new attributes to my environment.

pw

For the zState attribute, we want to limit the available values to only the states within our custom picklist States. To accomplish this, we add the following SQL Select statement to the Value list section on the Value tab of the attribute properties dialog. Toggle on Limit to list to ONLY allow these values.

SELECT PicklistValue FROM CustomPicklists WHERE PicklistName LIKE ‘States’

pw2

For the zCity attribute, we want to limit the values here to correspond to the selection within the zState attribute. For example, if we select Colorado in the zState attribute, we will only see the cities within Colorado within the zCity attribute. Again, we add the following SQL Select statement to the Value list section and toggle on Limit to list. The $EDIT#zState$ is replaced in the query with the zState attribute value. If nothing, is selected, no cities will be returned.

SELECT PicklistValue FROM CustomPicklists WHERE PicklistName LIKE ‘$EDIT#zState$’

pw1

The two attributes were added to the Default interface.

pw3

Within ProjectWise Explorer, I created a new folder and applied the Cascading Attributes environment to it.

pwdoc3

Now when a new document is created, we can select the state.

Only the 3 states entered within our Picklist display.

pwdoc2

And then the corresponding city.

Notice, that only those cities within Colorado are available.

pwdoc1

 

pwdoc4

4 thoughts on “How To: Create Custom Picklists within ProjectWise Using PowerShell”

  1. Brian, any chance of supporting PickList descriptions along with Picklist values, as does the built in PickList editor?

    I can make it work like this:
    1) Create your picklist values using the semicolon character as your delimiter (as does PW), so my states look like this:
    $States = “NY;New York”, “CO;Colorado”, “SC;South Carolina”, “OH;Ohio”
    NOTE that using this approach REQUIRES the use of the semicolon in each value.
    2) Create the custom PickList:
    Add-PWPicklist -PicklistName “States” -PicklistValues $States
    3) Use the following for the SELECT statement for the picklist in the PWAdmin:
    SELECT
    SUBSTRING(PickListValue,1,CHARINDEX(‘;’,PickListValue)-1) as “Value”,
    SUBSTRING(PickListValue,CHARINDEX(‘;’,PickListValue)+1,99) as “Description”
    FROM CustomPicklists WHERE PicklistName = ‘States’ ORDER BY 1

    NOTE you don’t need to use the ‘as “Value”‘ or the ‘as “Description”‘ – I added them just to make it obvious what the intent is.

    Like

      1. Hi Brian, this would also be very useful to us – with the imminent move to the cloud of our ProjectWise datasources, having a way to manage the picklists from PowerShell is essential but the Description is critical for us. Dan’s workaround is an option but it’d be much better for us to have description as a separate column which isn’t part of the primary key.

        I could probably figure out how to use the C API to do it myself, but it seems like it’d be a fairly minor update to the powershell cmdlet rather than me fighting with C..!

        Consider this to be an expression of need! Thank you!

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.