GS-Base uses formulas in the following procedures/functions:
- You can enter them directly in fields. They will be evaluated and the results will be saved. This applies to the standard Text and Numeric fields.
- You can use them with the Insert > Generating formula command.
This command enables you to mass fill a given field in all records based on values of other fields
in related records. It can be used for all field types.
It can be especially useful when combined with the UDF() Python functions that can return to GS-Base any data (graphics or text) of any size to be inserted in the GS-Base binary fields (LongText, Code, Images/Files). You can, for example:
- use the FIELDS(start, end) and FIELDNAMES(start, end) functions to pass data series (up to the 16K field counter limit) to such Python functions to "mass"-generate charts in Image/Files field(s) in all records;
- use the FIELDS(start, end) function to archive all current Text/Number field values in the LongText fields;
- populate any binary fields in all records with files, documents, graphics, reports based on other fields, etc. See: Using Python functions as UDF()
- You can use them to create calculated fields:
in the Field Setup dialog box, from the Special (functionality) list choose Calculation formula and enter the desired formula in the field below.
If the menu option Tools > Automatic Updating is checked, each editing action within the table will cause automatic recalculation of the formulas for fields from records affected by that editing action.
For example, if you paste some values to a field for records 10 to 20, all fields in these records with such calculation formulas defined will be updated and the values will be inserted in those fields.
If the menu option Tools > Automatic Updating is unchecked, to update all calculated fields you must use the Tools > Update All Calculated Fields command to perform the calculations for the newly entered data. For details, please see: Managing database fields and tables. - You can use formulas to construct search expressions for fields. For details, please see: Searching / Filtering Records.
- Formulas can be used to define the "Increment/Decrement Operations". For details, please see: Using the Increment, Decrement and Operations commands.
- Formulas can be used when designing printed labels to specify what should be placed on a given label.
- You can use formulas when inserting formula-based series in records. For details, please see: Inserting series.
To Enter/Edit a Formula
Formulas available in GS-Base are similar to those used in spreadsheets, with a few exceptions:
- Cell references are replaced by field names. All field names occurring in a given formula refer to one and the same record.
- The range ":" operator is not available. Formulas requiring array/range parameters can use the "array()" function that creates an array out of database fields, strings, numbers, etc.
- References to other tables via the "!" and "_" operators are not available. Instead, there is a separate category of functions: Cross-table summarizing & lookup functions that group functions accessing data from other database tables in the same database. Some of those functions can be used to create links/relations between tables. (See the included "sample.zip" database and its calculated fields used to link the "products" and "orders" tables.)
For example:
to sum up two fields 'subtotal' and 'tax', use the following formula:
=subtotal + tax
to merge two fields 'name' and 'country', use the following formula:
=name & ", " & country
and to obtain a period string representing the difference between two given dates:
=dateDiff("2011-01-01", "2011-12-31") (which returns "P364D")
to find the current age for birth dates stored in the "date" field:
=year(today()) - year(date) - if(month(date) < month(today()), 0, if(month(date) > month(today()), 1, day(date) >= day(today())))
Note: If the "date" field is not a valid GS-Base date field (that is, a text field containing generic date/time YYYY-MM-DD strings that can be formatted to be displayed in the desired local format), the above "date" argument should be replaced by dateValue(date).
A few other examples:
=Unit_Price*Qty
="abc"
=1 + 2
="a" & 'b' & 3
=fv(0.75%, 36, -500, -5500, 0)
=LProg({2,2;1,2;4,0}, {1;1;1}, {14;8;16}, {2; 4},0,,)
=sum(field1, field2, field3, sum(field4, field5, field6))
Numbers and dates used as arguments cannot be formatted. For example, the following expressions are incorrect:
=$1,000.00 + 1
=dateDiff("6/3/11", "8/9/2011")
unless the description specifically states that parameters are arbitrary text strings to be converted to a specific type, like:
=value("$1,000.00") + 1
=dateDiff(dateValue("6/3/11"), dateValue("8/9/2011"))
Text strings should be delimited by either single or double quotation marks.
Formulas should not contain circular field references.
To browse all available formula categories and examples, please see the Field Setup dialog box or the Search dialog box.
Note: Square brackets [,] in parameter lists denote parameters that are optional and can be omitted. For example:
LProg(A, s, b, c, vector, [epsilon], [m])
However, you still have to use the corresponding parameter list separators, that is:
LProg(A, s, b, c, vector,,)
If your current Windows regional settings define commas as decimal separators, use semicolons (;) to separate function arguments, or, if you prefer locale-independent (US) settings, select the generic locale via the Settings > Locales > Generic command.
Available operators:
| Operator | Operation | Comments | Precedence |
|---|---|---|---|
| = | Equal | Compares numbers or text strings (the comparison is not case-sensitive). Example: A1=4, B2="abc" | 6 |
| < | Less than | Compares numbers or text strings (the comparison is not case-sensitive). Example: A1<4, B2<"abc" | 6 |
| > | Greater than | Compares numbers or text strings (the comparison is not case-sensitive). Example: A1>4, B2>"abc" | 6 |
| <= | Less than or equal | Compares numbers or text strings (the comparison is not case-sensitive). Example: A1<=4, B2<="abc" | 6 |
| >= | Greater than or equal | Compares numbers or text strings (the comparison is not case-sensitive). Example: A1>=4, B2>="abc" | 6 |
| <> | Not equal | Compares numbers or text strings (the comparison is not case-sensitive). Example: A1<>4, B2<>"abc" | 6 |
| + | Addition | Adds numbers. | 5 |
| - | Subtraction | Subtracts numbers. | 5 |
| & | String concatenation | Merges text strings. Example: A1 & "abc", "a" & "b" | 5 |
| * | Multiplication | Multiplies numbers. | 4 |
| / | Division | Divides numbers. | 4 |
| ^ | To the power of | Calculates the power of. | 3 |
| - | Negative | Changes the sign of a number. Example: -A1 | 2 |
| % | Percent | Specifies a number entered as a percentage. Example: 12% | 1 |