Create a dynamic table from 3 lists of values

[JulianYoung]

I have a database table with the table_name, columns(list of all columns),old_value(for each dynamic column),new_value(for each dynamic column) of an entire database. I would like to create a dynamic table of based on the table_name the user picks and populate the table with dynamic fields based on the names of the fields in the columns list and have the old and new values for each field.  I can show the users the three lists but it is hard to review as there can be 3 to 21 fields based on the table's name they pick to view.  What would a loader script look like to do this as each table's fields and values are dymamic based on the lists found in the database columns

[marcus_sommer]

I could imagine to create two tables whereby in the fact-table all tables are concatenated together after the resolution of the n list-information. Means creating a table with one field for the table-name + one for the value-information (old + new) and then the 21 fields just with the order-number as field-name.

The second table would be the dimension-table as a mapping and containing only the table-name and the order-number of the fields + another field with the names to these numbers.

Within the UI the labels of the fields would be dynamic with something like:

only({< FieldNumber = {1}>} FieldName)

for all n fields + depending on the view-requirements various show/hide conditions if these fields are existing within the selected table. You may investigate similar approaches within the community by searching for logic to dynamically translate field-names / field-values to a selected language.

If no advanced UI is needed you may skip the second table and mapping the field-names else just adding the field-names as third-value (old, new, name) to all numbered fields.

[JulianYoung]

Hi Marcus_sommer,

This is from one row of data and I mocked up 2 ways I would like to see it in excel.  I cannot use the order number as a field id.

Table_Name =Enrollments 

Column_Name = RunIn;StratificationFactor;Rate;LastId;NextId;Active;EnrollmentDate;NextScheduledDate;Status
Old_Value = null;null;null;SCR2;RAND;null;null;2025-09-10 00:00:00.0000000;4
New_Value = 6;1;3.05;RAND;D1;B;2025-09-08 00:00:00.0000000;2025-09-09 00:00:00.0000000;16

Either are acceptable.  I just want a table to populate like this as the user scrolls thru rows of data in the parent table if that is possible.

[marcus_sommer · Accepted solution]

Transforming the lists into a stream-data structure like in the second screenshot is quite simple - just looping through the lists, for example:

load 

   Table_Name, 

   subfield(Column_Name, ';', iterno()) as Column

   subfield(Old_Value, ';', iterno()) as Old_Value

   subfield(New_Value, ';', iterno()) as New_Value

from Source while iterno() <= substringcount(Column_Name) + 1;