I'm trying to use the view from a dynamic forms instance.  If I click "Generat Column Names", the screen flashes with the processing bar and then nothing happens.  I think i've traced this back to a few dynamic form questions repeating (the exact same question on different parts of the form, e.g. "First and Last Name").  Dynamic Views will not allow me to generate the columns and I receive an error if I try to export (error is a direct result of the above issue).  I get the error: 

A critical error has occurred.
The column 'degree (if applicable)' was specified multiple times for 'Main'.

 

This seems like a bug with dynamic views inability to accommodate the same name questions (that dynamic forms allows).  I wouldn't want to loose this functionality on DF, just need it added to work with DV.  It seems like DV isn't using the shortname & long name...it's using the "Question" field maybe ?

 

I've checked and verified that all Questions with the same exact "Question" text have different Short Names (not sure that DF would even allow this ?

Hi, yes, based on the stored procedure "DataSprings_DynamicViews_GetDynamicFormsResults", if you have SQL management studio, you can take a look at it or look at the SQL dataprovider file that comes with the zip install, it's doing this:

SELECT Question, DynamicQuestionID
FROM {databaseOwner}[{objectQualifier}dynamicforms_question]
where QuestionType <> 'HTML'
AND QuestionType <> 'HR'
AND Question NOT LIKE '%('
AND Question NOT LIKE ')%'
AND InActive=0
AND ModuleID = @ModuleID

so it's based on "question" and not "shortname" or "longname". Hence, you cannot have duplicated questions on the same Dynamic Forms moduleID. For now, the only work-around would be perhaps a 1,2,etc on the duplicate questions to make them not be duplicates.

The "question" field is the one being used to generate column names, not shortnames. But I suppose you can alter the stored procedure to select shortname instead of question though.

-- David

this sounds like it could be a bug then ?  will this be fixed in a future release ?

thanks,

Curtis

 

HI Curtis, I will defer this to Chad concerning whether to change the stored procedure to use shortname instead of question. But even with shortname, you cannot have duplicate shortnames. Will keep you posted since Chad doesn't come back until tomorrow. -- David

thanks David.

I would expect the short names to always be different.  It makes sense to be able to have multiple "question name" 's be the same as some forms would just have this.  The short name should always be different (like being a primary key for that DF instance)

thanks again,

Curtis

 

HI Curtis, here's the stored procedure you can use. This uses the ShortName fields instead of the questions. It also takes out questiontypes of "datagrid / buttons". We will have the option for the user to choose either "Question" or "Shortfieldname" as the column names in another release.

But here's the stored procedure that you can execute below that will use "Shortfieldname" instead. Go to Host / SQL and execute the following two statements:
================================
drop Procedure {databaseOwner}[{objectQualifier}DataSprings_DynamicViews_GetDynamicFormsResults]
================================
Create Procedure {databaseOwner}[{objectQualifier}DataSprings_DynamicViews_GetDynamicFormsResults]

@ModuleID int,
@UserID int,
@MySQL nvarchar(MAX) OUTPUT

AS
Declare curBubba Cursor
FOR
SELECT ShortFieldName, DynamicQuestionID
FROM {databaseOwner}[{objectQualifier}dynamicforms_question]
where QuestionType <> 'HTML'
AND QuestionType <> 'HR'
AND QuestionTYpe <> 'DataGrid'
AND QuestionType <> 'button'
AND Question NOT LIKE '%('
AND Question NOT LIKE ')%'
AND Question NOT LIKE ''
AND InActive=0
AND ModuleID = @ModuleID order by ShortFieldName,DynamicQuestionID


FOR READ ONLY;

Declare @ShortFieldName VarChar(500);
Declare @DynamicQuestionID uniqueidentifier;
Declare @SQLToUse VarChar(MAX);
Declare @SQLToUseJoin VarChar(MAX);
Declare @SQLToExecute Varchar(MAX);
Declare @Alias VarChar(10);
Declare @PreviousShortFieldName varchar(500);

Declare @intCounter Int;

set @ShortFieldName = '';
set @SQLToUse = '';
set @intCounter = 0;
set @Alias = '';
set @SQLToUseJoin = '';
set @PreviousShortFieldName = '';

OPEN curBubba;
FETCH NEXT FROM curBubba INTO @ShortFieldName, @DynamicQuestionID;

WHILE @@FETCH_STATUS = 0
BEGIN

set @intCounter = @intCounter + 1;

set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']';

if DataLength(@SQLToUse) = 0
BEGIN
set @SQLToUse = 'SELECT A.UniqueResponseID as UniqueID, A.UserID, IsNull(' + @Alias + '.response,''N/A'') as [' + lower(@ShortFieldName) + ']

'
set @SQLToUseJoin = '
From (
SELECT distinct Y.uniqueresponseID, Y.UserID, B.UserName
from {databaseOwner}[{objectQualifier}DynamicForms_QuestionResponse] As Y
inner join {databaseOwner}[{objectQualifier}DynamicForms_Question] as Z on (Y.DynamicQuestionID = Z.DynamicQuestionID)
Left Outer Join {databaseOwner}[{objectQualifier}Users] B on B.UserID = Y.UserID
where ((Y.UserID = ' + Cast(@UserID as varchar(10)) + ') OR (' + Cast(@UserID as varchar(10)) + ' = -2)) AND Z.ModuleID = ' + Cast(@ModuleID as

varchar(20)) + '
) As A
Left Outer join {databaseOwner}[{objectQualifier}DynamicForms_QuestionResponse] as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID

and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID)
'
END
ELSE
BEGIN
if @ShortFieldName <> @PreviousShortFieldName
begin

set @SQLToUse = @SQLToUse + ', ' + @Alias + '.response as [' + lower(@ShortFieldName) + '] '
set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join {databaseOwner}[{objectQualifier}DynamicForms_QuestionResponse] as ' + @Alias + ' on
(A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID) '
end
Set @PreviousShortFieldName = @ShortFieldName
END;

FETCH NEXT FROM curBubba INTO @ShortFieldName, @DynamicQuestionID;
END

CLOSE curBubba;
DEALLOCATE curBubba;

Set @SQLToExecute = Convert(varchar(MAX), @SQLToUse) + Convert(varchar(MAX), @SQLToUseJoin)


SET @MySQL = @SQLToExecute
RETURN
=============================================
Thanks for noticing this. -- David

Just a quick question on this - does this change the sproc in the specific instance of the module or does it change the sproc for every instance of the module deployed on the site?

Hi, this changes all the modules deployed on the site. -- David