I've created a series of forms using Data Springs' Dynamic Forms module, which is a great module and does all I need it to do except provide for a grid view that allows users to query submitted data and have these data displayed in a nice tabular report. Therefore, I plan to use SQLGridSelectedView or even the core report module to accomplish this (or any other 3rd party modules that would make this easier).
Here's the rub: all of the records being stored in the SQL db are going into one column! (screenshot of the table).
It's a mess for a person with zero SQL skills. There are actually two data tables that store the form information - one for the questions and one of the responses...and multiple IDs that link the question to the response; but given that everything from EVERY form is going into one column, I have to call in the big guns.
Here is a solution that Dynamic Springs gave me, but I don't really understand what is going on (then I'll discuss an easier method that was given to me and could use a little help):
You would create the stored procedure:
CREATE PROCEDURE DataSprings_DynamicForms_GetData
@ModuleId int,
@PortalID int
AS
CREATE TABLE #MyQuestions(DynamicID uniqueidentifier, ID int identity)
-- Fill the temporary table with an ordered list from
-- the source base table
INSERT INTO #MyQuestions (DynamicID)
SELECT dynamicquestionID
FROM dynamicforms_question where ModuleID = @ModuleID AND QuestionType <> 'HTML' AND QuestionType <> 'HR' AND InActive=0
CREATE TABLE #temp (rowid int IDENTITY (1, 1) NOT NULL,userid int)
INSERT INTO #temp (userid) SELECT userid from [UserPortals] WHERE PortalID = @PortalID
DELETE FROM #MyQuestions WHERE DynamicID = (select DynamicQuestionID from dynamicforms_question where Question = 'UserID')
DECLARE @maxq int, @qid int,@sql nvarchar(4000)
SELECT @maxq = count(*) FROM [#MyQuestions]
SET @qid = 1
WHILE @qid <= @maxq
BEGIN
DECLARE @QuestionName varchar(100)
DECLARE @DynamicQID uniqueidentifier
Set @QuestionName = (Select Question from dynamicforms_question where DynamicQuestionID = (select DynamicID from #MyQuestions where ID = @qid))
Set @DynamicQID = (select DynamicID from #MyQuestions where ID = @qid)
--select @QuestionName, @DynamicQID
SET @sql = 'ALTER TABLE #temp ADD [' + @QuestionName + '] varchar(500)'
EXEC(@sql)
SET @sql = 'UPDATE t SET t.[' + @QuestionName + '] = r.response FROM #temp t INNER JOIN [dynamicforms_questionresponse] r ON r.userid = t.userid AND r.DynamicQuestionID = ''' +
Cast(@DynamicQID as varchar(100)) + ''''
EXEC(@sql)
--select @sql
SET @qid = @qid + 1
END
SELECT U.UserName AS [DynamicUserName], Z.* FROM #temp Z
Inner Join Users U on Z.UserID = U.UserID
Order By U.UserNAme Asc
GO
"Then you would use the stored procedure within your 3rd party or reports module such as:
DataSprings_DynamicForms_GetData 496, 0
That would be assuming the moduleID you wanted to pull results from was 496 and the portalID was 0"
|
Again, knowing "nothing about nothing," this dynamic stored procedure is quite intimidating to me (probably given that I have never even created one) and been a show-stopper for a month. I then found out that when the form goes to save the records, the module has a feature that lets me execute dynamic SQL. Therefore, it was further suggested that I do something like this:
Insert Into YourTable(FirstName, LastNAme, Email) Values('$(FName)', '$(LName)', '$(Email)')
Looking at one of my forms for hotels, I have the following variables being used in the module; here's the text from the module itself:
SQL Parameters:
"Include these parameters below into your SQL query (some parameters are baesd on short field names for your questions), these parameters will be replaced when the SQL statement is executed."
$(hotstat)
$(HotBid)
$(checkin)
$(CheckOut)
$(HotRat)
$(HotName)
$(HotStreet)
$(HotCity)
$(HotState)
$(ZipCode)
$(HotZone)
$(HotSub)
$(PortalID)
$(ModuleID)
$(UserID)
So would I just have it execute a statement like:
Insert Into Hotels(hotstat, hotbid, checkin, ...) values('$hotstat', '$(hotbid)', $(checkin)', ...)))
...assuming I create a table entitled "Hotels" with each respetive field having its own column? And If I do this, would I need the first column to be ID or something and somehow have a unique key/ID inserted in the column to pull all the records for each submission? I just don't know where to start.
[edit] I was just thinking; once I get them in their own table with their own columns, I should then come up with a stored procedure that truncates the mess in the native tables to avoid duplicity? I'm still trying to understand the big picture, but this approach seems easier for me to understand over that cast right-inner join cube data stuff. :)