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. This used to be passed as @li_seq_no.

TIPS:

When debugging these procedures, note that TNEW maintains backward compatibility with the old @li_seq_no parameter by executing the procedure using @line_id first, and then if that execution fails, it submits an additional execution using @li_seq_no as the parameter name instead. It does not filter on execution failures due to missing parameters. This means that you could have written your procedure's parameter signature correctly but still have an unrelated error in your code, and the last message you see in the TNEW logs will be an error related to parameters, since this failure will be from TNEW retrying with the old parameter name. Make sure to scroll past the most recent error to the initial execution to see what the actual issue was with your procedure. Also, don't add both @li_seq_no and @line_id to your procedure's parameter signature as optional, because then if your procedure encounters an actual error state, it could get executed again, and that may cause duplicate data since these procedures are not intended to be idempotent. (N.B. TNEW should just get rid of this backward compatibility to make this all simpler; hopefully this occurs in V16.)

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 CONFIRM: All stored procedures are invoked when the order is being saved; after payment has been accepted, but before the order is fully completed.

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;

Recommended