SoftConstructors. Advanced technologies in software.
Program development. Programs that develop programs.
HomeTechnologiesApplicationsForumAbout Us
Separator
Home > Applications > TablePro > Online Help >

Formulas

The program supports formula calculations. Virtual columns show values which are calculated by formulas based on other columns values. For example: If there are "Price" and "Count" columns in a table, it is possible to add "Total" column, which will just show the price multiplied on count.

How formulas are presented

7+8
2-(100*4+8-(6*15-3/4))/177
10/(#1*#2)
"Name="+UpperCase(#1)
If(#3="";"Column3 is empty";"Column3="+#3)
If(And(#4<>"";#5<>"");Round(If(#6="";#4*#5;#4*#5/100*(100-#6));2);"")

Links

Formulas operate the above fields of each record separately; it is impossible for calculation of value in one cell of record to address the values of another record. For reference to the field of record, use symbol "#" and number of the column. For example, the formula #1*#2 means: value from the first column to multiply the value from the second column.

Data types

In formulas, only two types of data are supported: numbers and strings. Time and date types are not supported. The logic type is replaced numerically; zero value means False, and all others mean--True. Types in formulas will be transformed automatically. By default, it is accepted in the cells of the table, there are strings, instead of numbers. Therefore, to sum values into two columns, formula #1+#2 will be insufficient. The program will simply concate these values as strings; i.e., "100" plus "15" will appear as "10015". Here it is necessary to transform to the number type of data in the first argument: Val(#1)+#2; then the program will understand, that the values need to be put as numbers.

Formulas writing rules

It is possible to write names of methods in any ReGiStEr; it is also possible to insert blanks. They will be ignored. Strings should be covered with double quotes. If it is necessary to use double quotes inside of strings, it is necessary duplicate quotes: "What is ""TablePro""?". Values in methods can be listed only by the use of ";". For example: Max(#1;#2).

Methods

Method
 
Description
+
 
Concating of two lines or addition of two numbers. It is defined as the first argument. If values need to be combined with a number, and arguments are references to other columns, the first argument should be transformed to the number by method Val.
-
 
Difference of two numbers.
*
 
Multiplication of two numbers.
/
 
Division of two numbers.
>
 
Comparison of two numbers. Returns 1, if the inequality is correct; if differently, 0.
>=
 
Comparison of two numbers. Returns 1, if the inequality is correct; if differently, 0.
<
 
Comparison of two numbers. Returns 1, if the inequality is correct; if differently, 0.
<=
 
Comparison of two numbers. Returns 1, if the inequality is correct; if differently, 0.
=
 
Comparison of two numbers or strings. Returns 1, if the inequality is correct; if differently, 0.
<>
 
Comparison of two numbers or strings. Returns 1, if the inequality is correct; if differently, 0.
If(condition;true;false)
 
Checks a condition in the first argument and returns the second--if a correct condition, or the third condition, if the condition is incorrect (i.e., it is equal to 0). For example, the formula If(1<2;"Yes";"No") returns "Yes", and formula If(1=2;1000;5000), returns 5000.
Str(string)
 
Converts number to string.
Val(string)
 
Converts string to number.
Max(number;number...), Min(number;number...)
 
Returns the maximum/minimal value, as listed in brackets. It is possible to designate from 2 up to 256 values.
Length(string)
 
Shows length of a string, with a symbols count. For example, Length("Plintus") returns 7.
Not(condition)
 
Inverts condition, if 0, then returns 1, else returns 0.
And(condition;condition...)
 
Checks, to see that all conditions are true.
Or(condition;condition...)
 
Checks, to see that even one of conditions is true.
UpperCase(string), LowerCase(string)
 
Converts string to the upper/lower case.
Trim(string)
 
Deletes from a string, leading and trailing blanks if they are required. It is possible to also use TrimLeft(string) or TrimRight(string) for removal of blanks only, in the beginning or in the end of a string.
Copy(string;index;length)
 
Returns a part of the string, from symbol, with specified index (first symbol has 1 index) with specified length. It is possible to use CopyLeft(string;length) and CopyRight(string;length) to copy symbols from the beginning or from the end of the string.
Delete(string;index;count)
 
Deletes specified interval from the string. It is possible to use DeleteLeft(string;count), and DeleteRight(string;count), to delete symbols from the beginning or from end of the string. For example, Delete("Plintus";4;2) returns "Plius", and DeleteRight("Plintus";3) returns "Plin".
Insert(string;string;index)
 
Inserts first string to the second to the specified position. For example: Insert("nt";"Plius";4) returns "Plintus".
Repeat(string;count)
 
Repeat string count times. For example, Repeat("Plintus";3) returns "PlintusPlintusPlintus".
Round(number[;number])
 
Round number into integer. If the second non-essential argument is specified; then round to the specified sign count after comma. For example: Round(-123.456;2) gives a return of -123.45, and Round(-123.456) gives a return of -123.
Pos(string;string)
 
Brings up the symbol index, with the first string, which starts in the second string. If there is not first string in the second string, then returns 0. For example: Pos("nt";"Plintus") returns 4.
Cos(number), Sin(number)
 
Returns to cosinus/sinus of the number.
Sqr(number), Sqrt(number)
 
Returns to square/root of the number.

Error codes

In the formulas, two kinds of errors are possible: Errors in the writing of the formula (for example, it is not enclosed in brackets) and errors in the calculation of the formula (for example: Division by zero).
Error code
 
Description
41
 
Unknown method. Possibly, the name of a method is written with mistake.
42
 
An unknown symbol.
43
 
Opening bracket was found, but closing bracket is not found. Or the closing bracket was used before the opening bracket.
44
 
Closing bracket was found, but opening bracket is not found.
45
 
The end of a string is not found; i.e., an opening double quote is present, and the closing double quote is not present.
46
 
Invalid formula structure. For example, there are successively two signs with multiplication, or a division sign of right, after the opening bracket.
47
 
Invalid number.
48
 
Invalid link to column.
49
 
No enough actual arguments in a method.
50
 
Too many arguments in a method.
81
 
General (unknown) calculation error.
82
 
Cyclic link. For example, virtual column links to itself.
83
 
The specified column does not exist. For example, you use the link #6, but table contains only five (5) columns.
84
 
Division by zero.
85
 
Invalid data type. For example, method needs a number, but string was used.
86
 
Number overflow, suggests number is too high for calculations.
87
 
Number overflow, suggests number is too low for calculations.
88
 
Integer number overflow. For example, computer cannot integrate too big a float point number into the integer number.
89
 
Invalid floating point numbers operation.

See Next

Macro
 
Separator
Found an error? Inform us!
Site was updated 2015.03.30. Site Map
2015, SoftConstructors SoftConstructors logo