CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Search builder queries for membership problem
Pages: [1]

Author Topic: Search builder queries for membership problem  (Read 2092 times)

Tony Horrocks

  • I post occasionally
  • **
  • Posts: 110
  • Karma: 7
    • Fabriko Limited
  • CiviCRM version: 4.5.x
  • CMS version: Drupal 7
Search builder queries for membership problem
September 29, 2010, 01:53:13 am
Ive checked the search builder documentation but cannot find the answer.

Using search builder I want to get a list of members who are of a certain membership type.

I look up the membership type ID (which is 1) and put in membership type = 1 into search builder

I get an error "DB error - no such field"

I also want a list of members who have a certain membership status - lets say 2 (which is "Current")

So I select Membership Status = 2 in search builder
It returns all members

This happens on the demo site at http://drupal.demo.civicrm.org/civicrm/contact/search/builder as well as my own site

Can someone give me a sanity check - what am I doing wrong?
Tony Horrocks
Author of the CiviCRM CookBook https://www.packtpub.com/web-development/civicrm-cookbook

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Search builder queries for membership problem
September 29, 2010, 03:39:14 pm
Curious as to why you wouldn't use Advanced Search for this? You can definitely search by membership type and status there.
Protect your investment in CiviCRM by  becoming a Member!

AudreyKate

  • I post occasionally
  • **
  • Posts: 33
  • Karma: 1
  • CiviCRM version: 3.4.4
  • CMS version: Drupal 6.24
Re: Search builder queries for membership problem
September 29, 2010, 11:57:19 pm
I also came across this problem this week.

In my instance, I couldn't use Advanced Search because I wanted to find all participants in a specific event who were NOT members -- which meant either 1) excluding contacts whose Membership Status was New/Current/Grace or 2) searching for both expired/cancelled members and people whose Membership Status was NULL. But when I tried to search by Membership Status in Search Builder, it always returned all members, regardless of status.

My workaround: I put all Expired/Cancelled/Deceased members in a smart group. Then I used Search Builder to find participants in the event who either had a Membership Status of NULL or who were a member of that Expired Member smart group.

Best,
Danielle
 

Tony Horrocks

  • I post occasionally
  • **
  • Posts: 110
  • Karma: 7
    • Fabriko Limited
  • CiviCRM version: 4.5.x
  • CMS version: Drupal 7
Re: Search builder queries for membership problem
October 05, 2010, 01:30:34 am
Yes, my example above was illustrative of the general problem.

Like AudreyKate I actually want to build NOT queries and so forth, that are not available in Advanced Search.
Tony Horrocks
Author of the CiviCRM CookBook https://www.packtpub.com/web-development/civicrm-cookbook

xavier

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4453
  • Karma: 161
    • Tech To The People
  • CiviCRM version: yes probably
  • CMS version: drupal
Re: Search builder queries for membership problem
October 05, 2010, 01:53:14 am
Yeap, the smartgroup trick seems to be the best workaround.

You can also write a custom search if you know the sql you'd want and can write (or hire someone that can) a custom search with the NOT you want.

X+
-Hackathon and data journalism about the European parliament 24-26 jan. Watch out the result

chrissncco

  • I’m new here
  • *
  • Posts: 4
  • Karma: 0
    • Little Ship Club
  • CiviCRM version: 3.3.n
  • CMS version: Drupal 6.2
Re: Search builder queries for membership problem
October 21, 2010, 01:21:37 am
This thread seems to have fizzled out but the Search Builder issue remains in 3.2 where functionality that worked in 3.1 no longer works. This applies to some membership tables but I'm not clear about further extent of the issue.

Whilst it seems we agree that some queries can be dealt with in advanced search and by workarounds this is less than ideal for those of us trying to support a dispersed band of volunteers in a nfp organisation - where very specific help documents are the only way. "Try to work around it" just doesn't cut it.

As it says, 'I'm new around here'. Can someone please explain what I can do to capture attention to this issue? Does anyone know what the issue is? Can I gather support for sponsoring a fix? (I might be able to find a small budget). Surely Search Builder is an important component of CiviCRM?

Best,

Chris
« Last Edit: October 21, 2010, 01:46:38 am by chrissncco »

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Search builder queries for membership problem
October 21, 2010, 10:49:03 am

chris:

might want to start a new thread and elaborate a bit more on what functionality worked in 3.1 that does not work in 3.2. We have not intentionally broken functionality

Search Builder has been on "life support" for the past couple of years. There has not been a lot of active development with that code (if any)

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

MulderDSM

  • I post occasionally
  • **
  • Posts: 100
  • Karma: 0
  • CiviCRM version: 4.6.0
  • CMS version: Joomla 3.4.1
Re: Search builder queries for membership problem
December 09, 2010, 11:36:10 am
Search Builder is not working right.

I'm trying to build on one field "Membership Expiration Date" > "20110301" - so I want to see all members that expire after March 1st next year. When I run the search it only comes back with one record that expires in 2013. If I change the ">" to a "<" I get almost every record I have and I can see a lot of memberships that expire in 2011.

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Search builder queries for membership problem
December 11, 2010, 07:38:47 am
As Lobo noted above, Search Builder code hasn't had much development over the past 2 years. Consider whether Find Memberships or Advanced Search can meet your needs - OR you can debug and query and submit a patch to fix the issue.
Protect your investment in CiviCRM by  becoming a Member!

herb

  • I’m new here
  • *
  • Posts: 27
  • Karma: 2
Re: Search builder queries for membership problem
May 14, 2011, 08:17:12 am
Maybe there should be an official warning for Search Builder then - telling people that there's a good chance they'll get errors.

In my case a client found an error in the Membership Expiry Date search. I found that unless the > and < span a year then it will show zero results. E.g. if I go Membership Expiry Date > 20110101 and Membership Expiry < 20111231 it will give me zero results but if I do the search one day earlier: Membership Expiry Date > 20101231 and Membership Expiry < 20111231 then I get results (I replicated the same error on the demo.civicrm.org site running 3.4).

The reason the client needed it is also to find members with NO email address within a certain date range. But since there aren't that many results I think they can just live without it, or use the smart group trick mentioned above. I just wish there was a warning for Search Builder if there isn't much movement to improve it right now.
Web Developer
Freeform Solutions

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Search builder queries for membership problem
May 14, 2011, 11:28:24 am

We do hope that some folks in the community find it useful and are willing to investigate and submit patches to make it a lot more useful, fix bugs and add new features. It would be great if consultants/integrators who find that it matches their needs, help with the above. We can definitely help u'll get started with this on IRC

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Search builder queries for membership problem

This forum was archived on 2017-11-26.