Hi,

  I was looking over your stored procs and I see there is a way to pull responses by userid (with questionid) or by just question id.

I would like to suggests new option for an upcoming release.

I am trying to pull some of the dynamic profile data (not attached to the core fields) and I don't want to hard code the question id, since I will be testing on my dev server and don't want to have to change my module code when moving to the live site since the Question ID will be different.

So, I have writting my own SQL pull, which gets data based on the user id and the "shortfieldname" of the question.

Personally I think this should be included with your module.

Here is my example:

"select Q.DynamicQuestionID, Q.Question, R.Response from dbo.DynamicRegistration_Question AS Q

left outer join dbo.DynamicRegistration_QuestionResponse AS R

ON Q.DynamicQuestionID = R.DynamicQuestionID

where Q.ShortFieldName = 'Title' and R.UserID = 3 And R.InActive=0"

Just a thought though, obviously you can ignore it :-)

Thanks!

Gary

Hi. Well if you are needing custom results you might just try and our Dynamic User Directory w/ Search (also used as report generator/creator based on the Dynamic Registration Responses). The new version includes the ability to add a filter, also add as many dynamic search fields as you want for the user-facing side. You can check out the forum under Dynamic User Directory for more ways how to utilize the module and we also have a demonstrate on the product page.

Another sugestion might be to create Dynamic SQL Events that add to your own tables.

If these two options still do not work because you need something for another specific reason I would suggest creating another stored procedure (yours would work on short field name too but this would work as it is). Here is a procedure that is used to create a temporary table with each column name represents each question name for every user. This will get ever users response to every question.

CREATE PROCEDURE GetDataSprings_DynamicRegistration_UserRespones
@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 dynamicregistration_question where ModuleID = @ModuleID AND QuestionType <> 'HTML' 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
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 dynamicregistration_question where DynamicQuestionID = (select DynamicID from #MyQuestions where ID = @qid))
Set @DynamicQID = (select DynamicID from #MyQuestions where ID = @qid)


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 [dynamicregistration_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

Thanks again for your feedback...

-Chad