Exercise: Limit One Value List Based on the Current Value of Another

Work areas are intended to compliment and correspond with your existing projects outside of ProjectWise, therefore it is to your advantage to define work area properties so that they correspond to columns in your existing project's external database table. By creating a relationship between a work area property and an external database table column, you can ensure that the value list for that work area property in ProjectWise Explorer is being populated directly from the external database. This also reduces the amount of manual entry required for configuring value lists.

To get started, the first thing you need to do is create a read-only table in the ProjectWise database that points to the corresponding table in your external database. Once created, go to the read-only table in the ProjectWise database and find the column you want to hook up to the work area property. For this exercise, assume that there is a column in your database table labeled Project_Number, and a corresponding work area property in ProjectWise called Project Number. Using the procedure below, we will hook the Project Number property to the Project_Number database table column.

Create a Value List That Gets Its List Entries from a Column in the External Database Table

Expand the Work Area Types datasource node and select a work area type.
In the right-hand pane, right-click the work area property you want to configure and select Properties.
For this example we will use the Project Number property.
In the Value section, set Type to Select.
Click the Format button (...) next to the SQL SELECT statement field.
In the Enter SQL Statement dialog, enter the following SELECT statement:
```
SELECT Project_Number FROM Project_Data
```
...where Project_Number is a column in the database table named Project_Data.
Click OK to close the Enter SQL Statement dialog.
The statement is added to the SQL SELECT statement field.
Click OK.
Now go to ProjectWise Explorer. Log in to your datasource and open the Properties dialog for a work area that has this work area type (selected in step 1) assigned to it. Select the Properties tab. Find the Project Number property, and place your cursor in the value field of that property. You will see a list of available project numbers. This list is coming directly from the external database table, where each project number you see on the list is really just a row in the database table column.

To further refine what appears on a property's value list, you can configure one property so that its value is determined by the value of another property. For example, you might configure one property so that its value list displays all the states in a country. You might configure another property so that its value list displays all the cities in a particular state whenever the state property is defined.

For this next example, using a SQL SELECT statement similar to the one used in the previous procedure, we will hook up the Project Name property to get its values from the corresponding Project_Name database table column. This time however, we will also configure the Project Name's value list to be restricted to one value, based on the currently selected value for the Project Number property.

Create a Value List That Is Limited Based on the Value of Another Property

From the same work area type used in the previous procedure, right-click the work area property you want to configure and select Properties.
For this example, Project Name is the property we are going to configure.
In the Value section, set Type to Select.
Click the Format button (...) next to the SQL SELECT statement field.
In the Enter SQL Statement dialog, enter the following SELECT statement:
```
SELECT Project_Name FROM Project_Data WHERE Project_Number = '@"PROJECT_Project_Number"'
```
The above statement says, whenever a value is selected for the Project Number property (Project_Number = '@"PROJECT_Project_Number"'), get the project number's matching value for the project name from the Project_Name column in the Project_Data table.

Note: If you are familiar with ProjectWise environments, you will notice that the concept here is the same, but that the syntax is different. For environment variables, the syntax is '$EDIT#[attribute]$', whereas the syntax for project property variables is '@"PROJECT_Project_Number"'.

Note: When you add a property to a variable by clicking the Add Property button, the beginning and ending single quotes you see above are not automatically inserted, you need to add them yourself.
Click OK to close the Enter SQL Statement dialog.
The statement is added to the SQL SELECT statement field.
Click OK.
Now go to ProjectWise Explorer. Log in to your datasource and open the Properties dialog for a project that has this work area type (selected in step 1) assigned to it. Select the Properties tab. Find the Project Number property and select a value for it. Then find the Project Name property and place your cursor in the value field of that property. You will see that the list of available project names only contains one name — the project name that matches the project number in the database.

You can repeat this procedure to create a similar value list in each of the remaining properties in the selected work area type. Simply replace the database field name right after the SELECT call so that everything after the FROM statement will be the same as in the procedure above.

Note: In ProjectWise Explorer, when you select a value for a work area property that controls other work area property values, those other values will not be set until you actually click in each cell to select the value. If you do not do this, the properties will remain undefined.