TNEW Custom Form Stored Procedures

The API is described here at the VERY BOTTOM of the page:

https://www.tessituranetwork.com/TNEW_7/TNEW.htm#Topics/Form_Fields.htm

Note that @line_id is required on product-based custom forms, but not on a checkout survey form. @line_id means something different based on the product type that the form is being submitted on:

• Contribution: @line_id is the ref_no, and can be looked up in dbo.T_WEB_CONTRIBUTION for the duration of the session.
• Performance (Ticket): @line_id is the li_seq_no, and can be looked up in dbo.T_WEB_LINEITEM

TIPS:

TO CONFIRM: If all of the fields in a form are optional, the stored procedure will NOT fire if the form is interacted with, but WILL fire if the form is interacted with, even if the state of the form is the same as when the page loaded.

To suppress procedure functionality when the form is submitted empty, you must add conditional logic to your procedure.

When switching from default CSI creation to stored procedure execution, a couple of data points that are supplied automatically on CSIs are not provided directly to the procedure, depending on the product type.

For example, a custom form on a contribution saved to a CSI will include Contrib Type, Contrib Amount, Ref No (as Line Item ID), and Order Number by default in addition to the form fields, but when saving to a stored procedure, only @line_id is supplied along with the sessionkey and form fields. If you need these other data points, you must fetch them yourself.

Fetch Contrib Type Example:

DECLARE @contrib_type int;

WITH semicolon_split AS (
  SELECT
      value,
      ct_start = CHARINDEX('c', value) + 1,
      ct_end = CHARINDEX('_', value)
    FROM dbo.T_WEB_ORDER
      CROSS APPLY STRING_SPLIT(notes, ';')
    WHERE sessionkey = @sessionKey
      AND value LIKE '%c%[0-9]\_[0-9]%' ESCAPE '\'
), parsed AS (
  SELECT
      contrib_type = TRY_CONVERT(int, SUBSTRING(s.value, s.ct_start, s.ct_end - s.ct_start)),
      ref_no = TRY_CONVERT(int, SUBSTRING(s.value, s.ct_end + 1, LEN(s.value) - s.ct_end))
    FROM semicolon_split s
)
SELECT @contrib_type = contrib_type
  FROM parsed
  WHERE ref_no = @line_id;