vwCustomFormEntries

Fields

Field	Field Type	Null
ID	int
EntryID	uniqueidentifier
ClientSitesID	int	yes
SITEFOLDERSID	int
EntryState	int	yes
Printed	bit
UserName	nvarchar(50)	yes
FirstName	nvarchar(50)	yes
LastName	nvarchar(100)	yes
FormID	int
DataSource	uniqueidentifier
Title	nvarchar(200)
OwnerID	int	yes
ClientSiteFormCreatedOn	int	yes
AttributeType	int
AttributeValue	nvarchar(50)	yes
ChangeDate	datetime
PaymentMethod	nvarchar(100)	yes
HasDocuments	bit	yes

Definition

Copy


                CREATE view [dbo].[vwCustomFormEntries]
                as            

                SELECT    formentry.ID, formentry.EntryID, formentry.ClientSitesID, formentry.SITEFOLDERSID, formentry.EntryState, formentry.Printed,
                          clientuser.UserName,
                          FirstName = CASE formdatasource.DataSource
                                            WHEN '132E3702-0207-4d32-AA6F-7B265B1DC7D0' THEN CUSTOMFORMDASHBOARDFORMENTRY.STUDENTFIRSTNAME --Online Admissions
                                              WHEN '1927B45A-F7F8-44d7-BF45-66899B8C7D43' THEN CUSTOMFORMDASHBOARDFORMENTRY.STUDENTFIRSTNAME --Online Reenrollment
                                              ELSE clientuser.FirstName
                                      END,
                                      LastName = CASE formdatasource.DataSource
                                              WHEN '132E3702-0207-4d32-AA6F-7B265B1DC7D0' THEN CUSTOMFORMDASHBOARDFORMENTRY.STUDENTLASTNAME --Online Admissions
                                              WHEN '1927B45A-F7F8-44d7-BF45-66899B8C7D43' THEN CUSTOMFORMDASHBOARDFORMENTRY.STUDENTLASTNAME --Online Reenrollment
                                              ELSE clientuser.LastName
                                      END,
                          form.ID AS FormID, formdatasource.DataSource, form.Title, form.OwnerID, formsettings.ClientSitesID AS ClientSiteFormCreatedOn,
                          statusAttribute.AttributeType, statusAttribute.AttributeValue, statusAttribute.ChangeDate,
                          PaymentMethod = CASE formentry.EntryState
                                        WHEN 2 THEN formentry.PaymentMethod
                                        ELSE 'None'
                          END,
                          CONVERT(BIT,(SELECT COUNT(*) FROM dbo.CUSTOMFORMENTRYDOCUMENT WHERE dbo.CUSTOMFORMENTRYDOCUMENT.CUSTOMFORMENTRYID = formentry.EntryID)) AS HasDocuments      
                FROM      dbo.CustomFormEntry formentry INNER JOIN
                          dbo.CustomFormEntryAttribute statusAttribute ON formentry.EntryID = statusAttribute.EntryID INNER JOIN
                          dbo.CustomFormElement form ON formentry.FormGuid = form.Guid INNER JOIN
                          dbo.CustomFormDataSource formdatasource ON form.Guid = formdatasource.FormGuid LEFT OUTER JOIN
                          dbo.ClientUsers clientuser ON formentry.ClientUserID = clientuser.ID INNER JOIN
                          dbo.CustomFormSettings formsettings ON formentry.FormGuid = formsettings.FormGUID LEFT OUTER JOIN
                          dbo.CUSTOMFORMDASHBOARDFORMENTRY ON CUSTOMFORMDASHBOARDFORMENTRY.CUSTOMFORMENTRYID = formentry.EntryID
                WHERE     formentry.EntryState = statusAttribute.AttributeType