Searching for someone not in a relationship
up vote
3
down vote
favorite
We're using CiviCRM to primarily build a network of activist groups around the country. There are "area organisers" and people they are linked with. We are using the relationship function to link people together.
It would be really helpful to be able to search for people who haven't yet been linked to an area organiser (because there isn't one yet in their locality), but I can only work out how to search for relationships between specific individuals. Is there a way to search for everyone who doesn't yet have a relationship with anyone else in the database?
search relationships
add a comment |
up vote
3
down vote
favorite
We're using CiviCRM to primarily build a network of activist groups around the country. There are "area organisers" and people they are linked with. We are using the relationship function to link people together.
It would be really helpful to be able to search for people who haven't yet been linked to an area organiser (because there isn't one yet in their locality), but I can only work out how to search for relationships between specific individuals. Is there a way to search for everyone who doesn't yet have a relationship with anyone else in the database?
search relationships
I love the the title of your question 😄
– William Mortada
Nov 15 at 10:02
add a comment |
up vote
3
down vote
favorite
up vote
3
down vote
favorite
We're using CiviCRM to primarily build a network of activist groups around the country. There are "area organisers" and people they are linked with. We are using the relationship function to link people together.
It would be really helpful to be able to search for people who haven't yet been linked to an area organiser (because there isn't one yet in their locality), but I can only work out how to search for relationships between specific individuals. Is there a way to search for everyone who doesn't yet have a relationship with anyone else in the database?
search relationships
We're using CiviCRM to primarily build a network of activist groups around the country. There are "area organisers" and people they are linked with. We are using the relationship function to link people together.
It would be really helpful to be able to search for people who haven't yet been linked to an area organiser (because there isn't one yet in their locality), but I can only work out how to search for relationships between specific individuals. Is there a way to search for everyone who doesn't yet have a relationship with anyone else in the database?
search relationships
search relationships
asked Nov 7 at 20:46
Andrew Metheven
414
414
I love the the title of your question 😄
– William Mortada
Nov 15 at 10:02
add a comment |
I love the the title of your question 😄
– William Mortada
Nov 15 at 10:02
I love the the title of your question 😄
– William Mortada
Nov 15 at 10:02
I love the the title of your question 😄
– William Mortada
Nov 15 at 10:02
add a comment |
4 Answers
4
active
oldest
votes
up vote
6
down vote
The 'Include/Exclude' search is very handy for situations like this:
- create a group of all contacts of interest
- create a group of those who DO meet some criteria
- use include/exclude to include group 1 and exclude group 2 to
produce those who DON'T meet the criteria
So in your case:
- create a group of all people
- create a group who do have a relationship (Advanced Search, open Relationships section, select relationship types of interest, search, save group)
- go to
Search > Custom Searches > Include/Exclude search
include your 'all' group, exclude the 'has relationship' group (select either AND/OR - doesn't matter since you are not using tags but it is still required) Save as a group if you want.
If you want to use this often, create all 3 groups as smart groups.
Thank you, I can try that!
– Andrew Metheven
Nov 8 at 9:50
add a comment |
up vote
2
down vote
One way is to export two lists to Excel and then use the power of Excel to find the missing ones.
- Under Reports - Contact Reports, run the Relationship Report and under Columns check "Contact ID" so that it appears in the output.
- From the Actions dropdown export that to CSV.
- Under Reports - Contact Reports, run the Constituent Summary Report and under Columns check "Contact ID".
- From the Actions dropdown export that to CSV.
- Do something like described here to find the contact ids that are in the second list but not the first. Note you'll need to sort the lists by contact id first for this method to work.
https://www.extendoffice.com/documents/excel/3124-excel-compare-two-columns-for-missing-values.html#formula
There are other ways to do it in Excel if you don't like that one above. Search the internet for something like "how to tell which values are missing from two lists in excel". If you're using LibreOffice or similar I'm sure it can be done too.
add a comment |
up vote
2
down vote
While this isn't an ideal fix, it's a quick one if you know SQL:
- Create a new group called "Contacts without relationship".
- Note the group ID. You can find the group id by hovering the mouse over the "Contacts" link of the group on the Contacts » Manage Groups page. In the screenshot below, you can see that the group ID of "Summer Program Volunteers" is "3".
- Run this SQL statement:
INSERT INTO civicrm_group_contact (group_id, contact_id) (SELECT '3', cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_a WHERE cr.id IS NULL AND cc.is_deleted = 0 AND cc.id IN (SELECT cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_b WHERE cr.id IS NULL AND cc.is_deleted = 0));
Replace the "3" above with YOUR group ID before you run it.
Now, everyone without a relationship will be in your group.
Screenshot for group ID (click to zoom):
Nice, although is it missing an INSERT? I might also add cc.is_deleted = 0 to both the main and subqueries. And possibly restrict it to ACTIVE relationships, which might even require comparing end_date to the current date.
– Demerit
Nov 8 at 0:46
Oops - yeah, I modified then forgot to add the INSERT. I'll try to tweak this.
– Jon G - Megaphone Tech
Nov 8 at 1:01
Thank you - I don't know SQL, but someone else in our group might.
– Andrew Metheven
Nov 8 at 9:50
add a comment |
up vote
0
down vote
Either you can create a custom report to achieve that or can be done through CiviCRM API in a module code.
add a comment |
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
6
down vote
The 'Include/Exclude' search is very handy for situations like this:
- create a group of all contacts of interest
- create a group of those who DO meet some criteria
- use include/exclude to include group 1 and exclude group 2 to
produce those who DON'T meet the criteria
So in your case:
- create a group of all people
- create a group who do have a relationship (Advanced Search, open Relationships section, select relationship types of interest, search, save group)
- go to
Search > Custom Searches > Include/Exclude search
include your 'all' group, exclude the 'has relationship' group (select either AND/OR - doesn't matter since you are not using tags but it is still required) Save as a group if you want.
If you want to use this often, create all 3 groups as smart groups.
Thank you, I can try that!
– Andrew Metheven
Nov 8 at 9:50
add a comment |
up vote
6
down vote
The 'Include/Exclude' search is very handy for situations like this:
- create a group of all contacts of interest
- create a group of those who DO meet some criteria
- use include/exclude to include group 1 and exclude group 2 to
produce those who DON'T meet the criteria
So in your case:
- create a group of all people
- create a group who do have a relationship (Advanced Search, open Relationships section, select relationship types of interest, search, save group)
- go to
Search > Custom Searches > Include/Exclude search
include your 'all' group, exclude the 'has relationship' group (select either AND/OR - doesn't matter since you are not using tags but it is still required) Save as a group if you want.
If you want to use this often, create all 3 groups as smart groups.
Thank you, I can try that!
– Andrew Metheven
Nov 8 at 9:50
add a comment |
up vote
6
down vote
up vote
6
down vote
The 'Include/Exclude' search is very handy for situations like this:
- create a group of all contacts of interest
- create a group of those who DO meet some criteria
- use include/exclude to include group 1 and exclude group 2 to
produce those who DON'T meet the criteria
So in your case:
- create a group of all people
- create a group who do have a relationship (Advanced Search, open Relationships section, select relationship types of interest, search, save group)
- go to
Search > Custom Searches > Include/Exclude search
include your 'all' group, exclude the 'has relationship' group (select either AND/OR - doesn't matter since you are not using tags but it is still required) Save as a group if you want.
If you want to use this often, create all 3 groups as smart groups.
The 'Include/Exclude' search is very handy for situations like this:
- create a group of all contacts of interest
- create a group of those who DO meet some criteria
- use include/exclude to include group 1 and exclude group 2 to
produce those who DON'T meet the criteria
So in your case:
- create a group of all people
- create a group who do have a relationship (Advanced Search, open Relationships section, select relationship types of interest, search, save group)
- go to
Search > Custom Searches > Include/Exclude search
include your 'all' group, exclude the 'has relationship' group (select either AND/OR - doesn't matter since you are not using tags but it is still required) Save as a group if you want.
If you want to use this often, create all 3 groups as smart groups.
answered Nov 8 at 9:09
Aidan♦
5,584426
5,584426
Thank you, I can try that!
– Andrew Metheven
Nov 8 at 9:50
add a comment |
Thank you, I can try that!
– Andrew Metheven
Nov 8 at 9:50
Thank you, I can try that!
– Andrew Metheven
Nov 8 at 9:50
Thank you, I can try that!
– Andrew Metheven
Nov 8 at 9:50
add a comment |
up vote
2
down vote
One way is to export two lists to Excel and then use the power of Excel to find the missing ones.
- Under Reports - Contact Reports, run the Relationship Report and under Columns check "Contact ID" so that it appears in the output.
- From the Actions dropdown export that to CSV.
- Under Reports - Contact Reports, run the Constituent Summary Report and under Columns check "Contact ID".
- From the Actions dropdown export that to CSV.
- Do something like described here to find the contact ids that are in the second list but not the first. Note you'll need to sort the lists by contact id first for this method to work.
https://www.extendoffice.com/documents/excel/3124-excel-compare-two-columns-for-missing-values.html#formula
There are other ways to do it in Excel if you don't like that one above. Search the internet for something like "how to tell which values are missing from two lists in excel". If you're using LibreOffice or similar I'm sure it can be done too.
add a comment |
up vote
2
down vote
One way is to export two lists to Excel and then use the power of Excel to find the missing ones.
- Under Reports - Contact Reports, run the Relationship Report and under Columns check "Contact ID" so that it appears in the output.
- From the Actions dropdown export that to CSV.
- Under Reports - Contact Reports, run the Constituent Summary Report and under Columns check "Contact ID".
- From the Actions dropdown export that to CSV.
- Do something like described here to find the contact ids that are in the second list but not the first. Note you'll need to sort the lists by contact id first for this method to work.
https://www.extendoffice.com/documents/excel/3124-excel-compare-two-columns-for-missing-values.html#formula
There are other ways to do it in Excel if you don't like that one above. Search the internet for something like "how to tell which values are missing from two lists in excel". If you're using LibreOffice or similar I'm sure it can be done too.
add a comment |
up vote
2
down vote
up vote
2
down vote
One way is to export two lists to Excel and then use the power of Excel to find the missing ones.
- Under Reports - Contact Reports, run the Relationship Report and under Columns check "Contact ID" so that it appears in the output.
- From the Actions dropdown export that to CSV.
- Under Reports - Contact Reports, run the Constituent Summary Report and under Columns check "Contact ID".
- From the Actions dropdown export that to CSV.
- Do something like described here to find the contact ids that are in the second list but not the first. Note you'll need to sort the lists by contact id first for this method to work.
https://www.extendoffice.com/documents/excel/3124-excel-compare-two-columns-for-missing-values.html#formula
There are other ways to do it in Excel if you don't like that one above. Search the internet for something like "how to tell which values are missing from two lists in excel". If you're using LibreOffice or similar I'm sure it can be done too.
One way is to export two lists to Excel and then use the power of Excel to find the missing ones.
- Under Reports - Contact Reports, run the Relationship Report and under Columns check "Contact ID" so that it appears in the output.
- From the Actions dropdown export that to CSV.
- Under Reports - Contact Reports, run the Constituent Summary Report and under Columns check "Contact ID".
- From the Actions dropdown export that to CSV.
- Do something like described here to find the contact ids that are in the second list but not the first. Note you'll need to sort the lists by contact id first for this method to work.
https://www.extendoffice.com/documents/excel/3124-excel-compare-two-columns-for-missing-values.html#formula
There are other ways to do it in Excel if you don't like that one above. Search the internet for something like "how to tell which values are missing from two lists in excel". If you're using LibreOffice or similar I'm sure it can be done too.
answered Nov 7 at 22:33
Demerit
2,9941516
2,9941516
add a comment |
add a comment |
up vote
2
down vote
While this isn't an ideal fix, it's a quick one if you know SQL:
- Create a new group called "Contacts without relationship".
- Note the group ID. You can find the group id by hovering the mouse over the "Contacts" link of the group on the Contacts » Manage Groups page. In the screenshot below, you can see that the group ID of "Summer Program Volunteers" is "3".
- Run this SQL statement:
INSERT INTO civicrm_group_contact (group_id, contact_id) (SELECT '3', cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_a WHERE cr.id IS NULL AND cc.is_deleted = 0 AND cc.id IN (SELECT cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_b WHERE cr.id IS NULL AND cc.is_deleted = 0));
Replace the "3" above with YOUR group ID before you run it.
Now, everyone without a relationship will be in your group.
Screenshot for group ID (click to zoom):
Nice, although is it missing an INSERT? I might also add cc.is_deleted = 0 to both the main and subqueries. And possibly restrict it to ACTIVE relationships, which might even require comparing end_date to the current date.
– Demerit
Nov 8 at 0:46
Oops - yeah, I modified then forgot to add the INSERT. I'll try to tweak this.
– Jon G - Megaphone Tech
Nov 8 at 1:01
Thank you - I don't know SQL, but someone else in our group might.
– Andrew Metheven
Nov 8 at 9:50
add a comment |
up vote
2
down vote
While this isn't an ideal fix, it's a quick one if you know SQL:
- Create a new group called "Contacts without relationship".
- Note the group ID. You can find the group id by hovering the mouse over the "Contacts" link of the group on the Contacts » Manage Groups page. In the screenshot below, you can see that the group ID of "Summer Program Volunteers" is "3".
- Run this SQL statement:
INSERT INTO civicrm_group_contact (group_id, contact_id) (SELECT '3', cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_a WHERE cr.id IS NULL AND cc.is_deleted = 0 AND cc.id IN (SELECT cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_b WHERE cr.id IS NULL AND cc.is_deleted = 0));
Replace the "3" above with YOUR group ID before you run it.
Now, everyone without a relationship will be in your group.
Screenshot for group ID (click to zoom):
Nice, although is it missing an INSERT? I might also add cc.is_deleted = 0 to both the main and subqueries. And possibly restrict it to ACTIVE relationships, which might even require comparing end_date to the current date.
– Demerit
Nov 8 at 0:46
Oops - yeah, I modified then forgot to add the INSERT. I'll try to tweak this.
– Jon G - Megaphone Tech
Nov 8 at 1:01
Thank you - I don't know SQL, but someone else in our group might.
– Andrew Metheven
Nov 8 at 9:50
add a comment |
up vote
2
down vote
up vote
2
down vote
While this isn't an ideal fix, it's a quick one if you know SQL:
- Create a new group called "Contacts without relationship".
- Note the group ID. You can find the group id by hovering the mouse over the "Contacts" link of the group on the Contacts » Manage Groups page. In the screenshot below, you can see that the group ID of "Summer Program Volunteers" is "3".
- Run this SQL statement:
INSERT INTO civicrm_group_contact (group_id, contact_id) (SELECT '3', cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_a WHERE cr.id IS NULL AND cc.is_deleted = 0 AND cc.id IN (SELECT cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_b WHERE cr.id IS NULL AND cc.is_deleted = 0));
Replace the "3" above with YOUR group ID before you run it.
Now, everyone without a relationship will be in your group.
Screenshot for group ID (click to zoom):
While this isn't an ideal fix, it's a quick one if you know SQL:
- Create a new group called "Contacts without relationship".
- Note the group ID. You can find the group id by hovering the mouse over the "Contacts" link of the group on the Contacts » Manage Groups page. In the screenshot below, you can see that the group ID of "Summer Program Volunteers" is "3".
- Run this SQL statement:
INSERT INTO civicrm_group_contact (group_id, contact_id) (SELECT '3', cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_a WHERE cr.id IS NULL AND cc.is_deleted = 0 AND cc.id IN (SELECT cc.id from civicrm_contact cc LEFT JOIN civicrm_relationship cr ON cc.id = cr.contact_id_b WHERE cr.id IS NULL AND cc.is_deleted = 0));
Replace the "3" above with YOUR group ID before you run it.
Now, everyone without a relationship will be in your group.
Screenshot for group ID (click to zoom):
edited Nov 8 at 1:02
answered Nov 8 at 0:25
Jon G - Megaphone Tech
25k11767
25k11767
Nice, although is it missing an INSERT? I might also add cc.is_deleted = 0 to both the main and subqueries. And possibly restrict it to ACTIVE relationships, which might even require comparing end_date to the current date.
– Demerit
Nov 8 at 0:46
Oops - yeah, I modified then forgot to add the INSERT. I'll try to tweak this.
– Jon G - Megaphone Tech
Nov 8 at 1:01
Thank you - I don't know SQL, but someone else in our group might.
– Andrew Metheven
Nov 8 at 9:50
add a comment |
Nice, although is it missing an INSERT? I might also add cc.is_deleted = 0 to both the main and subqueries. And possibly restrict it to ACTIVE relationships, which might even require comparing end_date to the current date.
– Demerit
Nov 8 at 0:46
Oops - yeah, I modified then forgot to add the INSERT. I'll try to tweak this.
– Jon G - Megaphone Tech
Nov 8 at 1:01
Thank you - I don't know SQL, but someone else in our group might.
– Andrew Metheven
Nov 8 at 9:50
Nice, although is it missing an INSERT? I might also add cc.is_deleted = 0 to both the main and subqueries. And possibly restrict it to ACTIVE relationships, which might even require comparing end_date to the current date.
– Demerit
Nov 8 at 0:46
Nice, although is it missing an INSERT? I might also add cc.is_deleted = 0 to both the main and subqueries. And possibly restrict it to ACTIVE relationships, which might even require comparing end_date to the current date.
– Demerit
Nov 8 at 0:46
Oops - yeah, I modified then forgot to add the INSERT. I'll try to tweak this.
– Jon G - Megaphone Tech
Nov 8 at 1:01
Oops - yeah, I modified then forgot to add the INSERT. I'll try to tweak this.
– Jon G - Megaphone Tech
Nov 8 at 1:01
Thank you - I don't know SQL, but someone else in our group might.
– Andrew Metheven
Nov 8 at 9:50
Thank you - I don't know SQL, but someone else in our group might.
– Andrew Metheven
Nov 8 at 9:50
add a comment |
up vote
0
down vote
Either you can create a custom report to achieve that or can be done through CiviCRM API in a module code.
add a comment |
up vote
0
down vote
Either you can create a custom report to achieve that or can be done through CiviCRM API in a module code.
add a comment |
up vote
0
down vote
up vote
0
down vote
Either you can create a custom report to achieve that or can be done through CiviCRM API in a module code.
Either you can create a custom report to achieve that or can be done through CiviCRM API in a module code.
answered Nov 7 at 21:47
Hassan Farooq
533110
533110
add a comment |
add a comment |
Thanks for contributing an answer to CiviCRM Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcivicrm.stackexchange.com%2fquestions%2f27240%2fsearching-for-someone-not-in-a-relationship%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I love the the title of your question 😄
– William Mortada
Nov 15 at 10:02