nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Constituent both active and past

Ryan Moore

[I'll preface this as I'm still fairly new and learning everything and just fumbling my way through]

So one of the ways we track our constituents is putting in the code.

For our “Payroll Deduction” employee givers we put in a ‘from’ date when they sign up and if they leave a ‘to date’. So it should be fairly easy to pull an active list by asking a query

Con_code = “payroll deduction”

AND con_code date to ‘is blank’ // meaning not filled in so still giving.

The fun for me is to go through and determine how many years they'd been giving to the program. So just export it and determine a datediff and there's their years of giving pin.

…BUT… some people gave from 2/01/2009 - 4/15/2014, and then we have another code for 6/14/2019 - __

How I built the query it just would pull up the second one and calculate that difference, but it would ignore the first.

Any way some of you might be able to figure out a way to calculate this constituent's giving of the proper 5 + 5 years

My thoughts were to just do a pull of all con_code with from/to dates, and have to manually delete the ones where the to is not blank and they're not duplicated, then have to run a column that did a

=if(TO="", datediff(from,today), datediff(from,TO)

then make another column of

=unique(names)

with next to it

=sumif(yearsgiving,unique_names)

hopefully the excel makes sense

Find more posts tagged with

Query Import Export

Comments

JoAnn Strommen

@Ryan Moore Wow, what a lot of work. Can you not pull by gift data?

Our constituent code for employees is faculty/staff. We use a gift code of payroll deduction and/or gift attribute to track the payroll gifts.

It's hard to know for sure as I don't know how your data is entered but I would think using a query of records with your code of ‘payroll deduction' to do an export with summary gift info I think would be easier. It would take a bit of work to set up export with criteria for each year but would be easy next year to just add another field to the export. In excel just use a sum formula to get the number of years.

Just some thoughts…

Alex Wong

@Ryan Moore
do you want the 5 + 5 or just the “current”?

if you want the 5 + 5, then just use query to filter on constituent code, output the from and to date, then in excel for each row calculate the date diff.

then you can do a pivot table pivot on each constituent, and sum of the date diff

Ryan Moore

@JoAnn Strommen @Alex Wong

thanks for the replies

What i want is the 10 for that character, but I also don't want any of the ‘retired’, i.e. ones that aren't giving anymore

Alex Wong

@Ryan Moore
Add a column with countifs on constituent id column and date to column being blank. If no data error only constituent with a blank to will have a 1 on the column. In pivot table filter on this column being 1

Karen Diener

@Ryan Moore I'm in complete agreement with @JoAnn Strommen on this. When I was reading your original post, I thought “you should be doing this via your gift coding”.

I like to think of things like this as nouns and adjectives. Constituent codes are adjectives that describe the constituent, who in this analogy is the noun. Payroll deduction is the adjective that describes the gift, so should be connected there.

Whenever I see people use constituent codes to record information about giving - such as sustaining / recurring donor, major donor, etc. - they do a fair amount of work to maintain it yet it is always out of date.

Joe Moretti

@Ryan Moore I have to agree with some of the others on here, pyaroll deduction should not be a constituent code since that will cause headaches down the road and whenever I see a constituent code like that, I just cringe and I have had to clean up things like that many times in my career. Most payroll deductions are recurring gifts, so you could easily do what you want by a date range and a consituent code of Staff, which that should be the constituent code. You also have a gift subtype that you can use as payroll deduction. This would make life much easier and would not be a chore to get such information. Any contituent code that has to do with money, etc. like major donor, sustaining donor, capital donor, etc. is not the way to go, since any financial information is right there in the gift tab and can easily be figured out there.