Formulas

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

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.

Menu in Genio

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. The first 3 fields are mandatory and the other 2 are optional ones.

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.

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.

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.

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 this example, by doing "([Player->1])" we are counting all players in the team.

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.

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.

Concatenate rows

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

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.

Changelog

The complete changelog of all notable changes made to Formulas. Descriptions are written as New: or Fixed: for changes that are not considered a maintenance release.

Genio 311.09

• (MVC)(CPP) New: Fixed a behavior with the last value formula, it no longer allows the user to insert an order field and a closing field with different types. If today is specified on the closing, the order field has to be Date or DateTime.

Genio 310.01

• (MVC)(CPP) New: Change to the closing statement behavior. Leaving the field blank will now retrieve the latest record possible, instead of the latest until today's date. A "Today" option was added in order to achieve the previous behavior.
• (MVC)(CPP) Fixed: Fixed an issue where date fields indicated in the closing would be completely ignored.

Genio 308.01

• (MVC)(CPP) New: The conditions of Last Value formulas now use the "==" operator, instead of just "=", to compare values.