On Account money in 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

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