Helpdesk Field Length Validation

An example of Helpdesk Validation based on field length.

Basically this is a Validation Rule that will check the length of a Custom Field and see how long it is.

The screen shots below are an example of validating that a field [Accounting Code] must be 0 OR 7 characters to successfully Save the incident.

Click to view.

Validation error if input any number of characters in "Accounting Code" except 0 or 7.

Click to view.

Click to view.

Testing whether the HDQUERY[[Select len('WORKITEM(workitem_accountingcode)')]] is not equal to 0

_

Click to view.

Testing whether the HDQUERY[[Select len('WORKITEM(workitem_accountingcode)')]] is not equal to 7

When both of those evaluate as true you have one of the following:

1. A blank accounting code field
2. A 7 character Accounting code field.

------------------------------------------------------------------
--HIT F5.                     --
------------------------------------------------------------------

Declare @SQLtoRun as nvarchar(4000)
Declare @Workitem_Column_Name as nvarchar(255)
Declare @JoiningAliasName as nvarchar(255)


Set @Workitem_Column_Name = 'AccountingCode'
Set @SQLtoRun = ''
Set @JoiningAliasName = @Workitem_Column_Name + '_alias'


Set @SQLtoRun = ' Workitem ADD ' + @Workitem_Column_Name + ' nvarchar(0) NOT NULL CONSTRAINT DF_workitem_' + @Workitem_Column_Name + ' DEFAULT 0'
print '|||| Running SQL -->' + 	@SQLtoRun	
exec sp_executesql @SQLtoRun	

Set @SQLtoRun = 'Exec sp_addviewdef N''Workitem_only_View'',N''workitem.' + @Workitem_Column_Name+ ' AS workitem_' + @Workitem_Column_Name +''',N'''''
print '|||| Running SQL -->' + 	@SQLtoRun	
exec sp_executesql @SQLtoRun

Set @SQLtoRun = 'Exec sp_createHDDview ''workitem_only_view'', ''Workitem'''
print '|||| Running SQL -->' + 	@SQLtoRun	
exec sp_executesql @SQLtoRun

Set @SQLtoRun = 'Exec sp_createHDDview ''workitem_detail_view'', ''Workitem_only_view'''
print '|||| Running SQL -->' + 	@SQLtoRun	
exec sp_executesql @SQLtoRun


Set @SQLtoRun = 'Drop View [dbo].[workitem_current_view]'
print '|||| Running SQL -->' + 	@SQLtoRun	
exec sp_executesql @SQLtoRun

Set @SQLtoRun = 'Create View [dbo].[workitem_current_view] AS Select Workitem_Detail_View.* From Workitem_Detail_View Where (Workitem_is_last =N''1'')'
print '|||| Running SQL -->' + 	@SQLtoRun	
exec sp_executesql @SQLtoRun