Filter out certain Constituent Codes by date

Patrick Flynn

This may be more of a DB view question, so Admin, please move to the right spot if necessary.

I am creating a query of active donors who have given in the last two years (Gift date 01/01/022 to today) and who DOES NOT have a certain constituent code with an active date before a certain day.

Basically what I am trying to do is build a mailing list for active donors who have not previously attended one of our quarterly donor socials in the last year. If there is a cleaner way to do that, please feel free to share.

Thank you in advance.

Joe Moretti

@Patrick Flynn You would only need to do Gift Date Between 01/01/022 to whatever end date AND Consitutent Code one of (whatever codes you want). I do not think you need to be concerned with the constituent Date if you only what those who have given a gift during a certain time frame and have a certain consitituency code. Now if your consituents have more than one code, you will also need to add AND constituency code does not equal (whatever that code is) in case they contain both codes.

Patrick Flynn

@Joe Moretti Thank you.

Yeah, I didn't see a way to date limit the constituent code either. I'm just wondering if there is a better way to create that list and measure who has or has not attended the event. I wonder if maybe using the Event Participation tag might be a better way to handle this….

JoAnn Strommen

@Patrick Flynn Assuming you have the event module. To your query can you add Event criteria like event name / event date is one of or not one of or something else…

Just thought that might work.

Patrick Flynn

@JoAnn Strommen I do have the Event Module, I think that will work, I am building that in right now…

Patrick Flynn

@Patrick Flynn Just a heads up to all, the Event Module based criteria did not work, at least not the way I had hoped. I defaulted back to using the constituent codes. It's not perfect, but it will work, until I figure out something better.

Karen Diener

@Patrick Flynn Can you explain more? I thought for sure this would work. The reliance on constituent codes for this information is troubling.

Kelly Campion-Socol

@Patrick Flynn I think I am understanding this correctly:

donation date greater than or equal to 01/01 /2022 and

use parenthesis to keep the constituency code criteria together

(constituency code not one of select constituency codes and

constituency date from not between 01/01/1901 and insert certain active date here)

Patrick Flynn

@All I think I figured it out! So in NXT view, I created a constituent list of those with the Constituent Code I created to indicate that person had attended the donor social in the past (filterable by date if wanted).

I then created another constituent list using any parameters I want (given in the last two years, LYBUNT, current donors, etc. It doesn't matter what these parameters are.) Then I clicked on the “Include/Exclude” button, and setup an exclusion using the previously created constituent list in step one. Voila! It now filters out those who have attended the event in the past, and now I only invite people who haven't been!