Question:
How can I update other SQL data when a Spitfire document is saved?
Can I inject complicated validations and business logic into the sfPMS data layer?
Answer:
Use the SaveCustomDocExtn rule group, described below.
Overview:
The SaveCustomDocExtn rules allow specific stored procedures to be run during the document save transaction. These advanced rules require TSQL programming. In addition, these rules have no effect unless the DocTypeConfig | CustomSaveExtenstions rule is checked (see KBA-01154).
Note: Except for the On Final rule, if your stored procedure raises an exception, the entire document save transaction is rolled back and the exception text is displayed, making it possible to perform extensive validations. We recommend you first consider using either the UI Configuration ‘required when’ feature or a Document Event Workflow Script. Warning: use of this rule group can have unintended and serious consequences!
The SaveCustomDocExtn rule group is found in the Rules Maintenance tool on the System Admin Dashboard. Each rule in the group consists of a rule name, a filter value (which can be blank) and a result value.
Informing the User
If your procedure raises an exception it will be display to the user. Your message can include some simple HTML markup. For example, this snippet prevents two documents from having the same date:
-- vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
DECLARE @ALTDMK UniqueIdentifier
SET @ALTDMK = (SELECT top 1 xh.DocMasterKey
FROM dbo.xsfDocHeader xh
JOIN dbo.v_DocState ds on ds.DocMasterKey = xh.DocMasterKey and ds.DocState <> 'C'
WHERE DATEDIFF( day, xh.DocDate ,@DocDate ) = 0
and xh.DocMasterKey <> @pDocKey
and xh.DocTypeKey = @ThisDTK
ORDER BY DocNo )
IF @ALTDMK is not null BEGIN
SET @MSG = 'A <a title="Open" class="sfDocHL" data-key="'
+ cast(@AltDMK as varchar(44))
+ '" data-permits="0" href="javascript:$PopDoc();">Doc with this date</a> already exists'
RAISERROR (@MSG, 16, 1)
END
-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The result is a message with an HTML anchor. The anchor carries a class indicating a document hyperlink is to be applied and a data attribute with the key of the document is to be opened.
The system will display a dialog similar to this
Filter Value:
Any Doc type
The SaveCustomDocExtn rules accept a Doc type (as defined in the Doc Types tool and appearing on the filter drop-down) as the filter value.
Result Value:
Stored Procedure
The SaveCustomDocExtn rules accept the name of any stored procedure as the result value. The procedure must be owned by ‘DBO’. Consult the Developer’s Primer for details on the parameters passed to the stored procedure: @pDocKey, @pDocRevKey and @pUserKey.
Rules:
OnApproveInitial
Specifies (in the result value) the stored procedure to be used at the beginning of the save transaction, when the new document state is changing and will be Approved.
OnApproveWrapup
Specifies (in the result value) the stored procedure to be at the end of the save transaction when the new document state is changing and will be Approved. Note: full coverage of every save requires OnWrapup, OnNewWrapup (below), OnPendingWrapup (below), OnApproveWrapup and OnCloseWrapup (below) to be used in combination.
OnApproveWritten
Specifies (in the result value) the stored procedure that should be used near the end of the save transaction when the new document state is changing and will be Approved, after row data has been updated, but before any other side effects or postings.
OnCloseWrapup
Specifies (in the result value) the stored procedure that should be used at the end of the save transaction when the new document state is Closed (but not Approved). Note: full coverage of every save requires OnWrapup, OnNewWrapup (below), OnPendingWrapup (below), OnApproveWrapup (above) to be used in combination.
OnFinal
Specifies (in the result value) the stored procedure to be used to send data after the save transaction. The extension will have access to audit data using a fourth required parameter (@pAuditWhen) but cannot roll back the save because it has already been committed. Likewise, since the extension is fired so late, any errors from this procedure (such as a missing parameter) will only appear in the web application log, not on the document window.
OnInitial
Specifies (in the result value) the stored procedure to be used at the start of the save transaction for previously saved documents.
OnNewWrapup
Specifies (in the result value) the stored procedure to be used at the end of the save transaction for new documents.
OnPendingInitial
Specifies (in the result value) the stored procedure to be used at the beginning of the save transaction, when the new document state is Pending/Committed.
OnPendingWrapup
Specifies (in the result value) the stored procedure to be used at the end of the save transaction, when the new document is Pending/Committed. Note: full coverage of every save requires OnNewWrapup (below), OnWrapup, OnApproveWrapup and OnCloseWrapup (above) to be used in combination.
OnPendingWritten
Specifies (in the result value) the stored procedure to be used near the end of the save transaction, after row data has been updated, when the new document state is changing and will be Pending/Committed.
OnWrapup
Specifies (in the result value) the stored procedure to be used at the end of the save transaction for previously saved documents. Note: full coverage of every save requires OnNewWrapup, OnPendingWrapup, OnApproveWrapup and OnCloseWrapup and OnWrapup all to be used in combination.
Additional Comments:
For information about the UI Configuration tool or the Rules Maintenance tool, see the Focus on System Administration guide.
KBA-01183; Last updated: September 22, 2017 at 11:54 am;
Keywords: save extension, rules