# Question
Title: Importing Contacts, Memberships and Contributions in 2020
I need to import from an external database 1500 contacts with their memberships and contributions.

Everything I read about it in Stackexchange was written between 2015 and 2017.

Is the following still up-to-date?

* I can't import everything within a single .csv file, CiviCRM is not build this way
* It takes 3 steps:
    1. import contacts with external id connection prevents to look up for contact ID within Civicrm
    2. import memberships with external id field
    3. import contributions with the proper membership\_type
* Using CSVimporter is the proper tool

Should I do this way or do is there a quicker way?

best regards,

Guillaume

# Answer
> 2 votes
The "normal" import tools tend to focus on a single entity (contact, contribution membership, participants...) indeed, so as you identified, it needs several separate imports and trying to find some common keys so civi knows how to link them

If you have complex/composite structures to import, it might be easier to write a simple php script that calls all the needed apis to create the entities. If you are familiar with php and the civicrm apis, it might be simplier (and it makes it way easier to "replay" the import)

good luck!

---
Tags: import
---


# Question
Title: Network Error when Looking for Smart Groups
Under the groups tab in a contact summary if we click to view the smart groups the contact is in we get:

Network Error Unable to reach the server. Please refresh this page in your browser and try again.

We have checked the setting for URLs & Directories & cleared the cache. If we do a search for contacts in the a smart group all is fine. Below is the error & backtrace from the log.

Install is WordPress 5.4 and CiviCRM 5.23.4.

```
 [debug] $backTrace = #0 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(192): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/packages/DB.php(998): PEAR_Error->__construct("DB Error: syntax error", -2, 16, (Array:2), "SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...")
#3 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-2, 16, (Array:2), "SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...")
#4 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -2, 16, (Array:2), "SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...", "DB_Error", TRUE)
#5 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1925): PEAR->__call("raiseError", (Array:7))
#6 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(935): DB_common->raiseError(-2, NULL, NULL, "SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...", "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(405): DB_mysqli->mysqliRaiseError()
#8 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1231): DB_mysqli->simpleQuery("SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...")
#9 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2691): DB_common->query("SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...")
#10 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...")
#11 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(421): DB_DataObject->query("SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...")
#12 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1497): CRM_Core_DAO->query("SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...", TRUE)
#13 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(569): CRM_Core_DAO::singleValueQuery("SELECT COUNT(*)  FROM civicrm_contact contact_a   \nLEFT JOIN  ON .entity_id ...")
#14 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3191): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
#15 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3092): CRM_Contact_BAO_Query->addGroupContactCache((Array:1), "civicrm_group_contact_cache_5e8dc68a3c7d6", "contact_a", "IN")
#16 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(1819): CRM_Contact_BAO_Query->group((Array:5))
#17 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(2057): CRM_Contact_BAO_Query->whereClauseSingle((Array:5), FALSE)
#18 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(581): CRM_Contact_BAO_Query->whereClause(FALSE)
#19 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(528): CRM_Contact_BAO_Query->initialize(NULL)
#20 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4614): CRM_Contact_BAO_Query->__construct((Array:1), (Array:1), (Array:24), TRUE, FALSE, 1, TRUE, TRUE, FALSE, NULL, "AND", NULL, "1")
#21 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(210): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:1), NULL, NULL, 0, 0, FALSE)
#22 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(192): CRM_Contact_BAO_GroupContactCache::add((Array:21))
#23 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(688): CRM_Contact_BAO_GroupContactCache::loadAll()
#24 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/Page/View/ContactSmartGroup.php(41): CRM_Contact_BAO_GroupContactCache::contactGroup("9445")
#25 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/Page/View/ContactSmartGroup.php(89): CRM_Contact_Page_View_ContactSmartGroup->browse()
#26 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(268): CRM_Contact_Page_View_ContactSmartGroup->run((Array:4), NULL)
#27 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(68): CRM_Core_Invoke::runItem((Array:14))
#28 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:4))
#29 /home/<<account>>/public_html/wp-content/plugins/civicrm/civicrm.php(1449): CRM_Core_Invoke::invoke((Array:4))
#30 /home/<<account>>/public_html/wp-includes/class-wp-hook.php(287): CiviCRM_For_WordPress->invoke("")
#31 /home/<<account>>/public_html/wp-includes/class-wp-hook.php(311): WP_Hook->apply_filters("", (Array:1))
#32 /home/<<account>>/public_html/wp-includes/plugin.php(478): WP_Hook->do_action((Array:1))
#33 /home/<<account>>/public_html/wp-admin/admin.php(254): do_action("toplevel_page_CiviCRM")
#34 {main}

 [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT COUNT(*)  FROM civicrm_contact contact_a
LEFT JOIN  ON .entity_id = .id  WHERE (contact_a.is_deleted = 0)   AND contact_a.id NOT IN (
                          SELECT contact_id FROM civicrm_group_contact
                          WHERE civicrm_group_contact.status = 'Removed'
                          AND   civicrm_group_contact.group_id = 17 )  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON .entity_id = .id  WHERE (contact_a.is_deleted = 0)   AND contact_a.id NOT IN ' at line 2]
    [type] => DB_Error
    [user_info] => SELECT COUNT(*)  FROM civicrm_contact contact_a
LEFT JOIN  ON .entity_id = .id  WHERE (contact_a.is_deleted = 0)   AND contact_a.id NOT IN (
                          SELECT contact_id FROM civicrm_group_contact
                          WHERE civicrm_group_contact.status = 'Removed'
                          AND   civicrm_group_contact.group_id = 17 )  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON .entity_id = .id  WHERE (contact_a.is_deleted = 0)   AND contact_a.id NOT IN ' at line 2]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT COUNT(*)  FROM civicrm_contact contact_a
LEFT JOIN  ON .entity_id = .id  WHERE (contact_a.is_deleted = 0)   AND contact_a.id NOT IN (
                          SELECT contact_id FROM civicrm_group_contact
                          WHERE civicrm_group_contact.status = 'Removed'
                          AND   civicrm_group_contact.group_id = 17 )  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON .entity_id = .id  WHERE (contact_a.is_deleted = 0)   AND contact_a.id NOT IN ' at line 2]"]
)

```

Thanks in advance for any insight.

# Answer
Looking at the sql and backtrace, it looks like it is trying to rebuild the smart group id 17. Try resaving the group id 17 search criteria.
> 2 votes

---
Tags: smartgroups
---


# Question
Title: Submission of a form on wordpress OK with chrome but not valid with Firefox, Safary
We are in an emergency situation with Covid-19. We prepared a form on wordpress CRM here : https://international-impact.com/covid-19-ecuasol/ The link is ok on Chrome but not on Safari, Firefox, etc. What mistake have we done ?

# Answer
Its down to permission, make sure anonymous user can access the profile. It is possible that on chrome you are logged and safari you accessing the profile as anonymous user.

1. Navigate to CiviCRM \>\> Administer \>\> Users and Permissions \>\> Permissions (Access Control). Click on 'Wordpress Access Control' link.
2. Look for 'CiviCRM: profile create' and check the box under anonymous or authenticated role column and save the form.
> 0 votes

---
Tags: wordpress, forms
---


title: Remove ability to add email attachments

text:
# Question
Title: Remove ability to add email attachments
I want to remove the ability to add attachments. Particularly, it should not be used for CiviMail as it's a great way to hurt email reputation and is simply unnecessary. I would rather load into my CMS media library and then add it into CiviMail via a hyperlink or image. I suppose I could argue that this option shouldn't even exist in CiviMail.

In **Misc (Undelete, PDFs, Limits, Logging, Captcha, etc.)** there are two options for attachments.

*Maximum Attachments*

*Maximum Attachments For Backend Processes*

Does *Maximum Attachments* mean only for front end forms?

To prevent attachments from being used, I set both to 0. I also tried to set this with an override in civicrm.settings.php but it does not accept a 0, so I tried -1 and that 'save right' but still had no effect.

What are those settings effecting then if not CiviMail? How can I remove attachment functionality? My last resort is removing the option thru a tpl.extra. I would prefer not to do that and fix it at the source.

# Answer
> 1 votes
I think how these options work

**Maximum Attachments**

This options is used to build number of attachment on offline forms like Sending Email in the form activities etc. Till now its handled on All email forms, Note, Activity, Campaign and Grant. Setting 0 means no attachment field on all those forms.

**Maximum Attachments For Backend Processes**

This options is used to process number of files can be saved in the system. This are handled in Note, Activity, Campaign and Grant. Setting this option will limit the number of attachment stored for the entity. Setting 0 means no files will be stored in database or system. This option will restrict sending while sending email as an activity. However the same option is used to restrict files when saving Mailing(will work incase mailing created via api or cloned) but the UI forms explicitly calls separate function to attach files where this setting option is not respected till now.

There is also no UI setting to hide the attachment tab for Mailings so its down to customization. You can do this by one of the following method

1. Hide using css

code:

```
form[name='crmMailing'] li[aria-controls='tab-attachment'] {
    display: none;
}

```
2. Hide/remove using JS

code:

```
$("form[name='crmMailing'] li[aria-controls='tab-attachment']").remove();
OR
$("form[name='crmMailing'] li[aria-controls='tab-attachment']").hide();

```
3. Remove the Attachment tab using hook\_civicrm\_alterAngulars()
4. Remove attachment while sending email using hook\_civicrm\_alterMailParams()

HTH

Pradeep

# Answer
> 1 votes
I don't think either of those settings would help prevent attachments for mailings. The first one the help text says "Maximum number of files (documents, images, etc.) which can be attached to emails or activities. This setting applies to UI forms and limits the number of fields available on the form." Setting it to 0 does remove the attachments field from activities, but the setting's not used I guess for mailings.

The second setting won't help. It's for things like the inbound email processor and is just a sanity/denial-of-service prevention limit.

I think this would need to be a customization.

---
Tags: civimail, email, attachments
---


# Question
Title: Limit price field of type Text/Numeric to only numbers and with a minimum of 4
Is it possible to enforce a numbers only input with a numerical minimum on a `Text/Numeric` price field? I'm guessing it could be done with jQuery, but I would rather have this set up in the Civi Admin UI, since it would be much cleaner.

**More details:**

I have a price field set up like this:

*On the front end, it looks like this:* *(but I want 4 to be the lowest allowed number, with no letters input)*

Basically, I want the HTML equal of `<input type="number" min="4"/>` in the cleanest and safest way possible. *(safe as in not hacking up some jQuery and then moving an element 3 months later and breaking it)*

# Answer
> 2 votes
Ideally you would develop a little extension that uses the validateForm hook (check https://docs.civicrm.org/dev/en/latest/hooks/hook\_civicrm\_validateForm/). This will allow you to refuse input that does not meet your criteria.

---
Tags: civimember, price-sets
---


# Question
Title: Not receiving email from Scheduled Reminder for Activity
I am attempting to set up a Scheduled Reminder for Scheduled Activities (CiviCRM 5.20.2/Drupal 7).
I have a Scheduled Reminder set up as follows:

This is setup to send to the Activity Assignees, 0 hours before a Scheduled Phone Call activity. It is enabled, and has a simple email message.

Under Scheduled Jobs, I can see that "Send Scheduled Reminders" (Job.send\_reminder) is enabled, is running always, and the logs say it is successful.

If I then create an Activity of type Phone Call, set its status to Scheduled, set the time to 30min in the future, and assign it to myself, I will immediately get a system generated Activity Summary email. In 30min time I will **not** get a reminder from the Scheduled Reminder, with the template I defined.

Can anyone tell me why, or tell me where to look for more information? Are there logs which will show me if the reminder is actually being triggered, or if an attempt is made to send the email?

# Answer
OK, answering my own question after much investigation. This was a bitnami civiCRM stack and the crontab to run Job.execute was running as the bitnami user (our error), but needed to run as daemon. That is documented on bitnami's site: https://docs.bitnami.com/bch/apps/civicrm/configuration/configure-scheduler/

Strange behaviour as a result of this - cron seemed to be running fine, but it clearly didn't have permissions to send emails.
> 3 votes

---
Tags: activities, scheduled-reminders, scheduled-job, logs
---


# Question
Title: When adding an activity, a new contact can be created, but it only asks for name and email. How to configure (add fields)?
When a CiviCRM user adds an activity, there is an option of adding a new contact through a pop-up (instead of selecting a contact). I can't find any documentation on whether/how this form can be customized. I'm also not a programmer.

For the organization I work for, it would be extremely helpful to be able to add a bit more info at this stage (at least the phone number, ideally a few other fields). To do so, do we need to modify code (and if so, where) or is there an easier way to configure which fields appear here (ideally through profile, I suppose)?

Thanks!

# Answer
Steve welcome to CiviCRM stack exchange. Yes you can add more fields to the form. They are rendered from profile.

1. Navigate to CiviCRM \>\> Administer \>\> Customize Data and Screens \>\> Profiles
2. Click on Reserved profiles tab
3. Find for 'New Organization', click on 'Fields' link
4. Add more fields you would like to have. You can only add contact and organization field else the form won't be rendered and through error.

HTH

Pradeep
> 4 votes

---
Tags: activities, configuration, documentation
---


title: Uploading donations from a spreadsheet

text:
# Question
Title: Uploading donations from a spreadsheet
In another CRM program I was able to upload several donations at once into the system (from a spreadsheet). Can I do this in CiviCRM? the program would look for matches of names and update those contacts, then would create new records for ones where it could not match a name.

I have over 100 donations in a spreadsheet that I want to upload rather than going to each donor record and uploading all the information.

# Answer
welcome to CiviCRM Stack Exchange! In CiviCRM there is an option in the Contributions menu called "Import Contributions". This allows you to upload contributions from a CSV file. Can you give that a try?
> 1 votes

---
Tags: donations, upload
---


# Question
Title: how to keep previous values of custom Membership fields upon renewal
Civi 5.15.1, WP 5.3.2

My organization provides low-cost farm plots to farmers. We use Civi purely back-end (no website exposure). Each farmer can have multiple plots, which we have implemented in Civi as multiple separate "Farm Plot" Memberships. Farmers usually keep the same plots year after year, so when we renew each Membership, the "Assigned Plot Number" field carries over, which is exactly what we need for that field.

However, we also need to record whether each farmer cleaned up each of their plots at year's end ("Plot Condition"), and we need to keep that information tied to that specific period/plot/farmer. When we renew the Membership, last year's value will carry over to the new year, which is NOT what we want for this field.

We considered switching how we handle Farm Plots from Memberships to Contributions (b/c they're already time-specific). But the plot assignments happen well before plot payments come in, which happens well before we assess Plot Condition. So we'd have to log the Contribution at the start of the year so the plot# is there (no more easy renewal carry-over from last year), set Contribution Status to Pending until we receive payment, and edit the record again at year's end to add Plot Condition. Yuck.

Could anyone suggest any alternatives (other than using Notes)? Many thanks!

# Answer
> 2 votes
Suggest you consider using Activities since these offer greater flexibility imo.

Cases could also be considered with each plot of land representing a Case.

Either ways you end up with an annual Activity for "Plot Condition" etc etc

---
Tags: civimember, custom-fields
---


title: Listing Groups in a mail template

text:
# Question
Title: Listing Groups in a mail template
I'm creating an email template to be used to confirm a contact's membership status, and I would like to include a list of the Groups (representing Committees in our case) that the contact belongs to.

I have tried including the token {contact.Group(s)} (with various capitalizations, inclusion of parentheses, etc.) without success.

I can include a token that sends the user to the form where they can see and edit those memberships, but I would rather just list them. I thought this would just be in the drop-down list of tokens, but no, and I can't find any reference to this in the manuals. Any suggestions?

Thanks as always. I'm using CiviCRM for WordPress, current versions.

# Answer
> 4 votes
As far as I know, that token doesn't exist, in core or in an extension. It's not a bad idea though; it would be quick to write. As with most tokens that list multiple fields, the real trick is in formatting. Some will want them in a particular order; some want them comma-separated vs. each on their own line, etc.

# Answer
> 4 votes
As said by Jon its not available in core or in an extension. You can either write extension and implement hook\_civicrm\_tokens and hook\_civicrm\_tokenValues to add and generate token for the contact or you can use smarty with the help of Civi api

eg:

```
{capture assign=contactId}{contact.contact_id}{/capture}
{crmAPI var='result' entity='GroupContact' action='get' sequential=0 contact_id=$contactId status="Added"}

{foreach from=$result.values item=groupcontact}
  {$groupcontact.title}</br>
{/foreach}

```

HTH

Pradeep

---
Tags: groups, tokens, templates
---


title: Search box to email membership details

text:
# Question
Title: Search box to email membership details
I think I can do this with CiviRules, but my reading has left me a bit confused. Here is what I would like:

A search box on our Wordpress website. Upon a website visitor entering an email into that search box, the system would send an email to the entered address with the membership status of any found members.

I have built forms with Caldera forms, so I think maybe a combination of CiviRules and Caldera forms might work.

CiviCRM 5.20.2 on Wordpress 5.4

# Answer
Yes, I think you can setup caldera form to create an activity of type X in Civi and than have Civirule to send email (using email api extension) when activity of type X is created.

You can use Civi token to include memberships details in email message.

Thanks

Pradeep
> 3 votes

---
Tags: civimember, caldera-forms
---


# Question
Title: extension ACL for activity types error "[] operator not supported for strings"
WP 5.3 civi 5.19.3 php 7

select contact record - error as follows:

```
"Fatal error: Uncaught Error: [] operator not supported for strings in /home/leepngo/public_html/wp-content/uploads/civicrm/ext/biz.jmaconsulting.activitytypeacl-master/activitytypeacl.php:278 Stack trace:
#0 /home/leepngo/public_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/Hook.php(300): activitytypeacl_civicrm_selectWhereClause('Activity', Array)
#1 /home/leepngo/public_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/Hook/WordPress.php(155): CRM_Utils_Hook->runHooks(Array, 'civicrm_selectW...', 2, 'Activity', Array, NULL, NULL, NULL, NULL)
#2 /home/leepngo/public_html/wp-content/plugins/civicrm/civicrm/Civi/Core/CiviEventDispatcher.php(90): CRM_Utils_Hook_WordPress->invokeViaUF(2, 'Activity', Array, NULL, NULL, NULL, NULL, 'civicrm_selectW...')
#3 /home/leepngo/public_html/wp-content/plugins/civicrm/civicrm/vendor/symfony/event-dispatcher/EventDispatcher.php(184): Civi\Core\CiviEventDispatcher::delegateToUF(Object(Civi\Core\Event\GenericHookEvent), 'hook_civicrm_se...', Object(Civi\Core\CiviEventDispatcher))
#4 /h in /home/leepngo/public_html/wp-content/uploads/civicrm/ext/biz.jmaconsulting.activitytypeacl-master/activitytypeacl.php on line 278

```

# Answer
> 5 votes
I think you are running very old version of Activity acl extension. The issue you facing must have been fixed last year. Can you update the extension by downloading from here?

---
Tags: activities, debug
---


# Question
Title: When reinstalling CiviCRM, it will not allow connection to a database with preexisting data
I am reinstalling CiviCRM on Wordpress after Civi quit quit working. I deleted the folder civicrm in /plugins and also in /uploads. This allowed me to upload the new plugin version of Civi and took me to the CiviCRM Installer. However, I get this message "CiviCRM data from previous installation exists in 'database\_april'" which is the name of the database from my previous installation. How can I get the Installer to allow connection to the prior database? Thanks for your help!

# Answer
You'll need to use mysql or phpmyadmin or equivalent to run sql/civicrm\_drop.mysql. You may also need to manually remove the civicrm\_install\_canary table.

What it's technically complaining about is that you still have data in the civicrm\_contact table.

**UPDATE** for more instructions using phpmyadmin: On the Import tab browse for the sql/civicrm\_drop.mysql file and then click the Go button. You won't be able to browse on the server, so you'll need a copy of the civi download unzipped on your machine to browse for it.

To drop civicrm\_install\_canary, go to the SQL tab and enter `DROP TABLE civicrm_install_canary` and click Go.
> 2 votes

---
Tags: wordpress, database, installation
---


# Question
Title: How to redirect user when a civicrm error message shows up?
Often I see in the drupal log that some people try to access invalid URLs. By default civi shows a message like this

> $Fatal Error Details = Array ( \[message\] =\> We can't load the requested web page. This page requires cookies to be enabled in your browser settings. Please check this setting and enable cookies (if they are not enabled). Then try again. If this error persists, contact the site administrator for assistance.
> Site Administrators: This error may indicate that users are accessing this page using a domain or URL other than the configured Base URL. EXAMPLE: Base URL is http://example.org, but some users are accessing the page via http://www.example.org or a domain alias like http://myotherexample.org.
> Error type: Could not find a valid session key. \[code\] =\> )

How can I redirect user to 404 page or similar page? Is it something possible to do? Thanks

# Answer
> 4 votes
May be 'CiviCRM Report Error' extension may do your work! The extension does have setting to redirect from error page.

---
Tags: error-page
---


# Question
Title: How can I add Recaptcha Version 2 for civicrm 4.3
> "You are receiving this email because you are registered as a website administrator using reCAPTCHA, and your website is still using reCAPTCHA v1, which will be turned off on March 31, 2018. We announced the reCAPTCHA v1 deprecation in May 2016. Starting in November 2017, a small percentage of reCAPTCHA v1 traffic will begin to show a notice informing users that the old API will soon be retired. Any calls to the v1 API will not work after March 31, 2018. To ensure continued functionality, you’ll need to update your website to a current version of reCAPTCHA. You can learn more about reCAPTCHA v2, Invisible reCAPTCHA and reCAPTCHA Android API in our Developer’s Guide. The new APIs are simple to implement and will streamline the captcha experience for your users. If you need help, you can engage in the reCAPTCHA Google Developer Group or post to Stack Overflow with the ‘recaptcha’ tag."

How can I update the recaptcha in civicrm to v2 , where should I make the changes in the code and in frontend to make the recaptcha functionality to work in tandem with v2 ?

# Answer
> 3 votes
I was able to apply these patches directly to CiviCRM 4.5.4. Looking at the code, this version is virtually identical in the ReCAPTCHA code, so it should work fine (although you may need to manually apply the patches).

Update this file: /civicrm/packages/recaptcha/recaptchalib.php

with: https://github.com/civicrm/civicrm-packages/pull/92/files

Update this file: /civicrm/CRM/Utils/ReCPATCHA.php

With: https://github.com/civicrm/civicrm-core/pull/5015/files

In browser go to: https://www.google.com/recaptcha and update / create a V.2 key for the website.

then go to: (Administer -\> System Settings -\> Misc) civiURL: /civicrm/admin/setting/misc?reset=1

Update the Public / Private keys with the new keys.

Cleanup caches: via your civi URL: /civicrm/admin/setting/updateConfigBackend?reset=1

# Answer
> 1 votes
Unfortunately /civicrm/packages/recaptcha/recaptchalib.php is quite dated and does not work with reCaptcha v2 for old CiviCRM setups ( have not tested with the most recent 5xx CiviCRM). I have not been able to make @Koji solution work either, so had to rewrite it all (both /civicrm/packages/recaptcha/recaptchalib.php and /civicrm/CRM/Utils/ReCAPTCHA.php). On the bright side, since this topic does not seem to gain much attention lately ( apart from this follow-up ) it seems reCaptcha v2 works in the latest CiviCRM.

---
Tags: recaptcha
---


title: Broken Help Link

text:
# Question
Title: Broken Help Link
https://docs.civicrm.org/user/en/stable/the-user-interface/searching

Search Builder Help link is broken, referenced here:

# Answer
> 1 votes
Welcome to SE! Thanks for reporting that. There are several changes happening with the documentation infrastructure resulting in this glitch. Apparently it should be fixed soon. Meanwhile if you substitute 'latest' for 'stable' in the URL you can get to the documentation.

---
Tags: search-builder
---


# Question
Title: Non admins cannot access event info page
Wierd problem: anonymous user can view event register page, but not event info page (it is completely blank). Have checked permissions, all ok (if I remove View Event Info, there is an access denied message, but if that permission is present, it is the white screen). Have checked custom fields and profiles. No PHP errors logged, nor any errors in Civi's log. Any other ideas what could be the problem?

# Answer
> 2 votes
Thanks @Fabian and @Aidan. I finally discovered my error. I had stupidly given 'Access CiviEvent' permission (Drupal) to anon users. Removing that cleared the problem. https://civicrm.stackexchange.com/a/25326/7190

---
Tags: civievent
---


title: How to change the labels font size

text:
# Question
Title: How to change the labels font size
I don't find how to change the font size when I print labels (I have a CiviCRM Spark account). The default size is far too small and the Post office don't accept it...

# Answer
> 3 votes
Yes there is a option to change the font size. The UI setting is not user friendly but it does allow you to update the attributes.

1. Navigate to CiviCRM \>\> Administer \>\> System Settings \>\> Option Groups.
2. Find for Mailing Label Format.
3. Click on "Edit options' link besides 'Mailing Label Format'
4. You will see list of formats, click on 'Edit' besides one of the format type.
5. In 'value' text field find for 'font-size' and change the value from old to new

for eg if the old value was `"font-size":8` than replace it with `"font-size":16`

HTH

Pradeep

---
Tags: address-labels, printing
---


# Question
Title: Is there a hook to impact custom data before it's saved?
I feel like I've hit this issue before and found a solution, but I'm drawing a blank right now. I need to be able to compare the value of a custom data field before it's saved in order to conditionally perform another action. For core fields I would typically do this with the pre hook -- as you can look up the existing value and compare with the value passed in the hook. But the pre hook is not hit with custom value updates (why?), and the custom hook is hit *after* the data is saved, so you can't compare with the prior value (it's basically the equivalent of the post hook, specific to custom data).

I'd rather not work with form hooks, as I want this to be universally triggered without me needing to track down all the forms where the data might be potentially updated.

Any suggestions?

# Answer
Not as far as I know. You could, however, apply this patch, which introduces generic `preInsert` and `preUpdate` hooks to CiviCRM. The copy and demo code for PR explains how you can use these hooks.
> 3 votes

---
Tags: custom-fields, hooks
---


# Question
Title: use of "Dear you" extension when there is no first/last name only email and custom name field
What if I don't have First name/last name but only a custom field called "full name" How to display that field or if thats missing display: "Supporter" instead?

# Answer
> 2 votes
You can use Smarty in greetings handling. So first - make sure you're using the "Email Greeting" or "Postal Greeting" token instead of the "First Name" etc. tokens.

Then you can do something like this:

```
{capture assign=ln}{contact.last_name}{/capture}{if $ln ==""}{contact.custom_1}{else}{contact.last_name}{/if}

```

See this blog post on greetings handling for more detail.

EDIT: This approach won't work if you need accented characters because of how Smarty works. See https://www.dxsdata.com/2014/05/php-smarty-empties-strings-with-special-characters/ for details.

---
Tags: tokens
---


# Question
Title: How do I prevent CiviSMS from converting my URLs to longer clickthrough-aware URLs?
CiviMail and CiviSMS both default to tracking click-throughs. To do so, it converts a link like `http://www.cnn.com` to something much longer, like `https://www.example.com/wp-content/plugins/civicrm/civicrm/extern/url.php?u=91701&qid=2521100`. That's fine in CiviMail, when you a) can turn it off, and b) you can hide it behind a hyperlink - but it can be really annoying in a text message.

Since there's no "track click-throughs" checkbox when creating an SMS, how do I fix this?

# Answer
> 5 votes
You can install this extension: https://github.com/PalanteJon/coop.palantetech.nosmsurltracking. This will automatically turn off click-through tracking on all mass SMS messages. There's no checkbox (unless someone codes one in), so it turns tracking off on ALL messages - but if you need tracking on a single message, you can disable the extension temporarily.

# Answer
> 0 votes
If you do not use https:// my Android phone recognizes it as a link and makes it hyperlinked.

On Civi 5.23.4

---
Tags: civimail, sms
---


# Question
Title: Drupal Views needs settings file update for 5.24
I upgraded a client to 5.24 and it broke Drupal Views - my various views all had broken items. Views message was "This view has been automatically updated to fix missing relationships". However, while saving one view worked, another didn't. Not sure if the right place to alert people to this, but here goes. Previously the client was on 5.19.4 so your situation may vary. My answer is below

# Answer
The answer is to update the Drupal settings file with the tables listed in menu Administer/System Settings/CMS Database Integration. There have been new & removed tables. I then cleared the Views cache, and all was well.
> 1 votes

# Answer
We find a 'drush cc all' or 'clear all caches' via the UI sorts these situations post-upgrade
> 1 votes

---
Tags: drupal-views
---


# Question
Title: Does archiving mailings do anything in particular?
CiviMail has an option to archive mailings (when browsing mailings).

Does changing the status to archived have any concrete impact on mailings, such as cleaning up information in the database? If not, what's the main use case for it?

# Answer
> 2 votes
As far as I can tell from the code and from trying it out, archiving a mailing will mark a mailing as archived. And that's it. Someone please correct me if I'm wrong.

In terms of deleting a mailing, it'll remove the mailing report and other data, but it'll keep the activities associated with contacts, which might be useful for some reporting.

# Answer
> 1 votes
In my first Civi site (Joomla), I didn't archive my sent emails. Then, when I tried to send a new email, the old, sent email would be re-sent in the same cron job as well. CiviDesk told me the fix was to archive sent emails, and I have been doing it without fail ever since. The problem has not recurred.

---
Tags: civimail
---


# Question
Title: Error in Class CRM_Extension_Mapper 'Class CRM_Extension_Info not found' when installing 5.24 in Drupal 7
I have a problem enabling CiviCRM 5.24.2 after installation in Drupal 7.69 under PHP 7.2.27 under Centos7 Apache/2.4.6

CiviCRM installs correctly using both UI and CV method - ie builds database & civicrm.settings, but fails to enable (same error in both install methods).

`drush en -y civicrm` produces the following error (and breaks Drupal site):

```
WD php: Error: Class 'CRM_Extension_Info' not found in CRM_Extension_Mapper->keyToInfo()
Error: Class 'CRM_Extension_Info' not found in CRM_Extension_Mapper->keyToInfo() (line 170 of /srv/web/climatebonds.net/web/sites/all/modules/civicrm/CRM/Extension/Mapper.php).
Drush command terminated abnormally due to an unrecoverable error.

```

`drush dis -y civicrm` returns site to working order.

No problems with installation under local environment: Apache/2.4.35 (Unix) PHP/7.2.19

# Answer
This issue fixed .. simply due to missing file in deployment.
> 1 votes

---
Tags: drupal, installation
---


# Question
Title: Why is the CiviCRM Data Processor "Country" field displayed as a number in Word Press Content Views?
I successfully installed Content Views CiviCRM with CiviCRM Data Processor for pulling data via Content Views in WordPress. This integration that is explained here works fine. However, I'm encountering a problem that I don't know if it's a bug or a mishandling on my part: The country field is displayed as a number (e.g. I get 1193 instead of Slovenia). How can I fix this? Thank you for your support!

# Answer
The country name should display correctly if you set the field from 'Raw field value' to 'Option label'.
> 2 votes

---
Tags: wordpress
---


# Question
Title: Ajax pop-ups are not interactable and sized improperly
Before reporting a bug, I just wanted to double check if it's anything obvious or localized.

The mailing preview pop-up and the "add activity" pop-up from the contact page are two examples of broken functionality on our site. They just don't work. The frame does, you can move it around and close it, but actual interactions with the content don't. It's like it's locked or there was a div over it blocking event dispatch.

Eg. that internal scrollbar doesn't work. If I zoom out enough in firefox for the scrollbar to disappear, that doesn't help anything. Can't click or input. I also don't see any problematic elements in the DOM nor are there any javascript errors logged.

Am I the only one with such problems? Is the port of the theme incomplete? I couldn't find any related bug reports. CiviCRM 5.24 on Drupal 8.8.

# Answer
> 1 votes
What theme are you using in drupal 8. Some things look weird in bartik but I haven't noticed this problem. In the meantime, you can go to Administer - Customize - Display Preferences and turn off popups. It's a checkbox near the bottom underneath WYSIWYG editor: "Enable Popup Forms".

---
Tags: drupal-8, ui
---


# Question
Title: Create contact report with last added contacts
I'm trying to achieve something that sounds trivial. In order to take a look and quantify how many new newsletter subscriber we have, I want to create a contact report listing all contacts belonging to a group (corresponding to a form), filtered by "added\_date". Although this date information exists when I consult a contact, I can not find how to filter my contact report! Thanks for your help!

# Answer
> 1 votes
You could go to Search/Custom Searches and then run 'Search by date added to CiviCRM' and make a smart group from that list. Then use that group as a filter in the report.

---
Tags: contacts, reports
---


# Question
Title: How can I find a contributor to help my organization with an urgent/difficult upgrade?
We are a global NGO based in Costa Rica and I haven't been able to find an expert to help us upgrading our CiviCRM System. We are now using CiviCRM 4.7.23. and have a new site on Wordpress. We haven't updated it because we haven't found someone to safely transfer our old data to a newer version. We need help!

# Answer
> 2 votes
Javiera welcome to CiviCRM stackexchange, You can find experts on CiviCRM website or drop a message on CiviCRM mattermost channel.

# Answer
> 1 votes
Have you tried 'Find an Expert' on CiviCRM.Org?

---
Tags: upgrade
---


# Question
Title: Emails get stuck on send. How do I debug it?
I can send emails individually, but bulk emails never seem to work right. I've had no luck troubleshooting it, so I created a test group and added my own contact user to it and verified that it has my email set to home and primary. I created a new mailing to the group and sent it, and it says it started sending, but it hasn't sent anything, and the completed column is still blank. I clicked report on it, and it shows one recipient, but when I click on intended recipients, it says "There are currently no Intended Recipients". So I created another contact with a different email address, added it to the group, and sent another bulk mail. When creating the mail and selecting recipients, it shows the two expected contacts. Then I used "send test to group" to the same group, and both contacts received the test email immediately. So I submitted it, but it still didn't send to either user. The report shows 2 recipients, but when I click to see them, it says there are none. I checked a previously successful email, and it does list the recipients correctly. I turned on debugging, but I guess I don't know how to use it, because it didn't show anything. The scheduled job log shows the same generic success messages down the page, no errors or anything useful. Anybody know what's going on or how I can get some visibility into it? Is there a log file besides the cron log, or is there a way to get more messages into it?

# Answer
This is known issue reported by Andy. Quick fix would to remove the `$mailer->disconnect()` function at line 77 in src/Listener/DefaultSender.php file
> 0 votes

---
Tags: civimail
---


# Question
Title: two identically configured users see different list of contacts
I'm using smart groups with access controls so that when I add contacts to a particular group they get access to some contacts. I have two identically configured wordpress users with two identically configured contacts records, but one user sees the correct list of contacts, and the other user only sees himself. Any idea what I might have misconfigured?

# Answer
Before trying anything else, clear your caches. Find it under **Administer menu » System Settings » Cleanup Caches and Update Paths**.

After that, make sure you're setting both the user roles AND the ACLs correctly - but assuming you've done that, the best advice is at https://shouldiblamecaching.com.
> 2 votes

---
Tags: contacts
---


# Question
Title: Is anyone creating tickets or printing bar codes from CiviCRM?
We would like to be able to store and print machine-readable codes (bar codes or QR codes). We might print these on receipts for event registrations, to act as tickets, or on signin sheets or ID cards to track attendance at meetings, etc.

I saw a reference in an old question about this here on Stack Exchange, but nothing about it in the CiviCRM documentation. Is someone offering an extension perhaps that does this? Has anyone integrated CiviCRM with a code-generating Word-Press plugin? Do you think it's possible?

# Answer
Years ago we used 'QR Code Checkin' extension for one of our clients. Using the extension you can email the QR code to participant and same extension can also be used to confirm the registration.
> 2 votes

---
Tags: printing
---


# Question
Title: How do notifications for recurring payments work?
I've got a site that has had recurring payments (via paypal) working for some time, but only recently had the ipn settings configured correctly. Now that that is working we're getting more notifications than anticipated, so I just want to check my understanding on what's happening.

Memberships were set up to cc an info@ address in to the membership reciept. And also when the "member details" profile in the contribution page was submitted.

This now works as expected for new membership subscriptions.

The unexpected side is that the membership admins are getting the same notifications for the automated renewals. They are cc'd into membership renewal receipt, and it looks like the recurring payment resubmits the profile data so they're getting a notification from that as well.

I've not had much dealing with recurring payments before, I just want to check that this is the expected behaviour.

In which case. cc into reciept and notify from profile submission from the membership contribution page are the wrong way to notify membership admin about new members. I'm guessing a scheduled reminder for member join, limited to the admin email only would be a better way to go.

Also the contribution page that created the membership has been updated several times over the years so it now is no longer a membership page, and uses different profiles. So the notifications are linked to the original payment record and changing the contribution page makes not a blind bit of difference to associated recurring payments.

Removing the submission notification address on the profile will stop future notification emails (I guess). But I can't see a way to stop the cc notification from each membership renewal reciept, at least in the civi user interface- can this be amended from the sql tables?

So two questions 1) Is civi behaving as it should, and our expectations of the original set up was wrong? 2) How can I kill off future notifications for recurring payments

----editting after further work---- Scheduled reminders don't function as I thought, and can't add a cc to a scheduled reminder. So can't send a "welcome to us" message and cc the admin address in.

Looked at Civi Rules on the demo site- can set a trigger for membership added. But, "Send email to..." not available as an action.

# Answer
> 2 votes
There is an answer here that says among other things

You'll need to install these:

https://github.com/CiviCooP/org.civicoop.emailapi / https://civicrm.org/extensions/e-mail-api

---
Tags: recurring-contributions
---


title: Stripe Settings not there . .

text:
# Question
Title: Stripe Settings not there . .
CiviCRM 5.24.4/WordPress 5.4

Need to sort out Stripe webhook settings, but Administer\>CiviContribute\>Stripe Settings just takes me to the main admin page. Nothing there at all.

Anybody know why this might be happening? Or how I should adjust webhooks at Stripe, instead?

# Answer
> 1 votes
Just needed to refresh the cache.

---
Tags: wordpress, civicontribute, stripe
---


title: A couple of typo mistakes

text:
# Question
Title: A couple of typo mistakes
Sorry if this is not the right place for this signalization:

I found a mistake on civicrm\_state\_province table regarding Potugal and Coimbra state/province, typed as "Colmbra". The other group of typo mistake id that of all Poland state/provionce, typed in lower case. Best regards, Pino Vasarelli pino@vasarelli.it

# Answer
Pino, thanks for reporting this I have submitted a PR to fix above typo

For Potugal and Coimbra state/province

For Poland state/provionce, typed in lower case

Would you mind doing some test or adding some thoughts on appropriate PR to speedup the merge process?
> 1 votes

# Answer
Thanks for posting. For Poland, are you in Poland? It seems strange they would *all* be wrong so it might be correct that they are lower case - see e.g. https://en.wikipedia.org/wiki/Administrative\_divisions\_of\_Poland#Voivodeships. Or maybe the wikipedia article is a different context. I don't know.

Usually the best place to report this is at https://lab.civicrm.org/dev/core/-/issues
> 0 votes

---
Tags: i18n
---


# Question
Title: (Stripe) CRM_Core_Payment_Manual::setAPIParams() Fatal Error
CiviCRM 5.24.4/Wordpress 5.4

Getting hourly alerts from Cron, pertaining to some kind of API failure with Stripe. I suspect it has to do with the version-setting, but I'm unclear on how to address this.

In fact, (as originally posted here), I'm getting really strange behavior from Stripe payment processing; in addition to returning an error 100% of the time ("This PaymentIntent's amount could not be updated because it has a status of requires\_capture. You may only update the amount of a PaymentIntent with one of the following statuses: requires\_payment\_method, requires\_confirmation, requires\_action." -- there's another thread here about that already), the Stripe dashboard shows a consistent $200 addition to all payments (these are in steps of $25, $50, $100, & $500 . . . and whatever I choose has exactly $200 extra tacked on).

Seems a little nuts. Anyone have any idea?

# Answer
Gonna mark this as answered, for now, as I've logged it as an extension issue.

The core of the problem, just FYI, seems to be that the auto-webhook process is failing (it appears to work, and a webhook does indeed appear on the Stripe dashboard, but the errors persist and I'm *hoping* that the real weirdness (the additional $200) is at least related to this.
> 0 votes

---
Tags: civicontribute, stripe
---


# Question
Title: How could I restrict view access to message templates
Wordpress. I know I can restrict edit access but I would like to restrict "use". So I can allow some users to send an email but not select a template. (otherwise they can use for instance CEO signature)

# Answer
> 0 votes
I think its down to adding some code via php or js to restrict use of message template on some screen based on permission.

---
Tags: permissions, message-templates
---


# Question
Title: how do I turn off access control and allow all users to see all contacts?
When we started setting up civicrm many months ago we applied access controls so that users wouldn't see any contacts until we add them to their particular group. Over time we've come to the conclusion that there are too many situations where we need to see each other's contacts, so I've been requested to undo the access controls and leave it more wide open. What is the cleanest way to make this transition?

# Answer
There is a permission in the CMS (Drupal, Wordpress, Joomla) called "CiviCRM: View all contacts". Give this permission to any role/user that is supposed to see contacts in CiviCRM.
> 1 votes

---
Tags: contacts
---


# Question
Title: How to generate a monthly report for multiple years?
I would like to generate a report that shows monthly breakdowns for last 4-5 years. Is there a shortcut to do it? thanks

# Answer
You can try Report plus extension, it does provide some reports grouped by contribution date in month or year may be Contribution Summary Plus or Contribution Matrix Plus
> 2 votes

---
Tags: reports
---


# Question
Title: Where should bower_components reside under Drupal 8
I am running a couple of test sites under Drupal 8, installing them using Composer. Bower\_components seem to be living in two places.

/vendor/civicrm/civicrm-core/bower\_components/

and

/web/libraries/civicrm/bower\_components/

Which is the 'right' one, or is there an issue where files are duplicated?

# Answer
It's both. One of the main issues with civicrm+drupal8 is that drupal 8 (correctly) doesn't allow web access to files in the vendor folder, since that can be a security issue. Both roundearth and civicrm-asset-plugin try to deal with it, just a bit differently, by copying the files to a web-accessible folder, via composer events that are triggered when you run composer.

It doesn't copy all files, just the ones needed. It is possible for them to get out of sync if for whatever reason the composer event doesn't trigger, but you can re-sync by deleting the one under /web/libraries and running `composer install` again. You may need to delete one the /vendor bower\_components subfolders to get that to trigger it. I never know what composer is really looking at when it decides what to do.

UPDATE: If you're using the civicrm/civicrm-asset-plugin (as opposed to roundearth), running `composer civicrm:publish` from the top folder (above vendor) will re-sync the appropriate files from vendor to web. For roundearth, I haven't tested this but it might work to use `composer civicrm:sync-web-assets`, also from the top folder.
> 2 votes

---
Tags: drupal-8
---


title: Remove files after upgrade to 5.24.4?

text:
# Question
Title: Remove files after upgrade to 5.24.4?
After upgrading from version 5.24.1 to 5.24.4 (Joomla) i get the

> System Status: Critical File 'C:\wamp\www\Joomla\administrator\components\com\_civicrm\civicrm\bower\_components/google-code-prettify/styles/demo.html' presents a security risk and should be deleted. Same for: C:\wamp\www\Joomla\administrator\components\com\_civicrm\civicrm\bower\_components/qunit/addons

Can I remove these files?

# Answer
yes, its safe to remove the folder as it was part of security advisory.

see: CIVI-SA-2020-08: XSS VIA JS LIBRARIES
> 4 votes

---
Tags: upgrade
---


# Question
Title: How to show custom data on subsequent recurring contributions or how to setup anonymous donations?
Drupal 7.69 CiviCRM 5.20.0 iATSPayments 1.7.2

How do we get custom data to show with subsequent contributions in a recurring series or to have recurring anonymous donations? We use a custom data profile so donors can select that "they want this donation to be anonymous." If they check this box, we need this information to continue on with each recurring contribution. In the reporting, the staff can see the donations to projects they oversee. If the donor was anonymous, they do not see the donor info just the amount. Does anyone have any suggestions on how to accomplish this since subsequent recurring contributions no longer carry the custom data forward? It used to work using custom data. This is a mess for our office staff because they have to try to catch these anonymous donations and manually add the missing custom data before the other staff see the the names.

# Answer
where are the custom field stored right now? on the 1st "payment" of the recurring donation or on the recurring donation itself?

Anyway, we had similar problems with tracking the source of the donation (say if the recurring donation came from a specific campaign or mailig, we want the subsequent contributions being attributed to the initial campaign.

We had to write custom code for that, as it's not necessarily universal that whatever custom field you filled at the initial contribution ought to be duplicated to each new monthly donation.

I can see three paths:

* you modify (or pay/convince someone to do it) iats extension so it does copy the custom fields. as I mentioned, it might not be what every user of this extension wants, so might be trickier
* you write a custom extension that checks if a new contribution has the relevant custom fields, and if not checks if it needs to duplicate them from the recurring contribution. This might have the benefit of working as well if you use another payment provider than iats (and could help someone else that has similar needs)
* you modify the report(s) so they do not only anonymise the donor info if the "want the donation to be anonymous" is set on the specific donation, but as well if it's set on the recurring contribution itself
> 1 votes

---
Tags: custom-fields, iatspayments
---


# Question
Title: Error editing Default Organization Address in CiviCRM 5.2.2
we are running CiviCRM 5.2.2 on a Drupal 7 website

We need to edit the organization address information.

In CiviCRM, find ADMINISTER: COMMUNICATIONS: ORGANIZATION ADDRESS and CONTACT INFO https://dama.org/civicrm/admin/domain?action=update&reset=1

This opens an editor where you can change the DEFAULT ORGANIZATION ADDRESS.

But when I change the address and try to save it, I get the following error:

> Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred. DB Error: unknown error Error Details Return to home page.

Any idea how to fix this?

Thanks

Here is the backtrace for the error:

```
#0 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/Error.php(190): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/forge/dama.org/sites/all/modules/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
#3 /home/forge/dama.org/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...")
#4 /home/forge/dama.org/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...")
#5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, NULL, NULL, "\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...", "DB_Error", TRUE)
#6 /home/forge/dama.org/sites/all/modules/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))
#7 /home/forge/dama.org/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#8 /home/forge/dama.org/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...", "DB_Error", TRUE)
#9 /home/forge/dama.org/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, NULL, "1419 ** You do not have the SUPER privilege and binary logging is enabled (yo...")
#10 /home/forge/dama.org/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#11 /home/forge/dama.org/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...")
#12 /home/forge/dama.org/sites/all/modules/civicrm/packages/DB/DataObject.php(2443): DB_common->query("\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...")
#13 /home/forge/dama.org/sites/all/modules/civicrm/packages/DB/DataObject.php(1635): DB_DataObject->_query("\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...")
#14 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/DAO.php(415): DB_DataObject->query("\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...")
#15 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/DAO.php(1371): CRM_Core_DAO->query("\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...", FALSE)
#16 /home/forge/dama.org/sites/all/modules/civicrm/Civi/Core/SqlTriggers.php(225): CRM_Core_DAO::executeQuery("\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...", (Array:0), TRUE, NULL, FALSE, FALSE)
#17 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(3438): Civi\Core\SqlTriggers->enqueueQuery("\n    CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER ...")
#18 /home/forge/dama.org/sites/all/modules/civicrm/Civi/Core/SqlTriggers.php(62): CRM_Contact_BAO_Contact::triggerInfo((Array:5), NULL)
#19 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/DAO.php(2092): Civi\Core\SqlTriggers->rebuild(NULL, FALSE)
#20 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/DAO.php(2107): CRM_Core_DAO::triggerRebuild()
#21 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/BAO/Phone.php(74): CRM_Core_DAO::checkSqlFunctionsExist()
#22 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/BAO/Block.php(348): CRM_Core_BAO_Phone::add((Array:8))
#23 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/BAO/Location.php(66): CRM_Core_BAO_Block::create("phone", (Array:12), NULL)
#24 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Contact/Form/Domain.php(248): CRM_Core_BAO_Location::create((Array:12), TRUE)
#25 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/Form.php(453): CRM_Contact_Form_Domain->postProcess()
#26 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()
#27 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine->perform(Object(CRM_Contact_Form_Domain), "next", "Next")
#28 /home/forge/dama.org/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Contact_Form_Domain), "next")
#29 /home/forge/dama.org/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Domain), "next")
#30 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("next")
#31 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller->run()
#32 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/Invoke.php(283): CRM_Utils_Wrapper->run("CRM_Contact_Form_Domain", "Organization Address and Contact Info", NULL)
#33 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:17))
#34 /home/forge/dama.org/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#35 /home/forge/dama.org/sites/all/modules/civicrm/drupal/civicrm.module(445): CRM_Core_Invoke::invoke((Array:3))
#36 [internal function](): civicrm_invoke("admin", "domain")
#37 /home/forge/dama.org/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:2))
#38 /home/forge/dama.org/index.php(21): menu_execute_active_handler()
#39 {main}

```

Let me know if you have any insight into what's wrong and how to fix it.

Thanks

Mike

# Answer
In number 9 it says "You do not have the SUPER privilege and binary logging is enabled". Your mysql user doesn't have enough privileges.
> 2 votes

---
Tags: drupal
---


# Question
Title: What Civicrm Theme(s) are better than Garland?
I've been using Garland for a long time, but our staff complain that it's drab. Anyone have a suggestion for something brighter but also very clean (ie. good workflow, low rendering overhead)?

# Answer
> 2 votes
Personally I often like https://www.drupal.org/project/liquid\_coolness

Simple, clean theme, that is still pleasing to see. Has liquid width, so if you have a big screen you can make use of it for CiviCRM screens.

Not really maintained though.

# Answer
> 5 votes
Bright and clean: I use Seven (ships with Drupal 7 Core - so well supported) as CiviCRM Administration theme - for nearly all of our projects. Easy to subtheme as well - making it a MySeven - if/when required.

# Answer
> 5 votes
I've always used Adminimal. I think it makes the Drupal backend as a whole much easier to use, and it's always worked well for me with the Civi backend too.

# Answer
> 2 votes
There is also a CiviBartik theme that I use often: https://www.drupal.org/project/civi\_bartik

# Answer
> 2 votes
A couple of others not listed in other answers

https://github.com/vingle/st.visuali.finsburypark/

and

https://github.com/artfulrobot/aah

# Answer
> 0 votes
I use Bluemarine, which is an old classic: https://www.drupal.org/project/bluemarine

Not very exciting, but efficient use of screen space if you disable sidebars

# Answer
> 0 votes
I've been using a couple of themes based on the AdaptiveTheme https://www.drupal.org/project/adaptivetheme. I find both Pixture Reloaded and Corolla to be easy to use and adapt for large screens.

---
Tags: drupal, theme, usability
---


title: Importing using API CSV importer

text:
# Question
Title: Importing using API CSV importer
I am importing a large dataset using the API CSV importer extension.

The data needs to be divided into 3 chucnks - contact, address and email.

The second two imports I was planning to match up with the first import of contact data by downloading the contacts added inthe first import and adding the civicrm id to the second two imports (and doing the match offline)

However I have come to the third import (email address) and there is no ability to match by contact id.

My plan is to turn it round and import the email first then the contact data and then the address data so I think I have a solution. However, it seems odd to me that I can't upload the email and match against contact ID. The only option seems to be email (match by external identifier).

Am I missing something?

Thanks Caroline

# Answer
> 2 votes
It turns out that the field 'email contact' when importing is actually contact id. So there you go!

---
Tags: import
---


# Question
Title: How can I revert a set of bulk changes to the database?
I made a bulk update of addresses that turned out to be incorrect. I have advanced logging enabled. I can go into each contact's change log and revert the mistake one by one; how can I do this for all the addresses I altered?

# Answer
> 6 votes
I was able to create a script to revert in bulk. I needed to look up the `log_conn_id` value associated with the bulk change I made. I made the change using the API import, but any change that happens in a single connection can be reverted. Simply change the value of `$log_conn_id` in the script below, and change `$tablesToRevert` to include the table(s) that need reversion.

```
#!/usr/bin/php
<?php
eval(`cv php:boot`);

$log_conn_id = '5dcf086e9a837';
$log_date = NULL;
$tablesToRevert = ['civicrm_address'];

$reverter = new CRM_Logging_Reverter($log_conn_id, $log_date);
$differ = new CRM_Logging_Differ($log_conn_id, $log_date);
$diffs = $differ->diffsInTables($tablesToRevert);
$reverter->setDiffs($diffs);
$reverter->revert();

```

---
Tags: import, logging
---


# Question
Title: Error uploading CiviCRM contacts. Can I revert?
Does anyone know how I can revert a contact import (via the upload contacts script included in the site). The process included merges with existing contacts.

# Answer
> 4 votes
To revert an import done with the import functionality at **Contacts \> Import Contacts**, you will need to restore from your backed up database file. As far as I know, there is no automatic "undo" on imports.

To your point, it might be worthwhile to add a new warning to the import "Are you sure" verification message that pops up right before the import fires:

For example, "Are you sure you want to import now? Be sure you have backed up your database as there is no 'undo' for this." *(Update: I submitted a patch so if the core team agrees this will be added in future versions)*

# Answer
> 1 votes
Note that if you have advanced logging turned on, there's an option to revert - though changing an import would require you know the names of all the tables touched by the import. See the script on this answer for doing the reversion.

The tables you'd revert would always include `civicrm_contact`, but also potentially `civicrm_address`, `civicrm_email`, `civicrm_phone`, `civicrm_website`, `civicrm_im`, and `civicrm_relationship`. Fortunately there's no real harm in specifying tables that weren't touched.

---
Tags: contacts, import
---


# Question
Title: Including Premiums section breaks Contribution Page when using Stripe
The "Make Contribution" button fails to load confirmation page. We had recently added a captcha and a premium section to the page, so tried disabling them. We found that the premium section was the culprit, and disabling it allows the page to work as normal.

Running 5.19.4 on Drupal 7. No useful errors on Civi or Drupal logs. Is anyone else having this problem?

# Answer
April 21:

The issue appears to be related to Stripe. The submit button is generating a couple of ajax calls to the stripe url as:

https://q.stripe.com/?event=elements.event.focus&event\_count=12&timestamp=1587498851543& ...

https://q.stripe.com/?event=elements.event.blur&event\_count=13&timestamp=1587498851574& ....

After that first click, presumably the stripe extension is going to disable the submit button to avoid multiple clicks, and then it doesn't re-enable it, presumably because it doesn't get back the answer it expects, so no further action happens on clicking submit again.

Apr 28:

For anyone else trying to debug this - the first clue is to enable javascript debugging, i.e. at /civicrm/admin/setting/stripe:

Enable Stripe Javascript debugging?

That provides me with the clue that it's getting blocked at line 335 of civicrm\_stripe.js (of the unminified version), i.e. it's using a jquery validation plugin that thinks the form is not valid. If I remove that check, it goes ahead (and tells me my credit card is invalid).

So we've narrowed it down to some kind of conflict between the premiums section and the stripe extension's use of the jquery validation plugin.
> 2 votes

---
Tags: civicontribute, stripe, premiums
---


# Question
Title: Contact export runs very slowly, creates huge temporary DB files
I was alerted to this problem when a contact export was performed that maxed out the 40GB of spare disk space and crashed the entire server.

I found that exporting even a small number of contacts from a smart group is extremely slow and causes MariaDB to use a huge amount of disk space for temporary files.

Specifically, my testing showed that exporting 70 contacts using the default output fields takes 9.4 minutes and creates an 11 GB temporary file.

CiviCRM operations other than exporting are running at reasonable speed.

The single query of interest (shown below) was run with various numbers of exported contacts to obtain the following measurements:

```
Contacts   Time (S)         Rows       Temp File
Exported                 Examined        (GB)
    1       0.24             ?             ?
    5        4.0             ?             ?
   10        4.4             ?             ?
   15         40          8,753,893       1.0
   20         92         18,432,164       2.4
   25        103         21,884,677       2.7
   30        136         25,507,988       3.1
   40        325         44,676,670       5.0
   50        342         52,419,343       5.8
   60        593         92,125,429      11.0*
   70        565         95,407,449      11.0*

```

* Yes, I confirmed the last two cases. It’s not clear why there was little difference between 60 and 70 contacts, but each added contact has its own unique effect based on its number of groups, tags, notes, etc.

## Questions:

* Does this seem normal for a database of this size (see row counts below)?
* Might it be due to a poorly optimized query?
* Does it suggest a MySQL configuration issue?
* Any suggestions?

## Slow query log results for the case of 70 contacts:

```
Query_time: 845.694403  Lock_time: 0.341576  Rows_sent: 70  Rows_examined: 95407449
Full_scan: No  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: Yes
Filesort: Yes  Filesort_on_disk: No  Merge_passes: 7  Priority_queue: No

```

## Methodology:

* Performed CiviCRM export with default output fields of all 70 contacts in a smart group.
* MySQL slow query log was used to find the offending query.
* That query was then executed repeatedly in MySQL workbench with various numbers of requested contact\_id’s.
* MySQL slow query log was used to determine “Rows Examined”
* Temp file size was monitored with `watch ls -alh /tmp/*.MA?`
* Times shown are for the query only (excluding fetch times of up to 340 seconds) as reported by MySQL Workbench

## Server:

* 2 core VPS, 8GB RAM, SSD
* MariaDB 10.0
* Ubuntu 16.04
* Drupal 7
* CiviCRM: 5.23.4

## Row counts in critical CiviCRM tables:

* civicrm\_contact: 24,206
* civicrm\_group\_contact\_cache: 129,211
* civicrm\_group: 367
* civicrm\_group\_contact: 93,572
* civicrm\_tag: 33
* civicrm\_entity\_tag: 12,761
* civicrm\_note: 5,349

## Critical MariaDB/MySQL parameters:

* sort\_buffer\_size = 8M
* join\_buffer\_size = 8M
* innodb\_buffer\_pool\_size = 4096M
* key\_buffer\_size = 128M
* table\_open\_cache = 512

## Query:

```
SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, contact_a.display_name as `display_name`, contact_a.do_not_email as `do_not_email`, contact_a.do_not_phone as `do_not_phone`, contact_a.do_not_mail as `do_not_mail`, contact_a.do_not_sms as `do_not_sms`, contact_a.do_not_trade as `do_not_trade`, contact_a.is_opt_out as `is_opt_out`, contact_a.legal_identifier as `legal_identifier`, contact_a.external_identifier as `external_identifier`, contact_a.nick_name as `nick_name`, contact_a.legal_name as `legal_name`, contact_a.image_URL as `image_URL`, contact_a.preferred_communication_method as `preferred_communication_method`, contact_a.preferred_language as `preferred_language`, contact_a.preferred_mail_format as `preferred_mail_format`, contact_a.hash as `hash`, contact_a.source as `contact_source`, contact_a.first_name as `first_name`, contact_a.middle_name as `middle_name`, contact_a.last_name as `last_name`, contact_a.prefix_id as `prefix_id`, contact_a.suffix_id as `suffix_id`, contact_a.formal_title as `formal_title`, contact_a.communication_style_id as `communication_style_id`, contact_a.email_greeting_id as email_greeting_id, contact_a.postal_greeting_id as postal_greeting_id, contact_a.addressee_id as addressee_id, contact_a.job_title as `job_title`, contact_a.gender_id as `gender_id`, contact_a.birth_date as `birth_date`, contact_a.is_deceased as `is_deceased`, contact_a.deceased_date as `deceased_date`, contact_a.household_name as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, contact_a.sic_code as `sic_code`, contact_a.user_unique_id as `user_unique_id`, contact_a.employer_id as `current_employer_id`, contact_a.is_deleted as `contact_is_deleted`, contact_a.created_date as `created_date`, contact_a.modified_date as `modified_date`, contact_a.addressee_display as addressee_display, contact_a.addressee_custom as addressee_custom, contact_a.email_greeting_display as email_greeting_display, contact_a.email_greeting_custom as email_greeting_custom, contact_a.postal_greeting_display as postal_greeting_display, contact_a.postal_greeting_custom as postal_greeting_custom, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_location_type.id as location_type_id, civicrm_location_type.name as `location_type`, civicrm_address.street_address as `street_address`, civicrm_address.street_number as `street_number`, civicrm_address.street_number_suffix as `street_number_suffix`, civicrm_address.street_name as `street_name`, civicrm_address.street_unit as `street_unit`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.supplemental_address_3 as `supplemental_address_3`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_address.manual_geo_code as `manual_geo_code`, civicrm_address.name as `address_name`, civicrm_address.master_id as `master_id`, civicrm_address.county_id as county_id, civicrm_address.state_province_id as state_province_id, civicrm_address.country_id as country_id, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as `phone_type_id`, civicrm_phone.phone as `phone`, civicrm_phone.phone_ext as `phone_ext`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_email.is_bulkmail as `is_bulkmail`, civicrm_email.signature_text as `signature_text`, civicrm_email.signature_html as `signature_html`, civicrm_im.id as im_id, civicrm_im.provider_id as `im_provider`, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_openid.id as openid_id, civicrm_openid.openid as `openid`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`, civicrm_website.id as website_id, civicrm_website.url as `url`,
  CONCAT_WS(',',
    GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
    GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
  )
as `groups`, GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags, GROUP_CONCAT(DISTINCT(civicrm_note.note)) as notes  FROM civicrm_contact contact_a   LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id  )  LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id )  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id )  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id )  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id )  LEFT JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id  )  LEFT JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id  LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id  LEFT JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id  LEFT JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = contact_a.id )  LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id )  LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id   LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_5e8691eef0e92 ON contact_a.id = civicrm_group_contact_cache_5e8691eef0e92.contact_id   LEFT  JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id  LEFT JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id  WHERE  (  (  ( civicrm_group_contact_cache_5e8691eef0e92.group_id IN ("381") )  )  )  AND (contact_a.is_deleted = 0) AND contact_a.is_deleted != 1 AND  contact_a.id IN ( 6442,22905,990,17543,6317,614,21675,22232,543,5161,5532,19913,593,1016,25226,12381,3,2,4596,19253,574,8641,14216,757,8263,5219,22235,16554,14028,2276,17450,21002,555,21312,8076,5191,10562,12706,11653,760,8113,315,2293,1369,765,19581,1303,4893,21344,1907,1814,771,21299,25138,1819,22464,22818,8553,795,10568,925,1296,5557,17018,911,17932,750,21552,10364,11819 )    GROUP BY contact_a.id LIMIT 0, 100000;

```

## Explain:

```
id  select_type  table                                      type     possible_keys                                             key                               key_len   ref                                                              rows  Extra
1   SIMPLE       civicrm_group_contact_cache_5e8691eef0e92  range    UI_contact_group,FK_civicrm_group_contact_cache_group_id  UI_contact_group                  8         NULL                                                             70    Using where; Using index; Using temporary; Using filesort
1   SIMPLE       contact_a                                  eq_ref   PRIMARY,index_is_deleted_sort_name                        PRIMARY                           4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     Using where
1   SIMPLE       civicrm_address                            ref      FK_civicrm_address_contact_id                             FK_civicrm_address_contact_id     5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1
1   SIMPLE       civicrm_country                            eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_address.country_id                                   1     Using where
1   SIMPLE       civicrm_email                              ref      FK_civicrm_email_contact_id                               FK_civicrm_email_contact_id       5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1
1   SIMPLE       civicrm_phone                              ref      FK_civicrm_phone_contact_id                               FK_civicrm_phone_contact_id       5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1
1   SIMPLE       civicrm_im                                 ref      FK_civicrm_im_contact_id                                  FK_civicrm_im_contact_id          5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1
1   SIMPLE       civicrm_openid                             ALL      FK_civicrm_openid_contact_id                              NULL                              NULL      NULL                                                             1     Using where
1   SIMPLE       civicrm_location_type                      eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_address.location_type_id                             1     Using where
1   SIMPLE       civicrm_group_contact                      ref      UI_contact_group                                          UI_contact_group                  4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         2
1   SIMPLE       civicrm_group_contact_cache                ref      UI_contact_group                                          UI_contact_group                  4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         2     Using index
1   SIMPLE       civicrm_entity_tag                         ref      UI_entity_id_entity_table_tag_id                          UI_entity_id_entity_table_tag_id  199       sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id,const   1     Using where; Using index
1   SIMPLE       civicrm_note                               ref      index_entity                                              index_entity                      198       const,sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id   1     Using where
1   SIMPLE       civicrm_worldregion                        eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_country.region_id                                    1     Using where
1   SIMPLE       civicrm_tag                                eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_entity_tag.tag_id                                    1     Using where
1   SIMPLE       civicrm_website                            ref      FK_civicrm_website_contact_id                             FK_civicrm_website_contact_id     5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1

```

---

**EDIT 4/9/20** The following is based on the query that occurs during a regular (non-smart) group export of 2 contacts. It's very similar to the above smart-group query. The table below shows the results of that single query for contact #1 alone, contact #2 alone, and both contacts together, as each table is added to the SELECT and JOIN sections.

```
Exported Contact(s)                             | ---------------#1---------------| -------------#2--------------- | ------------------#1 & #2----------------- |
                                                | Count  Rows Examined   Time (S) | Count  Rows Examined  Time (S) | Rows Examined Sum  Rows Examined  Time (S) |
Full query (from Export from reg. group)        |        13,170,619      44       |        5,258,678      16       | 18,429,297         32,064,083     349      |
SELECT contact_id only (no joins)               |        1               0.1      |        1              0.1      | 2                  2              0.188    |
  + group_contact (regular groups)              | 81     89              0.1      | 36     38             0.1      | 127                129            0.1      |
    + group_tag (smart groups)                  | 32     2,937           0.18     | 21     836            0.1      | 3,773              3,775          0.1      |
      + tag, entity_tag                         | 18     105,465         0.29     | 6      10,412         0.14     | 115,877            115,879        0.39     |
        + notes                                 | 6      669,369         4.1      | 11     110,960        0.48     | 780,329            780,331        3        |
          + email                               | 4      2,677,480       11.5     | 3      332,883        1.2      | 3,010,363          3,010,365      10.8     |
            + phone                             | 2      5,354,964       19.5     | 4      1,331,535      4.6      | 6,686,499          6,686,501      23.8     |
              + address, country, location_type | 2      10,709,930      40.8     | 3      3,994,608      13.5     | 14,704,538         14,704,540     51.5     |
                + open_id                       | 0      10,709,946      38       | 0      3,994,644      13.2     | 14,704,590         28,339,376     136      |
                + im                            | 0      10,709,946      38       | 0      3,994,644      13.5     | 14,704,590         28,339,376     136      |
                + im, open_id                   | 0      10,709,946      37.8     | 0      3,994,644      13.5     | 14,704,590         28,339,376     103      |
                   + world_region               | 1      13,170,619      39.7     | 1      5,258,678      13.8     | 18,429,297         32,064,083     145.5    |

```

For example:

* The "+ email" row indicates the query results including (in Select and Joins) email, notes, and all other tables above it.
* The "+ im" row indicates the query results including address, phone, etc., but excludes the open\_id table as that row has the same indentation.

Points of interest:

* The column "Rows Examined Sum" is simply the sum of Rows Examined for Contacts 1 and 2. This is generally very close to the actual Rows Examined for the query run with both contacts, but in some cases the latter is much greater. Specifically, when the open\_id or im tables are introduced, there is little effect on the Rows Examined for either contact #1 or contact #2, but for the case of #1 AND #2, there is a large difference! Oddly, neither contact has a row in either of those two tables, and the total row counts of these two tables is only 0 and 6, respectively.
* The last row, "+ world\_region" adds a considerable amount to Rows Examined, although each contact has only one unique country in all of their address records (and therefore only one world region).
* The increases in execution time are disproportionally greater than the increases in Rows Examined. .e.g for "+ world\_region", the Records Examined for the case of #1 AND #2 increased by 13%, while the time to execute increased by 41%! Perhaps this is due to greater reliance on temporary tables on disk.

# Answer
MySQL is forced to generate temporary tables when the query contains GROUP\_CONCAT (among other reasons), which this query does in order to capture all values of groups, tags and notes into single fields. The huge temporary tables are the predictable result of several exported fields each having many rows per exported contact, where the those numbers multiply rather than add to achieve the high row count in the temporary table.

Restructuring the query to use subqueries in the critical joins resulted in a speed improvement of 170X!

Additional information here.

Thanks all for your help!
> 2 votes

# Answer
Ok, I'm all out of ideas on this I'm afraid. I would probably try a few different export formats to see if its a particular one thats doing it. Check for any extensions - turn them off etc etc. Haven't seen this on a standard install.
> 0 votes

# Answer
1. That's a lot of rows examined, a red flag. Perhaps you could try it on a non-smart group instead?
2. I'll guess you're doing the default export. Especially with some older CiviCRM versions, that generated much larger exports that you'd expect or want. Try specifying some specific fields instead and see if that runs much faster.
3. Do tell what version of CiviCRM you're using.
> 0 votes

---
Tags: export, performance
---


title: Finding another case manager's cases

text:
# Question
Title: Finding another case manager's cases
Is there a way of using advanced search to find another case manager's cases? I find the reports really complicated but would like to find another person's cases so I can update the notes. It's really easy to find mine as I just search my cases only. Is there an easy way to search for someone else's cases too? Thanks

# Answer
> 2 votes
1. Go to search - advanced search.
2. In the dropdown where it says "display results as" choose "Cases".
3. Expand the relationships section.
4. For relationship type choose the case manager type (e.g. "homeless services coordinator is").
5. For target contact put the name or partial name of the manager.
6. Click search.

Another way is:

1. Go to the manager's contact record.
2. On their relationships tab look for the manager type (e.g. "homeless services coordinator is").
3. On the far right under the "More" link it will say "Manage case #7", and you can click that to go to that case.

---
Tags: civicase, advanced-search
---


# Question
Title: 'Household member' relationship type can't be (re?)activated through the interface?
Somehow I seem to have deactivated the type of relationship 'Household member' (is a member of what household) through the interface while configuring a fairly new CiviCRM. As a result, I don't seem to be able to associate individuals to households. Nothing has been changed in code, so I must have just clicked on something. On the relationship types page, this relationship (is a member of) is of course still there (it can't be deleted through the interface, afterall), but doesn't allow itself to be actived (though other options like 'child of' or 'spouse of' can be activated and deactivated). I'm the only person who's been configuring the site, so I don't see how it can be a permissions issue (it must be a problem that I created).

Any ideas what I need to do to re-activate this basic relationship type?

Thanks very much!

Steve Baird, Citizen's Action Committee of Verdun

# Answer
Do you have any extensions that might have disabled it? Otherwise I'm not sure how that would happen either but if you want to live on the edge you can use phpmyadmin or equivalent to do:

`UPDATE civicrm_relationship_type SET is_active=1 WHERE name_a_b = 'Household Member of';`

You can also do it with api explorer (/civicrm/api3) and choose:

Entity: RelationshipType

Action: Create (yes, create - it means update)

Remove the suggested parameters and add a parameter `Relationship Type ID = 8`. You can confirm the id by hovering over the View link in the Relationship Types listing screen and looking at the url at the bottom of the browser, but it's always 8.

Then add a parameter `Relationship type is Active = Yes`

Click Execute.
> 1 votes

---
Tags: relationships, households
---


# Question
Title: How do I use a hidden column in a custom search template?
I have created a custom search based on relationships. Each row shows a contact and a related organisation in separate columns as well as other info. Each contact is automatically linked to the view page in the search results but I would like the organisation to also be linked. I return a column aliased to "org\_id" in the `all` function select piece.

```
, tempTable.org_id
, tempTable.organization_name

```

I have not added `org_id` to my columns array in the constructor because I do not want it to display. If I do then the template file works but I have an extra column.

I have sucessfully overridden the template file as follows:

```
{if $fName eq 'sort_name'}
   <td><a href="{crmURL p='civicrm/contact/view' q="reset=1&cid=`$row.contact_id`&key=`$qfKey`&context=custom"}">{$row.sort_name}</a></td>
{elseif $fName eq 'organization_name'}
   <td><a href="{crmURL p='civicrm/contact/view' q="reset=1&cid=`$row.org_id`&key=`$qfKey`&context=custom"}">{$row.organization_name}</a></td>
{else}
   <td>{$row.$fName}</td>
{/if}

```

It does display a link on the organisation name cell. However the template is not seeing the `org_id` value and the link stops at `cid=` EG. `civicrm/contact/view?reset=1&cid=&key=fb7ffdb351588d7d7d9df84f3b3e03f5_1119&context=custom`

Edit: I have also tried this without the template override and used alterRow. This also only works if the `org_id` result column is added to the columns list of the custom search.

```
  function alterRow( &$row ) {
    $url = CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid={$row['org_id']}");
    $row['organization_name'] = "<a href=\"{$url}\">{$row['organization_name']}</a>";
  }

```

# Answer
OK. I figured it out eventually. This is how I got it to work. It seems a little wonky but it functions

1. Add the column to the list of columns
2. Override the template
3. Alter the rows to add the link
4. AND unset the column in Alter rows

**The constructor columns must contain**

```
  ts('Org id') => 'org_id',

```

**The override**

```
  public function templateFile() {
    return "CRM/Contact/Form/Search/Custom/mySearch.tpl";
  }

```

**The template**

Add snippets in each of the column headers section and the rows section skipping an item in the loop. Eg. `{if $header.sort neq 'org_id'}`

For the headers:

```
 {foreach from=$columnHeaders item=header}
    {if $header.sort neq 'org_id'}
      <th scope="col">
      {if $header.sort}
        {assign var='key' value=$header.sort}
        {$sort->_response.$key.link}
      {else}
          {$header.name}
      {/if}
     </th>
   {/if}
{/foreach}

```

For the rows:

```
{foreach from=$columnHeaders item=header}
   {assign var=fName value=$header.sort}
   {if $header.sort neq 'org_id'}
     {if $fName eq 'sort_name'}
       <td><a href="{crmURL p='civicrm/contact/view' q="reset=1&cid=`$row.contact_id`&key=`$qfKey`&context=custom"}">{$row.sort_name}</a></td>
      {else}
        <td>{$row.$fName}</td>
      {/if}
   {/if}
{/foreach}

```

**The alter rows**

Note the added unset line.

```
function alterRow( &$row ) {
  $url = CRM_Utils_System::url('civicrm/contact/view', "reset=1&cid={$row['org_id']}");
  $row['organization_name'] = "<a href=\"{$url}\">{$row['organization_name']}</a>";
  unset($row['org_id']);
}

```
> 2 votes

---
Tags: templates, custom-search
---


# Question
Title: Can you change Pending (Incomplete Transaction) to Pending (Pay Later)?
Is it possible to change pending transactions from incomplete to pay later? Without editing db?

# Answer
> 2 votes
Using api explorer you can do it or exporting the record and re importing it with status as pending and pay later as 1 or yes.

---
Tags: civicontribute, contributions, pending
---