WordFly perf reminder recipe?

I'm putting Heath's emails below for everyone:

Hey Margaret,

So this is from 2019 but we use the same procedure

• List Suppression: is because Philanthropy want to control some people
• Constituent Type is because of schools and corporations that it gets lost at and we’ll likewise cater for them specifically
• Tkt Hist Prod Season: is obvious
• Tkt Hist Role: in our case we want it to go to the order owners only but if you are ok with Initiator Recipients (and it’s a good idea if you do that) then select as needed
• Tkt Hist Run Date:  OK this is interesting – it’s fine to do >= +3 and then ,<= + 3 for your needs
  • BUT because we have check the scheduled email Send to a subscriber ONCE A WEEK in Wordfly I left this as the below.
• Tkt Hist Performance: We wanted to leave some out (like schools and the dress rehearsal) that appear in the Prod Season
• Eaddress: like %@% because we have some website addresses to bad entries occasionally crop up

And then regarding the Output set:

Ahh … I did that myself.  I set up a local view for the Next Performance (attached).

Output is Customer_no, Perf_name and perf_dt

Otherwise you might be able to set up a TR_QUERY_ELEMENT_GROUP from https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_ticketing-0/10587/output-set---next-performance

H

USE [impresario]
GO

/****** Object: View [dbo].[LV_NEXT_PERF] Script Date: 11/10/2021 11:57:25 AM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE View [dbo].[LV_NEXT_PERF]
AS
Select distinct
pn.customer_no,
pn.perf_name,
pn.perf_dt
from VS_TICKET_HISTORY pn
JOIN (select th.customer_no,
perf_dt = min(th2.perf_dt)
from VS_TICKET_HISTORY th
Join (select customer_no, perf_dt
from VS_TICKET_HISTORY
where
perf_dt >= GETDATE() ) th2 on th.customer_no = th2.customer_no
group by th.customer_no ) thd ON
Cast(thd.customer_no as varchar(20))+Cast(thd.perf_dt as varchar(20))
= Cast(pn.customer_no as varchar(20))+ Cast(pn.perf_dt as varchar(20))

GO

Heath, Thank you so much for this!  Clearly is was really needed by this community and you are an angel!

I'm putting Heath's emails below for everyone:

Hey Margaret,

So this is from 2019 but we use the same procedure

• List Suppression: is because Philanthropy want to control some people
• Constituent Type is because of schools and corporations that it gets lost at and we’ll likewise cater for them specifically
• Tkt Hist Prod Season: is obvious
• Tkt Hist Role: in our case we want it to go to the order owners only but if you are ok with Initiator Recipients (and it’s a good idea if you do that) then select as needed
• Tkt Hist Run Date:  OK this is interesting – it’s fine to do >= +3 and then ,<= + 3 for your needs
  • BUT because we have check the scheduled email Send to a subscriber ONCE A WEEK in Wordfly I left this as the below.
• Tkt Hist Performance: We wanted to leave some out (like schools and the dress rehearsal) that appear in the Prod Season
• Eaddress: like %@% because we have some website addresses to bad entries occasionally crop up

And then regarding the Output set:

Ahh … I did that myself.  I set up a local view for the Next Performance (attached).

Output is Customer_no, Perf_name and perf_dt

Otherwise you might be able to set up a TR_QUERY_ELEMENT_GROUP from https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_ticketing-0/10587/output-set---next-performance

H

USE [impresario]
GO

/****** Object: View [dbo].[LV_NEXT_PERF] Script Date: 11/10/2021 11:57:25 AM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE View [dbo].[LV_NEXT_PERF]
AS
Select distinct
pn.customer_no,
pn.perf_name,
pn.perf_dt
from VS_TICKET_HISTORY pn
JOIN (select th.customer_no,
perf_dt = min(th2.perf_dt)
from VS_TICKET_HISTORY th
Join (select customer_no, perf_dt
from VS_TICKET_HISTORY
where
perf_dt >= GETDATE() ) th2 on th.customer_no = th2.customer_no
group by th.customer_no ) thd ON
Cast(thd.customer_no as varchar(20))+Cast(thd.perf_dt as varchar(20))
= Cast(pn.customer_no as varchar(20))+ Cast(pn.perf_dt as varchar(20))

GO

Heath, Thank you so much for this!  Clearly is was really needed by this community and you are an angel!

If someone is in a scenario where they need near real time or plans on using something like this to send post visit e-mails and is in a situation where tickets can be upgraded to a membership I would highly advise against using any of the ticket history tables for this kind of thing as they will miss customers.

In a related scenario regarding building the criteria we had a situation where WF was crashing after about 8 months of operation due to the volume of duplicate customers being sent up each day based on the Criteria logic. While this is interest and not tickets the same logic could easily be applied to tickets as well.

IF OBJECT_ID (N'dbo.LFT_MBA_NewInterestsByLists', N'IF') IS NOT NULL  
    DROP FUNCTION dbo.LFT_MBA_NewInterestsByLists;  
GO  
CREATE FUNCTION dbo.LFT_MBA_NewInterestsByLists (
    @KeywordNo        INT
    ,@ListNo        INT
)
    /*Docu
        Date Created: - 06/03/2021
        Created by:  Ronald Radford/Jon Shaffer
        Purpose: Pull list of customers based on interest who have not previously received notification
        
        @KeywordNo        INT -interest to check for
        ,@ListNo        INT - List number bing used in

        Object Notes:
        This is a stop gap solution, at some point when the other fires are taken care of we will need
        to circle back and build a a logging table for tracking the customers as there is the potential for
        some to be missed if WF experience an error on last run.


        */
RETURNS TABLE  
AS  
RETURN   
(  
SELECT * FROM
(
SELECT customer_no , MIN(create_dt)as CreateDate, count(*) as Hits FROM TX_CUST_TKW
where tkw = @KeywordNo
AND selected = 'Y'
GROUP BY customer_no
) as K
WHERE CreateDate >= ISNULL((
SELECT MAX(DateLastUpdated) FROM LT_WF_EXPORT
WHERE ListNumber = @ListNo), DATEADD(day,-1, GETDATE()))
)

To get the list no for a new list you have to save the list first and then update the function with the list number. Here is how it is used in the list: