Hi everyone -
Is there a way to get on account money into an output set/ criteria? We ideally would want to be able to eventually use this field as a data mapping for WordFly so that we can update patrons on their credits available.
I'm not super familiar with the tables I would need to enter in order to be able to get this information, so any help is appreciated.
Thank you!
Kari
*I have very little SQL knowledge and ideally would be able to do this via the system tables for keywords (Criteria) and query elements (output set)*
Hi Kari,
I achieved this by adapting the code from the system table TR_FLEX_HEADER_ELEMENT for the On Account flex header element. My way of doing it does require creating a local view in SQL - if you're comfortable doing that let me know and I'd be happy to share my code with you (you can reach me at ssur@publictheater.org).
Best,
Sheela
I unfortunately have very little experience with SQL (on my list for 2020), but thank you!
No worries! I think I've figured out how to do it all in System Tables in Tess:
In TR_QUERY_ELEMENT_GROUP, add a new row with these values:
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 = leave blank
Category = whatever you like, maybe Constituent?
Then in TR_QUERY_ELEMENT, add a new row with these values:
Description = On Account Value
Group ID = On Account
Data Select = !.amt
Control Group = the control group that needs access to it
Single Row= checked (I think?)
I think that would work, just did a very quick test of it in my Test environment. This should work if you only use one On Account payment method, or want to sum all of your On Account payment methods together in the output.
This worked beautifully for me! Thanks so much, Sheela!
SQL is also on my list of things to learn for 2020, but in the meantime I greatly appreciate the support from fellow Tessi users here on the forums.
This was super helpful Thank you!
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
Hi Michelle,
I think you should be able to do:
Select customer_no, NULLIF((-1 * SUM(pmt_amt)),0) as 'amt'From [dbo].t_payment Where pmt_method=85 group by customer_No