Search by constiuent ID in a Query

Teresa Hosch

Hi All,

When attempting to run a query, I want to use "one of", and then enter the constituent ID.  However, I am finding that when I enter say, 263, Raiser'sEdge doesn't select just record #263.  It chooses several records that contain the number 263.  How can I have it select 263?  I have this problem when the ID number is short, fewer than 6 digits or so.

Thank you,

Teresa

John Heizer

Teresa Hosch:

Hi All,

When attempting to run a query, I want to use "one of", and then enter the constituent ID.  However, I am finding that when I enter say, 263, Raiser'sEdge doesn't select just record #263.  It chooses several records that contain the number 263.  How can I have it select 263?  I have this problem when the ID number is short, fewer than 6 digits or so.

Thank you,

Teresa

I'm running version 7.93 and when I create a Constituent Query with criteria of "Constituent ID one of 263" my system returns only the one record where the ID = 263.  I don't get ID = 1263 or 26345, just like you're expecting to get, so I'm not sure why you're getting different results.

Patti Posey

Teresa Hosch:

Hi All,

When attempting to run a query, I want to use "one of", and then enter the constituent ID.  However, I am finding that when I enter say, 263, Raiser'sEdge doesn't select just record #263.  It chooses several records that contain the number 263.  How can I have it select 263?  I have this problem when the ID number is short, fewer than 6 digits or so.

Thank you,

Teresa

Hi Teresa,


You would use equals 263.  The one of will give you anything with 263 in it.  The equals will only give you 263.


Patti Posey

Stamford Hospital Foundation

Teresa Hosch

I know about equals, but I want to search for
several records, one of them being 263.

John Heizer

Patti Posey:

 

Teresa Hosch:

Hi All,

When attempting to run a query, I want to use "one of", and then enter the constituent ID.  However, I am finding that when I enter say, 263, Raiser'sEdge doesn't select just record #263.  It chooses several records that contain the number 263.  How can I have it select 263?  I have this problem when the ID number is short, fewer than 6 digits or so.

Thank you,

Teresa

Hi Teresa,


You would use equals 263.  The one of will give you anything with 263 in it.  The equals will only give you 263.


Patti Posey

Stamford Hospital Foundation

 

"Like" would give you anything with 263 in it.  "One of" should not.

Teresa Hosch

Are you using “one of” or “equals”?  I
want to search for several, so I’ll use “one of”, but when some of
the IDs are just a few digits long, RE selects more than just the
263.

 

Teresa Hosch

I know about “equals”, but I want to use “one
of” so that I can search for several at once.  I want to know
if there’s a way to basically do an “equals” within a “one of”.

 

JoAnn Strommen

John Heizer:

Teresa Hosch:

Hi All,

When attempting to run a query, I want to use "one of", and then enter the constituent ID.  However, I am finding that when I enter say, 263, Raiser'sEdge doesn't select just record #263.  It chooses several records that contain the number 263.  How can I have it select 263?  I have this problem when the ID number is short, fewer than 6 digits or so.

Thank you,

Teresa

I'm running version 7.93 and when I create a Constituent Query with criteria of "Constituent ID one of 263" my system returns only the one record where the ID = 263.  I don't get ID = 1263 or 26345, just like you're expecting to get, so I'm not sure why you're getting different results.

 

I'm with John - just tested also.  One of only pulled exact match.  I took it down to only 2 and it pulled nothing.  I'd ask support why.  Do you possibly have such a long list of ID's that there is one in the list your're not aware of with 263 with a wildcard?

Steven Cinquegrana 2

Teresa Hosch:

Hi All,

When attempting to run a query, I want to use "one of", and then enter the constituent ID.  However, I am finding that when I enter say, 263, Raiser'sEdge doesn't select just record #263.  It chooses several records that contain the number 263.  How can I have it select 263?  I have this problem when the ID number is short, fewer than 6 digits or so.

Thank you,

Teresa

Teresa, could you post a grab of the query criteria? I can't replicate this either. My criteria (Sample DB) are:




Results, as expected, are:




Steve

Alan French

I've just tested this on both 7.93 and on our 7.96 pre-production system and couldn't replicate it on either. I even tried with *, ? and % wildcards and it still only included the records that were an exact match.

Cathleen Mai

Teresa Hosch:

Hi All,

When attempting to run a query, I want to use "one of", and then enter the constituent ID.  However, I am finding that when I enter say, 263, Raiser'sEdge doesn't select just record #263.  It chooses several records that contain the number 263.  How can I have it select 263?  I have this problem when the ID number is short, fewer than 6 digits or so.

Thank you,

Teresa

I couldn't replicate it either. When you are copying from excel, could it have to do with the formatting of the cells?

 

Carol Hanchett

Hi

 

I tried it
on mine and I chose Constituent ID – EQUALS and typed in the ID
number that way and it only brought up one.

 

It’s the
‘one of’ that is throwing it off as you can have more than ‘one of’
if that make sense.

 

Good
luck!

 

 

One in two
young women has experienced controlling behaviour in a
relationship; more than a
third of young people would not know where to turn for
support. Watch and
shareBRIT-nominated
singer-songwriter Frances and Refuge’s new music video, which tells
young women: you are not alone.

 

 

Carol
Hanchett

Database and digital senior executive

 

t: 020 7395 7760

Visit www.refuge.org.uk to set up a direct debit
and sign up to a running event or our annual
newsletter

 

Text 'REFU40 £5' to 70070 today to give Refuge a £5
donation
 

 

Follow Refuge on Twitter

Like Refuge on Facebook

 

 

The information in this email and any attachments thereto is
confidential. If you are not the intended recipient, you must not
read, use, copy or disseminate that information. If you have
received this email in error, please notify the sender and destroy
it immediately. It is your responsibility to protect your system
from viruses and any other harmful code or device. We try to
eliminate them from emails and attachments; but we accept no
liability for any which remain. We may monitor or access any or all
emails sent to usin
accordance with applicable law and
regulations.Refuge
is a registered charity number 277424 and a company limited by
guarantee, incorporated in England and Wales, with registered
number 1412276 and registered office at International House, 1 St
Katharine's Way, London E1W 1UN.Further
information is available from www.refuge.org.uk

 

 

 

Ryan Hyde

Just to further clarify what responders are trying to get across here:


"one of" means "Exactly matches one of the following things." 


So if you're choosing "one of" and your'e getting matches that are NOT in the list of options, then either you've done something wrong (maybe included something else in your criteria that's throwing the whole thing off?) or there is a bug in RE. In either case, if you can't figure it out, I would recommend reaching out to RE support and do a screen share because this is not the expected behavior of the "one of" operator. 

Teresa Hosch

That’s great!  Did you use “equals” or
“one of”?

 

If I use “equals”, I get just the one record,
but I want to use “one of”, and when I do, I want to receive each
of the records as I entered them.  For instance I want 46718,
65725, and 623.  I don’t want all records that include the
sequence 623 - 623, 62348, 6234, etc.

 

Thank you,

Teresa Hosch

Prospect Researcher

Cal Poly Pomona

3801 W. Temple Avenue

Pomona, CA  91768

(909) 869-2969

 

Teresa Hosch

I’m not copying from Excel, I’m entering the
numbers manually.  Is it possible to copy from Excel and not
have the issue?

 

Teresa Hosch

Prospect Researcher

Cal Poly Pomona

3801 W. Temple Avenue

Pomona, CA  91768

(909) 869-2969

 

Teresa Hosch

Yes,
it works fine if I choose EQUALS, but then I can only choose one
record.  I know it’s the “one of” that’s throwing it off, but
it shouldn’t because I don’t have more than one of any particular
constituent number.  I want it to give me only the record for
the number I enter, not all records that contain that sequence of
numbers in their IDs.

 

Cathleen Mai

I never had the issue either way, so if it is a Raisers Edge bug it will not help you, but you can certainly try and copy -up to 500 records- from excel into the query.

Choose Constituent ID is one of - then scroll all the way down, click into the last cell, Ctrl-V, scroll back up and they should be there.

Ryan Hyde

Teresa Hosch:

Yes, it works fine if I choose EQUALS, but then I can only choose one record.  I know it’s the “one of” that’s throwing it off, but it shouldn’t because I don’t have more than one of any particular constituent number.  I want it to give me only the record for the number I enter, not all records that contain that sequence of numbers in their IDs.

 

 

 

You really need to reach out to support on this. What you're experiencing is not expected. If what you're describing is actually happening, then there's a bug that we're not going to be able to identify as a community. "One of" should only find exact matches to the field of parameters. What you're describing is what you'd expect from a "contains one of" operator, which off the top of my head I don't think actually exists in RE (running an export right now so I can't verify).


To answer one of your other questions though, you can indeed copy/paste from a list within Excel.


1.) copy the list

2.) Select your 'one of' operator

3.) Scroll all the way to the bottom of the possible "one of" fields (all fields are empty at this point

4.) Click on the very last field (I know this is sounding unituitive - that's because it's unintuitive)

5.) Cntrl+V to paste in your list

6.) Even if you don't see anything change, hit "ok" and all of your values will be there


Note: There's a limit of 500 options for any "one of" operator, so if you have more than 500 items in your list, you'll need to string together multiple 'one of' criteria as OR statements.

Teresa Hosch

I did reach out to Blackbaud, and was told
that what I want to do cannot be done.  It’s difficult to
believe that our institution is the only one that wants this
capability, so I reached out to the community.  It sounds like
others want it, but have it, so I’ll keep working to get it to work
for us.

 

Thank you for the suggestion regarding
Excel.  Others, too, mentioned that, and I will definitely
give it a try.

 

Thank you!

 

Teresa Hosch

Prospect Researcher

Cal Poly Pomona

3801 W. Temple Avenue

Pomona, CA  91768

(909) 869-2969

 

Jenny Marquardt

Have you tried choosing 'specific record id' instead of constituent id one of

Jenny Marquardt

Jenny Physioc:

Have you tried choosing 'specific record id' instead of constituent id one of

Or when entering the constituent id having the box checked for Exact match

Teresa Hosch

I’m working in Query where there is no
“specific record ID” and no “exact match” box.

 

Teresa Hosch

Prospect Researcher

Cal Poly Pomona

3801 W. Temple Avenue

Pomona, CA  91768

(909) 869-2969

 

John Heizer

Teresa Hosch:

 

I did reach out to Blackbaud, and was told that what I want to do cannot be done.  It’s difficult to believe that our institution is the only one that wants this capability, so I reached out to the community.  It sounds like others want it, but have it, so I’ll keep working to get it to work for us.

 

Thank you for the suggestion regarding Excel.  Others, too, mentioned that, and I will definitely give it a try.

 

Thank you!

 

Teresa Hosch

Prospect Researcher

Cal Poly Pomona

3801 W. Temple Avenue

Pomona, CA  91768

(909) 869-2969

 

 

"Cannot be done"????  But you see that others are doing exactly that, unless there's something else you told Blackbaud that you're trying to do that you're not telling us in the community.


The behavior you're describing is not what others are experiencing (no one else here gets the same results you say you get with a Query using the criteria you've described).  If the community has all the same information you gave to Blackbaud then your RE is not behaving as designed and you have discovered a bug that should be documented and fixed.  There should be no reason that this "cannot be done."

Jenny Marquardt

In query under Constituent Information

​choose Specific Record or Constituent ID

​Choose One of

​search (click on binoculars) and your search window appears

check the exact match box then start entering your number


​You do have to search after each entry but it does work.

I used to do it this way until I started using SmartPaste.


​I think it would be quicker to type all of your numbers in Excel under one column then copy and

paste them back in using SmartPaste.

Patti Hommes, bCRE-PRO

Teresa Hosch:

Hi All,

When attempting to run a query, I want to use "one of", and then enter the constituent ID.  However, I am finding that when I enter say, 263, Raiser'sEdge doesn't select just record #263.  It chooses several records that contain the number 263.  How can I have it select 263?  I have this problem when the ID number is short, fewer than 6 digits or so.

Thank you,

Teresa

I actually didn't read all of the responses so my apologies if I'm being repetitive. Instead of using constituent ID have you tried a Specific Record query? Specific Record one of....and you can select each constituent instead of usind an ID.

Donna Mortensen

Teresa Hosch:

Hi All,

When attempting to run a query, I want to use "one of", and then enter the constituent ID.  However, I am finding that when I enter say, 263, Raiser'sEdge doesn't select just record #263.  It chooses several records that contain the number 263.  How can I have it select 263?  I have this problem when the ID number is short, fewer than 6 digits or so.

Thank you,

Teresa

Could you use the parentheses function in the query criteria to say "(equals 263) OR (one of...for the other constituents you want to find)"?  That might eliminate the multiples with 263 in them, by segregating the 263 search criteria from the rest of the search criteria, if you're not having problems with the rest of the criteria results.  


I do this a lot with names, where I'm pulling specific students' parents but my list includes a common last name.  I add the first name to that last name criteria and put them in a parenthesis with an "AND" operator.

Lisa Fields 2

Ryan Hyde:

Teresa Hosch:

Yes, it works fine if I choose EQUALS, but then I can only choose one record.  I know it’s the “one of” that’s throwing it off, but it shouldn’t because I don’t have more than one of any particular constituent number.  I want it to give me only the record for the number I enter, not all records that contain that sequence of numbers in their IDs.

 

 

 

You really need to reach out to support on this. What you're experiencing is not expected. If what you're describing is actually happening, then there's a bug that we're not going to be able to identify as a community. "One of" should only find exact matches to the field of parameters. What you're describing is what you'd expect from a "contains one of" operator, which off the top of my head I don't think actually exists in RE (running an export right now so I can't verify).


To answer one of your other questions though, you can indeed copy/paste from a list within Excel.


1.) copy the list

2.) Select your 'one of' operator

3.) Scroll all the way to the bottom of the possible "one of" fields (all fields are empty at this point

4.) Click on the very last field (I know this is sounding unituitive - that's because it's unintuitive)

5.) Cntrl+V to paste in your list

6.) Even if you don't see anything change, hit "ok" and all of your values will be there


Note: There's a limit of 500 options for any "one of" operator, so if you have more than 500 items in your list, you'll need to string together multiple 'one of' criteria as OR statements.

 

Did you know that you don't need to scroll down that far? Just one turn of the mouse wheel will do (any little helps)

Ryan Hyde

Wow! I didn't know that. I just trusted what the support rep told me ages ago and never experimented. Thanks!

Lisa Fields 2

No problem

John Zajc 2

John Heizer:

Teresa Hosch:

 

I did reach out to Blackbaud, and was told that what I want to do cannot be done.  It’s difficult to believe that our institution is the only one that wants this capability, so I reached out to the community.  It sounds like others want it, but have it, so I’ll keep working to get it to work for us.

 

Thank you for the suggestion regarding Excel.  Others, too, mentioned that, and I will definitely give it a try.

 

Thank you!

 

Teresa Hosch

Prospect Researcher

Cal Poly Pomona

3801 W. Temple Avenue

Pomona, CA  91768

(909) 869-2969

 

 

"Cannot be done"????  But you see that others are doing exactly that, unless there's something else you told Blackbaud that you're trying to do that you're not telling us in the community.


The behavior you're describing is not what others are experiencing (no one else here gets the same results you say you get with a Query using the criteria you've described).  If the community has all the same information you gave to Blackbaud then your RE is not behaving as designed and you have discovered a bug that should be documented and fixed.  There should be no reason that this "cannot be done."

 

I can replicate this behavior in my database and I have a guess as to why others aren't able to.

The database I am working in has lived through several differernt ID schemes. So really old records have 3 or 4 digits and newer ones have 9 digits. Others have TB or TBN at the start of the ID.  If the database stuck to one scheme, then this quirk would not appear until you "turned off the odometer."

A similar thing happens in my database when I sort by ConsID in a query; It seems to behave as if the ConsID field is text irather than numerical (it's early and I'm at home without access to RE, so I apologize if I just wrote something stupid about the field).

 

Colleen Jones

Apologies if my"reply to this post" shows up in the wrong place.  I find the formatting confusing.....

However, I wanted to add my thought about formatting ID numbers.  We have 7-digit IDs, and if it is an older ID, it may have leading zeros, and that must be considered when querying on one or many IDs.  I have no trouble using "one of" and pasting in an excel list - but those leading zeros are required or RE doesn't find them.


John Zajc:

 

John Heizer:

Teresa Hosch:

 

I did reach out to Blackbaud, and was told that what I want to do cannot be done.  It’s difficult to believe that our institution is the only one that wants this capability, so I reached out to the community.  It sounds like others want it, but have it, so I’ll keep working to get it to work for us.

 

Thank you for the suggestion regarding Excel.  Others, too, mentioned that, and I will definitely give it a try.

 

Thank you!

 

Teresa Hosch

Prospect Researcher

Cal Poly Pomona

3801 W. Temple Avenue

Pomona, CA  91768

(909) 869-2969

 

 

"Cannot be done"????  But you see that others are doing exactly that, unless there's something else you told Blackbaud that you're trying to do that you're not telling us in the community.


The behavior you're describing is not what others are experiencing (no one else here gets the same results you say you get with a Query using the criteria you've described).  If the community has all the same information you gave to Blackbaud then your RE is not behaving as designed and you have discovered a bug that should be documented and fixed.  There should be no reason that this "cannot be done."

 

I can replicate this behavior in my database and I have a guess as to why others aren't able to.

The database I am working in has lived through several differernt ID schemes. So really old records have 3 or 4 digits and newer ones have 9 digits. Others have TB or TBN at the start of the ID.  If the database stuck to one scheme, then this quirk would not appear until you "turned off the odometer."

A similar thing happens in my database when I sort by ConsID in a query; It seems to behave as if the ConsID field is text irather than numerical (it's early and I'm at home without access to RE, so I apologize if I just wrote something stupid about the field).