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
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.