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.
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.
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.
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’
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$’
The two attributes were added to the Default interface.
Within ProjectWise Explorer, I created a new folder and applied the Cascading Attributes environment to it.
Now when a new document is created, we can select the state.
Only the 3 states entered within our Picklist display.
And then the corresponding city.
Notice, that only those cities within Colorado are available.