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) »
  • Using Search Builder to find members not in another group
Pages: [1]

Author Topic: Using Search Builder to find members not in another group  (Read 3701 times)

Dennis Gray

  • Ask me questions
  • ****
  • Posts: 472
  • Karma: 1
  • CiviCRM version: Various. See post.
  • CMS version: Drupal, Wordpress and Joomla. See post.
  • MySQL version: TBA
  • PHP version: TBA
Using Search Builder to find members not in another group
January 26, 2008, 04:12:29 pm
We have members who are subscribers to a CiviCRM Group "Australia".
These members may also be members of another state group, such as "Australia-NSW".

How can the Search Builder be used to find members who are members of Australia but not in any of the state groups. There doesn't seem to be a !IN function. Will have have to go to the database directly?

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: Using Search Builder to find members not in another group
January 27, 2008, 06:01:11 am

Yes, you will need to interact with the DB directly. The new custom search functionality in 2.0 will allow u to create custom search pages for the below scenario in a slightly easier manner

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

dalin

  • I post occasionally
  • **
  • Posts: 89
  • Karma: 8
  • CiviCRM version: many
  • CMS version: Drupal 6
  • MySQL version: 5.0
  • PHP version: 5.2
Re: Using Search Builder to find members not in another group
March 09, 2008, 08:59:33 pm
Hi lobo,
Can you go into a bit more detail into how I would get a list of "all contacts not in group X" .  You mention two techniques:
  • interacting with the db directly - Our client requires having this integrated into the standard civicrm search results so if this means building a custom search page, it might not work in this situation. 
  • using new custom search functionality in 2.0. - I can't seem to find what you're referring to.  However this may help my case for getting this site upgraded to 2.0
--
Dave Hansen-Lange
Web Developer
Advomatic LLC
http://advomatic.com
Hong Kong office

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: Using Search Builder to find members not in another group
March 09, 2008, 09:09:22 pm

dave:

you need to subscribe to the civicrm blog :) http://civicrm.org/blog/ or http://civicrm.org/blog/feed/ (for the rss feed)

the specific blog entry for custom search is here: http://civicrm.org/node/298

custom searches give you *most* of the functionality of normal civicrm search. since 2.0 is the first release, there will be a few cases that we have not yet covered. i suspect there will be quick fixes for those

In CiviCRM v1.x, one ugly workaround is:

a. tag all contacts with "group tag not x"
b. remove the tag "group tag not x" for all contacts in group X (remove tag is an action in search)
c. you can find all contacts not in group x by searching for contacts with: "group tag not x"

this only works for a "static" group. if u add/remove contacts, u'll need to redo the procedure :( As mentioned above this is an ugly workaround

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

dalin

  • I post occasionally
  • **
  • Posts: 89
  • Karma: 8
  • CiviCRM version: many
  • CMS version: Drupal 6
  • MySQL version: 5.0
  • PHP version: 5.2
Re: Using Search Builder to find members not in another group
March 09, 2008, 09:27:07 pm
Awesome lobo.  I didn't totally understand the full scope of what that blog post was talking about it when I first read it.  Every org needs atleast one very strange search, this should be able to help with these. 

-dave
--
Dave Hansen-Lange
Web Developer
Advomatic LLC
http://advomatic.com
Hong Kong office

Dennis Gray

  • Ask me questions
  • ****
  • Posts: 472
  • Karma: 1
  • CiviCRM version: Various. See post.
  • CMS version: Drupal, Wordpress and Joomla. See post.
  • MySQL version: TBA
  • PHP version: TBA
Re: Using Search Builder to find members not in another group
April 02, 2008, 12:58:06 am
I finally got around to doing this "ugly workaround", which worked.

Thanks!

lcdweb

  • Forum Godess / God
  • I live on this forum
  • *****
  • Posts: 1620
  • Karma: 116
    • www.lcdservices.biz
  • CiviCRM version: many versions...
  • CMS version: Joomla/Drupal
  • MySQL version: 5.1+
  • PHP version: 5.2+
Re: Using Search Builder to find members not in another group
June 10, 2008, 03:16:09 pm
Is 2.1 expected to provide this capability?

Code: [Select]
Individuals / Group / = / 1
- AND -
Individuals / Group / != / 2
I believe the problem lies with the "not in group" part of the equation. I tried to trace back the history of this issue in Jira. 1.8 had the "NOT IN" operator available, but it didn't work reliably. Looks like in 2.0 it was removed altogether. I didn't see any plans to add it back in for 2.1 in Jira, though there's plans to add NULL/NOT NULL operators. Can we get the "NOT IN" operator back on the list?

support CiviCRM through 'make it happen' initiatives!
http://civicrm.org/mih

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: Using Search Builder to find members not in another group
June 10, 2008, 03:42:24 pm

This will not happen in 2.1, it might happen in a future release.

The != operator is actually another query and not a simple LEFT JOIN. Check: http://forum.civicrm.org/index.php/topic,2514.html for details. This complicates things a bit more as we would need to introduce a temp table to store those results etc. So some restructuring will be needed. The 1.8 implementation did not return the right results (for most dbs)

2.1 is feature frozen at this stage. We need to  focus on the USPIRG and Pledgebank projects and incorporate it before doing a release

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

lcdweb

  • Forum Godess / God
  • I live on this forum
  • *****
  • Posts: 1620
  • Karma: 116
    • www.lcdservices.biz
  • CiviCRM version: many versions...
  • CMS version: Joomla/Drupal
  • MySQL version: 5.1+
  • PHP version: 5.2+
Re: Using Search Builder to find members not in another group
June 14, 2008, 03:10:44 pm
I ended up using the workaround Lobo had proposed in an earlier post. But I realized you don't need to mess with tags because there's an equivalent remove from group action on search results. I needed to find all records in Group A, that are not in Group B. Steps:

1) Create new "delta" group
2) Add all Group A records to delta group
3) Remove all Group B records from delta group

It's not a smart group, so it's not ideal. But can at least give you a snapshot of the data.
support CiviCRM through 'make it happen' initiatives!
http://civicrm.org/mih

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: Using Search Builder to find members not in another group
June 15, 2008, 02:29:30 pm

We should write a custom search for this. Once done, it can be used as a smart group etc. A good project for 2.1 :)

Issue filed here: http://issues.civicrm.org/jira/browse/CRM-3207

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) »
  • Using Search Builder to find members not in another group

This forum was archived on 2017-11-26.