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, Leah,
I'm having the same problem. My workaround has been to create a new Query Element Group for "Latest Membership" and select output elements from THAT group, instead. That way, if there's no current membership, you can still have output values.
Hi! I'm stumbled on this and desperately need to know how to do this. I'm new to Tess, where can I create a query element group? Off to search now, but in case you get this in the meantime, thank you!
Query Element Groups can be created in System Tables.
You may find the Query Element Cookbook to be very helpful. You need to get the right version of this document. Assuming you are on Tessitura version 14.1 or later try this.
https://www.tessituranetwork.com/en/Files/Docs/Customizations/Output-Set-Manager-Cookbook
Hi, Jennifer,
TR_QUERY_ELEMENT_GROUP is where you put entries to build a collection of data you can then use in output elements (constructed in TR_QUERY_ELEMENT)
The standard Tessitura membership output elements are pulled from a virtual table of *only current memberships* (I think) and therefore, you can't get anything to populate your output if the person doesn't have a current membership.
Thank you, will check this out!
Yes, I discovered that pretty early on. I'm trying to add an element group, but not sure about the "Data from" field.
Ok, so you need to know SQL for this?
It's definitely helpful but a lot of people use the Cookbook Tom mentioned to create what they need without really knowing any SQL. If you want to email me, I have made a lot (too many) of output elements for my various groups and I'd be happy to send you any help I can? mwaltz@kalamazooarts.org
Here is a link to a video that will give you a bit more of a step by step approach to the topic.
https://www.tessituranetwork.com/Items/Videos/Training/Extending_Output_Sets
I really appreciate that! Let me review the video Tom shared and give it another shot tomorrow. If I fail, I’ll email you.
Thank you!!
I'm going to check all of this out today (while working from home of course!). Thanks for all the info guys!
UPDATE after working in Google hangouts with Jennifer Vinson yesterday:
I am a lying liar who cannot get output sets in v15.1.4 to actually output rows for any membership that is expired/not current. The code works if I just run it as various select statements in SSMS but when I put it into the TR_QUERY_ELEMENT_GROUP table and then create elements from the group/groups, most of them are just empty in the output. I should add that if I only create one element from the group, its's fine. But trying to have more than one not only creates multiple rows in the output, it outputs bad data.
Which leads me to my follow-up question:
**Does anyone know if there is something in the way the SQL code is "put together" from the table entries that would prevent the results from displaying appropriately? I feel like somehow/some way, it's getting caught up in the virtual "Membership" information (that only pulls current membership data) even though I am using VXS_CUST_MEMBERSHIP in my "Data From" field and not ANYthing virutal, so I cannot figure it out. Before I open a question ticket with RAMP (who are super busy helping people with refunds right now, I suspect) I wanted to put it out there to you all - any idea why this might not work?
UPDATE from Jen: Mindee is still awesome!!!
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