Hi Helmi

The stored procedure used to populate the web table is similar to yours - a lot of work has to be done to turn it into a table! I have written several routines and functions to do this in SQL Server. For each F&L module, I create an entry in the procedure below which maps out the columns in the specific web table corresponding to the specific F&L module instance..

ALTER PROCEDURE [dbo].[ST4WDC_ReportFormAndListModules] @PortalID INT, @ModuleTitle NVARCHAR (256) AS BEGIN

SET NOCOUNT ON;

IF @ModuleTitle = 'Participants' BEGIN
SELECT
m.ModuleID,
m.ModuleTitle,
r.UserDefinedRowId,
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Tag')) AS 'Tag',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Event')) AS 'Event',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Participant 1')) AS 'Participant 1',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Fin')) AS 'Fin',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Training 1')) AS 'Training 1',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Status')) AS 'Status',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Participant 2')) AS 'Participant 2',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Vehicle')) AS 'Vehicle',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Trailer')) AS 'Trailer',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Others')) AS 'Others',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Created by')) AS 'Created by',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Created at')) AS 'Created at',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Changed by')) AS 'Changed by',
dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Changed at')) AS 'Changed at'
FROM dbo.UserDefinedRows r
LEFT OUTER JOIN dbo.Modules m
ON m.ModuleID = r.ModuleID
WHERE m.ModuleTitle = @ModuleTitle
AND m.IsDeleted = 0
AND m.PortalID = 0
ORDER BY m.ModuleID, dbo.ST4WDCexpandData (@PortalID, r.UserDefinedRowId, dbo.ST4WDC_FieldID (0, m.ModuleID, 'Event'))
RETURN
END
-- repeat for each module instance
END

The supporting functions are:

ALTER FUNCTION [dbo].[ST4WDC_FieldID] (@PortalID INT, @ModuleID INT, @FieldTitle NVARCHAR (50)) RETURNS INT AS BEGIN

RETURN (SELECT UserDefinedFieldID FROM dbo.UserDefinedFields WHERE ModuleID = @ModuleID AND FieldTitle = @FieldTitle)
END


ALTER FUNCTION [dbo].[ST4WDCexpandData] (@PortalID INT, @UserDefinedRowId INT, @UserDefinedFieldID INT) RETURNS NVARCHAR (MAX) AS BEGIN

DECLARE @ModuleID INT = (SELECT ModuleId FROM dbo.UserDefinedRows WHERE UserDefinedRowId = @UserDefinedRowId)
DECLARE @Visible BIT = (SELECT Visible FROM dbo.UserDefinedFields WHERE UserDefinedFieldId = @UserDefinedFieldID)
DECLARE @FieldType VARCHAR (20) = (SELECT FieldType FROM dbo.UserDefinedFields WHERE UserDefinedFieldId = @UserDefinedFieldID)
DECLARE @FieldTitle NVARCHAR (50) = (SELECT FieldTitle FROM dbo.UserDefinedFields WHERE UserDefinedFieldId = @UserDefinedFieldID)
DECLARE @FieldValue NVARCHAR (MAX) = (SELECT FieldValue FROM dbo.UserDefinedData WHERE UserDefinedRowID = @UserDefinedRowID AND UserDefinedFieldID = @UserDefinedFieldID)
DECLARE @InputSettings NVARCHAR (2000) = (SELECT InputSettings FROM dbo.UserDefinedFields WHERE UserDefinedFieldId = @UserDefinedFieldID)
DECLARE @Default NVARCHAR (2000) = (SELECT "Default" FROM dbo.UserDefinedFields WHERE UserDefinedFieldId = @UserDefinedFieldID)
DECLARE @Field NVARCHAR (MAX)

SET @Field = CASE
WHEN @FieldType IN ( 'Boolean', 'ChangedAt', 'ChangedBy', 'CreatedAt', 'CreatedBy', 'Currency', 'Decimal', 'EMail', 'Int32', 'String', 'TextHTML' )
THEN @FieldValue
WHEN @FieldType IN ( 'Date' )
THEN CONVERT (NVARCHAR (11), CAST (@FieldValue AS DATETIME), 106) --1995-06-10T00:00:00
WHEN @FieldType IN ( 'LookUp' )
THEN dbo.ST4WDCprofileValue (@PortalID, @Default, dbo.ST4WDCgetListData (@ModuleID, @InputSettings, @UserDefinedRowId))
WHEN @FieldType IN ( 'URL' )
THEN CASE
WHEN REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE
(@FieldValue, '9', ''), '8', ''), '7', ''), '6', ''), '5', ''), '4', ''), '3', ''), '2', ''), '1', ''), '0', '') = ''
THEN (SELECT TabName FROM dbo.Tabs WHERE TabID = CAST (@FieldValue AS INT))
ELSE @FieldValue
END
WHEN @FieldType IN ( 'UserLink' ) -- UserID=234
THEN (SELECT DisplayName FROM dbo.Users WHERE UserID = dbo.ST4WDCgetFirstInteger (@FieldValue))
WHEN @FieldType IS NULL -- could have been a lookup so this test comes after that one
THEN ''
ELSE 'Unknown Field Type'
END

IF @Field IS NULL RETURN ''
RETURN @Field
END

ALTER FUNCTION [dbo].[ST4WDCgetFirstInteger] (@string NVARCHAR (MAX)) RETURNS INT AS BEGIN

DECLARE @result NVARCHAR (MAX)
DECLARE @start INT = PATINDEX ('%[01234567890]%', @string)
DECLARE @end INT = PATINDEX ('%[^0123456789]%', SUBSTRING (@string, @start, 2000))

RETURN
CASE
WHEN @start = 0
THEN NULL
WHEN @end = 0
THEN CAST (SUBSTRING (@string, @start, LEN (@string) - @start + 1) AS INT)
ELSE CAST (SUBSTRING (@string, @start, @end - 1) AS INT)
END
--SELECT dbo.ST4WDCgetFirstInteger ('userID=32|crap')
--SELECT dbo.ST4WDCgetFirstInteger ('userID=|crap')
--SELECT dbo.ST4WDCgetFirstInteger ('userID=3')
--SELECT dbo.ST4WDCgetFirstInteger ('3crap')
--SELECT dbo.ST4WDCgetFirstInteger ('')

END

Hope this helps

Richard

First: i dont know how to thank you.

thank you very very very much.

Second: your reply is very helpful. but

is there anyway to find the exact Select statement that the F&L Module use??? like a Data source adapter or any other ways???

i would like to use the same exact statement that the module use.

thanks again

your help is very much appreciated.