We went live in January, and I'm still getting my feet wet w/r/t List Manager. I'm trying to set up our Pre-Show Reminder lists to work with WordFly and automatically send x number of days before the performance, depending on type. All well and good, until I start setting up the reminders for our School Day program, which is more of a group sales kind of situation. Due to the way some of the schools pay, we may not get paid until a month after the performance.
If I just use the following criteria, I only get paid orders:
Ideally, I'd like to set up two lists, one for paid and one for unpaid, so I can change the wording on the unpaid email and remind them they still owe us money. It's also way more important to remind the schools that haven't paid that they have a performance coming up, as the number of no-shows has skyrocketed since 2020.
It would be good to use on our Mainstage shows as well, since sometimes we reserve tickets for members and I'd love to send them an automatic message a week or two before the show reminding them of an outstanding balance.
However, I suspect this is not currently possible as things are set up. Has anyone managed to do this?
I don't think there are any out of the box criteria that look at unpaid orders. For lists, you can manually edit the criteria if you have a SQL query you want to use. This would probably work, but you have to lookup to see what the ID# is for your School Day Performance title. To find that just open Ticketing Setup and it should take you to Title Setup screen. Just find the ID number that corresponds to this title.
Once you have discerned what the ID # for that title is, you need to replace <title_no> with that number:SELECT o.customer_noFROM T_ORDER o join T_SUB_LINEITEM s on o.order_no = s.order_nojoin T_PERF p on s.perf_no = p.perf_nojoin T_PROD_SEASON ps on p.prod_season_no = ps.prod_season_nojoin T_PRODUCTION pr on ps.prod_no = pr.prod_noWHERE s.sli_status in (1,2)AND pr.title_no = <title_no>AND p.perf_dt <= DateAdd(dd, 7, CONVERT (VARCHAR, GetDate(), 112) + ' 23:59:59.997') AND p.perf_dt >= DateAdd(dd, 7, CONVERT (VARCHAR, GetDate(), 112))AND not exists (SELECT * FROM T_TICKET_HISTORY WHERE customer_no = o.customer_no and perf_no = p.perf_no)
Keep in mind, that this query excludes partially paid orders. Those orders will have ticket history and will appear in the list you described above. If you want a separate list for partially paid orders, then that would be a whole different query.
Amazing, that did the trick.
Out of curiosity, why would partially paid orders be a different query? My (completely uneducated in SQL) assumption in reading this is that the line about sli_status is pulling in the unpaid status - isn't there just a number that indicates partially paid you could add in there? Or can you only do one sli_status at a time?
Sub Line Items represent individual tickets, which can be either paid or unpaid, in this instance. There is no partial paid status. So a partially paid order will have some of the sub lineitems with the status of 1 or 2 for unpaid, and others with a status of 3 or 12 for paid. The paid ones will appear in the customer's ticket history and will pull into your first list even though they still have unpaid tickets in the order.
If you don't normally do partially paid orders, then it shouldn't be a problem, or if you are fine with partially paid orders appearing in your list for paid customers, then these 2 lists should work.
Good point with the SLI status. Check out TR_SLI_STATUS (which is why Jesse had sli.sli_status in (1,2) in the query).
Thank you, that makes sense.
For extra fun, here's a bit of Tessitura esoterica about what order money gets allocated in a situation where a ticket order is only getting partially paid. From the very bottom of this page in the documentation: https://docs.tessitura.com/Help_System_v151h/Tessitura.htm#Ticket_Order_Processing/Payment%20Window.htm
I remember learning this ages ago and this thread reminded me of it again!
When payments are made for a ticket order, the money is applied to the order contents in the following sequence: Contributions Fees Performances, in the following sequence: By sub line item status, first Seated, Unpaid (SUP), then Unseated, Unpaid (UNS) By performance date and time, from earliest to latest By sub line item number, from lowest to highest Performances are handled the same regardless of whether they are included in a package or not
When payments are made for a ticket order, the money is applied to the order contents in the following sequence:
Contributions
Fees
Performances, in the following sequence:
By sub line item status, first Seated, Unpaid (SUP), then Unseated, Unpaid (UNS)
By performance date and time, from earliest to latest
By sub line item number, from lowest to highest
Performances are handled the same regardless of whether they are included in a package or not
That is actually some good knowledge to have. Especially the performance sequence.
Circling back around why unpaid unseated orders are missing from out of the box criteria: They look at the view vs_ticket_history that's populated overnight from t_ticket_history, which is populated more frequently as mentioned above from orders, sub_lineitems,etc, where, among other details, the sli_status is ticketed and paid. The levels of roiling complication in all the details point to Order Search to sort them all out at any given moment.