Hello hivemind,
Our marketing/dev team is looking to send an email (L2) to all patrons that still have on account $ from cancelled productions. We would like to display the dynamic amount each constituent has on account. However, there is no on account output set elements.
Additionally, I have only used the default output set in the past.
Any ideas?
Michelle
Melissa Scott at Cincy Symphony set up an email output for me with this option. Copying her in here!
Hi Michelle,
I posted a solution for creating an On Account output set element here: https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_technical-9/25519/on-account-money-in-output-set/71910
Hope that helps!
-Sheela
Thank you! I am also not familiar with SQL (yet), so this is SUPER helpful.
Michelle,
Below is what we use for On Account output:
TR_QUERY_ELEMENT_GROUP
Description: On Account
Data From: (Select customer_no, NULLIF((-1 * SUM(pmt_amt)),0) as 'amt' From [dbo].t_payment a JOIN [dbo].vrs_payment_method b WITH (NOLOCK) ON a.pmt_method = b.id Where b.pmt_type = 2 group by customer_No)
Data Where: left blank
TR_QUERY_ELEMENT
Description: On Account Balance
Group Id: On Account
Data Select: !.amt
Single Row: checked
Thank you! I will give this a try!
Any ideas on how to accomplish this for only certain on account payment methods? I tried the following for the TR_QUERY_ELEMENT_GROUP - Data From, but the output results were wonky (i.e. showing 0 for constituents that had $ on account in that specific payment method).
Select customer_no, NULLIF((-1 * SUM(pmt_amt)),0) as 'amt'From [dbo].t_payment a JOIN [dbo].vrs_payment_method b WITH (NOLOCK) ON a.pmt_method = b.idWhere b.id IN (85) group by customer_No
I used Output filters and everything tracked correctly. I did one with no filters (to get total) then several unique or combined. I neede to know tickets Vs Education on Account balances.
There's a nice recipe book for this now as well: https://www.tessituranetwork.com/Help_System_v151h/Tessitura.htm#Recipe%20Books/Output%20Sets/On%20Account.htm - ditto Mary in that you can use filters to return specific on account payment methods.