Dynamic Fields

Concept

The dynamic fields mechanism allows working with complex data structures as if they were a flat table. Thanks to the system’s existing data retrieval and editing mechanisms, it is also possible to work with multi-values for a single record in the main flat table.

How It Works

In a minimal configuration, 3 entities participate in the process. The main entity is the one that forms the flat data. The second entity is the one that is transformed into the fields of the main entity, i.e., these are the columns that should appear in the main table to make the data more complete. The third entity is the link table, a table with the values of the second entity for records in the first one.

Typically, this is a set of characteristics of some entity. These characteristics can be of different types. The characteristic type determines the type of editor that will be installed (can be overridden) in the final table or form.

There can also be several values of a characteristic for a specific entity record; the mechanism supports working with multi-values. Client tables can display such data and allow customizing the editor (see Frame for the Editor).

Let’s consider an example with Products and their Characteristics. There can be many characteristics for various products, and since they can be added and removed during system operation, and due to their large number, they cannot be physical fields in the main entity (Product). Moreover, such a structure would not allow having multiple values for one Characteristic of a specific Product. Therefore, the structure can be as follows: a Products table, a Characteristics directory, and a table with Characteristic_Values_For_Product.

The values table can be a more complex structure because, for example, for values of the Directory type, separate tables with their values will be required. However, in this case, it makes sense to use SQL to bring the values table into a pre-prepared “VIEW” where physical values are also stored.

So, we have Products and their Characteristics (values). If we want to output data for a single Product, no particular problems arise since we can get the necessary data in 2-3 queries. However, if we want to work with a table, even with pagination, a different approach to data retrieval is required (especially if we want to export several thousand records to Excel). The mechanism builds an optimal query to obtain such data, outputting all characteristics as if they were fields of this table. Thanks to profiles, the system knows that these are fields from another table and knows how to work with them to ensure editing.

Dynamic Fields Set Limitation

As a rule, in such data structures, characteristics belong to certain entity categories (e.g., Product_Categories). In this case, we need to be able to limit the set of dynamic fields to a certain category (and its children). The system allows this by specifying a filter table. Then, if data is loaded in a form (e.g., a form of a specific Category), the system will refer to the filter table and apply constraints so that only characteristics belonging to that category are loaded.

Hierarchy by Filter Table

If the table used for filtering is hierarchical, as in our example with Categories (a Category can have subcategories, which in turn have their own, and so on), the filter can take this into account (with a certain setting) and provide Characteristics not only for the specified Category but also for all its children and/or all parents.

The characteristics directory can also be filtered by default by some attribute; it’s not necessary for all Characteristics to be included in the dynamics.

In summary, the mechanism is flexible and can be further refined for even greater customization if functions are allowed to prepare data slices based on input parameters. However, the current implementation is sufficient for the described scenario. It allows implementing a complex data interaction mechanism through simple configuration.

Value Inheritance

The core supports a value inheritance mechanism for regular physical fields in hierarchical entities. The is_inherit setting in the field profile serves this purpose. For dynamic fields, there are their own profile settings that dynamically complement the profile of the main entity (see Dynamic Field Profile Settings). There is also an is_inherit setting which, like for physical fields, allows enabling inheritance for dynamic fields.

Management

Dynamic fields can only be applied to a client object. Subsequently, it can be implemented in the interface both in a form/frame and in a table.

Creating a “Dynamic Fields Pair”

Open the menu Dynamic fields -> Dynamic fields pair. Create a pair. Specify the Name, Source Class (e.g., Characteristics directory), Target Client Object (the client object based on the class to be complemented with dynamic fields, e.g., Products), and Values Table (the table that stores Source values for the Target, e.g., Characteristic Values for Product).

Most fields have a tooltip; hover your mouse over the column header.

The system assumes that the keys in the values table correspond to the source and target table names (excluding prefixes like “ds_” or “d_”), for example, product_id and trait_id. However, they can be overridden (Key to target and Key to source).

The system assumes that the value will be stored in a field named “val1”, but this can be overridden (Value key).

Filter Table Settings

You can specify a filter table, and then the filter will be applied to the set of characteristics when data is requested inside a form/frame (the system passes parent_id). At the same time, if the client object is loaded without a parent (and table_for_filter is not empty), no dynamic field will be loaded.

Specify table_for_filter, for example, product_category (a link table defining which categories a Product belongs to).

Specify parent_class_for_filter, which will define the entity that imposes constraints (Categories). This is especially important if the entity is hierarchical and characteristics of child records relative to the filtered one should also be connected.

src_key_for_filter. Defines the “characteristic” key in the filter table, for example, trait_id.

this_and_parents_for_filter and this_and_childs_for_filter. Allow enabling the use of the “up” and/or “down” hierarchy along the filter table. See tooltips in the interface. For example, the system has filtered characteristics by a certain Category; with these settings, the system can add Characteristics belonging to parent and/or child Categories to them.

Frame for the Editor

You can create a separate (client object) frame that will open when attempting to edit a dynamic field. This frame will be opened in a modal window, and it will be passed the ID of the target record (Product; accordingly, it must be created based on the Product class (frm_edit_product_traits)) and the ID of the Source record (Characteristic). In this frame, you can simply place a table of Product_Characteristic_Values (product_trait_value), which will be filtered by the specified Product and the specified Characteristic. Thus, a table with the values of exactly this Characteristic for the specified Product will open, and you will be able to interact with it, including, for example, changing other fields of this table, such as the is_active checkbox (or anything else that matches your project’s business logic).

Dynamic Field Profile Settings

Since dynamic fields are added “on the fly” to the main entity, there is no profile for them in the main entity (Target). Field profiles in the values table may not be suitable for dynamically connected fields, although these profiles are used as a basis. Therefore, their own profiles are automatically created for them, and they are located in the menu Dynamic fields -> Dynamic field.

There you can override the Field Name, as well as many other settings for this field, just as if you were configuring a regular field profile (for example, making a field non-editable).

map_source_to_dynamic_field

You can specify an object in JSON format, for example, '{"inherit":"df_is_inherit"}'. This allows, based on the field value in the source table (e.g., “inherit”), setting it as a profile value for the corresponding dynamic field (“df_is_inherit”).