Records missing from query to Export

JoAnn Strommen

Created a query of past donors and added specific record criteria to pull in 3 additional records that they want included in the fund raising.  Query runs fine - pulls in past donors and the new records.  Am taking that query to the Export function. Export settings for HOH are export both separately (not an issue as business and no spouse records involved) and Include these Constituents - only checked Constit with no valid address (not relevant for missing records as they have addresses).  Running 7.96.6401.8, self hosted.


Export to excel is short a couple records and it is the additional records I added to the query. I have renamed the query to ensure I'm using the correct one. I am. Why would export drop these records that are in the query?????

Lisa Slawinski

JoAnn Strommen:

Created a query of past donors and added specific record criteria to pull in 3 additional records that they want included in the fund raising.  Query runs fine - pulls in past donors and the new records.  Am taking that query to the Export function. Export settings for HOH are export both separately (not an issue as business and no spouse records involved) and Include these Constituents - only checked Constit with no valid address (not relevant for missing records as they have addresses).  Running 7.96.6401.8, self hosted.


Export to excel is short a couple records and it is the additional records I added to the query. I have renamed the query to ensure I'm using the correct one. I am. Why would export drop these records that are in the query?????

Hi JoAnn,


Could the export not like that you manually added a few of those records to the past donor list?   Or could they be duplicates without you knowing it?   Would you venture creating a query list of the past donors and then manually add the 3 additional records to complete the query to then send to export?


Just some ideas.  I see you answer many questions in the Community, so you may have already thought of these.


Lisa

JoAnn Strommen

They are not duplicate records. Looked in db before adding the records. They are not in the Excel file created by Export at all.


For the query I used our existing criteria to capture the past donors we wanted and then added an And criteria for specific record one of.  The records pull into the query so why would Export drop them?


I just made a Query List from the query with donors and those records. They pulled into the Query List. When I use this new query in Export the records are included.


This is frustrating as if I hadn't been looking for one of those records would not have noticed the records didn't pull. Doesn't seem to be a logical reason for them not not pull.

Aaron Rothberg

Hi JoAnn-


I'm confused on your query criteria. When you mention, "For the query I used our existing criteria to capture the past donors we wanted and then added an And criteria for specific record one of."


To me that means your additional donors need to also match the "existing criteria" because you have used an AND. If the additional constituents don't match that "existing criteria", the AND should drop your extra constituents. If the additional constituents do meet your "existing criteria", then your query should drop everyone else not specified by Constituent Id since the constituents that match your "existing criteria" will not match the constituent Ids you added in your AND statement.


It sounds like you should replace your AND with an OR.

JoAnn Strommen

Sorry, I meant "OR". Yes, that is what I used. They are in the query. Question is why are they pulling for the export?.

Aaron Rothberg

Are you doing anything funky in the Record Processing or Advanced Processing tabs in the query? Assuming you've ruled out Deceased, Inactive, would you mind posting a screenshot of your query criteria as well as the query options?


The only things I know of that can prevent a constituent in a constituent query from displaying in a constituent export is Deceased, Inactive, No Valid Address, and HoH. Things like Soft Credit don't come into play since you're asking for the constituents by Constituent Id and this is a constituent export, not a gift export. If the constituent, for example, had a populated Date To on the address that would stop the address from displaying in the export, but the constituent should still come through.

JoAnn Strommen

Nothing funky that I can find. Nothing marked/checked in the query record processing or advanced processing. For gift processing gift SC to both. All the include boxes are checked to include inactive, deceased, no valid address.


I just don't get it. I just manually entered the data for the three records into my Excel Export. Much quicker than trying to figure out what's happening.


Will try to attach screen shot. Last time it didn't work for me - will try it as a Word doc.

Aaron Rothberg

You're using the Name field to add your extra constituents!? I have not seen that before. Looking for "John Smith" in the name field isn't guaranteed to get you the right John Smith. My next step would be to add those constituents by Constituent Id and see if that gets them into the export. I bet it will. You're also mixing ANDs and ORs, but I don't see any parenthetical notation. I wonder if not having parentheses wrapped around your first two conditions is adding to the problem?

Jen Claudy

If the records are showing up in the Query but not in the Export, then I think the problem must lie with the Export rather than the Query.  Unless by chance it's a Static Query that you ran but didn't refresh and RE therefore didn't save those records in the Query.


I would try adjusting the Query to include only those additional records and see what the Export does.


I've been told that parentheses matter with Summary Fields, even when they aren't logically needed.  But I don't know if that's really the case (I haven't found it to be the case).  And regardless, that's not your issue because the Query is pulling all of the records.


Rebuilding the Query and the Export is always a possible solution (although that can be a big old pain in the neck!).  Or even using Save As for "fresh" copies of each piece.


If you figure out what happened, please post an update!  I'm curious now.

JoAnn Strommen

I had not bothered with parentheses in the criteria as it was pulling donor info correctly without them. I tend to only add when needed. Using the specific record - one of I know I have the correct records so didn't feel need for constituent ID.


It's a dynamic query.


If name vs. ID is the reason they don't pull in Export that doesn't make sense to me. Donors are not identified by ID. When all the records I need pulled in query, I just don't understand why they are dropped in Export.


Maybe later this week I'll have time to get back to it and play some more. Adding them manually to the Excel Export at least let me get the project done.


Thanks for all the replies.

Aaron Rothberg

For what it's worth I just created a constituent query looking for anyone with a summary of gifts greater than $100,000 OR Name equals Aaron Rothberg. My record showed up in both the query and the export so that's not the problem. Good luck JoAnn!

Steven Cinquegrana 2

Aaron Rothberg:

For what it's worth I just created a constituent query looking for anyone with a summary of gifts greater than $100,000 OR Name equals Aaron Rothberg.

I did the same-ish thing (Gifts > $0, etc Specific Record = me) as Aaron and got correct results all round as well. (Specific Records are selected so I'm not sure how there could be any ambiguity between actual records here.)


IMHO, parentheses should generally be used when there is any ambiguity even if they (seem to) make no difference. For example, "Constituent ID = "123" AND Gifts > $0 OR Date Of Birth > '1999'", "(Constituent ID = "123" AND Gifts > $0) OR Date Of Birth > '1999'", "Constituent ID = "123" AND (Gifts > $0 OR Date Of Birth > '1999')" are all likely to produce a different result set. (Though precedence may result in the first being equivalent to either the second or third, but you know what I mean.) And even if there actually is no difference, it helps someone else reading the Query to understand the intention.


Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions

 

Christine Cooke 2

JoAnn Strommen:

Created a query of past donors and added specific record criteria to pull in 3 additional records that they want included in the fund raising.  Query runs fine - pulls in past donors and the new records.  Am taking that query to the Export function. Export settings for HOH are export both separately (not an issue as business and no spouse records involved) and Include these Constituents - only checked Constit with no valid address (not relevant for missing records as they have addresses).  Running 7.96.6401.8, self hosted.


Export to excel is short a couple records and it is the additional records I added to the query. I have renamed the query to ensure I'm using the correct one. I am. Why would export drop these records that are in the query?????

Since the Query is fine but the Export is missing some -- I am thinking that some of the fields you are exporting may be filtering out those that are not showing up in Export.  Carefully review the output fields in Export and see if any of the info you are asking RE to spit out contradict details in those records.  You did say that you marked Invalid -- if those records are valid but have the Send Mail unchecked they would most likely get dropped, so that is one thing to check.

JoAnn Strommen

One of the first things I did was to check the send address box on the three records.  All have box checked on their records. As to the valid address, none of them have the "has no valid address" box on Bio 1/Org 1 tab checked.


I don't see how any Export output fields could be filtering out those 3 records. If the data is not one their record, field would export blank just like it does for other records in the Export.

Aaron Rothberg

I just ran a test trying to closely mirror your query criteria and I still can't replicate your results.


If you look at my screenshot below, the first summary is FY18, the second is FY17, and the third is FY16. My results include 6,752 constituents and my "Specific Record Is One Of" constituents (which are not pulled in by the other criteria because neither record has a Team Raiser Id Attribute and neither has made a donation in the last three fiscal years) are in both the query results and my export results.





I don't know JoAnn. ¯\\_(ツ)_/¯


I'd recommend you throw your computer out the window and get a new one.

Leslie Choitz

JoAnn,


It sounds like the problem must be in the Export.  Could you post a screenshot of the Export criteria?  (Or did you do that already I just missed it?)

Aaron Rothberg

Output or Criteria? I only displayed Constituent Id and Name. For criteria I included Deceased, Inactive, No Valid Address and exported both constituents separately.

JoAnn Strommen

There isn't much for criteria when exporting. ???  As mentioned in original post I have checked Export both under HOH processing and checked include constituents with no valid address. None of the three missing records are marked as inactive or deceased.


I'm outputting bio, some relationship and giving data but that shouldn't affect which records are pulled.

 

Aaron Rothberg

I completely agree. This is a weird mystery.


If you get super motivated to solve this and have a way to share your desktop remotely, I'm happy to get on the phone with you to figure it out. There's got to be a logical explanation for why this is happening, but without seeing your process first-hand, I'm at a loss.

JoAnn Strommen

Thanks Aaron. It could be a fun Friday project. I ran the query and export numerous times before posting on the forum and while discussion was going. Those 3 records did not appear in export. I looked for all 3 each time.


So, before trying a screen share, figured I'd run export once more and check the results. Guess what? They pull in Export now. I did not make any changes to the records, the query or the Export. Ugh!  "Yes, Dad my car was making a strange noise when I called you to come over. I don't know why it's not making it now." memories. LOL

Jen Claudy

Ha!  I love the line about your car.  That makes me think of when I first started in a previous job, a co-worker was teaching me how to use the (massively beyond what we needed to have, more complicated than Kinkos) copy machine.  She said that when you encounter an issue, the first step is to find out where the IT support guy was...because as soon as he appeared at the copier, it would start behaving properly.  She claimed (and after first-hand experience, I concurred) that there was a direct correlation between how far away he was and how difficult the copier was being.  She also told me that because of that, he had been forbidden to ever leave the country!

Cathleen Mai

Hi, hi,hi - they say that about me- just standing there and doing nothing!!!!! (or alternatively do the same thing they just did) will solve the issue - it's magic ;-).

Heather MacKenzie

There's a time lag of 30 minutes, I think, before you can expect that your newly added records in NXT can be completely done being copied over to RE7, which means that exports and queries have struggles with these records.  I just found this out last week while trying to help a co-worker.  Perhaps this is related to your project's gremlins?  I forget if you have NXT or not.


I'm glad it worked out, and I love the funny stories about the "didn't work then, but works now that you're here" situations!