Differences

This shows you the differences between two versions of the page.

--- versago:versago_custom_procedures [2019/08/01 11:35]
runger created
+++ versago:versago_custom_procedures [2023/07/12 14:15] (current)
dlee [Obtaining Form Values]
@@ Line 59: / Line 59: @@
 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.
-<code sql>declare @vgoRecNum nvarchar(20)
+<code>
+Sample SQL to extract fields from xml parameter @FormData (Header table)
-SELECT @vgoRecNum = frm.value('(vgoRecNum)[1]','nvarchar(20)') FROM @FormData.nodes('/formData') as HDR(frm);
+DECLARE	@formmode char(1),
-</code>
+		@text nvarchar(50),
+		@number numeric(19,6),
+		@date datetime
+SELECT	@formmode = frm.value('(twbsformmode)[1]','char(1)'),
+		@text = frm.value('(FormField1)[1]','nvarchar(50)'),
+		@number = frm.value('(FormField2)[1]','numeric(19,6)'),
+		@date = frm.value('(FormField3)[1]','datetime')
+FROM	@FormData.nodes('/formData') AS HDR(frm);
+FormField1, ..2, ..3 must match the Field Name in the form control settings. It is case sensitive
+---------------------------------------------------------------------------------------
+Sample SQL to extract fields from xml parameter @GridData (Detail table)
+SELECT	frmdetail.value('(FormField1)[1]','nvarchar(50)') AS Col1,
+		frmdetail.value('(FormField2)[1]','numeric(19,6)') AS Col2,
+		frmdetail.value('(FormField3)[1]','datetime') AS Col3
+INTO	#TempTable
+FROM	@GridData.nodes('/GridData/Datas') AS DTL(frmdetail);
+FormField1, ..2, ..3 must match the Field Name in the form control settings. It is case sensitive
+Use SQL cursor to process each record
+</code>
 The same general statement can be used to obtain other values from the form data as well.
@@ Line 93: / Line 117: @@
 <code sql>
-CREATE TABLE [dbo].[VGO_Form_ChangeHist](
+SET ANSI_NULLS ON
-	[RecordID] [int] IDENTITY(1,1) NOT NULL,
+GO
-	[FormID] [int] NULL,
-	[RecNum] [int] NULL,
+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,
-	[ModDate] [datetime] NULL,
+	[UserEmail] [nvarchar](50) NULL,
-	[Action] [nvarchar](10) NULL
+	[DataRecordID] [int] NULL,
+	[TransType] [nvarchar](10) NULL
 ) ON [PRIMARY]
+GO
 </code>
@@ Line 110: / Line 145: @@
 <code sql>
 -- Code for change history log
-DECLARE @formmode CHAR(1), @RecNum INT
+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
  --
- -- change "vgoRecNum" in the following select based on the value in the form
+-- Enter the Form ID (the number at the end of the procedure name) below
-SELECT  @RecNum = frm.value('(vgoRecNum)[1]','char(19)') FROM @FormData.nodes('/formData') AS HDR(frm)
+set @FormID = 74
-SELECT  @formmode=frm.value('(twbsformmode)[1]','char(1)') FROM @FormData.nodes('/formData') AS HDR(frm)
+--
+-- 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
-	(formID, RecNum, UserID, ModDate,Action)
+	(TransDateTime,formID, FormName, ActionType, FormMode, UserID, UserEmail, DataRecordID, TransType)
 	VALUES
-	(39, @RecNum, @UserID, getdate(), 'Add')
+	(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
-	(formID, RecNum, UserID, ModDate,Action)
+	(TransDateTime,formID, FormName, ActionType, FormMode, UserID, UserEmail, DataRecordID, TransType)
 	VALUES
-	(39, @RecNum, @UserID, getdate(), 'Update')
+	(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
-	(formID, RecNum, UserID, ModDate,Action)
+	(TransDateTime,formID, FormName, ActionType, FormMode, UserID, UserEmail, DataRecordID, TransType)
 	VALUES
-	(39, @RecNum, @UserID, getdate(), 'Delete')
+	(getdate(), @formid, @formname, 'A', @formmode, cast(@UserID as int), @useremail, @recnum, 'Delete')
 END
 </code>

Wiki

User Tools

Site Tools

Differences

Page Tools