We want to process elevated event reservations online and would like to create a procedure that populates the event listing with form data instead of generating a CSI.
1. I generated the different parts of the form in LTR_TNEW_CUSTOM_FORM_DATA and made note of the Field Name I used for each row. 2 text fields, and an integer field.
2. I created the Procedure LRP_TN_EXPRESS_WEB_SAVE_PPD_DATA and added the 3 Field Names from system tables as parameters to the procedure along with the session key and line id parameters.
3. I finished the procedure and the procedure works as expected when run from the database. I generated an order online and used the session key from that web order as the parameter and then manually entered in the values that I would have submitted on the form. I did not use the line id parameter for anything since it doesn't appear to me that an elevated event contribution generates a row in the T_WEB_LINEITEM table, so that parameter was left null. The event listing in the customers history tab was updated correctly.
4. I added this procedure to the TR_LOCAL_PROCEDURE table.
5. I added a line to the LTR_TNEW_CUSTOM_FORM_SAVE_DATA table, selected my new procedure from the Procedure Name drop down and check the Use Stored Proc box.
6. I also made sure to reset the cache and form data for TNEW before going to the test site to submit a new reservation.
However, when I submit a new event reservation online, the event listing is not updated or created. I feel like I've done everything correctly according to the documentation but I must be missing an important step.
P.S. I noticed a new forum section called Developers? I've always posted technical questions in IT but if Developer section is more suited for it, I can start posting there from now on.
Another note to anyone who does have any thoughts on this. I did forget to grant execute rights to the ImpUsers for the procedure. I corrected that, and then reset the cache and custom forms on the TNEW admin page and attempted another web transaction. No luck though on it working this time either. So I must have missed something else.
Maybe check your names and I would suggest creating logging at each step to see where it stops firing off.
"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."
I should mention too, that we do event registrations online and insert/update directly into the elevated events (create new or update invitation). I will say it is much easier if you just create your own form on your content site or spit out some html forms in a precart page and simply call your custom procedure. The process you mentioned I feel has too many failure points (I know it is the recommended, but...).
The only two thoughts that occur to me are these: 1) firstly, I have not worked with Elevated Events much at all, and maybe there is a disconnect between that and the contribution type you are trying to use to associate to the order (since there is no keyword in use) in the Custom Form Save Data table given that, as you said, there is not going to be an @line_id generated, or 2) is it possible that you have a duplicate entry in the Save Data table for that one? That has been known to cause issues.
If it is neither of those two, similar to what Travis suggests, you could always try simplifying everything to see when it STARTS firing, and maybe that will clue you in to what was going wrong. Like take it down to one parameter with a simple query (though the length and complexity of the procedure should not theoretically be causing the issue here) just to see if it works first.
I will admit to having had to go through a lot of testing the first time I wanted to use a local procedure for such a situation, too, but I got there in the end.
Best of luck!