Attribute related query needs troubleshooting help

Heather MacKenzie · October 2018

I've got a query that's driving me bonkers.

I need to find this year's terminated employees who gave even a penny to us last year, and what total amount they gave last year.

Here's my query:

Constituent type query because of the need for the summary field

Attribute "Type of Individual" is where we record former employee status, along with the termination date in the attribute date field

Here's, hopefully, a screenshot of my query criteria:

I have 6 employees getting pulled into who all meet the criteria, but I also know, for sure, of one employee who should be getting pulled in that is not. This makes me suspect I'm missing more than just the one employee. Any ideas?

shani traum · October 2018

What happens if you move the open parenthesis to before the summary portion (rather than just including the attribute parts in the parens)?

How about using a date range rather than "last calendar year" and "this calendar year"? (maybe these are being wonky)

Good luck!

Mergen Peries · October 2018

Yeah I've had trouble with those 'calendar year' operators too. The other wonky things could be the attributes - I've never been sure that the date criteria is looking at only those types specfied.

I would cut back some of those criteria (maybe attrib date), and put the query through Export. Then you can manually sift through in Excel. If you find the people you are missing in the export, you can add criteria back into the query until it gets closer to what you want.

Aaron Rothberg · October 2018

Hi Heather-

You mentioned you need to see former employees that donated last year, but I'm wondering why you are including "Constituent Attribute Individual Date equals this Calendar Year" in your criteria? Does it matter when they left employment as long as you know they are currently not employed? If you remove that constraint, what does your list look like then?

Also, just to be sure you're not missing any assumptions query might be making under the hood, I would replace your dates with date ranges rather than last calendar year. I'm sure this has nothing to do with your incorrect query results, but when I'm having trouble getting at data I like to be as specific as possible to ensure someone else's algorithm isn't getting in my way.

Heather MacKenzie · January 2019

Aaron Rothberg‍ I needed to see employees that terminated "this year" who gave us even one penny "last year." That's why I have the date in there because that lives as an attribute in our system.

I ended up doing the export to solve my problem at that time, but I will mess around with the date ranges as you all suggest and see if that solves it. I do have a need to run this again in the future, so cross your fingers that solves it. I'll try to come back and let you all know.

Christine Cooke 2 · January 2019

instead of calendar year, have you tried putting in the date range instead?

On a side note, why not keep your employees in Relationships? Have an Org record for your org, or multiple org records as it appears you have more than one location, and then link the individual employee constituent record to the org and utilize the From and To dates of that relationship to note when they arrived and when they exited.

Cathleen Mai · January 2019

Hi Heather,

Sometimes it works better to run 2 short simple queries instead of one long one and then use "merge' , in your case with the AND operator.

Attribute related query needs troubleshooting help

Comments

Categories