Is there a report writer module for FnL data that does not require programming skills? I'm looking to place subsets of a master list on various pages.
The subsets would include selected columns. I see I can filter data on other instances of the module, but not display only selected columns. Thank you.
John
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.
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.
SELECT * FROM dbo.UserDefinedFields WHERE FieldTitle IN ('Restaurant', 'Address', 'Phone', 'Type') AND ModuleID=2185
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
Michael TobischDNN★MVP
That's wonderful, Michael. Thanks so much!
These Forums are dedicated to the discussion of DNN Platform.
For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:
Awesome! Simply post in the forums using the link below and we'll get you started.