Hi John,

There commercial modules like Live Visualizer (Mandeeps) or an entire Low code solution (plant an app) that would allow for this.

If you want to check out open source, maybe DNN Reports ( https://github.com/DNNCom...ity/DNN.Reports/wiki ) is for you.
Another approach might be to use 2SXC (also open source) but that would require a bit of tech knowledge.

Thank you, Thcho. Maybe I'm misunderstanding the FnL data structure. I tried Live Visualizer, but it only reads SQL tables and Live Forms data, which I think is also SQL. . 

I'll explore at your other two references. 

John 

The FnL data structure is a typical structure for the very dynamic creation of smaller lists.

Let's start: The table UserDefinedFields (the name of the table is because of the former name of the module, "User Defined Tables") contains all the column definitions. The ModuleID is the one you have to filter to get the definitions of a specific module.

UserDefinedRows contains a RowId and the ModuleID where the row belongs to.

UserDefinedData contains the FieldId (from the UserDefinedFields table), the RowID (from the UserDefinedRows table) and the value that is "cell" (this column of this row).

UserDefinedFieldSettings contains some settings of the column, eg. if it should be displayed as link or if a link should be opened i a new window etc. For queries to get data in a report this is normally not so interesting.

I give you an example. I made a list of restaurants, containing the name, address, phone number and type of the restaurant (and more, but I just want these four columns in the report).

First, find the ModuleID of the module instance (let's say it's 2185), and then the definition of the fields, e.g.

<code>SELECT *<br /> FROM dbo.UserDefinedFields<br /> WHERE FieldTitle IN ('Restaurant', 'Address', 'Phone', 'Type') AND ModuleID=2185</code>

Remember the UserDefinedFieldId of each resulting row (let's say they are 350 for the restaurant's name, 353 for the address, 354 for the phone and 360 for the type). Now we query the rows joined to these fields, and you are done:

SELECT
   [Restaurant].FieldValue AS [Restaurant],
   [Address].FieldValue AS [Address],
   [Phone].FieldValue AS [Phone],
   [Type].FieldValue AS [Type]
FROM dbo.UserDefinedRows
   LEFT OUTER JOIN dbo.UserDefinedData AS [Restaurant]
    ON dbo.UserDefinedRows.UserDefinedRowId = [Restaurant].UserDefinedRowId
     AND [Restaurant].UserDefinedFieldId=350
   LEFT OUTER JOIN dbo.UserDefinedData AS [Address]
    ON dbo.UserDefinedRows.UserDefinedRowId = [Address].UserDefinedRowId
     AND [Address].UserDefinedFieldId=353
   LEFT OUTER JOIN dbo.UserDefinedData AS [Phone]
    ON dbo.UserDefinedRows.UserDefinedRowId = [Phone].UserDefinedRowId
     AND [Phone].UserDefinedFieldId=354
   LEFT OUTER JOIN dbo.UserDefinedData AS [Type]
    ON dbo.UserDefinedRows.UserDefinedRowId = [Type].UserDefinedRowId
     AND [Type].UserDefinedFieldId=360
WHERE
   dbo.UserDefinedRows.ModuleId = 2185

Happy DNNing!
Michael

That's wonderful, Michael. Thanks so much!

John