Hello All,
We have recently implemented WordFly and would like to use it to send performance reminders to our subscribers. I'm wondering if there is a way to do this that would be totally automated. What we have in mind is to use a standard template that pull in fields from an output set that would populate the subscriber's name, the performance and the date of the performance. Since I am unable to use a standard output set that includes performance date and name without getting multiple rows for each subscriber, I'm wondering if anyone has figured out a way to do this in the back end - or if there's a way to limit the output in output builder to only the performance and perf_date that is associated with "run_date" + 3 days.
Thanks!
We are trying to implement this and have run into a problem where we have Parking set up as a Performance also. Is there a way to use a second performance name in the mail to indicate parking is purchased? Has anyone tried to send an email using two Performance Name fields?From: James Boncek <bounce-jamesboncek9049@tessituranetwork.com>Sent: 4/12/2011 8:13:45 PMHey! Well, this thread prompted me to finally dig in to the topic, and with some good success. Here is what i did;1: i created a View2: Made some custom TR_QUERY_ELEMENTs3: Set up an Output Set with those elements - say using "Report Run Date +1 day"4: Made a List of people where Tickets - Performance Date is between Report Run Date +1 day and Report Run Date +1 day5: run the list on the extraction or extraction on the list, however you think it...I'm curious to know if there is anything wrong with this method, or if it is just as well...The only problem i really anticipate, which is rare for us, is if 1 person had tickets to 2 events in 1 day.I started with figuring out what data i needed in my view, and modified the LVS_TKT_HIST just as heather did, and made LVS_TKT_HIST_PERF to include the data i needed. You can see, i LEFT OUTER JOIN additional content types for event data that wasn't available or as i wanted it; USE [impresario]GO/****** Object: View [dbo].[LVS_TKT_HIST_PERF] Script Date: 04/12/2011 21:03:07 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGO CREATE View [dbo].[LVS_TKT_HIST_PERF]AS /*************************************************************************************************Modified CWR 2/16/2004 to get rid of outer joins *************************************************************************************************/ SELECT a.customer_no, a.perf_no, CONVERT(VARCHAR, a.perf_dt, 101) as perf_dt, a.perf_name, a.mos_category, a.order_dt, a.num_seats, a.price_type, a.location, right(T_PERF.doors_open, 7) as doors_open, right(T_PERF.doors_close, 7) as doors_close, TR_THEATER.description, TR_THEATER.street, TR_THEATER.city, TR_THEATER.state, TR_THEATER.postal_code, TR_THEATER.driving_dir, a.tck_amt, b.value as opener, c.value as opener_time, d.value as headline_time, e.value as headline_nameFROM LT_TKT_HIST AS a INNER JOIN T_PERF ON a.perf_no = T_PERF.perf_no INNER JOIN T_FACILITY ON T_PERF.facility_no = T_FACILITY.facil_no INNER JOIN TR_THEATER ON T_FACILITY.th_no = TR_THEATER.id LEFT OUTER JOIN TX_INV_CONTENT AS b ON T_PERF.perf_no = b.inv_no AND b.content_type = '13' LEFT OUTER JOINTX_INV_CONTENT AS c ON T_PERF.perf_no = c.inv_no AND c.content_type = '14' LEFT OUTER JOINTX_INV_CONTENT AS d ON T_PERF.perf_no = d.inv_no AND d.content_type = '15' LEFT OUTER JOINTX_INV_CONTENT AS e ON T_PERF.perf_no = e.inv_no AND e.content_type = '16'WHERE (a.season IN (SELECT id FROM VRS_SEASON)) OR (b.content_type = '13') OR (c.content_type = '14') OR (d.content_type = '15') OR (e.content_type = '16') OR (ISNULL(a.season, 0) = 0) This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing 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!
We are trying to implement this and have run into a problem where we have Parking set up as a Performance also. Is there a way to use a second performance name in the mail to indicate parking is purchased? Has anyone tried to send an email using two Performance Name fields?
From: James Boncek <bounce-jamesboncek9049@tessituranetwork.com>Sent: 4/12/2011 8:13:45 PM
Hey! Well, this thread prompted me to finally dig in to the topic, and with some good success. Here is what i did;
1: i created a View2: Made some custom TR_QUERY_ELEMENTs3: Set up an Output Set with those elements - say using "Report Run Date +1 day"4: Made a List of people where Tickets - Performance Date is between Report Run Date +1 day and Report Run Date +1 day5: run the list on the extraction or extraction on the list, however you think it...
I'm curious to know if there is anything wrong with this method, or if it is just as well...The only problem i really anticipate, which is rare for us, is if 1 person had tickets to 2 events in 1 day.
I started with figuring out what data i needed in my view, and modified the LVS_TKT_HIST just as heather did, and made LVS_TKT_HIST_PERF to include the data i needed. You can see, i LEFT OUTER JOIN additional content types for event data that wasn't available or as i wanted it;
USE [impresario]
GO
/****** Object: View [dbo].[LVS_TKT_HIST_PERF] Script Date: 04/12/2011 21:03:07 ******/
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
CREATE View [dbo].[LVS_TKT_HIST_PERF]
AS
/*************************************************************************************************
Modified CWR 2/16/2004 to get rid of outer joins
*************************************************************************************************/
SELECT
a.customer_no, a.perf_no, CONVERT(VARCHAR, a.perf_dt, 101) as perf_dt, a.perf_name, a.mos_category, a.order_dt, a.num_seats, a.price_type, a.location, right(T_PERF.doors_open, 7) as doors_open,
right(T_PERF.doors_close, 7) as doors_close, TR_THEATER.description, TR_THEATER.street, TR_THEATER.city, TR_THEATER.state, TR_THEATER.postal_code,
TR_THEATER.driving_dir, a.tck_amt, b.value as opener, c.value as opener_time, d.value as headline_time, e.value as headline_name
FROM LT_TKT_HIST AS a INNER JOIN
T_PERF ON a.perf_no = T_PERF.perf_no INNER JOIN
T_FACILITY ON T_PERF.facility_no = T_FACILITY.facil_no INNER JOIN
TR_THEATER ON T_FACILITY.th_no = TR_THEATER.id LEFT OUTER JOIN
TX_INV_CONTENT AS b ON T_PERF.perf_no = b.inv_no AND b.content_type = '13' LEFT OUTER JOIN
TX_INV_CONTENT AS c ON T_PERF.perf_no = c.inv_no AND c.content_type = '14' LEFT OUTER JOIN
TX_INV_CONTENT AS d ON T_PERF.perf_no = d.inv_no AND d.content_type = '15' LEFT OUTER JOIN
TX_INV_CONTENT AS e ON T_PERF.perf_no = e.inv_no AND e.content_type = '16'
WHERE (a.season IN
(SELECT id
FROM VRS_SEASON)) OR (b.content_type = '13') OR (c.content_type = '14') OR (d.content_type = '15') OR (e.content_type = '16') OR
(ISNULL(a.season, 0) = 0)