Hi,
I'm looking for some help getting started with building stored procedures for TNEW custom fields vs sending them to CSI.
Having trouble getting started and not finding much documentation on this, I don't know what variables are sent to the procedure from TNEW, like session key, order number, etc.
Like for instance LRP_TNEW_GET_ORDER_CONTRIBUTIONS is a stored procedure that looks up order info, so if I have TNEW run that stored procedure, will it send it the order number?
Anyone have any examples of something simple, like using a stored procedure in a custom from field to update an attribute in a constituent record or something like that?
Thanks in advance for any help!
Mark
I've written a couple now and the link to the quite sparse documentation is below
Just a tip that other than the parameters below you must include ALL the form fields on the custom form need to be represented as parameters on the stored procedure otherwise it'll fail.
The sproc can do anything like adding data to order note fields, update interests, or whatever you set you mind on. I started with copying Beth Hawryluk's custom sproc from the the Education and TNEW presentation back from TLCC 2017 (slide #37) https://www.tessituranetwork.com/Passthrough?itemUri=/tlcc/2017/Pres/08_08_EDU_TNEW.pptm
Tess help documentation:https://www.tessituranetwork.com/TNEW_7/Content/Topics/LTR_TNEW_CUSTOM_FORM_DATA.htm
Using Local Stored Procedures to Save Data: Data entered into form fields can be saved to a field in the Tessitura database (e.g., a custom order field) using a local procedure associated in the LTR_TNEW_CUSTOM_FORM_SAVE_DATA system table. If a local stored procedure is used to save TN Express Web custom form data, the relevant field name values from LTR_TNEW_CUSTOM_FORM_DATA must be included as parameters in the local stored procedure. The field name and parameter name must be an exact match, or the data will not be correctly saved back into Tessitura. Do not use spaces or special characters (other than an underscore) in the field name/parameter name to avoid runtime errors in SQL Server. Other parameters required in the local stored procedure include: @sessionKey (varchar(64)) @line_id (varchar(50)) [for auxiliary items, gift certificates or contributions] Note: The use of the parameter @li_seq_no has been deprecated. It is recommended that procedures be updated to use the @line_id parameter. These procedures must also have SQL Server 'execute' permissions granted to ImpUsers and tessitura_app before they can be used by the Tessitura API.
Using Local Stored Procedures to Save Data:
Data entered into form fields can be saved to a field in the Tessitura database (e.g., a custom order field) using a local procedure associated in the LTR_TNEW_CUSTOM_FORM_SAVE_DATA system table.
If a local stored procedure is used to save TN Express Web custom form data, the relevant field name values from LTR_TNEW_CUSTOM_FORM_DATA must be included as parameters in the local stored procedure. The field name and parameter name must be an exact match, or the data will not be correctly saved back into Tessitura. Do not use spaces or special characters (other than an underscore) in the field name/parameter name to avoid runtime errors in SQL Server.
Other parameters required in the local stored procedure include:
Note: The use of the parameter @li_seq_no has been deprecated. It is recommended that procedures be updated to use the @line_id parameter.
These procedures must also have SQL Server 'execute' permissions granted to ImpUsers and tessitura_app before they can be used by the Tessitura API.
And another thing....
The parameter name in the stored procedure must exactly match the LTR_TNEW_CUSTOM_FORM_DATA.field_name value