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.
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:
This dialog enables you to update records based on a logical formula:
As shown, this dialog requires three pieces of information:
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
then, replace those cells by translating the period (.) to a blank space using the following formula
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).