Open topic with navigation

ARCHIBUS Smart Client

Replacing Column Values

In grid views, use this command to change all records in one column to a specified value, including blank values. The Replace Column command is a powerful way of updating a column with new values, or even the results of formulas.

The Replace Column command replaces a column's values for all records included in the restriction set for the view. If no restriction is set, all records are updated, even if they are not shown on the screen. If you want to update a subset of records, set a restriction for the subset. If it is not possible to set a restriction that gives the desired subset, use the copy and paste feature to update the records. See Copying and Pasting Grid Data.

To replace column values:

1. If needed, use the Filter row to restrict the view to the records you want to change.
2. Select anywhere in the column you want to change.
3. Invoke the Replace Column command by doing either of the following:

   • Select Grid/Replace Column from the Ribbon.

   • Right-click on the column header and select Replace Column from the column context menu.

4. Do one of the following:

   • To replace the column's values with a value that you enter, select the Value option button and enter the value to apply to the entire column in the Value field. If the field is validated, the Value field is a list of values from the validating table.

   • To enter the value as an SQL expression, select the SQL Expression option button, and enter a valid SQL expression in the multi-line box. See SQL Expressions.

5. Click OK. All records are updated.

   Note: If a view is restricted, this command only affects the records that meet the restriction.

Value Example

If you want to change all values in Room Standard column of the Rooms table to TECH, select the Room Standard column and select the Replace Column command. In the dialog box, check the Value option, select TECH from the Value list, and click OK. ARCHIBUS makes the change and returns you to the Rooms view, where you will see that all records have TECH as the value for the Room Standard column.

SQL Expressions

Any valid SQL statement can be used in the SQL expression field.

• One type of valid SQL expression is just a character or number value. For character fields, surround a value by single quotes, for example 'tech'; for number fields just type a value.
• Another type of valid SQL expression is to use any valid SQL function(s) for the database you are using. For example, you could update the Option1 field in the rm table with the value of the rm_std field by just entering rm_std in the SQL expression. A more complex example is to use SQL functions such as concatenation. For example:   rm_std || ' - ' || fl_id.

• You can use only SQL expressions referring to fields of the current table and SQL functions. You cannot use subquery SELECT statements or more complex statements, as the program will block them for security reasons.

Clearing Existing Values

The Replace Column command is also a good way of clearing existing values so that you can use the multirow paste to change them. For example, do the following to clear department codes for an entire division within the Rooms table:

• Use the Filter/ by Row command on the Ribbon to show rooms for the division you would like to clear.
• Right-click on the Department Code column header (the "Department Code" title itself).
• Select Replace Column Value from the context menu. Leave the Value control blank and press OK.