Field Calculation
Use the Field Calculation option to manipulate fields if fields, not necessarily the same fields, to meet certain conditions.
Instructions
On the Utilities menu, click Field Calculation to display the Field Calculation panel.
It is possible to resize this panel. Click and drag one of the corners.
The key patterns are the key fields from the database. It is possible to limit which individual records will be affected by the field calculation.
An asterisk (*) represents all entries in a text field and two decimal points (..) represent all entries in a number field (whether of integer, single or double type). To limit further the individual records, use the methods outlined in the table below.
| Number Fields | number.. number | Restricts the records to those between the two numbers, that is, 50.. 120. |
| number.. | Restricts the records to those after the number, that is, 25.. | |
| .. number | Restricts the records to those before the number, that is,.. 34 | |
| Text Fields | L* (multi-character wildcard) | Restricts the records to those that begin with an L, that is, L11 or L16-N4 |
L1? (single character wildcard) |
Restricts the records to those that consist of three characters starting with L1, that is, L11, L1A. |
Table
Select the table on which to perform the field calculations.
Condition
Enter the condition for calculation. Numbers can be entered in standard form (that is, 1, 1.2, -2.3) or in scientific notation (that is, 4.2e23). Two transcendental numbers are also built into the program, pi and e.
When using text fields in the condition, you must use double quotes for any field values, for example, HOLEID=="L22" will include the record that has a Hole ID of L22. You can also use * (the multi-character wildcard) when stating a condition, for example, HOLEID LIKE "L*" to include all holes beginning with L.
If your field variable has special characters, such as CU%, then enclose it in square brackets, for example, [AU@$%] +23.
Refer to Field Calculation Examples for examples (including diagrams) of the operators and functions.
The following operators can be used in conditions and equations.
| Operators | Description |
|---|---|
+, -, *, / |
Arithmetic operators. The '+' can also be used on strings. |
<, >, <=, >=, <> |
Comparison operators (all of which work with strings). Note: The <> (not equal to) operator replaces the != operator that was used in earlier versions of Vulcan. |
== ( two equal signs) |
Equals. |
% |
Integer modulus, for example 7%3 returns 1. Note: Exponents are right associated. That is 4^2^3 = 4^8 = 65536 rather than 4^2^3 = 16^3=4096. |
?: |
(if-else, <condition> ? <value if true> : <value_if_false>. Example: 4<3?"Four is less than three" : "Four is larger than three" would return "Four is larger than three". |
AND |
Both conditions must be true. Example: EAST<78327 AND NORTH>4730, includes all records that have both an Easting less than 78327 and a Northing greater than 4730. |
OR |
At least one of the conditions must be true. For example: EAST>78327 OR NORTH>4730, includes records that have either or both an Easting less than 78327 and a Northing greater than 4730. |
XOR |
Only one of the conditions must be true. Example: EAST>78327 XOR NORTH>4730, includes records that have either, but not both, an Easting less than 78327 and a Northing greater than 4730. |
NOT |
Everything that is not included by the condition. Example: NOT EAST>78327, includes records that have an Easting less than or equal to 78327. |
LIKE |
Everything that is included by the condition. Example: HOLEID LIKE "7*", includes records that start with with a 7. |
NOT LIKE |
Everything that doesn't follow the pattern. Example: HOLEID NOT LIKE "7*" includes records that do not start with a 7. |
It is also possible to include brackets () in your condition or equations. Expressions within brackets are evaluated first.
Example: (EAST+10)*2>74000, will include the records if the result of adding 10 to the Easting and then multiplying by two is greater than 74000. Without the brackets, EAST+10*2>74000 will include all records where the sum of the Easting and 20 is greater than 74000.
The following functions can be used in conditions and equations.
|
Trigonometric |
Returns the sine, cosine or tangent of a number. |
|
Inverse Trigonometric functions |
Returns the inverse sine, cosine and tangent of a number. |
|
Angular unit conversions |
Converts angular units. |
|
Logarithmic fountains |
Returns the natural logarithm of a number, or returns e raised to the power of the number or returns the logarithm to the base 10 of the number. |
|
Square root function |
Returns the square root of the number. |
|
Absolute value function |
Returns the absolute value of the number. |
|
Minimum and Maximum Value |
Returns the minimum or maximum value of a series of numbers. |
| ran() | Returns a random value between 0 and 1. |
| round(x) | Rounds to the nearest integer. |
| len(str) | Returns the number of characters in a text string. Where 'str' is the piece of text whose length you want to find. |
| lower(str) | Converts all uppercase letters in a text string to lowercase letters. |
| upper(str) | Converts all lowercase letters in a text string to uppercase letters. |
| substr(str,pos,<len>) | Returns a substring of the text string, which starts at the specified position and if specified, contains len number of characters. For example, substr(Maptek,2,3) returns pte. |
|
left (str, len) |
Returns a substring of the left/right part of the text string. len specifies the number of characters included in the substring. For example, left(Maptek,4) returns Mapt. |
|
ljust(str) |
Left or right justifies the text string. |
| pack(str) | Removes spaces in a text string. |
| index(str, substr) | Returns the first position of the substring in the text string. |
| rindex(str, substr) | Returns the last position of the substring in the text string. |
| reverse(str) | Reverses the text string. |
| match(str,pattern) | Returns true if the string contains the pattern and false otherwise. The search is case sensitive. Wildcards can be used in the search. |
| matchi(str,pattern) | Returns true if the string contains the pattern and false otherwise. The search is case insensitive, i.e matchi(abc,A*) returns true whereas match(abc,A*) returns false. Wildcards can be used in the search. |
| as_str(x) | Converts x to a text string. |
| as_num(str) | Converts the text string to a number. Text used in as_num must be inside simple quotation marks. |
| Example Expression | Result |
|---|---|
| 1+2 | 3.000000 |
| 2^3^2 | 512 |
| sin(deg_to_rad(145))*2.3 | 1.319226 |
| "my"+" " + "string" | "my string" |
| upper(substr("my string",3,4))+as_str(23) | "STRI23" |
| as_str(sqrt(200)) like "*6" AND min(2,3,4)>1 | 1.000000 |
| as_str(myvar=rand())+" is "+round(myvar)?"more" : "less")+" than 0.5" | "0.115146 is less than 0.5" |
Field
Select the field to be calculated.
Equation
Enter the equation or expression, for example, (EAST-1000)*0.5.
Apply first true equation for each table
Select this option to perform calculations on a particular table once. This means that after one field in a table has been manipulated, then any subsequent mention of that table is ignored.
Apply all true equations
Select this option to perform all of the calculations that you have entered in the panel. This means that fields can be manipulated more than once. The calculations are performed dynamically, this means that if a table is used more than once, then the fields contains updated values from the previous calculations.
Save
Select this option to save the field calculations. The saved information will be stored in a nominated Field calculations file (.fcl).
Load
Select this option to load a previous set of field calculations.
Click OK.
The field calculations are performed and the database is updated. To save your changes use the Save or Save As options.
