Differences

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

--- versago3:from_sp [2022/11/01 11:35]
dlee created
+++ versago3:from_sp [2024/04/09 14:33] (current)
dlee
@@ Line 1: / Line 1: @@
-====== Custom Store Procedures ======
+====== Custom Stored Procedures ======
 There are two custom procedures available for use.  These procedures are automatically created when a new form is created.
@@ 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),
+		@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>
@@ Line 81: / Line 106: @@
 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,'') = 'A' AND @ActionType = 'S' - the record is being added. \\
-IF isnull(@FormMode,'X') = 'F' AND @ActionType = 'S' - the record is being updated. \\
+IF isnull(@FormMode,'') = 'F' AND @ActionType = 'S' - the record is being updated. \\
-IF isnull(@FormMode,'X') = 'X' AND @ActionType = 'D' - the record is being deleted.
+IF isnull(@FormMode,'') = 'F' 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.

Wiki

User Tools

Site Tools

Differences

Page Tools