Foreign Gifts

Bryan Horz

Hi,


Trying to do a query of Foreign gifts in a month.  Need those where the current Home or Current Business address is not equal to the United States.  When I do just the home it seems to work but when I add the Or clause it gives me non sensical data.  I have tried with and without ().  Moved the Parenthesis around.  Nothing seems to work.

Below is a screenshot of my criteria.


Thank you

Bryan

Rachel Cavalier

I feel like you need to slap some brackets in around the whole of that address part - maybe slide in some always true conditions like "constituent ID is not blank" and "import ID is not blank" either side of that address section so you can put the extra brackets around the whole lot. Not sure if I explained that in a way that made sense...

Alan French

Rachel's right, it's the brackets - you're currently getting everyone who made a gift and has a home address outside the US, plus anyone with a business address outside the US regardless of whether they've given a gift that meets those criteria or not.


Rather than adding more brackets, you can just take out the 2nd and 3rd ones and leave the 1st and 4th ones. You don't need brackets around the pairs of home/business criteria because they're joined with Ands, not Ors.

Bryan Horz

Rachel and Alan,  Thank you both for responding.  I tried both of your suggestions and I still am getting records that have a gift in the criteria time frame but don't have a home or business address outside of the US.  The query works if I take out either the Home or business address criteria, but when both are in it gives me extra records. Below is the revised criteria I used based on Rachels suggestion.  I would have thought that this would be a simple query.  I am very concerned that I will struggle when I try much more complex criteria for existing reports that I need to transition over to RE.

Thanks again for your help.  I just have to get used to how RE querying works

Alan French

You can simplify the query a bit by removing the two lines with ID numbers and putting the brackets on the lines for 'Home Country' and 'Business Send Mail To This Address' instead.


You could try flipping the logic around the other way and say the country must be one of Afghanistan, Albania, Algeria... etc. rather than that it must not be equal to US. I get a different number of results when doing the query that way around compared to your way, which makes me think the issue is to do with people who don't have both those address types on their records and therefore the country is null. Unfortunately if you add any more countries to RE in the future, you'd need to manually add them to the query.

Bryan Horz

Thanks Alan,


After I read your comment, I remembered that our RE Trainer said something about not using more than one not statement in a query because it gives undesired results. Once I changed one of the addresses to include all other Countries, it worked.  That is absolutely crazy programming results.  Thank you so much