(I'm cross-posting this to a couple of forums; apologies to those who get multiple copies...)
Tessiturians -
I am currently working with our DBA to investigate the possibility of creating an order confirmation email that can be used by our financial aid staff. Currently, we use an excel sheet (shudder) and a Word/Outlook mail merge (shudder again) to accomplish this. Ideally, it would be infinitely better if the staff person could just send a confirmation right from Tess instead.
The problem we are running into is including some custom data in these confirmations. Our DBA and I have only a small smidgen of experience with Web API stuff, but from reading the documentation we are unsure about bringing in some of the data elements we'd like to have on the confirmation. Specifically, the name of the Recipient for each line item, and the total financial aid disbursement (which is recorded on a custom screen on the constituent record).
Does anyone have experience with doing something like this?
Thanks!
Patrick SchleyAcademy Services ManagerFirst Stage
Hi Patrick,
Yes we have something like this in place. In our case, we wrote a stored procedure to return data that wasn’t already available and made a SOAP API call in the template to execute that local procedure.
Here is some sample code:
<%
Tessitura.WebAPI.Tessitura tess = new Tessitura.WebAPI.Tessitura();
string sessionkey = tess.GetNewSessionKey("");
ds = tess.ExecuteLocalProcedure(sessionkey, 116, "@ORDER_NO=" + OrderData.OrderNumber);
string ticketSubheader = ds.Tables[0].Rows[0]["ticket_subheader"].ToString();
XmlNode xm = OrderData.CartDocument.SelectSingleNode("//Order");
if (xm != null)
{
XmlElement xeTicketSubheader = OrderData.CartDocument.CreateElement("ticket_subheader");
xeTicketSubheader.InnerText = ticketSubheader;
xm.AppendChild(xeTicketSubheader);
}
%>
The above is a basic example where the procedure is just returning one thing, but we have other more complex examples and I know I’ve seen other posts on this as well. We’re basically just grabbing the data from the procedure and extending the XML so it is accessible in XSLT.
I think I originally learned how to do this from something Jon Ballinger posted. I hope this helps.
Thanks,
David
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Patrick Schley Sent: Friday, August 28, 2015 12:57 PM To: David Frederick <DFrederick@scfta.org> Subject: [Tessitura Technical Forum] Using custom data in an email confirmation
Patrick Schley Academy Services Manager First Stage
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Hi again – here is another example that adds custom data retrieved from a stored procedure to one or more line items in the order confirmation data:
ds = tess.ExecuteLocalProcedure(sessionkey, 114, "@ORDER_NO=" + OrderData.OrderNumber);
for (int i = 0; i < ds.Tables[0].Rows.Count; ++i)
string liSeqNo = ds.Tables[0].Rows[i]["li_seq_no"].ToString();
string pkgDesc = ds.Tables[0].Rows[i]["pkg_desc"].ToString();
XmlNode xm = OrderData.CartDocument.SelectSingleNode("//LineItem[li_seq_no=" + liSeqNo + "]");
XmlElement xePkgDesc = OrderData.CartDocument.CreateElement("new_pkg_desc");
xePkgDesc.InnerText = pkgDesc;
xm.AppendChild(xePkgDesc);
Thanks, David – we’re going to dive into this and I may circle back with you if need be!
-p.
Patrick Schley Academy Services Manager
FIRST STAGE
Transforming Lives Through Theater
325 West Walnut Street | Milwaukee, WI 53212 (414) 267-2942 direct | (414) 267-2976 fax
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David Frederick Sent: Friday, August 28, 2015 5:33 PM To: Patrick Schley <pschley@firststage.org> Subject: RE: [Tessitura Technical Forum] Using custom data in an email confirmation
Okay, new question:
Is there a way to define separate "From"/"BCC" addresses for different email acks? Currently everything comes from our box office email (because until now, the box office have been the only ones using the functionality), but for the financial aid acknowledgements we want them to come from a different address. I see that in 12.5 there is TR_RECEIPT_SETTINGS, and it seems like maybe it's what I want, but that also seems to be designed to be used only with TRBO?
Any other insights?
I brought this problem to Jon Ballinger at the Conference during the "Hackathon" section, and he knocked out a proof of concept for doing it using interceptors.
I'd love to see the sample code for that. The only way I have found to do this natively is to add a separate parent table with its own settings, which is not a very practical way for managing this.
Unknown said: Is there a way to define separate "From"/"BCC" addresses for different email acks?
Is there a way to define separate "From"/"BCC" addresses for different email acks?
If you assemble your e-mail ack in SQL, this becomes a trivial case statement. We choose between multiple From/CC/BCC values, depending on order content.
I'll just add a new snippet of code into this thread, one that may read a little easier than some of the previous code snippets (which were also helpful to me).
We are using the latest TNEW out-o-the-box and needed a way to add into the order confirmation, the name/address/etc.. of the person that, via TNEW, our customer purchased a gift membership for.
So basically, it was just another customer service tidbit (non-line item related) that we wanted to throw into the email.
First was the creation of the stored procedure to pull the giftee membership info out of the CSI data that came in on the TNEW order. The stored procedure takes an order_no, and returns a single row with heading "recipient_info", containing the concatenated name and address information of the giftee. I'll add that later if someone wants it.
Next was to make the web api call and dump the SProc data into the order confirmation template, concatenating it with Contrib_DynEmailContent. Since we will always have a "Thank you..." message for contributions, I chose to add it into the bit of code that dumps out the dynamic email content for products of type "contribution". Also, I plugged in the "t" (var t = new Tessitura.WebAPI.Tessitura();) and session info (OrderData.SessionKey) via how they were first defined earlier in the email confirmation template code.
if (ced.Contrib_DynEmailContent.Length > 0) {
dynContent += ced.Contrib_DynEmailContent + "<BR/><BR/>";
//mnhs: begin
DataSet result2 = t.ExecuteLocalProcedure(OrderData.SessionKey, 52, "@order_no=" + OrderData.OrderNumber);
for (int i = 0; i < result2.Tables[0].Rows.Count; ++i) {
string giftMembershipInfo = result2.Tables[0].Rows[0]["recipient_info"].ToString();
dynContent += "<h3>Your Gift Membership Information</h3><table><tr>";
dynContent += "<td width='10px'></td><td>" + giftMembershipInfo + "</td></td>";
dynContent += "</tr></table>";
//mnhs: end
The 'for loop' was added because, even though there will only be a single row returned, i wanted the code ignored if someone bought a standard membership (not a gift membership).
This dropped the following lines into the standard TNEW order confirmation email if a Gift Membership was purchased:
Joe Bob Briggs 135 Rose ct nw andover, MN (1) 55304