when I'm trying to get expiration dates and membership levels for the last membership of a list of constituents if they are...why do some come up blank and how can I get it to populate? It looks like it only populates something if the constituent is active or lapsed, not inactive
Hi folks! You can build a query element group based on VS_ELEMENTS_MEMBERSHIP_MOST_RECENT, which is a view added in Version 14 that pulls the farthest out membership for a constituent, with separate rows for each membership org. This will be an inactive membership if they never renewed, or a pending membership if they have renewed early, or a current membership if they are current and haven't renewed yet. If you include a single membership organization in the Data_Where column, or if you only have one organization, you can check the Single Row checkbox for each element. In that view, the expiration date column is expr_dt, just like in the regular membership data.
So for a system with one membership organization:
I hope that gets you started!
-Michael Flaherty-Wilcox, Tessitura Network
Hi Michael, I am wondering if this solution will work for me as well - I'm having a similar problem, but slightly different. I am trying to get my output for e-renewals to pull membership expiration and level for everyone. Because we send to multiple emails per household, any line pulling the email associated with an individual within the household does not pull the membership expiration and level with it. Will this query element group work in my situation as well, or is this something different? I'm new to Tessitura and am trying desperately to have these merge fields in my e-renewals.
Thanks!
Genevieve
Great question, Genevieve. I would love help with this, as well!
Hi Genevieve and Alycia! Try a Query Element Group using following in the Data From column:
(SELECT b.expanded_customer_no AS "customer_no", a.expr_dt, a.memb_level_desc, a.recog_amt, a.campaign_desc, a.memb_org_no FROM VS_ELEMENTS_MEMBERSHIP_MOST_RECENT a LEFT JOIN VS_CUSTOMER_WITH_PRIMARY_AFFILIATES b ON a.customer_no = b.customer_no)
Make sure that when you copy/paste this into Tessitura, the quotes might get changed from "straight quotes" to "curly quotes." You might need to delete and retype them so they are straight double quote marks.
This should work with all the examples on this thread. It might run slower because it has to do more lookups in the system for each row.
Hi Michael,
This was the fix we needed! Thank you so much - I can't tell you how happy I am to have this solution!