Author Topic: Newbie question: searching for people with no gender (Read 1808 times)

minton · February 16, 2009, 09:47:50 am

Hi there,

I want to find everyone in my contacts who has not yet had a gender assigned - I can't find a way to do this.

Am I missing something?

thanks!

Mark

Kurund Jalmi · February 16, 2009, 10:12:36 am

Mark:

I guess you can use Search Builder for this: http://sandbox.civicrm.org/civicrm/contact/search/builder?reset=1

Then select " Individual - Gender - IS NULL "

HTh

Kurund

minton · February 20, 2009, 05:50:20 am

Thanks Kurund,

Search Builder doesn't let me do this (at least not in 1.9, which I'm using).

There's no such operator offered as "IS", and if I use "=" or "LIKE", I get no result when I use "NULL" or "IS NULL" or an empty fields as the text to match.

Also, if I use "<> MALE", I get only those entries with a gender of "FEMALE", and no entries without the gender recorded (although I know there are many in my dataset with no gender recorded)

Any more ideas?

Thanks

Mark

Kurund Jalmi · February 20, 2009, 06:49:46 am

Mark:

Sorry don't remember much about 1.9 release, it's no longer supported by CiviCRM team.

If possible you should upgrade to latest release...

Kurund

CiviTeacher.com · February 23, 2009, 01:34:59 pm

what about

WHERE gender <> "MALE" AND gender <> "FEMALE"

..or..
WHERE gender NOT IN ("MALE","FEMALE");

if you want you can try to use subqueries like so, not through search builder but my actually logging into the server and running queries through MySQL or through phpMyAdmin on your hosting control panel:

WHERE gender <> ANY (SELECT x FROM y);

Documentation here: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

-------- A way without a ton of MySQL ----

If this previous way is too SQL heavy, instead search for contacts that are not MALE and it will return all your contacts that are FEMALE or NULL. Export this contact into an EXCEL document. Sort by gender, and delete all the FEMALE. You now have a list of all your NULL gender contacts. Hopefully it is not so many. Then you can look up the contacts manually by name and set the gender.

minton · February 24, 2009, 05:54:00 am

Thanks Stoob,

that's a good set of suggestions

unfortunately, the "NOT" queries don't work as advertised - "WHERE gender <> FEMALE" returns only males, and "WHERE gender <> FEMALE AND gender <> MALE" returns nobody. (And the majority of my contacts have no gender assigned as yet)

this also blows out the export to excel plan, sadly - and while NOT IN would be a fine thing, CiviCRM 1.9 doesn't offer it in Search Builder (only IN or LIKE)

So it looks like my options are:

Upgrade to CiviCRM 2.x - is there a lovely simple guide for time-limited people about doing this safely?
Get to grips with mySQL and/or phpAdmin - this would be great to learn how to do, and the SQL itself doesn't worry me, but my previous form with it is in programming MSAccess and VB some years ago, and I have no idea how to use phpAdmin (or whatever) to poke about directly with my CiviCRM data - is there any (CiviCRM-specific?) documentation about getting under the DB hood with SQL and one of these tools?

Again, many thanks for your help!

Dave Greenberg · February 24, 2009, 01:50:26 pm

If you're using Drupal or Joomla, upgrading to 2.1 or 2.2 will required you to upgrade your CMS as well. That said, with 2.1 or 2.2 you could do the Search Builder search and then use Batch Update via Profile to input your missing gender values in a grid.

Directions are pretty straightforward (update your forum profile to let folks know your environment - i'm assuming Drupal):
http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Drupal+Sites+to+2.0
http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Drupal+Sites+to+2.1

petednz · February 24, 2009, 06:47:21 pm

For a static solution (or may even hold together with Smart Groups)

Create a Group of everyone but all it Ungendered

Create a Group of Males - and remove from above group

Create a Group of Females and remove from above group.

Or you could presumably do the above and the use Custom Search using the include/exclude groups option.

Does that provide a non-technical solution?

minton · February 25, 2009, 04:21:14 am

It certainly does provide a non-technical solution!

Thanks very much - I assigned everyone to an UnGendered group, then could use Search Builder to find all males and all females and remove them from the group.

Job done!

CiviCRM Community Forums (archive)

News:

Author Topic: Newbie question: searching for people with no gender (Read 1808 times)

minton

Newbie question: searching for people with no gender

Kurund Jalmi

Re: Newbie question: searching for people with no gender

minton

Re: Newbie question: searching for people with no gender

Kurund Jalmi

Re: Newbie question: searching for people with no gender

CiviTeacher.com

Re: Newbie question: searching for people with no gender

minton

Re: Newbie question: searching for people with no gender

Dave Greenberg

Re: Newbie question: searching for people with no gender

petednz

Re: Newbie question: searching for people with no gender

minton

Re: Newbie question: searching for people with no gender