Formulas

Formulas play a significant role in defining the logic and calculations needed for the correct functioning of the software applications being built. Formulas allow the implementation of complex calculations in a simplified, more effective and reliable manner.

It's possible to use Formulas in different places on Genio:

  • Defining how to calculate a specific table field;
  • Writing table conditions;
  • Writing form condition;
  • Defining condctions for:
    • Show a field;
    • Block a field;
    • Fill a field.

Formulas on Table Fields

This section explores Genio`s various attribute functionalities available within tables, empowering users to manipulate and analyze data more effectively.

1. Last Value Formula

The purpose of this formula is to get the last value of a given field from a related area. Imagine you have an area "Store" and want to have there the last product that was sold on a given store from the area "Sales", with this formula you can do so.

In order to deploy this formula, we must indicate some information about the data we want to save as well as were to fetch it from. After choosing the formula type to "U1 Last Value" and pressing F11, a menu shows up to help you build the formula, as you can see in Figure 1. The first 3 fields are mandatory and the other 2 are optional ones.

last%20value%20screen

Figure 1: Menu in Genio for Last Value type of Formula.

Read (mandatory) This is where you indicate the area where you'll be fetching the data from.

Sorting (mandatory) This is where you will indicate the field which will sort the data. This has to be a Date or Datetime, Character or a Numeric and its located on the table where you fetch the data from.

Value (mandatory) As the name suggests, this is where you indicate the field you want to fetch, in another words, it's the last record of this field that will be saved in the last value field.

Closing (optional) Here you can indicate a value (from the higher table) to filter up to which values to get. For example, if you specify a date, "2022/04/10", it will get the latest value up to this date. Please keep in mind that the order field type must be the same as the closing field type, for example, if the order is numeric the closing has to be numeric. In case the closing is "Today", the order must be a date. Instead of indicating a specific date, you can also insert the "Today" value which, as the name suggests, gets the latest value up to today's date. Leaving this field blank, will get the latest possible record.

Condition (optional) Here you can indicate a condition simple condition (cannot use && or || ), that lets you filter the data based on if it's true of false. For example, you want to get the last active record with the name "test", you would write something like the following:

[TST1->NAME]=="test"

Limitations

  • You can only fetch fields from a lower table, for example, table A and table B are related, and A has a secondary key that points to B. You can have the field with the last value formula in B, getting the data from A, but you cannot do the same the other way around.

  • The information on the field containing the formula only gets updated during reindexation, when a record from the lower table (the one where we are fetching the data from) gets updated or if the closing is "Today", it will update everyday.

2. Arithmetic

Arithmetic functionality facilitates computational operations involving table attributes. This feature allows users to perform mathematical calculations, such as addition, subtraction, multiplication, and division, on the specified table attributes. By utilizing arithmetic operations, users can derive new values based on existing attribute values, enabling various data transformations and analysis.

In Figure 2 it's possible to see an example of this kind of formula.

arithmetic

Figure 2: Example of data field insertion using Arithmetic type of Formula.

3. Replica

The replica feature ensures that a specific attribute in one table possesses an identical value to an attribute derived from another related table. This functionality establishes a connection between the two tables, synchronizing the values of the designated attributes. Any changes made to the source attribute in the original table automatically propagate to the replica attribute in the linked table, maintaining consistency and integrity between the related data entities.

In Figure 3 it's possible to see an example of this kind of formula.

replica

Figure 3: Example of data field insertion using Replica type of Formula.

4. Linked Sum

The linked sum operation involves calculating the cumulative total value of a specific attribute across multiple instances or records in a related or linked table. By applying the linked sum functionality, users can aggregate the attribute values from each associated record and obtain the sum as the overall result. This capability is particularly useful for performing data analysis, summarizing information, and obtaining cumulative insights across linked data entities.

In Figure 4 it's possible to see an example of this kind of formula.

linkedsum

Figure 4: Example of data field insertion using Linked Sum type of Formula.

In this example, by doing "([Player->1])" we are counting all players in the team.

5. Lookup Formulas

Lookup formulas allow you to fetch values from a table based on specified criteria, such as dates and optional filters. They are commonly used in scenarios where you need to retrieve specific information, such as prices, from a separate table that is unrelated to the current context.

In Figure 5 it's possible to see an example of this kind of formula.

lookup

Figure 5: Menu of data field insertion in Genio for Lookup Formula.

The goal is to find the price of an article on a specific date of purchase.

Read: Refers to the table that needs to be looked up. In the given example, it is referred to as "PRICE," which is a price list.

Link: Specifies the connection between the current context and the table being looked up. In this case, it is "PURCH->DATACOMP" . We provide the date of purchase as the connection as it will help us find the relevant price in the table.

Sorting: Specifies the order or arrangement of the data in the lookup table. In the example, it is "PRICE->DATAPREC", so we are sorting it by the date to which the price was set.

Value: Refers to the specific value we want to retrieve from the lookup table. In the example, it is "PRICE->PRICE". The value we want to find is the price itself.

Optional Filters: Additional criteria that can be applied to narrow down the search in the lookup table. In this case, there are two filters:

Filter 1 (src): Refers to the field in the lookup table that represents the establishment that set the price. It is specified as "PRICE->CODESTAB."

Filter 1 (dst): Refers to the field in the current context (purchase) that represents the establishment of the purchase. It is specified as "PURCH->CODESTAB."

CT Lookup formula Will return the table price by the date specified.

CS Lookup formula Returns the price immediately after the specified date.

6. Concatenate rows

The formula Concatenate rows is used to concatenate or merge text from a specific field within a related table.

In Figure 6 it's possible to see an example of this kind of formula.

concatenate_rows

Figure 6: Example of data field insertion using Concatenate rows.

Read: This parameter refers to the table that is being queried or searched. In the context of this formula, it is denoted as "RESPO".

Value: This parameter specifies the field within the "RESPO" table that contains the text values to be concatenated. It is identified as "RESPO->name".

Sorting: This parameter relates to the field within the "RESPO" table that dictates the order in which the fields will be read and concatenated. It is denoted as "RESPO->field".

Separator: The separator parameter defines the text that will serve as a delimiter between the concatenated fields. In the formula, it is indicated as "RESPO->text".

To utilize the formula effectively, you would provide the relevant query table ("RESPO"), the specific value field ("RESPO->name") containing the text to be concatenated, the ordering field ("RESPO->field") dictating the concatenation sequence, and the desired separator text ("RESPO->text") to be inserted between the combined fields.

Logical operators

== : Equal-to operator. Used to compare the values of two operands to check if they are equal. Returns a "boolean" value, True if both operands are equal and False otherwise.

!= : Not-equal-to operator. Used to compare the values of two operands to check if they are not equal. Returns a "boolean" value, True if the operands are not equal and False if they are.

> : Greater-than operator. Used to compare the values of two operands to check if the value of the left operand is greater than the value of the right operand. Returns a "boolean" value, True if the value of the left operand is greater than the value of the right operand and False otherwise.

< : Less-than operator. Used to compare the values of two operands to check if the value of the right operand is greater than the value of the left operand. Returns a "boolean" value, True if the value of the right operand is greater than the value of the left operand and False otherwise.

>= : Greater-than-or-equal-to operator. Used to compare the values of two operands to check if the value of the left operand is greater than or equal to the value of the right operand. Returns a "boolean" value, True if the value of the left operand is greater than or equal the value of the right operand and False otherwise.

<= : Less-than-or-equal-to operator. Used to compare the values of two operands to check if the value of the right operand is greater than or equal to the value of the left operand. Returns a "boolean" value, True if the value of the right operand is greater than or equal to the value of the left operand and False otherwise.

Functions and Examples

iif() function

This function takes in 3 parameters, it should be called like so : iif(1., 2., 3.).

  1. A certain condition returning a "boolean" value, so either True or False. For example using the "==" operator to compare the value of two different fields.
  2. The instruction that will be executed if the outcome of the condition is True.
  3. The instruction that will be executed if the outcome of the condition is False.

Here is an example of using the iff() function : iif([ITEM->Stock] == 0, RGB(255,0,0), RGB(0,255,0))

  • [ITEM->Stock] == 0: [ITEM->Stock] is a field that manages the stock of an item for example. When it equals to 0, this condition will return True, otherwise it will return False.
  • RGB(255,0,0): What will be executed if the condition is True.
  • RGB(0,255,0): What will be executed if the condition is False.

(In this example, if the field [ITEM->Stock] is equal to 0, wherever this function iif() was called, a row for example, will be colored in red, and in green otherwise.)

isEmptyX() functions

The isEmptyX() functions test for empty or null values. It returns a "boolean", True if the argument given to the function is empty or null and False otherwise. This function takes in 1 parameter, it should be called like so : isEmptyX(1.).

  1. A field where checking if it is empty makes sense.

Note: The 'X' has to be replaced by 'C' (Text value), 'D' (Date value), 'G' (Key value), 'L' (Boolean value), 'N' (Numeric value) or 'T' (Time value) based on the data type of the field that is being tested.

Note : adding '!' before the isEmptyX() function like so : !isEmptyX() will instead check if the field given as an argument is not empty. Returning True if it is not empty and Flase if it is.

Here is an example of using the isEmptyX() function : iif(isEmptyC([PERSON->Name]), "No Name", [PERSON->Name])

  • isEmptyC([PERSON->Name]): will check if the field [PERSON->Name] is empty. Return True if it is, False if it is not.
  • "No Name": If the condition returns True, the iff() function will return "No Name".
  • [PERSON->Name]: If the condition is False, the iif() function will return the existing value in the [PERSON->Name] field.

Note: These functions can be used in conditional expressions. Just like we've seen with the EMPTY() function used as a parameter of the iif() function, the iif() function can be used as a parameter of an iif() function.

Genio Base Formulas Description

Table 1 - Date Comparison Functions
Function Signature Detailed Description Return Type
ComparaDatas(Date t1, Date t2) Compares two dates and returns an integer indicating the relationship between them.
Returned value: Less than zero - t1 is earlier than t2; Zero - t1 is the same as t2; Greater than zero - t1 is later than t2.		 Numeric
DateCompare(Date t1, Date t2) Similar to ComparaDatas, compares two dates and returns an integer indicating their relationship.
Returned value: Less than zero - t1 is earlier than t2; Zero - t1 is the same as t2; Greater than zero - t1 is later than t2.		 Numeric
Table 2 - Date Creation Functions
Function Signature Detailed Description Return Type
CreateDate(Numeric year, Numeric month, Numeric day) Creates a date from year, month, and day components. Date
CreateDateTime(Numeric year, Numeric month, Numeric day, Numeric hour, Numeric minute, Numeric second) Creates a date with time included. DateTime
CreateDuration(Numeric days, Numeric hours, Numeric minutes, Numeric seconds) Creates a duration or timespan from its components. Duration
Table 3 - Date Addition Functions
Function Signature Detailed Description Return Type
DateAddDuration(Date date, Duration duration) Adds a duration to a date. Date
DateAddHours(Date date, Numeric hours) Adds a specified number of hours to a date. Date
DateAddMinutes(Date date, Numeric minutes) Adds a specified number of minutes to a date. Date
DateAddSeconds(Date date, Numeric seconds) Adds a specified number of seconds to a date. Date
DateAddDays(Date date, Numeric days) Adds a specified number of days to a date. Date
DateAddMonths(Date date, Numeric months) Adds a specified number of months to a date. Date
DateAddYears(Date date, Numeric years) Adds a specified number of years to a date. Date
Table 4 - Date Difference Functions
Function Signature Detailed Description Return Type
DateDiff(Date startDate, Date endDate) Calculates the duration between the start and end dates. Duration
DateDiffPart(Date startDate, Date endDate, Text unit) Calculates the duration between the start and end dates and returns the difference in the specified unit. Parameter unit can be D (days), H (hours), M (months), or S (seconds). Duration
Table 5 - Date Manipulation Functions
Function Signature Detailed Description Return Type
DateFloorDay(Date date) Truncates the time part of a datetime value. Date
DateGetDay(Date date) Gets the day of the month from the date. Numeric
DateGetMonth(Date date) Gets the month of the year from the date. Numeric
DateGetYear(Date date) Gets the year from the date. Numeric
Table 6 - Time Retrieval Functions
Function Signature Detailed Description Return Type
DateGetHour(Date date) Gets the hour of the day from the date. Numeric
DateGetMinute(Date date) Gets the minute of the hour from the date. Numeric
DateGetSecond(Date date) Gets the second of the minute from the date. Numeric
Table 7 - Date and Time Setting Functions
Function Signature Detailed Description Return Type
DateSetTime(Date date, Text time) Sets a specific time on a date. Date
DateSubstractDuration(Date date, Duration duration) Subtracts a duration from a date. Date
Table 8 - Day Component Functions
Function Signature Detailed Description Return Type
Day(Date date) Retrieves the day component of the date. Numeric
DoubleToHoras(Numeric value) Converts the number of hours elapsed from 00:00 to a string representing the time. Text
Table 9 - Duration Functions
Function Signature Detailed Description Return Type
DurationTotalDays(Duration duration) Gets the total days in the duration. Numeric
DurationTotalHours(Duration duration) Gets the total hours in the duration. Numeric
DurationTotalMinutes(Duration duration) Gets the total minutes in the duration. Numeric
DurationTotalSeconds(Duration duration) Gets the total seconds in the duration. Numeric
Table 10 - Miscellaneous Functions
Function Signature Detailed Description Return Type
GUIDCreate() Generates a new GUID. Text
GetAppThemeVariable(Text variable) Retrieves the variable of the current app. Text
GetEph(Text ephID) Retrieves the first value of a certain EPH. ephID is the EPH identifier. Key
GetLevelFromRole(Numeric level, Text roleId) Retrieves the corresponding access level for roles of type "Level". Returns 0 otherwise. Numeric
Table 11 - Color Functions
Function Signature Detailed Description Return Type
HEXCOLOUR(Text Hex) Returns an RGB color value. The hashtag (#) before the number is not required. RGB
NCOLOUR(Text colour_name) Returns an RGB color value based on the name of the color. RGB
RGB(Numeric Red, Numeric Green, Numeric Blue) Returns an RGB color value based on the RGB components. RGB
Table 12 - String Manipulation Functions
Function Signature Detailed Description Return Type
LEFT(Text value, Numeric length) Retrieves the left substring from a string, based on the specified length. Text
LTRIM(Text value) Removes all white-space characters at the beginning of the string. Text
LengthString(Text value) Gets the number of characters in the string. Numeric
RIGHT(Text value, Numeric length) Retrieves the right substring from a string, based on the specified length. Text
RTRIM(Text value) Removes all white-space characters at the end of the string. Text
SubString(Text value, Numeric startIndex, Numeric length) Retrieves a substring from a string, starting at a specified index and with a specified length. Text
Table 13 - Date Component Functions
Function Signature Detailed Description Return Type
Month(Date value) Retrieves the month component of the date. Numeric
Year(Date value) Retrieves the year component of the date. Numeric
Table 14 - Mathematical Functions
Function Signature Detailed Description Return Type
abs(Numeric value) Returns the absolute value of a double-precision floating-point number. Numeric
floor(Numeric value) Returns the largest integer less than or equal to the specified number. Numeric
sqrt(Numeric value) Returns the square root of a specified number. Numeric
Table 15 - Deprecated Functions
Function Signature Detailed Description Return Type
Agora Deprecated: Use [Now] instead to get the current date and time. -
Ano Deprecated: Use [DbYear] instead to get the current database year. -
CriaData Deprecated: Use CreateDateTime instead to create a date with time. -
CriaDataHora Deprecated: Use DateSetTime instead. -
Table 16 - Comparison and Validation Functions
Function Signature Detailed Description Return Type
emptyC(Text value) Checks if the specified string is null or empty. Returns 1 if true, 0 otherwise. Numeric
emptyD(Date value) Checks if the specified date is null or empty. Returns 1 if true, 0 otherwise. Numeric
emptyG(Key value) Checks if the specified key (GUID) is null or empty. Returns 1 if true, 0 otherwise. Numeric
emptyL(Logic value) Checks if the specified boolean (Logic) is null or false. Returns 1 if true, 0 otherwise. Numeric
emptyN(Numeric value) Checks if the specified number is null or zero. Returns 1 if true, 0 otherwise. Numeric
emptyT(Text value) Checks if the specified time is null or empty. Returns 1 if true, 0 otherwise. Numeric
Table 17 - Color Functions
Function Signature Detailed Description Return Type
HEXCOLOUR(Text Hex) Returns an RGB color value based on the provided hex code. RGB
NCOLOUR(Text colour_name) Returns an RGB color value based on the provided color name. RGB
RGB(Numeric Red, Numeric Green, Numeric Blue) Returns an RGB color value based on the provided RGB components. RGB
Table 18 - Conversion Functions
Function Signature Detailed Description Return Type
atoi(Text value) Converts the string representation of a number to its 32-bit signed integer equivalent. Numeric
IntToString(Numeric value) Converts a numeric value to its string representation. Text
NumericToString(Numeric value, Numeric digits) Converts a numeric value to its string representation with specified number of fractional digits. Text
StringToInt(Text value) Converts the string representation of a number to its 32-bit signed integer equivalent. Numeric
Table 19 - Miscellaneous Functions
Function Signature Detailed Description Return Type
GUIDCreate() Generates a new GUID. Text
GetAppThemeVariable(Text variable) Returns the value of a specified variable in the current app theme. Text
GetEph(Text ephID) Returns the first value of a specified EPH. Key
GetLevelFromRole(Numeric level, Text roleId) Returns the corresponding access level for roles of type "Level". Numeric
Table 20 - Mathematical Functions
Function Signature Detailed Description Return Type
abs(Numeric value) Returns the absolute value of a given number. Numeric
floor(Numeric value) Returns the largest integer less than or equal to the specified number. Numeric
sqrt(Numeric value) Returns the square root of a specified number. Numeric
Round(Numeric value, Numeric digits) Rounds a number to a specified number of fractional digits. Numeric
RoundQG(Numeric value, Numeric digits) Rounds a number to a specified number of fractional digits with a clearance of 0.001. Numeric
Table 21 - Deprecated Functions
Function Signature Detailed Description Return Type
Agora Deprecated: Use [Now] instead. -
Ano Deprecated: Use [DbYear] instead. -
CriaData Deprecated: Use CreateDateTime instead. -
CriaDataHora Deprecated: Use DateSetTime instead. -
GetCurrentDay Deprecated: Use [Today] instead. -
GetCurrentMonth Deprecated: Use Month([Today]) instead. -
GetCurrentYear Deprecated: Use Year([Today]) instead. -
Hoje Deprecated: Use [Today] instead. -
isValid Deprecated: Use !emptyD instead. -
Sigla Deprecated: Use [ClientId] instead. -
[Year] Deprecated: Use [DbYear] instead. -
Table 22 - Special Variables
Variable Detailed Description Return Type
[ClientId] Returns the client initials. Text
[DbYear] Returns the year of the current database. Numeric
[False] Returns a Boolean value of false. Boolean
[FormMode] Returns the mode that the form is opened in. Text
[Module] Returns the module initials. Text
[Now] Returns the current date and time. Date
[Today] Returns the current date. Date
[True] Returns a Boolean value of true. Boolean
[Username] Returns the username of the current user. Text

Some examples of the previous formulas are presented here:

  • ComparaDatas([TABEL->DATA],[TABEL->DATAFIM])
  • DateCompare([TABEL->DATA],[NOW])
  • CreateDate(2022,01,31)
  • CreateDateTime(2022,01,31,08,30,0)
  • CreateDuration(0,0,30,0)
  • DateAddDuration([TABLE->DT],CreateDuration(0,0,30,0))
  • DateAddHours([TABLE->DT],2)
  • DateAddMinutes([TABLE->DT],3)
  • DateAddSeconds([TABLE->DT],30)
  • DateAddDays([TABLE->DT],1)
  • DateAddMonths([TABLE->DT],4)
  • DateAddYears([TABLE->DT],10)
  • DateDiff([TABLE->DT],[Today])
  • DateDiffPart([TABLE->DT],[Today],"D")
  • DateFloorDay([TABEL->DATA])
  • DateGetDay([TABEL->DATA])
  • DateGetMonth([TABEL->DATA])
  • DateGetYear([TABEL->DATA])
  • DateSetTime([TABLE->DATE],[TABLE->TIME])
  • DateSubstractDuration([TABLE->DT], CreateDuration(0,0,30,0))
  • Day([TABAC->DATA])
  • DoubleToHoras([TABEL->NUM])
  • DurationTotalDays(CreateDuration(1,24,0,0))
  • DurationTotalHours(CreateDuration(1,24,0,0))
  • GUIDCreate()
  • GetAppThemeVariable("$primary")
  • GetEph("EMPLOYEE")
  • HEXCOLOUR("A0FF03")
  • HasRole("ADMIN")
  • HorasAdd([TABEL->HORA],90)
  • IndexOf("QWEB|MVC|VUE","VUE")>=0
  • IntToString([TABEL->NUM])
  • IsFeatureActive("NGERA")
  • JoinGeometries(list)
  • KeyToString([INDIC->CODINDIC])
  • LEFT([TABEL->NAME],4)
  • LTRIM([TABEL->NAME])
  • LengthString([TABEL->NAME])
  • Month([TABAC->DATA])
  • NCOLOUR("WHITE")
  • NumericToString([TABEL->NUM],2)
  • RGB(255,255,0)
  • RIGHT([TABEL->NAME],4)
  • RTRIM([TABEL->NAME])
  • Round([TABEL->NUM],2)
  • RoundQG([TABEL->NUM],2)
  • SomaDias([TABAC->DATA],10)
  • SplitGeometry([TABLE->GEOFIELD])