Edit table constrained
You can use this function to modify rows of data in a database table, and constrain the results to specific rows. If you are responsible for administering the database you might use this function to make small corrections to fields that are not key fields.
Caution: By using this function you are modifying the data in the database directly. GEOVIA recommends you back up your database before using this function except for small changes when you completely understand the schema of your database.
Prerequisites: A database is open.
To run this function: Select Database > Edit > Edit table constrained, or Survey > Stations > Edit table constrained, or Design > Blast design > Edit > Edit table constrained, or
- In the Function Chooser, type EDIT TABLE ROW, and press ENTER.
Fields on the Select the database table to process form
| Field | Description |
|---|---|
| Table name | The name of the table to view. |
When you click Apply, the fields in the table are listed in the following form.
Fields on the Define view/edit rows template form
| Field | Description |
|---|---|
| Table name | Read only. The name of the table you opened. |
| Field Name | This section shows each of the fields for which data will be displayed. Right-click on the column on the left to remove or add fields. You can also change the order in which fields are displayed using the drop-down list buttons in each cell. |
When you click Apply, the Define Query Constraints form is displayed. You can enter up to 17 constraints.
Fields on the Define Query Constraints form
| Field | Description |
|---|---|
| Table name | Read-only. The name of the table you opened is displayed at the top of the form. |
| Field name | The name of the field on which you want to impose a constraint. If you leave Field name blank, all records in the table are selected. |
| Operand |
The mathematical operation that defines the constraint. The operands are:
|
| Constraint Value |
The value for the constraint. Press TAB to add a second row, or right-click in the left column to add or remove constraint rows. When you have multiple constraint rows, Surpac returns only the records that pass all the constraints. The only exception is that you can you use the hole_id field and the = operator to return multiple rows — in that situation, an OR operator is used between the two constraints. For example, the following constraint would return two records:
Examples: For a surveying example, to select all the survey stations with a specific status, you could add a constraint:
The Constraint value for this constraint is C. To select drillholes or survey stations in a particular area, you could add three constraints:
To select all of the holes where the collar is on a specific bench of an open pit, you could enter two constraints like these:
|
| Load | Loads a constraint from the .dbc file you choose. Be aware that a set of constraints is normally suitable for only the table on which it was created, or on tables that have the same or a very similar structure. |
| Save | Saves the constraint to a .dbc file so you can reload it later. |
Output
When you click Apply in the final form, the View/Edit table rows form is displayed. This form displays rows of data from the table you chose. The maximum number of rows displayed is 200.
The cells with a grey background are read-only. If you edit a cell with a white background and click Apply, the data is changed.
Note : Memo fields are read-only because only the first 200 characters of any memo field are displayed. If you were able to edit the memo field using this function, characters after the 200th character would be lost.
Validation
If the data does not pass a validation rule, the update operation on the table is terminated and the row which is causing the problem is displayed as the first row on the form. All rows before this row on the form are successfully saved to the table when this occurs. This gives you the opportunity to correct the data or to abort the remainder of the edit operation.
Tip: Look at the Message window. If the data has not been updated because it did not pass a validation rule, a message is displayed.
MessagesUse this function to edit existing rows of data in a table. Constraints can be applied to the rows of data made available for editing so that only those rows of data that satisfy the field constraints are displayed for editing.
To run this function: Choose Survey > Stations > Edit table constrained, or Database > Edit > Edit table constrained, or...
Table name
Enter the name of the table that you wish to retrieve data from for editing.
Complete the SELECT THE DATABASE TABLE TO PROCESS form and choose Apply to display the DEFINE VIEW/EDIT ROWS TEMPLATE form.
When this form is displayed it contains a list of all of the editable fields in the selected table.
Complete the DEFINE VIEW/EDIT ROWS TEMPLATE form and choose Apply to display the DEFINE QUERY CONSTRAINTS form.
Table Name
This is the name of the table from which the data is to be deleted and cannot be changed on this form.
Function
The function to be performed is displayed.
Field Name
Enter the name of the field upon which you wish to impose field constraints.
Operand
Enter the operation that will define the field constraints. Valid entries for the operand are:
| < | less than |
| <= | less than or equal to |
| > | greater than |
| >= | greater than or equal to |
| <> | not equal to |
| = | equal to |
Constraint Value
Enter the value of the constraint that you wish to apply to the named field. When entering a character constraint value, e.g. station_id, it is important to enter the name exactly as entered earlier when setting up the database.
A typical example of a constraint may be to delete all of the stations with a certain status (i.e. survey reliability):
| Field Name | Operand | Constraint Value |
|---|---|---|
| status | = | C |
Another constraint may be to delete all stations in a particular area:
| Field Name | Operand | Constraint Value |
|---|---|---|
| y | < | 9010.000 |
| x | > | 2450.000 |
| z | => | 705.000 |
To delete a station whose name is `ABC':
| Field Name | Operand | Constraint Value |
|---|---|---|
| station_id | = | ABC |
Complete the DEFINE QUERY CONSTRAINTS form and choose Apply to display the VIEW/EDIT TABLE ROWS form.
All rows from the table which comply with the query constraints, up to a maximum of 200 rows, are displayed on the VIEW/EDIT TABLE ROWS form. You may visit any of the fields displayed on the form to modify the values which are displayed.
When you have completed making changes to the data on the form you must press Apply to have your changes saved to the table. If you press Cancel any changes which you have made will be lost.
Data Validation:
Various validation tests are applied to the data to ensure that the integrity of the data are maintained. Any attempt to create rows which have duplicate indexes will be rejected. For example 2 rows in the collar table with the same station_id.
If any of these data validation criteria are not complied with then the update operation on the table will be terminated and the row which is causing the problem is displayed as the first row on the form. All rows prior to this row on the form will have been successfully saved to the table when this occurs. This gives you the opportunity to correct the offending data or to abort the remainder of the edit operation.
Result
The modified data are saved to the table, provided compliance with the data validation criteria is achieved.
Messages
Updated XXX rows.