Given that there isn't a formal report feature that allows us to display the form records in, say, a grid view, how do we get the data out?

I was going to try SQLGridSelectedView, a 3rd-party module (free) to display the data and allow members to query the data, filter, etc.

I also heard I could do the same with the 'Reports' module bundled with the core.

Therefore, I am wondering:

1.) Best way to get the data out and displayed, allowing members to query/search/filter.

2.) Where are the data stored? In the dnn db or elsewhere?

3.) Do I need a different connection string to tap the form data?

4.) Some basic SQL parameters/commands/whatever they are called to pull the data for the report modules. 

5.) Are there other modules people use that make all this easier. I am willing to try anything to finish this project up! 

Any help would be greatly appreciated.

 

John,

Hi. We plan to add more out of the box functionality for Dynamic Forms searchable results and so forth but each users implementation is different so more than likely this post will help many other users as well. I know we have worked on implementation for clients using the standard reports module but any 3rd party module that uses SQL can work.

A few comments on your questions.
1. One would be to query the data directly, all data is stored in relational tables for DynamicForms_Question and DynamicForms_QuestionResponse there are a few keys linking these two fields and you would need to add parameters to the query such as the moduleID to only pull in results for that form.

One comments: Ill dig up the query but depending on your exact implementation it can be a hefty query, it creates a temporary table of all questions/responses, shouldn't be a big load but debugging it could be a pain. One suggestion I would make would possibly be to create your own table and use a Dynamic SQL Event within the completion events to insert the data into that table.

For example you might create a table with First Name, Last Name, Email and then execute a SQL query such as:

Insert Into YourTable(FirstName, LastNAme, Email) Values('$(FName)', '$(LName)', '$(Email)')

etc...

2. I think I answered this in #1 but basically

DynamicForms_Question and DynamicForms_QuestionResponse tables, still within the CORE database but their own tables.

3. No

4. Ill dig up the query you can use for the core, you still might consider posting to your own table depending on your requirements though, this can provide easier ways to query the data later.

-Chad

Also, if you are wanting to pull from the default tables here is a stored procedure you can create/use. This has a few assumptions such as: 1. Users are signed in when submitting the form and not anonymous, that query would be different and 2. your tables don't use object qualifiers etc...

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.

-Chad

Thanks a lot, Chad! Knowing "nothing about nothing," I've stared at this code for a week now and think I understand it. 

I went into the db and looked at those two respective tables - DynamicForms_Question and DynamicForms_QuestionResponse.

And then the code made sense; all of the responses are in 1 column! I also say the unique keys, so I see what you are doing....pairing all the responses together for the form based on similar keys...more or less? 

After reading you response and never having created a stored procedure, I think you suggestion about creating my own table and using a Dynamic SQL Event within the completion events to insert the data into that table would be my best bet.

So I would, using my 3 basic forms for hotels, flights, rental cars, create 3 tables named, say, hotels, flights, cars.

Then create a column for each question (e.g., check_in, car_class, depart_air, city, state, et al)?

Then create an SQL event that writes each field to the respective column? Something like (using your example):

Insert Into Hotels(Name, City, State) Values('$(HName)', '$(HCity)', '$(HState)') ?

What I can't visualize is how the rows/columns would be setup so that when a user selects, say, hotels in New York, it will return all hotels submitted for New York with the respective price, hotel name, etc.. I am assuming that each row would need a unique key and then pull all the data for that key?

If I could get the data into a clean table with individual columns for each field, I am confident I could get the gridview done on my own...but with all the responses in one column, I am totally clueless, as you can tell.