Our development department needs to send letters that include the last level of membership and the date that it expired. Using output set elements it is easy to grab this info for a list of current members, but when the same set is applied to a list of expired members those two fields are blank. The Constituent Profile report pulls old membership info, so clearly there is a way to access it. Has anyone else encountered this and found a way to get that info out in a way that can be used to export for letters and reports?
Also, they have tried to pull a list of members for a specific date range (for example 1/1/19 – 12/31/19). If the constituent is a current member, their current level of membership appears not the level for the date range. Of course, in most instances they are the same, but not all.
Any insight would be much appreciated.
Thanks!Anne
If I recall correctly, the standard Membership output elements are built off of a virtual table that does something clever to make sure there's only one current answer for each constituent. I think that means that you could build a separate set of output elements off of, say, TX_CUST_MEMBERSHIP that would pull the historical data.
I had a feeling it was something like that and that it would require new output set elements. Thanks! I will take a look at the table you suggest.
I'm pretty sure you can accomplish what you're looking for in your first paragraph using the Memb Most Recent list and output elements. Those look specifically at the most recent membership, which should always be the expired one if people haven't renewed.
I think those must be custom elements that are set up in your system. I don't have them in the membership section of either List Manger or Output Set Builder. For some reason I can't access the documentation today, but I plan to work on setting up custom ones for us after Mardi Gras.
Looks like you are correct! Happy to send you the definitions of the view powering it all if you'd like!
That would be fantastic! Thank you! My email is anne.robichaux@hnoc.org.
I am actually trying to pull last membership expiration date too and am running into this issue. Were you able to figure it out? Would you mind sharing your insights? Thank you in advance!
Hi Dulce -- Scott Colby (Past Member) sent me the SQL code to set up a view that I was then able to use to create some new output set elements. In looking at the email chain it looks like we went back and forth a few times. Scott -- do you want me to forward the chain? Or do you want to send something?