Table component

A Table component is used to load tabular data from a data source as well as filtering and modifying that data.
This data is organized in a table of records. A record corresponds to one row of the data source and consists of multiple columns, where each column can be a number, text or other value. A table is a grouping of records and provides functions to access a specific record.

You can use a table to:

• fill a ComboBox or DataView control, to allow users to see and choose one record
• access individual records and use its values in your product logic

Properties

A Table component has 2 properties: Query and Value.

• The Query property is where you write a query to load data from a data source.
• The Value property holds the resulting table. It's used automatically when you refer to the table component in other rules, you typically don't need to access it directly.

Using a table component

A typical use case is to select a particular record from a table; this could e.g. be the record that a user selected in a ComboBox or DataView control.

Let's assume we have a table component with the name parts and that it has 3 columns partNumber, name and price.
To select one part from this table, create a value component with the name part and enter this code:

parts.Get(Input or 0) or parts.GetFirst()

You also have to change the Input type of the value component to Number.
When you now type a part number into the Input field of your value component, you should see a part record in the result panel. Try typing different part numbers into the Input field to view different parts!

This value component can now be connected to UI controls to hold a part selected from the parts table.
Let's break down what the code above does:

1. We try to look up a part with a particular number with parts.Get(…)
2. As part number, we want to use Input, which can be connected to a UI control to automatically update
   • To prevent errors, we supply a default value for the Input with Input or 0.
   • Instead of 0 you could use the number of a part you want to be selected by default.
3. In case the Input contains a part number that cannot be found in the table, we use the first record of the table as fallback
   • We get the first record of the table with parts.GetFirst()
   • Now our component will always select a part record, no matter what we type into the Input box!

Queries

To load data from a data source, we can use a so-called query in our Hive code.
Let's assume our data source is called BikeParts, then the simplest query we could write is:

from BikeParts select all

This will load all records from the data source and include all its columns.
We can choose to only load specific records and columns too:

from BikeParts select {
  .partNumber,
  .name,
  .price
}
filter .partNumber > 100

This will produce a table that only has 3 columns – partNumber, name and price – and it will only contain parts that have a partNumber of 100 or greater.

Filtering Data

The filter of a query has no fixed syntax; you can write any Hive code you want, as long as it results in either true or false.
The filter will be run for each record in the data source. When it results in true, the record will be loaded, otherwise it won't.
You can access all columns of the data source in your filter, but also components (though there are some limitations for components).

Here is an example of a more advanced filter:

from BikeParts select all
filter
  if BikeCategory <> "" then .category.toLower() = BikeCategory else true end
  and .netWeight + AccessoryWeight < MaxWeight 

A few things to note:

• a filter can be made optional by using an if/then/else expression
  • in the example we only filter by .category when the BikeCategory component is not an empty text
  • by using true or false in the else branch of an if, we can include or exclude the record
  • we could also rewrite the category filter to not require an if/then/else expression like this:
  • (BikeCatogery = "" or .category.toLower() = BikeCategory)
• you can call functions on column values or use them in calculations:
  • we convert the .category to lower-case before comparing it with BikeCategory
  • we calculate the sum of .netWeight and the AccessoryWeight component and compare the result with MaxWeight

Projecting Data

Data can not only be filtered, it can also be molded to fit your use case: you can choose which columns to include in your table and also generate new columns.

Let's see an example:

from BikeParts select {
  .nr = .partNumber,
  .name,
  .price = .price * if .onSale then 0.8 else 1 end,
}

A few things to note:

• if you want to use a different name for a column, you can just assign that column to a new one
  • in the example, we create a column nr that will hold the value of the partNumber column
  • Note: column names of the resulting table must be unique; you cannot have 2 columns with the same name
• you can adjust the value of a column; in the example we lower the price by 20% when the onSale column is true
  • Note: modified columns are not available in filters or when modifying other columns. For instance, you cannot use .nr in a filter or the .price calculation (you muste use .partNumber). Similarly, when you type .price you will receive the value stored in the data source, not the lowered price! (this might become available in a future update)
• when adjusting a column value, you can use any Hive feature you want (*) – there is no specialized syntax
  • (*) the same limitations as for filters apply, see the Limitations section

Sorting Data

You can change the order in which records are returned by a query by sorting them by one or more values:

// sort first by category, then by net weight
from BikeParts select all
sort by .category, .netWeight desc

The sort by statement must come after a filter (if there is one).
By default, values are sorted in ascending order. You can add desc after a sort expression to sort in descending order (you can also add asc to clarify your intent).

While the value to sort by will typically involve a column, you can also write more complex expressions.

// sort first by rounded weight, then sort so parts without a description come at thet end
from BikeParts select all
sort by
  .netWeight.round(),
  (if .Description = "" then 0 else 1 end) desc

Note that a sort value must be a Number, Text or Logic value.
Furthermore, you must put complex sort expressions in parenthesis (), as in the example above (a column name or function call works without (), but anything more advanced requires them).

Limiting the number of rows

You can optionally restrict how many rows can be returned by a query or skip over a number of records:

// ignore the first record, then return up to 1000 records
from BikeParts select all
sort by .category
skip 1
take 1000

NOTE: this feature is best used in combination with sort by, as the order of rows in your data source may be subject to how it is imported. If you e.g. use a workflow to automatically update a data source, the order of rows may not be guaranteed.

Both skip and take expect a Number. This can be a literal value like 1 or an expression that involves a component like MaxRows – but you cannot use columns, as the limits affect the entire query and a column value belongs to a specific record.
Similar to sort, you must put complex expressions in parenthesis ().

Limitations

When talking about what is permitted in a query (i.e. in the filter or when calculating a column value), we must differentiate between 2 areas:

• the parts of a query that involve a column and are evaluated by the query engine – these have limits
• the parts of a query that do not involve a column, which are evaluated beforehand – these are limitless
  • we call these parts "query parameters"

For instance, it's currently not allowed to use a record component in a filter, because sending an entire record table to the query engine would often be expensive and slow down your query. However, a record component can still be used in a query parameter – considere these two cases:

// not allowed
// a column value is used in Get(), thus the entire component is needed to evaluate the query
filter myRecordComponent.Get(.partNumber).Price < MaxPrice

// allowed
// everything to the right of (>) is a query parameter, as it doesn't involve a column value
// the query parameter is a simple number that can be calculated before the query itself is run
filter .price > myRecordComponent.Get(SelectedPartNumber).Price

The key takeaway is that you don't have to worry about limits in query parameters, only in code that directly involves column values. The system automatically tries to run as much code of the filter as possible before the query is started (i.e. turning those parts into parameters).
💡 If your query results in an error due to limits, try to restructure it so that the parts highlighted by the error do not depend on column values.

These types and features can currently be used in filters:

• Number: all operators (>, >=, <, <=, =, <>, +, *, -, /, mod, ^) and functions (toText, round, floor, ceiling, truncate, atLeast, atMost)
• Text: all operators (+, =, <>, ~=, *) and functions (length, contains, skip, take, replace, toUpper, toLower, toNumber)
• Logic: all operators (not, and, or, =, <>)
• Lists: all functions (length, get, contains)
• if/then/else expressions
• variables
• optional chaining operator ?.