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
Hi Mark,
I have a process which accepts data from fields set up in LTR_TNEW_CUSTOM_FORM_DATA . For a particular production, the purchaser is required to answer 6 questions - the stored procedure then inserts the responses into a local (ie custom) table for reporting. For clarity, we are on RAMP running Tessitura v15.1 and TNEW v7.40
Feel free to email me directly at martin.keen@nida.edu.au if you want to discuss this more.
Martin
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
Just want to direct people's attention to a space for community documentation (anyone is free to add to it) about TNEW Custom Form stored procedures here: https://community.tessituranetwork.com/topical_groups/developers/w/community-developer-documentation/783/tnew-custom-form-stored-procedures -- you might find some useful tips here.
Thank you all so much for the info, Martin, I'll be reaching out when I'm back at work Thurs to see if you can send me a copy of the code. All of this is great info for me to get started, I'm not a newbie to SQL or programming in general but just needed some examples to get my head around how it works. Eventually the first thing I'd like to do is the following:
- When someone submits additional names under a new membership purchase via a tnew custom field it triggers a stored procedure that:
- (checks for if the additional name is already connected to the account) - checks if name is already an existing const record - if so...
- If not then adds new constituent record for additional name on membership and connects to the household (via affiliate I believe is how our setup is)
That's the very short of the first programming I'd do - but it just would automatically create a new record and connect to the household constituency vs our membership staff having to do this manually every time someone adds an additional name on 'memberships + additionals' (our memberships are confusing which makes things harder, but just trying to save staff time!!!)
More to come, thanks again!