I'm trying to create an output set that will include the date and name of the next performance a constituent will attend. This output set will be used along with Mail2 to automatically remind people that they have a performance coming up. I've been looking a lot at the TR_QUERY_ELEMENT table and put together 2 queries that I thought should work. These queries are as follows:
Description: Next Performance Name
Data Select: !.perf_name
Data From: (SELECT TOP 1 a.* FROM lvs_tkt_hist a WHERE perf_dt >= getdate() ORDER BY perf_dt)
Data Where: (blank)
single row is checked
Description:Next Performance Date
Data Select: !.perf_dt
Data From: (SELECT TOP 1 a.* from lvs_tkt_hist a WHERE perf_dt >= getdate() ORDER BY perf_dt)
When I use the Execute An Output Set report, these fields are always left blank. To me it seems that they should only be returning one row and that the desired data should appear. Can anyone tell me what I may be doing wrong or a better alternative for this?
A few things. You are looking at ticket history. Chances are that the next performance is not even in the history table. Secondly, if this is for Mail2 why not create a view that mail2 can pull directly from? You will most likely want to look at the order itself. If you would like to see and example view let me know .
Travis
Travis,
I wasn't aware that you could set up views for Mail2 to automatically pull from until now, but that sounds like it could be very useful. Seeing an example view would actually be great for me so I can get an idea of what it should include. Thanks for the help!
Mason