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
YOU ARE AMAZING, thank you.
Thank you, Michael. Can you tell me this...
Is there a way to use this to output level and recognized amount as well?
Definitely. Here are the Entries for TR_QUERY_ELEMENT:
Level:
Recognition Amount:
YOU JUST MADE MY YEAR!!!!!! I can't thank you enough!
wait, one more thing. how about being able to output the membership campaign???
Of course :-)
Where have you been all my life? Or the past 3 months since I've been working in Tess...
Hey Michael, I'm going to try this in TEST right now but just wondering, because you say for a system with one membership organization, what if we have multiple, we have one for Capital Campaign, one for Organizations, and one for Individuals? I'm really only trying to use this for Individuals right now but is it going to mess it up if there are others???
OMG, this is AMAZING! You have no idea how much time this will save me and the years I've longed for this! It does seem to spit out multiple rows if someone has a Capital Campaign and an Individual membership but if all I have to do is delete the Capital Campaign rows I can certainly live with that. Thank you so much!
any chance you know how to pull the last Plan's information like worker, notes, ask, goal...?
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.
I'm glad it's working for you! You can limit the results to one membership organization with an output filter. To create one, add a row to TR_QUERY_ELEMENT_PARAMETER with the following details:
After saving that, add the new output filter to the output set, and select the Individual membership organization.
Unfortunately there is not a standard database view that shows the most recent plan in the same method as the most recent membership, so I don't have any tricks to pull out of my sleeve on this one :-) You could get close by using the standard plan output elements, and adding an output filter to the plan data in the output set. Your filter could limit the results to plans within a certain start date range or a campaign, for example.
If that doesn't work and you do end up needing a custom Most Recent Plan output set group, you can email us at hello@tessituranetwork.com to get a quote for something custom, or someone from the community might be able to provide you one they've already wrote.