Guide on Advanced Data Modeling and Analytics Using Dynamic Tables

If your software product has an extensive number of data tables, you may notice that they look similar on individual application pages. Differences between them are due to the context of use. The same item can be represented in various ways depending on who uses the data.

For sales department employees, the table must show the name of the product they sell and pricing information, including costs for shipping, storage, depreciation cost, etc. In turn, procurement and warehouse management teams are more interested in the quantity of the product in the warehouse, average consumption rate, forecast date of the next purchase, and so on. Since these data tables describe the same entity, they will have some similarities. The context of use determines the difference in their content.

In such a scenario, developers usually implement several data tables, each for its specific use case. However, there’s a more optimal approach to solving this challenge. Dynamic tables can alter the data they show according to their configuration. Today, we’ll consider their main features and examples of use.

What Is a Dynamic Table?

At the product development stage, developers create the table and specify how to configure it. Next, they insert the table with the required configuration on specific app pages. The critical factor is how the configuration is set. There are the following options:

Statically during the development. Developers specify the table configuration and data to display in the application code;
Dynamically by users. Users configure the tables according to their needs;
Dynamically by managers. Here, managers configure the product according to the end users’ requirements and and permissions.

Here, by “configuration”, we mean both the visual representation of data (column order, show/hide the column, page size, etc.) and its structure (data as it comes from the server). Dynamic tables don’t simply hide data that shouldn’t be shown to specific users but don’t load any of it at all. For example, if the table has 20 columns and is configured to display 7 of them, only data for these 7 columns will load from the server.

Major Benefits of Using Dynamic Tables

The most obvious benefit of choosing dynamic tables is that, in this case, developers don’t have to duplicate their work. Building dynamic tables is more complicated, longer, and more expensive than static ones. But let’s suppose the app has multiple tables. Here, building and using a dynamic table many times would be easier than creating dozens of separate static tables. Especially if they work with similar data sets. Moreover, you can provide product users with a powerful tool for table customization.

Benefits increase dramatically if your product is a SaaS solution and you distribute the right to use it by subscription. Companies that use your SaaS app have different preferences, needs, and habits. They can quickly configure an existing dynamic table to suit their current requirements. The best part is that no additional effort will be required from you or the developers. A subscriber company’s manager or administrator with the rights to configure tables can make all needed adjustments.

Read Also SaaS Product Checklist Creation for Successful Launch

Where to Use Them

Dynamic tables are an excellent choice for SaaS solutions or any software product that operates an extensive quantity of tables. For example, they can be used for:

Enterprise systems, such as ERP, CRM, HRM, WMS, EAM, etc.;
Marketplaces;
Accounting and reporting systems.

Suppose your app has several similar tables, you don’t know beforehand what type of data the users will need to access, or you want to provide them with vast customization possibilities. In that case, you should think of using dynamic tables.

How to Get Started With Dynamic Tables

At the requirements generation stage, developers separate dynamically configured properties and table behaviors from those that will be configured statically. Following our rich experience in developing products with dynamic tables built with Webix and DHTMLX libraries, we usually use the following rules:

Table Property	Dynamic	Static
Column Name	v
Column Name Alignment (Left/Right)	v
Column Number	v
Draggable Column (Enabled/Disabled)	v
Column Availability	v
Ability to Hide/Show a Column	v
Ability to Freeze a Column	v
Column Data Type	v
Value Validation	v
Required Value	v
Max. Number of Characters	v
Business Rules Validation	v
Grouping by Repeated Value in Column	v
Editable/Non-editable	v
Inline Editor Type	v
Array of Values for the Dropdown List	v
Value Alignment (Left/Right)	v
Column Filter Type	v
Default Column Width (if not adjustable)	v
Min. Column Width (when the screen size is reduced)	v
Column Width Shrinks When Screen Width Changes	v
Row Height		v
Default Columns Width Adjusts to the Value		v
Font		v
Color and Size of Font and Grid		v
Boolean Values Visualization (checkbox, icon, text)		v

Separately, the following properties are dynamically set for the entire table:

Default sorting;
Default grouping;
The default number of lines per page;
Columns visible by default (when first opened by the user);
Context menu options.

Nothing forbids developers from dynamically configuring those features we marked as “static” in this table, such as row height or font. However, in practice, it can damage the UI, leading to user dissatisfaction. Therefore, to ensure the application is usable and visually attractive, we recommend disabling their dynamic configuration and keeping them static.

Configuring Dynamic Tables

According to the configuration method, tables can be divided into four types, each of which includes the previous one:

Static configuration during development. Developers describe table configuration and data to display in the code. This approach speeds up the implementation of similar tables;
Dynamic configuration by users. Users configure the tables according to their needs, using the UI developed for this purpose.
Dynamic configuration by managers. Here, managers can configure tables for the users through a special UI. Also, they can restrict access to some columns of the table, depending on the user’s access level;
Dynamic configuration with Query Builder. In addition to the previous option, managers can define the criteria for filling the table with data from the selected data sources.

Static Configuration

Here, developers are fully responsible for configuring the data tables. In the app’s code, they can assign the necessary values to the attributes of the dynamic table to get the desired result. Users can only access these tables via the front end and don’t have any possibility to change them.

Dynamic Configuration by Users

In this case, developers must provide access to the UI allowing users to configure tables. Let’s look at an example of a user interface for configuration settings:

Source: UI/UX Modernization for a Construction Management Web App

There are 25 columns in this table, but according to the default configuration, only 7 are visible when first opening the app. Here’s how users can change the configuration:

Select columns to load from the server by marking the corresponding checkboxes in the pop-up window;
Change the column name by clicking it in the pop-up window;
Arrange columns by dragging them;
Configure width by resizing columns in the table.

Created configuration can be saved for the user on the currently used device or all devices. Such a feature is helpful when users work on many devices with different screen sizes. The next time the user opens the application, it’ll receive from the server a table with already configured columns, names, width, and all other settings. All data in the table will be displayed as the user wants.

Dynamic Configuration by Managers

In this scenario, managers have extended possibilities of configuring data tables through a specially designed module. It allows changing the table’s view and behavior. For example:

Determine available columns by selecting them from resources available on the server;
Choose default names, order, and width for the columns;
Choose a default column for sorting;
Choose columns to group by duplicate values;
Enable or disable the ability to hide and drag specific columns;
Define filter types for table columns;
Choose the criteria by which the table will be filled with data coming from the server.

The configuration created by the manager will become the default one. It will determine what users will see opening the table for the first time.

Dynamic Configuration with Query Builder

Some products require a more robust configuration tool for data tables and also work with other types of data representation and visualization. Here we can, first of all, note Business Intelligence (BI) solutions. Implementing such software requires understanding the internal structure of the product and will include:

Combining multiple data sources in one visualization;
Setting criteria for selecting data from a data source (Webix Query Builder can speed up development);
Switching between different visualization types: table, graph, heatmap, etc.

Our Webix Report Manager Demo shows what a UI for creating data table configuration may look like. Its module for creating and editing reports visualized as a table looks like this:

Here, users can choose one or more data sources, create a new database query, choose columns to display, create, edit, delete, or add filters and sorting. Also, users can apply various functions to the table:

Choose columns for grouping according to specific criteria;
Select columns to sort and choose sorting direction;
Create, edit, and delete columns that display data according to a set of particular functions.

The last one is achievable through controls from the Functions and Column areas of the demo app. The Function area allows setting the column data processing function. You can choose one of the following options:

Sum (sum of all column values);
Count (number of all column values);
Average (average value);
Max (maximum value);
Min (minimum value).

The Column area allows selecting a specific column to which the selected function will be applied. The selector dropdown only contains columns with numeric values. You can change the name of the additional column and create categories (group names) for field values.

Adding Dynamic Tables to a Product

Creating dynamic tables when you build a product from scratch is relatively easy. The tricky thing is that when you start a new project, you don’t know how it will evolve in the future. Developers follow requirements and their own expertise to deliver a software product that will help the customer reap all the benefits of adopting it. However, even a well-proven software solution may sometimes require adjustments and additions. It brings us to the following question: “How can you add a dynamic table to an existing product?”

Webix is a JavaScript UI library that provides access to over 100 fully customizable widgets and controls. Each of these components is independent, but you can combine them using Webix Jet. In our case, component independence is a pretty important feature since it allows modernizing the UI by adding dynamic tables to an existing app.

The solution you use already sends and receives some data for tables to work. The task is to find a balance between the required dynamic features and the necessary amount of code changes. Here, much depends on the tech stack used for building the initial software product and its developers’ adherence to best programming practices. Before diving into the code, it’s hard to say whether the modernization process will cause some significant issues. If developers followed best practices, we have always been able to implement dynamic tables, even if the product was developed a long time ago.

Conclusions

In conclusion, you can check our use case of implementing dynamic tables when switching to a new UI/UX and reusing the existing back-end structure. There, you’ll learn how our developers have replaced a static table inside a complex Webix Document Management widget with a dynamic one. This case perfectly describes the flexibility of Webix components and their rich customization possibilities.