Search Builder - find one word amongst many

Question

I'm in the process of testing CiviCRM for managing our non-profit contacts. I'm in the process of testing the import function. We already have our current contacts organised into groups. When I export out of the old database, all the groups end up in a single cell. So I have mapped them all to a custom field on import. So far so good.

Now I need to search that custom field to filter the groups so I can tag them with new tags inside CiviCRM. I hope this makes sense?

I have been trying to use the 'search builder' to query the custom field, using the LIKE and % but I don't seem to get all the records.

I know there should be 91 records with PAWS in them but I only end up with 4. See the screenshots below.

Is there a better way to do this?

Answer 1

The %PAWS% should work.

Another option is, which depends on the number of groups you have.

Export all people of group PAWS to an excel with a unique identifier (external id or email address for example). Import them and place all of them in right group.

Answer 2

As @ErikH commented correcting the data format before import may be easier if %PAWS% doesn't work.

It looks like you're using ' - ' as your delimiter in that field, which appears to be a single value text field.

If you set this custom field as multiple value with a comma delimiter and import you would be able to use advanced search and your group values should be available as a picklist rather than doing a string search. I believe the cell should look like aaa,bbb,ccc but you can manually put in a data point and export to find out.

A bit more info: http://wiki.civicrm.org/confluence/display/CRMDOC/Importing+Data+-+Notes

https://forum.civicrm.org/index.php?topic=12440.0

Answer 3

I would give '%PAWS%' a try, otherwise you only get the cases where PAWS is the first one?

Answer 4

Unfortunately, CiviCRM's import UI only allows you to add folks to one group at a time.

If you don't have a ton of groups, another solution is to do multiple imports, one per group. If you'd like to try that approach:

• First, make sure your import data has some sort of unique identifier, which hopefully you imported to the "external identifier" field. If not, you may want to delete and reimport. If your data doesn't have a unique identifier, you can add one in a new column in Excel/LibreOffice - instructions are here.
• Use the "Filter" or "Sort" option in Excel to get all the members of a single group together. Save a copy of the spreadsheet with only those folks.
• To speed up the import - only save the "external identifier" column! There's no need to reimport all the data that's already there.
• Do this for each group.
• Import these saved CSV files to CiviCRM, mapping the external identifier column correctly. CiviCRM will recognize that these contacts already exist, and will ONLY add them to the group you specify on import.

Still another approach is to use the API CSV import tool or its friend the API CSV Import GUI - but those are NOT tools for CiviCRM beginners! I only mention it here for future visitors to this question. When I use those tools, I use a lookup tool that brings the contact ID and the group ID into a CSV file. Then I can import a single CSV file that populates all the group data.