Custom Validation Example:  

Below is an example of a custom validation written to validation dates entered to verify that they are within 90 days from today. 

To create a validation, you can use the following steps. It is a good idea to test this in a test database before impacting your live database.  

  1. In UD User Validation Create a new record for the Validation such as Date90Days, Vista will add usp to the beginning of the name.
  2. Enter a Description such as Date +- 90 Days from Today.
  3. In the SQL Tab, enter the validation code. (Code is listed below.)
  4. Save the validation and run the Compile button at the bottom of the form.
  5. In any Vista form such as AP Transaction Entry entry or AP Unapproved Transaction Entry form, press F3 on the Invoice date field and go to the System Overrides tab. (This validation can be applied to any date field within Vista)
  6. In the validation section, set the level to your preference such as Warning allow Save if you want to allow saving.
  7. Select the Custom validation from the lookup on the Procedure field.
  8. Find the Field Seq# at the top of the form, in the APUnappInv form, field 25 is the InvDate. Enter 25 into the parameters field to link the date entered into the field to the parameter.
  9. Click OK on the Field Properties form and it will reload the entry form.
  10. Test your validation to ensure it works as you expect.

SQL Query

USE [Viewpoint]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspDate90Days] 

/** User Defined Validation Procedure **/ 

/** Purpose: Used to Validate dates entered to verify they are within 90 days from today **/ 

/** Created by Constructive Tech Solutions 1/31/2019 **/ 

(
		@dateone bDate
		,@msg VARCHAR(255) OUTPUT
		) AS

	DECLARE @rcode INT 
	SELECT @rcode = 0

	IF @dateone > DATEADD(DAY,90,DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE()))) --Suggestion from Ken Jones, Business Information Group
		BEGIN

			SELECT @rcode=1,@msg='Date greater than 90 days in future' 
		
		END

	IF @dateone < DATEADD(DAY,-90,DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE()))) --Suggestion from Ken Jones, Business Information Group
		BEGIN

			SELECT @rcode=1,@msg='Date greater than 90 days in the past' 
		
		END

	spexit:

	RETURN @rcode

GO

GRANT EXECUTE ON [dbo].[uspDate90Days] TO VCSUsers

GO