Output Elements/Set for WordFly Triggered Emails

Madeline,

We use Wordfly and have outputs that use next performance. Are you looking for the steps to create the query element? I can share my set up for TR_QUERY_ELEMENTS if that's what you need.

Melissa

Melissa,

I believe that is exactly what I'm looking for. You can e-mail me at mdummerth@centerstage.org if that's easier. Thank you!

Madeline 

I would love to have that too - mfrench@atlantaballet.com

I would love this too.  Triggered emails have been on my "to do" list for ages and I haven't been able to figure out a good way to pull this information.  Thank you in advance!  musadel@phoenixsyphony.org

Oh yes please

rmartin@waltonartscenter.org

I'd appreciate seeing this as well if it is okay? jcheek@perthfestival.com.au - thank you.

Hi Madeline,

I have done this for a few organisations now in a couple of different ways. Previous organisations had multiple shows on a week and we wanted to limit them, to only getting 1 email a week so for those I had to use a more dynamic view using a Table Function so that I could pull in all the performances a customer was attending that week.

For my current organisation this is not needed as we had week long runs at one venue and at the other the shows are individual enough that it is unlikely you would get too many emails in a week.

Here is a little explanation of what we are doing just so the logic in the View will make more sense to whoever is looking at it.

Before I came here 4 keywords being applied indicating whether they are eligible for Pre or Post Show emails for Theatre Royal or City hall, which is why I still reference those in theory would have one for Pre and one for Post only reason would keep the 2 is that it gives you more flexibility to switch on and off the emails for the shows, they are also at performance level so that yo can easily stop the emails if a show is cancelled,or if there is a cast change etc and you want to send a manual email instead.

For City Hall pre show emails get sent out the day before, for Theatre Royal 4 days before.
In our Post Show Email for City Hall we add the recommendations to Tessitura to pull through, as we can have different shows on Fri-Sun and didn't want someone logging in to manually change those.

Here is the View I currently use.

CREATE view [dbo].[lv_pre-post_emails] as

select
      theatre=case when f.th_no = 2 then 'Theatre Royal' when f.th_no=8 then 'City Hall' else 'Other' end,
      email_type=case
                             when p.perf_dt<GETDATE() then 'POST-SHOW'
                            else 'REMINDER'
                        end,
        o.customer_no,
        weekday=DATENAME(weekday,p.perf_dt) ,
        date=convert(varchar(11),p.perf_dt,113),
        start_time=replace(right(convert(varchar(20),min(p.perf_dt),100),8),':00',''),
        production_name=min(i.description),
        running_time=min(ISNULL(rt.value,'Please check the website closer to the performance.')),
        drinks_allowed=min(ISNULL(da.value,'Yes')),
        warning=min(ISNULL(wa.value,'NA')), -- Adding NA rather than blank/null as guarantee the response; write code to say if warning not equal to 'NA'
        header_image=min(case
                                            when img.value IS null and f.th_no=2 then 'theatre-royal.jpg'
                                            when img.value IS null and f.th_no=8 then 'city-hall.jpg'
                                            else img.value
                                        end ),
        perf_url=min(ISNULL(url.value,'')),
        order_no=min(o.order_no),
        email=MIN(ea.address),
        tickets_to_collect=max(case when sli.sli_status in (3) then 'Y' else 'N' end), --if tickets not printed can display collection details
        tickets_to_bring=max(case when sli.sli_status in (12) then 'Y' else 'N' end), --If tickets printed can re ind customer they have them to bring with them
        rec1_perf_url=min(ISNULL(pr1url.value,'')),
        rec1_perf_name=min(ISNULL(pr1name.description,'')),
        rec1_perf_text=min(ISNULL(pr1text.value,'')),
        rec1_perf_img=min(ISNULL(pr1img.value,'generic.jpg')),
        rec1_perf_dates=min(dbo.LFS_ProdDatesStr(pr1.value)),
        rec2_perf_url=min(ISNULL(pr2url.value,'')),
        rec2_perf_name=min(ISNULL(pr2name.description,'')),
        rec2_perf_text=min(ISNULL(pr2text.value,'')),
        rec2_perf_img=min(ISNULL(pr2img.value,'generic.jpg')),
        rec2_perf_dates=min(dbo.LFS_ProdDatesStr(pr2.value))
from t_facility f(NOLOCK)
join T_PERF p (NOLOCK) on p.facility_no=f.facil_no
and
(
                (f.th_no = 2 and convert(varchar(10),dateadd(day,4,GETDATE()),102)=convert(varchar(10),p.perf_dt,102)) --4 days before for TR
        or
                (f.th_no = 8 and convert(varchar(10),dateadd(day,1,GETDATE()),102)=convert(varchar(10),p.perf_dt,102)) --Day before for CH
        or
                convert(varchar(10),dateadd(day,-1,GETDATE()),102)=convert(varchar(10),p.perf_dt,102)
)

join T_SUB_LINEITEM sli with (NOLOCK) on sli.perf_no=p.perf_no and sli.sli_status in (3,12)
join T_ORDER o with (NOLOCK) on o.order_no=sli.order_no and o.customer_no>0
join T_EADDRESS ea with (NOLOCK) on o.customer_no=ea.customer_no and isnull(ea.primary_ind,'N')='Y' and isnull(ea.inactive,'N')='N'
join t_inventory i with (NOLOCK) on p.prod_season_no=i.inv_no
join TX_INV_TKW ik with (NOLOCK) on i.inv_no=ik.inv_no or p.perf_no=ik.inv_no
join TR_TKW k with (NOLOCK) on ik.tkw=k.id and k.category=8 --TNEW performance category

left join TX_INV_CONTENT rt with (NOLOCK) on (i.inv_no=rt.inv_no or p.perf_no=rt.inv_no) and rt.content_type=37 --running times
left join TX_INV_CONTENT da with (NOLOCK) on (i.inv_no=da.inv_no or p.perf_no=da.inv_no) and da.content_type=38 --drinks allowed
left join TX_INV_CONTENT wa with (NOLOCK) on (i.inv_no=wa.inv_no or p.perf_no=wa.inv_no) and wa.content_type=39 --warnings
left join TX_INV_CONTENT img with (NOLOCK) on (i.inv_no=img.inv_no or p.perf_no=img.inv_no) and img.content_type=40 --header image file
left join TX_INV_CONTENT url with (NOLOCK) on (i.inv_no=url.inv_no or p.perf_no=url.inv_no) and url.content_type=41 --url bit after www.theatreroyal.co.uk/.../ ie young-frankenstein

left join TX_INV_CONTENT pr1 with (NOLOCK) on (i.inv_no=pr1.inv_no or p.perf_no=pr1.inv_no) and pr1.content_type=42
left join TX_INV_CONTENT pr1url with (NOLOCK) on pr1.value=pr1url.inv_no and pr1url.content_type=41
left join t_inventory pr1name with (NOLOCK) on pr1.value=pr1name.inv_no
left join TX_INV_CONTENT pr1text with (NOLOCK) on pr1.value=pr1text.inv_no and pr1text.content_type=44
left join TX_INV_CONTENT pr1img with (NOLOCK) on pr1.value=pr1img.inv_no and pr1img.content_type=40

left join TX_INV_CONTENT pr2 with (NOLOCK) on (i.inv_no=pr2.inv_no or p.perf_no=pr2.inv_no) and pr2.content_type=43
left join TX_INV_CONTENT pr2url with (NOLOCK) on pr2.value=pr2url.inv_no and pr2url.content_type=41
left join t_inventory pr2name with (NOLOCK) on pr2.value=pr2name.inv_no
left join TX_INV_CONTENT pr2text with (NOLOCK) on pr2.value=pr2text.inv_no and pr2text.content_type=44
left join TX_INV_CONTENT pr2img with (NOLOCK) on pr2.value=pr2img.inv_no and pr2img.content_type=40

join tx_inv_tkw ps with (NOLOCK) on p.perf_no=ps.inv_no and ((ps.tkw in (59,91) and p.perf_dt<GETDATE()) or (ps.tkw in (60,90) and p.perf_dt>GETDATE()))

where f.th_no in (2,8)

group by
        case when f.th_no = 2 then 'Theatre Royal' when f.th_no=8 then 'City Hall' else 'Other' end,
        case when convert(varchar(10),GETDATE(),102)=convert(varchar(10),p.perf_dt,102) then 'TODAY' when p.perf_dt<GETDATE() then 'POST-SHOW' else 'REMINDER' end,
        convert(varchar(11),p.perf_dt,113),
        DATENAME(weekday,p.perf_dt),
        o.customer_no

Hope that helps

Mark

Happy to share!

Piling on, because why not: I'm actually implementing triggered performance reminders with WordFly right now at my organization. We wanted to do some pretty complex logic with regard to grouping multiple performances into single emails, while also making sure the email deploys no earlier than two days before the first performance in the group, and never sends the same performance to a customer twice. I've opted to do this with a two-step process. First, a "queuing" process runs every day that adds rows to a local table indicating which perf/customer pairs are to be sent today. (So if a customer has 6 performances in a weekend — yes, we actually do this — starting on Friday, the whole weekend will get queued the Wednesday prior.) Then, when WordFly does its daily list and output set pull, it gets the customers that were "queued" earlier in the day, and the output set rolls up all of the performance information for the customer (date/time, running time, venue, maps URL, other notes from content types) for ALL of his/her performances into a big ball of HTML that goes into a single data field in the WordFly template. We were originally going to output the perf information in multiple column sets, but WordFly can't do conditionals on data fields that contain other data fields. Frankly, I think it's a lot simpler this way; generating HTML from SQL isn't really that terrible. And it really would have been hell to deal with dozens of almost-identically-named data fields to cover multiple performances.

Hi Melissa,

If you don't mind another share, I'd love this info too.  cgoodwin@harbourfrontcentre.com

Thanks very much! Catherine

Hi Nick

Just saw your reply.

I actually did do this with multiple columns, and used the perf_no_X/prod_season_no_X field as the determining factor populating with NA when no performance.

Think I loaded up all my scripts for the dynamic view (table function) and the text for the wordfly template to the developer bit bucket account while I was at the NT

Mark

Hi Melissa,

Would love this information too! I'm at pia.fruin@ptt.wa.gov.au

Thanks,

Pia

Hi Melissa,

Could you share one more time?  brian_friedrick@asolo.org 

Thanks!

Hi Melissa!

This is a bit of an older thread, but pretty much the world I am living in right now.  Any chance you can share your setup with me?  kastop@theatrecalgary.com

Thanks!
Kristine

Hi Melissa!

Would you mind sharing the query elements with me as well? Thank you!

Mike Dorsey

CRM Specialist - National WWI Museum and Memorial

mdorsey@theworldwar.org