Wiki

There are two custom procedures available for use. These procedures are automatically created when a new form is created.

The vGo_CustomPreExecute procedure is executed before data is committed to the database from a Versago form. It is most commonly used for data validation prior to posting of the data.

The vGo_CustomPostExecute procedure is executed after data has been committed to the database from a Versago form. It is most commonly used to write additional data to other tables once a record has been successfully posted. An example might be to write a record to a transaction log table or do some other related update.

A specific variable, @OutputMessage, is used by these procedures. If this variable is not null (empty) the procedure assumes that an error has been detected and the transaction is not committed. A custom message is presented to the user explaining what failed.

To locate the appropriate procedure you will need to know the ID number of the form. This can be found by opening the form for maintenance in the Admin console and clicking on the “Form Setup” tab. The ID number is displayed in the first field of this page. The associated procedures are the names described abo ve, with the form ID at the end. For example, the PostExecute procedure for form ID 10 will be vGo_CustomPostExecute_10.

The first thing you will notice is that there is a large amount of code that is “commented out.” It is provided to assist with finding values you may need from the form. This code should remain commented as it appears.

There is also some “uncommented” code that is not strictly needed for pre- and post-processing. For clarity, this code should also be commented out. Add the start-comment (/*) and end-comment (*/) markers as shown in red. Remove the start-comment and end-comment markers already in place in the green section. The result should be that everything after the “Alter procedure” statement is commented out.

An example is shown below.

ALTER PROCEDURE [dbo].[VGO_CustomPreExecute_2] @FormData xml, @UserID nvarchar(50), @ActionType CHAR(1), @OutputMessage nvarchar(500) output AS
 
BEGIN TRY
SET ARITHABORT ON;
BEGIN TRANSACTION
 
--Please refer to core procedure for actual parsing information.
 
/*
 
Declare @formmode char(1);
SELECT @formmode=frm.value('(twbsformmode)[1]','char(1)') FROM @FormData.nodes('/formData') AS HDR(frm);
if(@formmode='F')
Begin
UPDATE HDR SET HDR.[RecNum]=HDRUP.[RecNum], HDR.[DataText]=HDRUP.[DataText], HDR.[DataNumInt]=HDRUP.[DataNumInt], HDR.[DataNumDec]=HDRUP.[DataNumDec], HDR.[DataDateDate]=HDRUP.[DataDateDate], HDR.[DataDateDateTime]=HDRUP.[DataDateDateTime]
FROM [RU_Build_56_Test01] AS HDR INNER JOIN (
SELECT ISNULL( case frm.value('(RecNum)[1]','nvarchar(19)') when '' THEN NULL ELSE frm.value('(RecNum)[1]','nvarchar(19)') END,NULL ) as [RecNum],frm.value('(DataText)[1]','nvarchar(50)') as [DataText],ISNULL( case frm.value('(DataNumInt)[1]','nvarchar(19)') when '' THEN NULL ELSE frm.value('(DataNumInt)[1]','nvarchar(19)') END,NULL ) as [DataNumInt],ISNULL( case frm.value('(DataNumDec)[1]','nvarchar(12)') when '' THEN NULL ELSE frm.value('(DataNumDec)[1]','nvarchar(12)') END,NULL ) as [DataNumDec],ISNULL( case frm.value('(DataDateDate)[1]','nvarchar(10)') when '' THEN NULL ELSE frm.value('(DataDateDate)[1]','nvarchar(10)') END,NULL ) as [DataDateDate],ISNULL( case frm.value('(DataDateDateTime)[1]','nvarchar(30)') when '' THEN NULL ELSE frm.value('(DataDateDateTime)[1]','nvarchar(30)') END,NULL ) as [DataDateDateTime] FROM @FormData.nodes('/formData') AS HDR1(frm)) as HDRUP on HDR.[RecNum]=HDRUP.[RecNum];
End
ELSE
Begin
SELECT @primaryfield=(isnull(MAX([RecNum]),0)+1) from [RU_Build_56_Test01] (NOLOCK) ;
INSERT INTO [RU_Build_56_Test01] ([RecNum],[DataText],[DataNumInt],[DataNumDec],[DataDateDate],[DataDateDateTime])
SELECT @primaryfield,ISNULL( case frm.value('(DataText)[1]','nvarchar(50)') when '' THEN NULL ELSE frm.value('(DataText)[1]','nvarchar(50)') END,NULL) ,ISNULL( case frm.value('(DataNumInt)[1]','nvarchar(19)') when '' THEN NULL ELSE frm.value('(DataNumInt)[1]','nvarchar(19)') END,NULL) ,ISNULL( case frm.value('(DataNumDec)[1]','nvarchar(12)') when '' THEN NULL ELSE frm.value('(DataNumDec)[1]','nvarchar(12)') END,NULL) ,ISNULL( case frm.value('(DataDateDate)[1]','nvarchar(10)') when '' THEN NULL ELSE frm.value('(DataDateDate)[1]','nvarchar(10)') END,NULL) ,ISNULL( case frm.value('(DataDateDateTime)[1]','nvarchar(30)') when '' THEN NULL ELSE frm.value('(DataDateDateTime)[1]','nvarchar(30)') END,NULL) FROM @FormData.nodes('/formData') AS HDR(frm);
End
*/
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN ERROR_MESSAGE()
END CATCH

To obtain values from the form requires some special SQL processing. The basic process is to create a variable, then use a special SQL Select statement to fill the variable. An example to get the primary key value follows below.

To work with the appropriate dataset, it is necessary to first get the record key from the form data. Following is the basic SQL statement to set a processing variable to the record number (vgoRecNum) value on a form. “vgoRecNum” is the column name used for this example. The value used in your form may be different.

DECLARE @vgoRecNum nvarchar(20)
 
SELECT @vgoRecNum = frm.value('(vgoRecNum)[1]','nvarchar(20)') FROM @FormData.nodes('/formData') AS HDR(frm);

The same general statement can be used to obtain other values from the form data as well.

The rest of the processing logic is straight SQL and will be custom for each installation’s requirements.

There are three actions that can occur in a Versago Form: Add, Update, and Delete. It is possible to determine what action is occurring based on two variables in the Custom_PreExecute and Custom_PostExecute stored procedures for the form.

The first variable is named @ActionType. This variable is automatically populated when a transaction is processed.

The second variable is named @FormMode. A small bit of code is required to obtain this value. Copy the following two lines and add it into the procedure.

DECLARE @FormMode CHAR(1) 
SELECT  @formmode=frm.value('(twbsformmode)[1]','char(1)') FROM @FormData.nodes('/formData') AS HDR(frm);

You can now determine the form action based on the following code combinations.

IF isnull(@FormMode,'X') = 'X' AND @ActionType = 'S' - the record is being added.
IF isnull(@FormMode,'X') = 'F' AND @ActionType = 'S' - the record is being updated.
IF isnull(@FormMode,'X') = 'X' AND @ActionType = 'D' - the record is being deleted.

The “IF” statements can be used in the form’s Custom_PreExecute procedure to prevent certain actions from occurring before the form record is posted. The same logic can also be used in the form’s Customer_PostExecute procedure to take other actions, like posting information to logging columns in the record or to a logging table, triggering a Bizweaver workflow, etc. Keep in mind that the updated record has already been posted when the Custom_PostExecute procedure is executed.

Using the @FormMode and @ActionType variables it is possible to create a change history log for forms. This is particularly helpful when forms are being added and changed by different users.

The change history is held in a database table. In general it should be in the same database where your form data tables are created (e.g. VersagoData). The following sample will create a basic table. Additional columns could be added if desired.

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[vgo_Form_ChangeHist](
	[TransRecId] [INT] IDENTITY(1,1) NOT NULL,
	[TransDateTime] [datetime] NULL,
	[FormId] [INT] NULL,
	[FormName] [nvarchar](100) NULL,
	[ActionType] [nvarchar](1) NULL,
	[FormMode] [nvarchar](1) NULL,
	[UserID] [INT] NULL,
	[UserEmail] [nvarchar](50) NULL,
	[DataRecordID] [INT] NULL,
	[TransType] [nvarchar](10) NULL
) ON [PRIMARY]
GO

The following code is then added to the Custom_PostExecute procedure for each form where a change log is desired. Note that there are two items in this example that may need to be changed.

1. “formID” is the ID of the Versago form being tracked. This will be different for each form. Change the value nn in three places in the sample code to make this change.
2. “vgoRecNum” is the name of the record ID (primary key) column of the form. The name of your column may be different so the procedure will need to be changed accordingly.

-- Code for change history log
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[VGO_CustomPostExecute_74] @FormData xml,@GridData xml, @UserID nvarchar(50), @ActionType CHAR(1), @OutputMessage nvarchar(500) output  AS
--
 DECLARE @formmode VARCHAR(1), @formid INT, @formname VARCHAR(100), @useremail VARCHAR(50), @recnum INT
 --
-- Enter the Form ID (the number at the end of the procedure name) below
SET @FormID = 74
--
-- change "vgoRecNum" in the following select based on the value in the form
SELECT  @RecNum = frm.value('(vgoRecNum)[1]','char(19)') FROM @FormData.nodes('/formData') AS HDR(frm);
SELECT  @formmode=frm.value('(twbsformmode)[1]','char(1)') FROM @FormData.nodes('/formData') AS HDR(frm);
--
-- Target database (vgoCommon_VersagoDemo in the sample below) will neeed to be updated for your environment
SELECT @useremail = Email FROM vgoCommon_VersagoDemo..TWBS_WS_OUSR WHERE ID = CAST(@UserID AS INT)
SELECT @formname = FormName FROM vgoCommon_VersagoDemo..TWBS_WS_FormMaster WHERE FormID = @formid
--
-- Code logic
IF isnull(@FormMode,'X') = 'X' AND @ActionType = 'S'
BEGIN
	INSERT INTO vgo_form_ChangeHist
	(TransDateTime,formID, FormName, ActionType, FormMode, UserID, UserEmail, DataRecordID, TransType)
	VALUES
	(getdate(), @formid, @formname, 'A', @formmode, CAST(@UserID AS INT), @useremail, @recnum, 'Add')
END
IF isnull(@FormMode,'X') = 'F' AND @ActionType = 'S'
BEGIN
	INSERT INTO vgo_form_ChangeHist
	(TransDateTime,formID, FormName, ActionType, FormMode, UserID, UserEmail, DataRecordID, TransType)
	VALUES
	(getdate(), @formid, @formname, 'A', @formmode, CAST(@UserID AS INT), @useremail, @recnum, 'Update')
END
IF isnull(@FormMode,'X') = 'X' AND @ActionType = 'D'
BEGIN
	INSERT INTO vgo_form_ChangeHist
	(TransDateTime,formID, FormName, ActionType, FormMode, UserID, UserEmail, DataRecordID, TransType)
	VALUES
	(getdate(), @formid, @formname, 'A', @formmode, CAST(@UserID AS INT), @useremail, @recnum, 'Delete')
END