In the previous lesson #
We used the Excel Importer to add new objects to the Employee class. In Creating Your First Application, we also introduced the Livetable as a tool to examine objects/rows in the Employee class/table.
Explore the imported data #
Now that we have a sufficiently large and heterogeneous dataset at our disposal, we can delve deeper into the use of the Livetable and illustrate the tools that this web client component offers to nimbly explore and manage the large amounts of data in Livebase applications.
Use and customize the Livetable #
Start Workforce, log in to the application and open the Employee Livetable. It should look like in the picture and show all the new records coming from the Excel file.
We can customize the way records are displayed to accommodate different requirements: for example, we can focus on one set of data at a time by resizing or hiding columns, or by grouping, sorting, and filtering rows according to different conditions.
Finally, we can extract more information from the data by displaying statistics or defining derived attributes directly in the table in the form of computed columns.
A strong point of Livetable is that all customizations of its view are relative to the single user: this allows different users to adapt the view to their specific needs but in fact interacting with the same component. Moreover, the application remembers the customizations of each user and presents them again in subsequent sessions.
Puoi ripristinare la visualizzazione di default in ogni momento cliccando sull’icona
Restore default view in basso a destra.
1. Select and count records #
Let’s start with the basics: you can select a record by clicking on its row ’ and then inspect, edit or delete that record; you can select multiple records by holding down Ctrl and clicking on individual rows, or select one and ’ while holding down Shift ’ click on another row to select all records within that range. The only operation allowed on a multiple selection is removal.
Observe the bottom panel. On the left, a small pie chart () and a counter dynamically highlight how many records were selected relative to the total count (). At the same time, the graph is a shortcut you can click on to select all the records shown () or cancel the current selection.
2. Search records #
You can search for records having a specific string as the value of one of the attributes. To do that, open the search panel by clicking on the icon, type the string and click
Search ’ or press Enter. You can search for a string in all columns or expand the checkbox and narrow the search to a set of columns.
For example, type
doe and check the Last name column. Launching the search, the table scrolls to the first occurrence (the row for employee John Doe), which appears highlighted in yellow. The search icon is replaced by a box containing the searched string, while the left side shows the number of results found (two in this case). You can scroll through the search results by clicking on the two arrows ( and ) or end the search
3. Move, resize, hide columns #
In some cases ’ like ours ’ the table has many columns and it is difficult to navigate through all this information; however, we can rearrange the structure of the Livetable to our liking by using the headers of the columns:
- to reorder the columns, drag and drop their headers to the point in the table where you want them to appear;
- to resize columns, click on the right edge of the header and drag it to the desired length;
- to collapse a column to the minimum width (but without hiding it), double-click on the right edge of the header.
Let’s try to hide some columns, such as the derived attribute Full name: right-click on the header of its column and select
Hide to hide it. Notice how the hidden column icon is now highlighted in orange; hovering over it reads
1 hidden column.
You can display the hidden column again by clicking on the icon and selecting it from the Hidden columns panel. This way the column reappears in the table in the last position.
4. Sort the table #
By default, the records in a Livetable appear in the order in which their respective objects were created; in fact, you should see the first 6 objects we manually inserted at the top. To impose a specific order, just click on the header of a column: for example, click on Last name and the rows will be shown in increasing order with respect to the last name. By clicking again on the header you can impose a descending order.
You can add more groupings from the Sorting panel, which you can access by clicking on the icon. Click
Add, and add the sorted First name column as
ascending. Click Ok to confirm the sorting.
In this way, rows with the same last name will be ordered in turn with respect to the first name. Thus, for example, John Doe will come before Rici Doe.
5. Filter records #
In scenarios with numerous tables it can be useful to restrict the display to a specific set of records. For this purpose we can define filters from the Filter Manager, which you can access by clicking on the .
Filters can be simple or advanced. A simple filter uses a set of operators dependent on the data type of the column, and is therefore “simpler” to use. Let’s define one: click
Add basic to add a new filter to the list with the following fields:
- Column: select
Positionfrom the drop-down menu;
- Operator: select
- Value: type
Make sure the flag next to the filter is checked and the Visualization policy you choose is
Show objects evaluated TRUE, and click
Ok to apply it. The Livetable only shows employees who are software developers, while the other rows have been hidden. You can verify this by checking the counter, which shows
102 of 505 objects; also, the icon has turned orange, and hovering over it you can read
1 filter defined, 1 applied.
Let’s define another filter but this time an advanced one; for example, suppose we want to display only employees hired after 2015. Open the Filter manager again and click on
Add advanced; this filter has only one field where we have to write an expression. By clicking on the Σ next to the field you can open the
Expression wizard, containing the same functions we saw for the Function Picker in the Designer.
Type in the expression
getYear(date_joined) > 2015 and apply the filter. This way we have imposed both filters at the same time and only the 36 rows that satisfy them are shown.
You can show all records again by unchecking the active filters to disable them, or by deleting them by clicking on the
6. Define calculated columns #
In addition to filters, we can define calculated columns from an expression involving the values of other columns, similar to a derived attribute of the model. Look at the table: for some employees we ’ intentionally ’ omitted the phone number, for others the email address, and for some both. We want to identify employees who have no contact information so that we can add one; to do this, we can define a boolean computed column that is true if both Phone number and Email address are null.
Open the Computed columns panel by clicking on the icon, and select
Add column. Give the column a name, such as
NoContact, type the expression
isNull(phone_number) && isNull(email_address), and click
The column appears at the end of the table and can be used like any other column, to define groupings, filters, and even other calculated columns. For example, combining the column we have just defined with a simple filter like
NoContact = true we can quickly access the 9 employees who have no contact information, as in the figure:
7. Group and order by multiple columns #
To further simplify the navigation in the Livetable we can exploit the presence of repeated values in some columns to subdivide the rows into groups. For example, suppose we want to display all employees with position of Software Developer together; we can define a grouping based on the Position attribute. To do this, right-click on the column header and select
The list of objects is collapsed; the column is colored orange and is placed at the top of the table, indicating that it is used to group records; note in fact that 6 groups have been formed, one for each Position value ’ plus a group for rows with zero Position, containing the first five records that we manually inserted into the database.
You can expand a group by clicking on the next to it. As in the figure, the rows contained in it will be shown in the table on the right. Of course, you can collapse an expanded group by clicking on the .
Moving over a collapsed group displays a tooltip, as in the figure, indicating the number of objects in the group; for example, in the Designer group there are 76 objects.
Let’s now subdivide the records by Team, instead of by Position. To do this, there are two ways: you can right-click on the Position header and select
Ungroup, right-click on Team and select
Group By, or simply select the Team header directly and choose
You should see the Team column in orange at the head of the table, followed by Position returned normal. There are 6 groups in total, plus a group for records that don’t have a Team.
So far, we’ve defined only one level of grouping, but we can add more: for example, we can further subdivide the employees in a team by position. To do this, right-click on the Position header and select
Grouping levels can be sorted independently of each other and the rest of the table columns. For example, set an ascending sort for Team and a descending sort for Position. Also set a descending sort by Hourly cost. The result should be similar to the one in the figure:
8. Display statistics on columns or groupings #
The Livetable allows you to quickly display statistics for columns that contain numerical values. In our case, there is a chart icon () on the headers of the Age and Hourly cost columns. Clicking on either of them will show a histogram, which gives the distribution of values for that column for all records in the table. Shown in the figure, for example, is the one for Hourly cost.
The sum, average, and standard deviation of the values are shown on the right side of the chart; the interval of the hourly cost values for employees is shown at the bottom. By hovering over individual bars you can highlight additional information, such as the count of occurrences in a given range. To close the panel click anywhere in the Livetable.
Statistics are also available for individual groupings. For example, group the employees by Team again, and look at the Hourly cost column: at each collapsed group the graph icon is shown again. By clicking on it, you can see the histogram of the distribution of the hourly cost related to the employees of that specific team.
You can take advantage of grouping to show statistical data directly in the collapsed columns. To do this, again grouping by Team, right click on the header of Hourly cost and select
Statistics... to open the panel in the figure; from here you can check values to show for each group directly in the Hourly cost column.
For example, check the aggregation operators
std and click
Ok. As you can see in the figure, the selected statistics will be shown in orange for each collapsed group as sub-columns of Hourly cost.
Finally, by using grouping, we can also show statistical values for columns that do not contain numerical values. For example, we want to see how many employees in each team do not have a phone number or email address.
Group by Team again, right click on Phone number and select
Statistics again. The panel that is shown is similar to the one above, but contains only the three counting options
null and click
Ok. Repeat the same process for Email address.
The Livetable appears as in the figure, with the count of the null values of the two columns next to each team.
9. Export Livetable data #
The last function we see is the export tool. By clicking on
Export data you can access the panel in the figure, which allows you to download the Livetable content in a .csv file, a lightweight excel-compatible format.
By default, the export is for the rows and columns that are currently displayed in the Livetable, consistent with the filtering rules and active columns. If you want to ignore the customizations and export all data, you can check the two options
Include hidden columns and
Include filtered out rows. To start downloading the file click on
With this lesson we conclude our digression on data management in Livebase. For more information, you can refer to the guides in the Importing data section.
In the next lesson… #
We will introduce relationships and use them to connect Employee to new classes in the model: Modeling with relationships.