Updating and Replacing Records in a Table

Performing Standard Data Operations > Editing and Replacing Data > Updating and Replacing Records in a Table

Strata also provides you with the ability to update/replace individual cells in a table. This is often useful when you need make a global change to your dataset.

Updating Records Based upon Criteria in a Cell

You can replace a subset of records from a table based on criteria in a given cell.

replace_criteria

In this example, we will replace all records in the table where the Vendor Name is equal to "AVIVA FOODS." To update in this way, do the following:

1. Right-click on the cell that contains the criteria you want to use as the basis for replacing records, then move the mouse over "Update Values".

2. Select one of the options from the "Update Values" sub-menu, such as replacing records equal to the value, greater than the value, etc.

This action will automatically populate the the Update dialog as discussed below.

Updating Records Based upon a Formula

You can also use a custom formula to replace a group of records from your table. As seen above, you can pre-populate this dialog by selecting a replace operation based on the criteria in a cell. However, you can also do this by selecting Update Records from the Data menu. The following dialog will appear:

replace_custom

This dialog enables you to update records based on a logical formula:

1.	Replace values in a certain field/column

2.	With a certain value

3.	When a certain condition is true

As shown, this dialog requires three pieces of information:

•	Field Name: This is a drop-down menu where you specify the field you want to update with new values.

•	Replacement Value: This is the new value that will replace the old value.

•	Boolean Formula: This is the required Boolean condition needed for the update to occur.

In the example above, when a field called "Vendor Name" is equal to "AVIVA FOODS", we will replace the cell in the field called "Discount" with a new value, "0.05".

NOTE: To pre-populate your dialog, simply highlight the field(s) you wish to use before selecting the Sort option.

Updating Records using Formulas

In the previous example, the Replacement Value was a specific number. However, there are occasions where you may want to perform a more advanced replace operation. In this case, instead of a specific value, you can use formulas.

For example, let's say you had a field named "Address" where most records were in the "PO Box" format but some others were in the "P.O. Box" format. You may use a replace operation to say:

In the field called Address, when the following formula matches certain cells

contains(Address,"P.O.")

then, replace those cells by translating the period (.) to a blank space using the following formula

translate(Address,".","")

replace_formulas

In this data set, this specific replace operation would replace all the cells in a consistent "PO Box" format. It is advised to first try using a calculated field to test your logic prior to actually running your replacement operation.

NOTE 1: A Field Name is the only item required to run an update operation. This means that if you leave the Replacement Value empty, each matching value will effectively be deleted (replaced with no value). If there is no formula entered, this means that you want to run the replace operation on the entire field. If you choose either of these operations, please proceed with caution.

NOTE 2: In order to append data to a table, you must first go to the Edit menu and make sure that "Protect Data" is turned off (unchecked).