# Question
Title: Performance Bug in Trigger Causing ~1000x Slowdown
While looking for the root cause of a performance issue for a client, I tracked it down to a couple of triggers performing an inefficient UPDATE statement:

civicrm\_activity\_before\_update civicrm\_activity\_before\_delete

Original SQL (~4 seconds):

```
UPDATE civicrm_case SET modified_date = CURRENT_TIMESTAMP WHERE id IN (SELECT ca.case_id FROM civicrm_case_activity ca WHERE ca.activity_id = OLD.id);

```

~1000x faster SQL to do the same thing:

```
UPDATE civicrm_case, civicrm_case_activity SET civicrm_case.modified_date = CURRENT_TIMESTAMP WHERE civicrm_case.id = civicrm_case_activity.case_id AND civicrm_case_activity.activity_id = OLD.id;

```

I tracked this down to the following PHP file: sites/all/modules/civicrm/Civi/Core/Container.php

The patch to fix the SQL payload in the file:

```
--- Container.php.orig  2021-04-12 11:30:35.039270421 +0100
+++ Container.php   2021-04-12 11:32:00.485650901 +0100
@@ -286,7 +286,7 @@
             'table' => 'civicrm_activity',
             'when' => 'BEFORE',
             'event' => ['UPDATE', 'DELETE'],
-            'sql' => "\nUPDATE civicrm_case SET modified_date = CURRENT_TIMESTAMP WHERE id IN (SELECT ca.case_id FROM civicrm_case_activity ca WHERE ca.activity_id = OLD.id);\n",
+            'sql' => "\nUPDATE civicrm_case, civicrm_case_activity SET civicrm_case.modified_date = CURRENT_TIMESTAMP WHERE civicrm_case.id = civicrm_case_activity.case_id AND civicrm_case_activity.activity_id = OLD.id;\n",
           ],
         ],
       ]

```

The bug doesn't manifest if you rewrite the UPDATE as a SELECT, but in the UPDATE form it takes an absurdly long time when a sub-select is used. Tested on MariaDB 10.4 and 10.5.

Any thoughts on this? Functionally the two queries are identical. What does it take to get this merged upstream?

# Answer
Nice find. Thanks for posting. Yes the inner join is generally better than a subquery.

To answer your secondary question, to get it merged you need to create a ticket at https://lab.civicrm.org/dev/core/-/issues/new and then if you're able to also make an associated pull request at https://github.com/civicrm/civicrm-core against master. See also https://docs.civicrm.org/dev/en/latest/tools/git/#contributing. Then someone needs to confirm and test it.

When you do post your ticket, it would help to also describe the parameters (e.g. # of activities in the database, any other relevant variables...) so that others can reproduce/evaluate.
> 4 votes

---
Tags: issues, core, performance, bug-reporting
---


# Question
Title: How to request OK to communicate by email (only)? Without coding
In the 'Manage Contribution Payment' screen we want to simply ask the donor if they are happy to be contacted by email - a simple Yes/No option in a profile. It doesn't seem possible to do this using the standard fields - 'Communication Preferences' brings up all the communications options. Is there any way to just expose the email field as a preference? I can do this with a custom field, but it would be better to set the actual email preference field. I've a feeling this must be possible, and have a strong preference to avoid any coding!

# Answer
Does the optinfields extension do what you want?
> 1 votes

---
Tags: civicontribute
---


title: Cannot install on live Joomla site

text:
# Question
Title: Cannot install on live Joomla site
Have attempted many times to install 5.35.1 or later 5.36.0 on live Joomla site. Find little info anywhere. Must not be many Joomla installations. Followed instructions explicitly and kept getting php error on file not found which was civicrm.settings.php. Not writing that file to the folders. Removed all files and extracted locally, then FTPed into tmp folder. Install failed, this time the above mentioned file was written, however got a unknown database error failing to create table:

```
Cannot execute CREATE TABLE `civicrm_extension` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Local Extension ID', `type` varchar(8) NOT NULL , `full_name` varchar(255) NOT NULL COMMENT 'Fully qualified extension name', `name` varchar(255) COMMENT 'Short name', `label` varchar(255) COMMENT 'Short, printable name', `file` varchar(255) COMMENT 'Primary PHP file', `schema_version` varchar(63) COMMENT 'Revision code of the database schema; the format is module-defined', `is_active` tinyint DEFAULT 1 COMMENT 'Is this extension active?' , PRIMARY KEY (`id`) , UNIQUE INDEX `UI_extension_full_name`( full_name ) , INDEX `UI_extension_name`( name ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC: DB Error: unknown error

```

Just about given up this. Anyone suggest what to do?

Well, I loaded WordPress in a subdirectory and then tried to install Civicrm per the instructions. Everything looked like it was working until it gave a fatal error and stopped with failure to create tables AGAIN. What I get is: An error of type E\_ERROR was caused in line 204 of the file /home1/flscv/public\_html/wp/wp-content/plugins/civicrm/civicrm/setup/src/Setup/DbUtil.php. ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4\_unicode\_ci ROW\_FORMAT=DYNAMIC: Specified key was too long; max key length is 767 bytes in /home1/flscv/public\_html/wp/wp-content/plugins/civicrm/civicrm I use HostGator with Mysql 5.6 for this site and think now it is a host issue (what else is new?) I can create a new db and actually created the subject table with no issue. The Joomla db was created several years ago and has not presented any problems. The wp db was just created, yet both dbs have the same problem. My Wamp server is running 5.7. I am going to contact hostgator support and hopefully learn if I can get this resolved. May end up going to another host like I have done with other sites due to HostGator issues.

Considered the input in the answer and comments, I have reached the conclusion that at least part of the problem does indeed lie with the MySql configuration settings in HostGator. The version is 5.6 and one of the Innodb settings is set to off and should be on. Talked with HostGator support and they will not make any change to the configuration nor are they considering upgrading the shared server MySql version to 5.7. Bottom line, I cannot use CiviCRM on this server. I use another host and checked that one as well and determined that the MySQL version has the same configuration as HostGator. I have reached out to them to see if they will either make the change and upgrade the version. I am willing to move to another host if I can find one that can meet the CivCRM requirements while keeping costs reasonable. This is for a small non profit organization that has limited funding so working under other constraints as well.

# Answer
> 0 votes
Welcome to StackExchange! There are fewer installations of CiviCRM on Joomla than Drupal or WP, but it does work, and for most of what it does, Civi doesn't care which CMS it sits on.

You may be hitting problems described here with HostGator. In general, I'd only try to run CiviCRM on a hosting provider that explicitly supports CiviCRM.

The end of the error message simply says "DB Error: unknown error" - which is not much use to you or us! Do your log files provide any more information about the error?

If the logs don't provide further info you could try connecting to the db directly (either command line or phpmyadmin) and copy/paste in the create table statement and see what error it gives.

---
Tags: installation
---


# Question
Title: How to automatically create/import some 4000 groups in bulk from a csv file
We are considering data migration to CiviCRM. We have two CSV files, one has the details of thousands of "individuals" and another has a list of over 3000 needed "groups". Importing process for "individuals" appear to be well documented, but how do we import/create "groups" (including group properties) from a CSV file beforehand.

# Answer
> 1 votes
Welcome to SE!

You could import the groups with the API CSV importer extension

3000 groups is a lot: there may be better ways to represent your data in CiviCRM. How do you want to use these groups?

---
Tags: import, groups
---


# Question
Title: Where are the 'Contributions - Receipt (on-line) templates stored?
I want to customise on line receipts, but am struggling to make a start. I can see the on line receipt in the Message Templates - 'Contributions - Receipt (on-line)' but can't find where the other files associated with this template are located. The documentation doesn't seem to explain this at all.
I want to make the receipt look very different from the default. Also if any one has some customised receipt templates they can share, that would be great too. Thanks!

# Answer
Do you mean the code that uses the template? If so, look at CRM/Contribute/BAO/ContributionPage.php and look for 'contribution\_online\_receipt' which is the template name for 'Contributions - Receipt (on-line)'.

Unless you want to pass different data to the template, you should just need to edit the template at `Administer > Communications > Message Templates > System Workflow Messages`

If you change the templates you might find this extension useful when you need to reconcile future core changes with your changes: https://lab.civicrm.org/extensions/msgtplhelper

And if you're making your templates look better, try this: https://lab.civicrm.org/extensions/prettyworkflowmessages
> 2 votes

---
Tags: civicontribute, receipts
---


# Question
Title: Do I have to empty the database of a fresh civicrm install, before I import a dump from another server? (and a question about mysql 5.7 dump ->8.0)
Salut. I am following this wonderful guide https://docs.civicrm.org/sysadmin/en/latest/misc/switch-servers/ in order to migrate civicrm from one server to another (wordpress, external civicrm database).

It is unlclear to me, however, if I need to completely clear the database of the new civicrm install on the new server, before importing the dump file there. I do get quite some errors when I import into an already existing structure (f.e. table already exists, trigger already exists, foreign key....).

My case is double-complex, it seems, since the original dump is mysql 5.7 and the new database is mysql 8.0. I know mysql version should be the same, but it is impossible in this case.

Has anybody tried this yet? If the mysql version does not match, is it a lost cause?

Thanks for any hints. Michael

# Answer
> 1 votes
Are you using phpmyadmin to export the old one? You can check the box for `ADD DROP TABLE` which should solve this. There should also be an option to disable key checks but the drop table might add that automatically.

Using mysqldump on the command line it does this by default, and also by default it disables key checks.

---
Tags: wordpress, upgrade, migration
---


# Question
Title: Using a profile with custom fields for event online registration
new to CiviCRM. Have set up a profile that I would like to use for online event registration. It looks very ugly and I was wondering if there was a way to make it look better? I'm using a mixture of text boxes (they look ok) and some questions have radio buttons and check boxes. It's the radio button and check box questions that look very cluttered. Any ideas? Thanks.

# Answer
> 1 votes
Try a theme for CiviCRM, like Shoreditch.

---
Tags: civicrm-event
---


title: civicrm email scheduling

text:
# Question
Title: civicrm email scheduling
I scheduled an email to send at 9.40am and it actually sent at 9.49am which I assume is to do with how often the send mail cron job runs.

Is there a way of actually getting an email to send at the time you specify?

EDIT my cron job for the above said that it triggered at 11.48am. Why is that? Is there a setting in civi which I need to change?

Thanks!

# Answer
If this is a one off thing, you can run the Send Scheduled Mailings as a one off directly from the user interface from Administer \>\> System Settings \>\> Scheduled Jobs. Select More and then Execute Now against Send Scheduled Mailings.

I don't think that tinkering with the cron jobs to do this is a sensible way to do this, though it would work. Some environments (externally hosted) will not want cron jobs to run too frequently as it wastes resources. Personally, I would try and set the time for emails to fit the cron schedule.

I agree with Lars SG about the 11:48 entry. If you look back at the Log for the Send Scheduled Mailings job you will also see 09:48 (and I expect 10:48 at least depending on the frequency.
> 1 votes

# Answer
How close to the exact time does it need to be? You can change the frequency of the cron job within reason, perhaps temporarily if you need a specific mailing to go out at a very specific time. You need to do that on your server, as you can't change this within CiviCRM. Your send scheduled mailings scheduled job is set to run Always, right?

11:48am sounds like it was the last time it was triggered.
> 0 votes

---
Tags: civimail, cron, cron-job
---


title: Unable to do a search using tag

text:
# Question
Title: Unable to do a search using tag
When doing a contact search using tags from search builder Civi throws DB syntax error, this was working before until Civi upgraded to 5.35.1

Error:

```
SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name\n
               FROM civicrm_contact contact_a    LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-60788273efd52` ON ( `civicrm_entity_tag-60788273efd52`.entity_id = contact_a.id  AND `civicrm_entity_tag-60788273efd52`.entity_table = 'civicrm_contact')  \n
              WHERE  (  ( `civicrm_entity_tag-60788273efd52`.tag_id IN (  ) )  )  AND ( 1 ) AND (contact_a.is_deleted = 0) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') )  )  AND ( 1 ) AND (contact_a.is_deleted = 0)' at line 3]
        """

```

# Answer
Can you check if the tag you selected is a parent tag and has child tags? If yes than this is a known bug on 5.35.1 when doing a search on parent tag and is logged at https://lab.civicrm.org/dev/core/-/issues/2502 and is fixed as part of 5.38 release.

Solution(one of the below):

1. Use 'IN' operator instead of '='.
2. Apply patch from here.
3. Wait for 5.38.0 release
> 1 votes

---
Tags: tags, search-builder
---


# Question
Title: HTML_QuickForm_Controller: Unhandled action "_qf_Main_reload" in page "Main"
Contribution page with membership signup enabled get `HTML_QuickForm_Controller: Unhandled action "_qf_Main_reload" in page "Main"` error once the form is submitted from main page

CiviCRM : 5.35.1 CMS: Drupal

# Answer
Could you please confirm if you have CiviDiscount extension enabled and on latest 3.8.2 version?

Similar issue was recorded for CiviDiscount on gitlab.

HTH

Pradeep
> 2 votes

---
Tags: contribution-page
---


# Question
Title: username and password invalid on cronjob setup through anacron on server control panel
I am trying to run all cron jobs at a one hour interval, but I keep getting "Could not open input file". my path: @hourly 0 my\_php\_script php -f /srv/data/web/vhosts/\[mysite\]/wp-content/htdocs/plugins/civicrm/civicrm/bin/cron.php. I am using the admin credentials for the database, which are the same for wordpress, and as administrator in Civi. Are there other credentials I need to use?

# Answer
Welcome to SE! The message is "Could not open input file" so the problem is finding the files it needs to run, not the username/password. What is 'my\_php\_script' in the cron entry? Does the cron entry literally include '\[mysite\]' or is that where you have redacted your site name?

There are several ways to run scheduled jobs but I suggest you use the 'cv' method if you can. See the documentation for details.

Administrator credentials will work, or you can set up a dedicated user for cron.
> 0 votes

---
Tags: error, cron
---


# Question
Title: Self-service update of credit card details - how to notify staff to re-process recurring contribution?
We would like to send out links for credit card recurring donors to update their credit card when a donation fails, using the solution from this question. Then, we need to somehow notify staff that the donor has made the update, so we can re-process their recurring donation to cover the missed donation. Does someone have a solution for this notification?

There is a notification sent to the donor about the update, but that only goes to the donor. I've tried CiviRules on recurring donation change, but that does not appear to be triggered by updating credit card details through this link.

We're using IATS.

# Answer
This works in core! There is an activity created of type Update Recurring Contribution Billing Details when a contact updates their details through the form. And then you can add a CiviRule to send an email when this activity is created. That will also capture back office updates and ideally it wouldn't but this is bearable. A back office update will have a target contact while a self service one will not, but I don't think there is any way to add lack of target contact as a condition in Civirules.

What I did is to only trigger on source contact activities and exclude updates with the source contact ID of one of our staff who might update a credit card from the CiviRule. Not pretty, but good enough.

Not sure if this always worked and somehow my testing didn't work before or something was fixed in our latest update to 5.35.1.
> 3 votes

# Answer
> as we discussed over email please log an issue for this in our iATS Extension Github account re: logging an activity when a user updates their credit card.
> 0 votes

---
Tags: civicontribute, recurring-contributions, iats
---


# Question
Title: Member reports javascript broken after update
Somewhere between 5.15 and 5.36, javascript starts breaking on all member reports.

In the html body, javascript is searching for

```
$originalElement = $dateElement.siblings('#membership_join_date_from').hide(),
        date_format = $originalElement.attr('format'),

```

and later does

```
date_format.indexOf('m') > -1

```

In my source, `membership_join_date_from` is not a sibling of $dateElement. it is nested inside a sibling `span.crm-form-date-wrapper` (and it doesnt have an attribute called `format`).

So `date_format` is undefined and the error stops javascript execution; the form layout with tabs and such breaks, even showing the SQL output when generating results.

Since nobody seems to have this, I am wondering if it is something with my reports ?

# Answer
OK, my bad. I had to update the `nz.co.fuzion.extendedreport` extension, too.
> 1 votes

---
Tags: civireport, javascript, 5.33.0
---


# Question
Title: Missing extension "moregreetings" after upgrade to CiviCRM 5.36
I am running CiviCRM with the extension Moregreetings.

After upgrading CiviCRM to version 5.36 without other problems, I got two notifications:

> Unknown extension: de.systopia.moregreetings Warning: Error loading module file (). Please restore the file or disable the module.

I am wondering how I have to solve this issue. I would like to know if it is safe just to reinstall the extension. I fear to loose the huge work for setting up this extension.

# Answer
> 2 votes
I'm guessing when you upgraded you somehow deleted your extensions folder. All you need to do is put the code files for the moregreetings extension back into that folder.

---
Tags: extension
---


# Question
Title: Civirules intent to select contact with address of one type with the exclusion of another type
I have created a type of address labeled "Old" for keeping a record of addresses that are no longer valid for a given contact.

I want to create a Civirules rule for tracking the contacts where the "Old" address is set and no other address is available.

I was hoping the condition "Address is unique" was meaning that there was only one type of address available for a given cntact, but I discovered that this condition is always valid when there is no other contact in the database with the same address.

I would like to know how I can solve my problem: creating a group with all the contacts having an "Old" address and no other address (like "Home", "Work", "Other", etc...).

# Answer
> 2 votes
This is not a direct answer but potentially this extension might help you to keep track of old addresses? https://civicrm.org/extensions/former-communication-data

---
Tags: civirules
---


# Question
Title: Editable "Other" option in multiple choice / select field?
Is there a way to create a CiviCRM form like this:

> What would you like to eat at the event?
>
> * Falafel
> * Kebap
> * Other: ......................... \<-- user can make a free entry here

As far as I can see, multiple choice fields in CiviCRM do not provide this functionality, or am I overlooking something? Any other ideas how to achieve this (or something similar)?

# Answer
> 6 votes
This has been a feature request from time-to-time, and would be a great one to submit to the Paid Issue Queue.

If you want to DIY a solution I'd suggest you create a simple textfield as your custom field, and then use jQuery to add some extra radios that hide and populate the field unless the "other" box is selected, then show the field.

# Answer
> 3 votes
Profile conditional ext can be used to provide this - https://civicrm.org/extensions/profile-conditionals

You dont have any UI option to build the conditions. But this can be easily done in civicrm.settings.php. The config we used for select-other field is pasted below -

* custom\_229 is the dropdown field wth options + other
* custom\_230 is the "Other" Text field.

```
global $civicrm_setting;
$select_other = [
  'onload' => [
    'selectors' => [
      '#editrow-custom_230' => [
        'display' => 'hide',
      ],
    ],
  ],
  'select-other' => [
    'conditions' => [
      'all_of' => [
        [
          'id' => 'custom_229',
          'op' => 'value_is',
          'value' => '7', //Other
        ],
      ]
    ],
    'states' => [
      'pass' => [
        'selectors' => [
          '#editrow-custom_230' => [
            'display' => 'show',
          ],
        ],
      ],
      'fail' => [
        'selectors' => [
          '#editrow-custom_230' => [
            'display' => 'hide',
          ],
          //Clear the text value if other is not selected in the dropdown
          '#custom_230' => [
            'value' => '',
          ],
        ],
      ],
    ],
  ],
];

//Apply the above condition to contribution page id 22, 23 and 24
$civicrm_setting['com.joineryhq.profcond']['com.joineryhq.profcond'] = [
  'contribution' => [
    22 => $select_other,
    23 => $select_other,
    24 => $select_other,
  ],
];

```

# Answer
> 2 votes
If you are based upon Drupal, an easy way to make something similar is to use a Webform with 2 items and a "conditionnal" : if item1 = "other" then item2 is shown

# Answer
> 0 votes
You don't have an option for entering a user value in Core Civi so you have to write a javascript to populate a field and you should read the value using POST and write to the respective table (custom field)

You can achieve by creating a new extension with some hooks

---
Tags: profile, custom-fields, forms
---


# Question
Title: Navigation (or other action) after afform submission
I am trying to set up a reduced entry form for new contacts to hide the complexity of the generic form and just collect the essentials. Thanks to afform\_gui it is not difficult to set up the form as such and it can indeed be used to add contacts.

But: When the submit button is clicked, all I get is that tiny green notification saying “Saving…” and then “Saved” in the upper right corner of the CiviCRM window. And then nothing happens.

I would expect to be able to navigate to the contact summary view or at least clear the form so that it becomes ready for the next contact or do something else upon submission of the form but I cannot find out how. It feels like I must be missing something very basic but I just cannot figure out what it is.

Any hints anyone?

# Answer
> 1 votes
This was just implemented a few days ago. See https://github.com/civicrm/civicrm-core/pull/20018

---
Tags: formbuilder
---


# Question
Title: How do I change the font colour in Front facing Joomla Forms
I am having an issue with the front facing forms in Joomla all the labels are white on a white background. How can I change the label font colour on all forms/profiles to black?

Thanks

# Answer
You can add styling in your theme css file to over-ride the default style.
> 0 votes

# Answer
The most commen cause of this problem with Joomla is that Civi adds styling specific to the `div.crm-container label` selector, and Bootstrap-based themes, and Joomla's Admin theme also styles `label`. Which part of the CSS you need to override depends on your front-end theme, but as Pradeep says, editing this is how to fix your problem.

If you are new to debugging CSS, use the Inspector panel in your browser..

and you can see all the different selectors impacting the `label`.

Generally selectors in Inspector that are higher-up override those further down in the 'cascade'. 'cascade' in Cascading Style Sheets refers to the order in which style information loads. So it sounds like CiviCRM's styles are loading higher in the cascade than your template's. By hovering over the different CSS filenames on the right, you can find which styling is added by your template, and what you'll specifially need to overwrite:
> 0 votes

---
Tags: profile, joomla, forms
---


# Question
Title: Mass Emailing based on event day and time
I have read different resources, but still can't determine the best way to email all attendees registered for a specific event date and time the day prior or the day of the event.

I'm sure this can be done but not sure of the correct steps. Does a group have to be created each time, can it be automated???

Thanks,

E

# Answer
> 2 votes
You can use schedule reminders to send notifications based on event dates and status

---
Tags: civievent, email
---


# Question
Title: Drupal View Not Rendering HTML From Activity Email Details Field
I am trying to display in a Drupal (8) view the content of emails that have been sent to contacts using the contact Send Email action. I am using the details field from the activity to show the content. The problem is that the view is outputting the HTML from the email and not rendering it. So it is showing for example `<p>Some text here <a href="...">A link</a></p>`.

Is there any way I can get it to render the HTML instead of outputting it? The formatter options for the field are only "Default" or "Trimmed". I did try overidding the individual field twig template but couldn't get this to work. I wasn't sure whether it would be possible using this method.

# Answer
> 0 votes
Drupal is very secure/paranoid when it comes to outputting html/text. I suspect that the views/field integration for the entity (i.e the activity civicrm entity) might need to be updated to allow the html through without any filtering.

---
Tags: drupal-views, drupal-8, civicrm-entity
---


# Question
Title: Weight Threshold error when creating a Deduplication Rule
This is on CiviCRM 5.36.

I try and create a rule with, say, First Name and Last Name, both having a weight of 10. If I set any Threshold greater than 10 so as to require both to match, I get the error:

`Weight Threshold to Consider Contacts 'Matching': 11 Total weight must be greater than or equal to the Weight Threshold.`

I first saw this when trying to change the usage on an existing rule which had worked previously.

# Answer
This has been fixed and will be part of next 5.36 release this week.

You can either wait for the never version release or apply patch from here.

HTH

Pradeep
> 2 votes

# Answer
Known error. Fixed in Commit https://github.com/civicrm/civicrm-core/commit/b1212927a34f1086e6911fea5405595a40c5bcf9
> 0 votes

---
Tags: dedupe-rules
---


title: Voting Election

text:
# Question
Title: Voting Election
I am reviewing the Civic CRM Election component for Joomla. I have created some basic election information and nominees. I need to add Amendments for this election. I don't where I would add this. Would I have to create custom fields? I haven't found any documentation on this.

# Answer
Sounds like you are interested in this feature request, https://github.com/agileware/au.com.agileware.elections/issues/5

In terms of custom fields, you can add them to Contacts in the Election. But then would need to update the templates to show the values.

The other Election entities, such as Election, Vote and others used specifically for the Election do not support custom fields.

The standard CiviCRM entities, of course do. So would recommend trying to use those.
> 1 votes

---
Tags: custom-fields, joomla
---


title: Installing IParl extension

text:
# Question
Title: Installing IParl extension
I have installed the extension to work with IParl. I am at the stage in the intructions where I want to test whether the API is working.

The instructions https://github.com/artfulrobot/uk.artfulrobot.civicrm.iparl

The instructions you can test it using curl or http. Where would I actually write this script? I do have access the command line on the server but I'm not overly familiar with it yet. Would I create the script in a small file and run it from the command line?

curl -k -L 'https://yoursite.org/civicrm/iparl-webhook'
-d secret=helloHorseHeadLikeYourJumper
-d name=Jo
-d surname=Bloggs
-d email=jo@example.com
-d actionid=1 or, with httpie

http --verify no -f POST 'https://yoursite.org/civicrm/iparl-webhook'
secret=helloHorseHeadLikeYourJumper
name=Jo
lastname=Bloggs
email=jo@example

Thanks for your help.

# Answer
While these commands are split into multiple lines for readability, that curl statement is a single command. You can paste that straight into your command line; depending on your shell, you might need to remove line breaks so it's all on a single line.
> 0 votes

---
Tags: extension
---


title: Workflow for managing duplicate cases

text:
# Question
Title: Workflow for managing duplicate cases
We've found an issue where a member of staff has created a new case for an existing contact instead of adding an activity to an existing case (Training and documentation is being addressed)

But it leaves us with a list of multiple cases of the same type on several client records.

There's no way of merging cases, so the best workflow I can think would be to identify one case as the "main case" to keep and work through the other cases and move any activities onto the main-case record.

Anybody got any other suggestions?

# Answer
> 2 votes
There is a merge case feature. The clients need to be the same client on both cases so you may need to merge contacts or change the client first. The feature is a little blue link about 1/4 way down the manage case screen.

You may also want to look at https://lab.civicrm.org/extensions/mergecaseandkeepid since, for unfortunate historical reasons, the case id changes when you do a merge.

---
Tags: civicase
---


title: Query string state/province field

text:
# Question
Title: Query string state/province field
On a profile listing page, you can query string to pre-select the field. e.g. add `&state_province-Primary=NN` where NN is the ID in the civicrm\_state\_province table.

However, on a profile create page, specifically on a contribution page, this does not work.

How can we make this work?

# Answer
Since you have the jQuery tag, I assume you're open to a jQuery solution.

* This StackOverflow question covers pulling a variable from a URL argument.
* Then it's just a matter of identifying the CSS identifier of the field you want to fill (right-click, use "Inspect Element") and referencing it. E.g.:

```
var state = GetURLParameter('state');
$('#state_province_id-2').val(state);

```

The Select2 documentation suggests this should work on Select2 widgets as well.
> 1 votes

# Answer
Alternative answer is a Civi extension that uses the `buildForm` hook to prepopulate the field. Here's some example code that does something very similar on a custom field:

```
function trackingcodes_civicrm_buildForm( $formName, &$form ) {
  if ($formName == 'CRM_Contribute_Form_Contribution_Main' ||
      $formName == 'CRM_Contribute_Form_Contribution_Confirm' ||
      $formName == 'CRM_Contribute_Form_Contribution_ThankYou' ) {
    CRM_Core_Resources::singleton()->addStyleFile('trackingcodes', 'trackingcodes.css');
  }
  if ($formName == 'CRM_Contribute_Form_Contribution_Main') {
    if (isset($_GET['track1'])) {
      $defaults['custom_180'] = $_GET['track1'];
    }
    if (isset($_GET['track2'])) {
      $defaults['custom_181'] = $_GET['track2'];
    }
    if (isset($_GET['track3'])) {
      $defaults['custom_182'] = $_GET['track3'];
    }
    $form->setDefaults($defaults);
  }
}

```
> 1 votes

---
Tags: profile, jquery
---


title: Crypto key has unrecognized type?

text:
# Question
Title: Crypto key has unrecognized type?
After CiviCRM upgrading v5.29 to v5.36, when I run the database update get the following error. I googled "CiviCRM Crypto key has unrecognized type" but did not find anything. Any suggestions what to do next?

> 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. Crypto key has unrecognized type Return to home page.

This is what I get with Debugging on:

```
Civi\Crypto\Exception\CryptoException: Crypto key has unrecognized type in /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm/Civi/Crypto/CryptoRegistry.php on line 76

Exception trace
#    Function    Location
0    Civi\Crypto\CryptoRegistry->parseKey('%%%%%%%%%%%%%%%…')    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm/Civi/Crypto/CryptoRegistry.php:76
1    Civi\Crypto\CryptoRegistry::createDefaultRegistry()    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Incremental/php/FiveThirtyFour.php:39
2    CRM_Upgrade_Incremental_php_FiveThirtyFour->setPreUpgradeMessage('<br />The defaul…', '5.34.alpha1', '5.29.0')    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Form.php:823
3    CRM_Upgrade_Form->setPreUpgradeMessage('<br />The defaul…', '5.29.0', '5.36.0')    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Page/Upgrade.php:108
4    CRM_Upgrade_Page_Upgrade->runIntro()    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Page/Upgrade.php:57
5    CRM_Upgrade_Page_Upgrade->run(Array, null)    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php:313
6    CRM_Core_Invoke::runItem(Array)    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php:69
7    CRM_Core_Invoke::_invoke(Array)    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php:36
8    CRM_Core_Invoke::invoke(Array)    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm.php:121
9    civicrm_invoke()    /home/customer/www/%%%%%%.org/public_html/administrator/components/com_civicrm/civicrm.php:40
10    require_once('/home/customer/w…')    /home/customer/www/%%%%%%.org/public_html/libraries/src/Component/ComponentHelper.php:402
11    Joomla\CMS\Component\ComponentHelper::executeComponent('/home/customer/w…')    /home/customer/www/%%%%%%.org/public_html/libraries/src/Component/ComponentHelper.php:377
12    Joomla\CMS\Component\ComponentHelper::renderComponent('com_civicrm')    /home/customer/www/%%%%%%.org/public_html/libraries/src/Application/AdministratorApplication.php:101
13    Joomla\CMS\Application\AdministratorApplication->dispatch()    /home/customer/www/%%%%%%.org/public_html/libraries/src/Application/AdministratorApplication.php:159
14    Joomla\CMS\Application\AdministratorApplication->doExecute()    /home/customer/www/%%%%%%.org/public_html/libraries/src/Application/CMSApplication.php:203
15    Joomla\CMS\Application\CMSApplication->execute()    /home/customer/www/%%%%%%.org/public_html/administrator/index.php:51
16    {main}

```

# Answer
Did you already set CIVICRM\_CRED\_KEYS in civicrm.settings.php? If so it probably has the wrong "type". https://docs.civicrm.org/sysadmin/en/latest/setup/secret-keys/#civicrm\_cred\_keys
> 2 votes

# Answer
While not the OP's issue, another cause for this error is copying a Civi database to a new site. Typically you'll install a fresh Civi, then load your backup. If you do this, in theory you need to match your `CIVICRM_CRED_KEYS` value to whatever was in the old `civicrm.settings.php`.

In practice, I found it much easier to delete the entry in the `civicrm_setting` database table that stored SMTP settings and re-enter them.
> 1 votes

---
Tags: joomla, upgrade, crypto
---


title: How can I create a membership number?

text:
# Question
Title: How can I create a membership number?
Is there a way to automatically create membership numbers for members?

There is the CiviCRM ID but this applies to all contacts, not just members. I'm looking for a way to create a number that only applies to members.

This question was asked a few years ago on the forums (Membership number/code) but I wonder if there have been any developments since then.

# Answer
Do you want a membership number, or a number for members.

Each membership in civicrm has an Membership ID associated with it (Used for linking fees paid to the memberships) Perhaps you could use that as your member number.

There are two downsides to that:
> 2 votes

# Answer
I'm not sure whether it would be any use to you but there is a CiviCRM Core Extension called "Custom Membership ID" by Grant Traynor which is at the following link Custom Membership ID
> 3 votes

# Answer
Grants extension "Custom Membership ID" isn't working with up-to-date CiviCRM (5.36).

If you like a view of the Membership ID (and type), please install the extension "Display MemberID" from https://civicrm.org/extensions/show-member-ids It shows the ID in Contact Summary (next to contact ID) and in Memberships.
> 1 votes

---
Tags: civimember
---


# Question
Title: Troubleshooting SELECT Privileges Issue with Drupal 9 Views Integration
I'm having an almost identical problem to this person (https://www.drupal.org/project/civicrm\_entity/issues/3198146) so I'm hoping to get an answer for both of us.

It looks like this may also be the same issue.

I have a Drupal 9/CiviCRM install set up on a shared hosting account on A2Hosting. Everything has gone smoothly so far, and I was able to follow the Views integration instructions exactly. However, when I try to set up a simple View to test, I get the following error:

```
SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user '<USER>'@'localhost' for table 'civicrm_contact': SELECT "civicrm_contact"."id" AS "id" FROM {civicrm_contact} "civicrm_contact" LIMIT 11 OFFSET 0; Array ( )

```

The error seems pretty self-explanatory - I need to assign SELECT privileges to the in question - but I've tried everything I can think of, namely:

1. I've given the USER in question ALL PRIVILEGES on the CiviCRM database in cPanel. I can't access the User list via phpMyAdmin, which I guess is common on shared hosting.
2. I've tried to use the command line to check the status of the USER's privileges, but I apparently need root access, which is not possible on the shared hosting plan.
3. I've contacted A2Hosting directly to see if they can help check (or correctly assign) the privileges for the USER in question. They say they can't.

So, I have a few questions that I'd love help answering (I'm a relative beginner at all of this):

* Is this a known issue? There are at least two of us now who've been stumped by it. Is there a quick fix I'm not seeing?
* Are the USER in cPanel and USER@localhost different entities? Is it possible that they would have different privileges?
* If Views integration is somehow not possible in a shared hosting environment, what is the preferred set up? Am I on the wrong hosting service?

# Answer
> 0 votes
For me and a few other users, with Civi & Drupal in separate databases, this was solved by comment #4 on Views integration error on D9:

> Try removing the back ticks ... '\`' from the prefix information in the settings.local.php

(or settings.php in my case.)

If it's not working for you with Civi & Drupal in the same database, assuming you've removed the code that you pasted into the settings file, maybe try Jon G's solution at How to get CiviCRM Views to work in Drupal 8 with separate CiviCRM database.

---
Tags: drupal-views, mysql, integration, drupal-9
---


title: Civibuild issues?

text:
# Question
Title: Civibuild issues?
Has anyone tried running `civibuild` recently to create a CiviCRM environment under Drupal 9? I have been running it on a Ubuntu 20.4 virtual machine and hit various issues. To build `drupal9-clean` I had to copy `composer` from a running Drupal 9 instance to `buildkit/build/drupal9-clean/vendor` as it was not installed by `civibuild`.

# Answer
**Edit: As of 8-26-2021 the below command works just fine with current buildkit, it's just not in the civibuild docs yet afaik:**

```
civibuild create d9dev --type drupal9-clean --civi-ver master --url http://d9.localhost

```

As of today (4-21-2021 env Ubuntu 20.04), I used the command

```
civibuild create d9dev --type drupal9-clean --civi-ver master --url http://d9.localhost

```

Which ran into errors because it couldn't find a valid civicrm root (/vendor did not include a civicrm directory at this time). I needed to run the command, then manually:

```
composer config extra.enable-patching true
composer config minimum-stability dev
composer require civicrm/civicrm-asset-plugin:'~1.1'
composer require civicrm/civicrm-{core,packages,drupal-8}:'~5.35'

```

Then I ran the same civibuild command again to finish out the build process and get the .sh file with login info etc. I ended up with a working copy of Drupal 9 version 9.0.12-dev and CiviCRM 5.36.0.

Hope that's helpful!
> 0 votes

---
Tags: civicrm-buildkit
---


# Question
Title: Mailing is saved but does not reach recipients
I'm really out of my depth now. Here is the situation:

CiviCRM version 5.36.0 Wordpress Plugin Hosted on Linux Server

Administer-\>System Settings-\>Outbound Mail: enteredSMTP-Server, Testmail is sent and arrives at destination.

View Contact-\>Actions-\>Send an Email: Mail is sent and arrives at destination.

Mailings-\>New Mailing-\>Testgroup: shows 2 recipients.

Send test (single) 2nd try: Mail is sent and arrives at destination.

Send test (Testgroup - same as final recipients): Mail is sent and arrives at destination.

Next-\>Send immediately-\>Submit Mailing: Mailing is listed as scheduled, Email does not arrive at destination.

The Mailing report doesn't show any bounces.

I have tried different recipient addresses (two different test groups) on different mail services. I have tried with two different Mail providers for outbound mail. Everything works except the final mailing. I have no idea what else to test.

Thanks Martin

# Answer
It's maybe not obvious but send immediately for mass mailings means "schedule it now". You need to set up the scheduled job that sends out mass mailings. See https://docs.civicrm.org/user/en/latest/initial-set-up/scheduled-jobs/#job\_process\_mailing
> 1 votes

---
Tags: civimail, groups, smtp
---


title: Detailed logging running continually

text:
# Question
Title: Detailed logging running continually
On some but not all records, we see over 2K change log entries.

On this record, the change log started and ran continuously from 11-26-19 to 1-20-2020

Another instance I had imported to the preferred communication field and the import ran for ~20 minutes. The change log was continually getting written to for the entire import process.

For now, I have turned off detailed logging. We are using a separate db for it. Thoughts on what is going on here?

# Answer
This PR resolved this https://github.com/progressivetech/net.ourpowerbase.sumfields/pull/62

with an **Exclude Summary Fields from logging?** feature.

> When advanced logging is turned on, you can exclude Summary Fields from being logged to increase performance and reduce clutter.
> 1 votes

---
Tags: logging
---


# Question
Title: Mange Event > Configure > Dithered Options
I am attempting to send emails to participants that are registered for events. When I access **Manage Events** and select **Configure** \- the option for **Schedule Reminders** is dithered out and not sure why. Any advice would be greatly appreciated.

# Answer
This is a known issue that happens with the Shoreditch theme. It is due to a problem between Core and Shoreditch/Bootstrap, that I've written about here. You can still click the top option and then use the top tabs to navigate to the page you need.
> 1 votes

# Answer
It's not disabled - dithered out means that part of the event has not been configured yet. You can go ahead and click it.
> 0 votes

---
Tags: civievent
---


# Question
Title: Reporting on contribution amounts when added to a membership page
We've added the option to accept additional donations when initiating or renewing a membership by enabling the Contribution Amounts section on a contribution page but there doesn't seem to be a way to report on these additional contributions. For example, if a member renews a family membership for $35 and makes a $20 donation, Civi records a $55 membership. We then have to go through each membership and each contribution to figure out how much they donated along with their membership fee. We've tried the ExtendedReport extension which has some great ways to view Price Set info but we can't use contribution price sets on membership pages :-(. Has anyone figured out a way to get this info into a report?

cheers ... :) gn

# Answer
i would do the following. use a single price set based on Memberships. add the donation line item in to it with its own financial type.

then the Extended Report extension should solve your last issue of how much is donation v member fee
> 2 votes

# Answer
Sorry to come in here after so long, but I've been tearing my hair out trying to figure out how to have CiviCRM do something as seemingly simple as accept a membership amount, and make its financial type "Membership" and a donation and make it's financial type "Donation". All on the same Contribution page.

And I just don't understand the above solutions. WE can't create a price set that has Memberships and Donations in it. But when I create a Contribution form with the form contribution type set to Donation, and have a membership also being taken, it will only show one financial type. The donation.

(I am using Stripe, so it can't do two charges. WHich is fine, since that would cost us two times the fixed transaction fee)

from petednz - fuzion

> single price set. memberships. add the donation line item there with its own financial type.

Add the donation line where? The error says we can't add it in the price set. So where is this donation line going?

Forgive my irritation, but this seems like such a fundamental REQUIREMENT in ANY country and in ANY tax system, that the sheer difficulty of making it happen is mind boggling.
> 0 votes

---
Tags: civimember, contributions, reports, financial-types
---


# Question
Title: IATS can retry failed donations 7 days after the failure?
Is there a way that IATS can retry failed donations 7 days after the failure happened rather than the following month?

# Answer
> 2 votes
The retry attempt on REJ response is dependent on the type of failure. Card Reported Lost or Stolen e.g. will be bumped to next month - whereas other failures will attempt next day. Note that you can always catchup or hit Process (if using iATS Legacy) after e.g. updating a Lost or Stolen card so you don't need to wait until next month to capture the monies.

---
Tags: civicontribute, iats, drupal-7, iatspayments
---


# Question
Title: Should one choose contact types or groups when organizing data and how can one customize built-in details
I'm setting up civi on a Wordpress site and after reading all that I could find I still have tons of questions with no-one to ask.

My NGO that has members, volunteers, people interested in NGO's activities but who don't fall into the first two categories. My problem is that I'm unsure in how to properly setup the data, ie. should I just enter individual contacts and then segment them by adding them into member, volunteer or similar groups or stick with Member or Volunteer contact type? The problem in the second option is that when a volunteer decides to become a member, change from one contact type to another deletes the data from the first and the only way to go about it is to merge the data but it seems like a clumsy way to work with it. Similarly, if a person decides to stop his membership he's still a member contact type but with that added info.

Also, if I want to use civiVolunteer for working with volunteers does the choice from the second paragraph affect it in any way?

Same thing with wanting to add contact data of other NGO's, public administration bodies and similar - are groups or contact types the way to go?

Also, is there a way of expanding the demographics field that doesn't rely on adding new custom data segments because the custom data I would add would fit nicely into demographics? And, can one remove the employer, job title, nickname and so on fields from contact details because, for the life of me, I haven't been able to find it?

Thanks a bunch.

# Answer
> 3 votes
Many of these questions are going to be specific to your organization's needs, and there are multiple legitimate ways to organize your data, depending on the mental model the users will have.

With that said, here's some info:

* Using groups is more lightweight than contact subtypes.
* It sounds like you're using custom field groups specific to certain contact subtypes, which is a good reason to consider contact subtypes. However, it sounds like maybe you're using the custom fields to capture data specific to membership, which likely belongs on the membership, not the contact itself.
* A contact can have multiple contact subtypes.
* If you could edit your question to say more about data that's "merged" when moving from volunteer to member you could get more specific help. If there's data that applies to both members and volunteers they shouldn't get separate sets of custom fields.
* Since it sounds like memberships aren't permanent, it may be worth making a smart group of members based on a membership record in CiviMember.
* It sounds like you want the Contact Layout Editor extension to customize your summary screen.

# Answer
> 1 votes
Using contact subtypes will enable you to add new custom fields to that contact type - so you could add additional info for volunteers that isn’t applicable to donors. But be careful it’s not easy to change a contact subtype once assigned and what would happen if a person is both a donor and volunteer. It is possible to create smart groups based on virtually any search criteria so you could have both contact subtypes and a group that automatically updates based on this. In my experience I have found limiting use of subtypes has been the way to go. I use the tags feature to identify a type of person. You have to use groups if you want a mailing list but can still create a smart group based on the tags people have - keep the number of groups you have as low as you can

---
Tags: wordpress, contact-types
---


title: Blank CiviCRM Home page after install

text:
# Question
Title: Blank CiviCRM Home page after install
This is my first time installing Civi. The install appeared to go OK but the Home and Configuration pages are blank.

The inspector shows these errors:

**Error: CRM.url called before initialization CRM.console @ Common.js?r=l6LAc:1617**

**Error: Cannot read property 'back' of undefined**

My error log shows:

**PHP Warning: include(/ebs2/vhosts/mvfn.ca/staging.mvfn.ca/wp-content/uploads/civicrm/templates\_c/en\_US//%%52/529/529A2042%%l10n.js.tpl.php): failed to open stream: No such file or directory in /ebs2/vhosts/mvfn.ca/staging.mvfn.ca/wp-content/plugins/civicrm/civicrm/packages/Smarty/Smarty.class.php on line 1273**

That file is indeed missing. The /templates\_c/en\_US/ directory exists but has only .htaccess in it. Permissions for that and all the other /uploads folders are 755, and the owner and group are the same for all.

Am I able to locate that file in the plugin ZIP and upload it manually? Any other ideas? Thanks.

I'm running WP 5.7.1.

**UPDATE**

Hi Aidan, thanks for your reply. I did follow that install procedure, yes, and the owner and group for that folder appear to be the same as for the rest of the server. In checking my logs I see a security warning at about the time I was doing the install -- some Googling suggests that this is a message from the Imunify security application. Sounds like I may have to try and sort this out with my hosting company.

**\[client 174.89.56.82\] ModSecurity: \[file "/etc/httpd/conf/modsecurity.d/rules/custom/004\_i360\_4\_custom.conf"\] \[line "905"\] \[id "77140992"\] \[msg "IM360 WAF: Suspicious access attempt (WP folders)!||SC:/var/www/vhosts/mvfn.ca/staging.mvfn.ca/wp-admin/options-general.php||T:APACHE||REQUEST\_URI:/wp-admin/options-general.php?page=civicrm-install||"\] \[severity "NOTICE"\] \[tag "service\_i360custom"\] \[tag "noshow"\] Warning. Match of "pmFromFile path\_excludes" against "REQUEST\_FILENAME" required. \[hostname "staging.mvfn.ca"\] \[uri "/wp-admin/options-general.php"\]**

# Answer
Welcome to SE! Sorry to hear you are having problems installing CiviCRM.

Have you followed the installation instructions here?

Files are created automatically under the `templates_c` directory. The only thing you need to do there occasionally is to remove all the files. (Normally that is taken care of by 'clearing the cache').

Is the file owner of the directory the same user as the one running your webserver?

Edit after the question update: yes, you'll need to talk to your hosting company about ModSecurity.
> 0 votes

---
Tags: civicrm-home
---


# Question
Title: Custom field saves, for all entries, instead of wanted data a number that is not in original data
I'm having an odd problem with a custom field within a custom data set. The field is made for personal identification number, data type is integer and it's a single line input field. Every time I enter the data either manually or through import (careful about choosing corresponding fields) upon clicking the save button instead of saving the wanted number it saves a completely different number that's not in my data (it's always the same number). I even tried to delete the custom field (with corresponding data), then create it again and manually add it back but nothing.

Using WP

# Answer
> 1 votes
Well it seems that I had to delete the set and re-create it again in order for it to work like it should. Why, I've no idea.

---
Tags: custom-fields, data-import
---


# Question
Title: CiviCRM module "Group-Based Pricing" doesn't work in Joomla
the module Group-Based Pricing (com.zengenuity.civicrm\_groupprice) can't be enabled in Joomla. I copy the files from https://github.com/zengenuity/civicrm\_groupprice into the media/civicrm/files and the module is still red in the page "Administer-\>System settings-\>Manage Extensions". The civicrm was moved from the Drupal site into the Joomla.

# Answer
Can you confirm that's the correct directory for extensions by going to **Administer menu » System Settings » Directories** and looking at the Extensions directory listed there? It may also need to be updated from the Drupal location if you copied the database. If there's a token like `[civicrm.files]` you can click the question mark in the blue circle in the text at the top of the page to see what that token resolves to.

If the directory is correct, it's likely a file permission issue.
> 0 votes

# Answer
I just noticed that this module appeared with another name in list of CiviCRM Extensions: "Limit Prices by Group (civicrm\_groupprice)". Author Wayne Eaker (wayne@zengenuity.com). This is a bit confusing.
> 0 votes

---
Tags: joomla
---


title: Unwanted unsubscribe in profile

text:
# Question
Title: Unwanted unsubscribe in profile
We have CiviCRM install with a group, used for newsletters, and some event registrations. Our customer added a checkbox to the event registration profile to subscribe to the neweletter.

Now, when someone already subscribed to the newsletter (added to group 'newsletter'), uses the event registration profile but not checking the checkbox to subscribe, this results in an unsubscription (removed by web).

My question: how to avoid this (evidently unwanted) behavior? And how to allow people to subscribe in a better way to subscribe to a newsletter from the event registration form.

Thank you!

# Answer
This is a frustration and I wish there was a better answer. I've asked a similar question myself.

One solution is to use tokens in links sent out in mailings as much as possible, so this checkbox is already selected, but you won't be able to cover all uses with this.

You can use two groups, the first of which is the one on your forms. Then set up a CiviRule that contacts are added to a second group, the real mailing list, when they are added to the first group. But then people who are logged in or using a tokenized link won't be able to remove themselves from the group, so I wouldn't recommend this unless you aren't using tokenized links or allowing logged in users.

I'd like to fix this, at some point.
> 1 votes

---
Tags: profile, unsubscribe, subscribe
---


# Question
Title: Membership start date and "member since" date not set with online contributions
I've noticed that ever since we started taking online contributions (last summer) new members have been getting created with null start date and "member since" date. Offline memberships (created via the backend) work fine, and the membership statuses all seem to work correctly otherwise. The end date is also set correctly. I'm using both GoCardless and Stripe payment processors and the problem appears with both of them.

The practical upshot of this is that these members are not being counted in the membership summary dashboard - this is probably also a bug - either the schema should enforce these values not being null, or the dates shouldn't be used to calculate a "current" member.

In case it's relevant, here are the membership status rules:

Some other details:

* the memberships are rolling
* hosted on Drupal 7 and MySQL
* running CiviCRM 5.34.0, but problem dates back to at least 5.28.1, the version running when the earliest membership of this type was created.
* Detailed logging is enabled, and there's no evidence of the dates being set and cleared.

There are similar issues described at Start/member since dates not being set since upgrade to 5.3.2 and Member Since and Start Date aren't being recorded in membership details but there is no resolution of either of those and they're both a few years old, so I figured a fresh report would not be a bad thing.

Edit to add screenshots of the contribution page (membership and profile sections):

Edit to add:

Some experiments I tried to rule out various causes

* The same thing happens with a dummy provider or a free membership.
* Pay later puts the membership into pending state, with no dates at all (based on comments this also seems unexpected
* The same thing happens on a clone of the live site with all extensions disabled
* The same test installation with a completely fresh install and the demo contribution form works as expected, populating all the dates correctly.

So have pinned it down to something in our live database at least, rather than anything basic with the environment or code deployment, but I've pored over the differences in settings and contribution forms from database dumps and found nothing promising.

# Answer
> 2 votes
This problem was seen on one of our sites and the issue was in the status rule page where Pending is selected as `Member` = `Yes`.

Pending is a reserved status that defaults to Current Member = No. Civi doesn't allow you to edit this status via UI but i see inline edit still works on these statuses. This is clearly a bug in core.

So i think it was unintentionally updated to Yes on your site since i see the same case on your attached screenshot.

Reverting this value back to `No` fixes the problem with the dates.

---
Tags: civimember, contribution-page
---


title: What is the EOL for 5.33-ESR

text:
# Question
Title: What is the EOL for 5.33-ESR
I need to know when the EOL of 5.33-ESR is. I'm a subscriber and I checked here and other places but couldn't find it.

Thx.

# Answer
> 1 votes
From https://civicrm.org/esr#faqs:

> For how long is each version of ESR supported?
>
> At present, each version of ESR will receive 6 months of support. The CiviCRM Core Team may extend the release to a full 12 months of support, depending on the financial viability of maintaining this version of CiviCRM.

So I calculate:

* Current: 5.36
* May: 5.37
* June: 5.38
* July: 5.39 =\> next ESR

# Answer
> 2 votes
See also https://lab.civicrm.org/dev/release/-/blob/master/README.md with tentative ESR dates.

---
Tags: esr
---


# Question
Title: MAJOR ERRORS after civicrm upgraded to 5.35.1 wordpress
I am getting multiple errors since upgrading our civicrm to the latest version.

When I try to enable debugging i receive db constraint issues and log file shows: Apr 06 11:54:29 \[error\] $Fatal Error Details = Array ( \[callback\] =\> Array ( \[0\] =\> CRM\_Core\_Error \[1\] =\> exceptionHandler )

```
[code] => -3
[message] => DB Error: constraint violation
[mode] => 16
[debug_info] => INSERT INTO civicrm_setting (`component_id`,`contact_id`,`created_date`,`created_id`,`domain_id`,`id`,`is_domain`,`name`,`value`) VALUES

```

(NULL,NULL,"20210406115429","2","1",NULL,"1","debug\_enabled","s:1:"1";") \[nativecode=1048 \** Column 'id' cannot be null\] \[type\] =\> DB\_Error \[user\_info\] =\> INSERT INTO civicrm\_setting (`component_id`,`contact_id`,`created_date`,`created_id`,`domain_id`,`id`,`is_domain`,`name`,`value`) VALUES (NULL,NULL,"20210406115429","2","1",NULL,"1","debug\_enabled","s:1:"1";") \[nativecode=1048 \** Column 'id' cannot be null\] \[to\_string\] =\> \[db\_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM\_Core\_Error::exceptionHandler prefix="" info="INSERT INTO civicrm\_setting (`component_id`,`contact_id`,`created_date`,`created_id`,`domain_id`,`id`,`is_domain`,`name`,`value`) VALUES (NULL,NULL,"20210406115429","2","1",NULL,"1","debug\_enabled","s:1:"1";") \[nativecode=1048 \** Column 'id' cannot be null\]"\] )

Also when I click on civicrm in wordpress menu the screen only shows: 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: no such field

# Answer
OK everyone, after much head scratching, log file digging and googling I managed to fix my issues as follows:

**Goal required:**

Migrate our WADT.org.uk web site from a Linux shared hosted package with 123reg to a cPanel package with cron facility with 123Reg.

I followed all the steps recommended by my provider - whoops - including the backup and restore of the MYSQL database that powers WordPress and CiviCRM.

**This was the start of my issue highway**

**When you migrate a MySQL database the tables loose all of their auto incrementing fields and primary keys including all WordPress and CiviCRM tables - DOH!**

This issue causes tons of platform issues including - new WordPress users created with ID's = 0, posts to not appear and CiviCRM ids all go to pot and contacts stop working etc.

To fix this issue I completely removed CiviCRM and contact data and then I had to address the auto increment / missing key issue.

I put the script 1 below together and this seems to have done the trick for WordPress, then I did a fresh install of CiviCRM 5.35.1, ran script 2 to identify any tables set to MYISAM and altered any such tables to be INNODB (e.g. if you add the CiviVolunteer extension new tables are created in the database with the storage engine of MYISAM and these must be altered to innodb before use otherwise CiviCRM throws an error)

All in all this has been a long learning curve but having just performed the 5.36.1 upgrade **without issue** then WOOHOO I am a happy bunny - for the moment!

Here are some scripts I used just in case they could be helpful to anyone (Remember to check database structures and add new tables to the script if required).

SCRIPT 1

Replace WPPrefix\_ with your WordPress database prefix :-)

1.

```
-- Starting with WPPrefix_users DELETE FROM WPPrefix_users WHERE ID = 0;
    -- ALTER TABLE WPPrefix_users ADD PRIMARY KEY  (ID); ALTER TABLE WPPrefix_users ADD KEY user_login_key (user_login); ALTER TABLE
    WPPrefix_users ADD KEY user_nicename (user_nicename); ALTER TABLE
    WPPrefix_users ADD KEY user_email (user_email);
    -- ALTER TABLE WPPrefix_users MODIFY ID bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_usermeta
    -- DELETE FROM WPPrefix_usermeta WHERE umeta_id = 0;
    -- ALTER TABLE WPPrefix_usermeta ADD PRIMARY KEY  (umeta_id); ALTER TABLE WPPrefix_usermeta ADD KEY user_id (user_id); ALTER TABLE
    WPPrefix_usermeta ADD KEY meta_key (meta_key(191));
    -- ALTER TABLE WPPrefix_usermeta MODIFY umeta_id bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_posts DELETE FROM WPPrefix_posts WHERE ID = 0; ALTER TABLE WPPrefix_posts ADD PRIMARY KEY  (ID); ALTER TABLE WPPrefix_posts ADD KEY post_name (post_name(191)); ALTER TABLE
    WPPrefix_posts ADD KEY type_status_date
    (post_type,post_status,post_date,ID); ALTER TABLE WPPrefix_posts ADD
    KEY post_parent (post_parent); ALTER TABLE WPPrefix_posts ADD KEY
    post_author (post_author); ALTER TABLE WPPrefix_posts MODIFY ID
    bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_comments DELETE FROM WPPrefix_comments WHERE comment_ID = 0; ALTER TABLE WPPrefix_comments ADD PRIMARY KEY
    (comment_ID); ALTER TABLE WPPrefix_comments ADD KEY comment_post_ID
    (comment_post_ID); ALTER TABLE WPPrefix_comments ADD KEY
    comment_approved_date_gmt (comment_approved,comment_date_gmt); ALTER
    TABLE WPPrefix_comments ADD KEY comment_date_gmt (comment_date_gmt);
    ALTER TABLE WPPrefix_comments ADD KEY comment_parent
    (comment_parent); ALTER TABLE WPPrefix_comments ADD KEY
    comment_author_email (comment_author_email(10)); ALTER TABLE
    WPPrefix_comments MODIFY comment_ID bigint(20) unsigned NOT NULL
    auto_increment;
    -- Starting with WPPrefix_links DELETE FROM WPPrefix_links WHERE link_id = 0; ALTER TABLE WPPrefix_links ADD PRIMARY KEY  (link_id);
    ALTER TABLE WPPrefix_links ADD KEY link_visible (link_visible);
    ALTER TABLE WPPrefix_links MODIFY link_id bigint(20) unsigned NOT
    NULL auto_increment;
    -- Starting with WPPrefix_options DELETE FROM WPPrefix_options WHERE option_id = 0; ALTER TABLE WPPrefix_options ADD PRIMARY KEY
    (option_id); ALTER TABLE WPPrefix_options ADD UNIQUE KEY option_name
    (option_name); ALTER TABLE WPPrefix_options ADD KEY autoload
    (autoload); ALTER TABLE WPPrefix_options MODIFY option_id bigint(20)
    unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_postmeta DELETE FROM WPPrefix_postmeta WHERE meta_id = 0; ALTER TABLE WPPrefix_postmeta ADD PRIMARY KEY
    (meta_id); ALTER TABLE WPPrefix_postmeta ADD KEY post_id (post_id);
    ALTER TABLE WPPrefix_postmeta ADD KEY meta_key (meta_key(191));
    ALTER TABLE WPPrefix_postmeta MODIFY meta_id bigint(20) unsigned NOT
    NULL auto_increment;
    -- Starting with WPPrefix_terms DELETE FROM WPPrefix_terms WHERE term_id = 0; ALTER TABLE WPPrefix_terms ADD PRIMARY KEY  (term_id);
    ALTER TABLE WPPrefix_terms ADD KEY slug (slug(191)); ALTER TABLE
    WPPrefix_terms ADD KEY name (name(191)); ALTER TABLE WPPrefix_terms
    MODIFY term_id bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_term_taxonomy DELETE FROM WPPrefix_term_taxonomy WHERE term_taxonomy_id = 0; ALTER TABLE
    WPPrefix_term_taxonomy ADD PRIMARY KEY  (term_taxonomy_id); ALTER
    TABLE WPPrefix_term_taxonomy ADD UNIQUE KEY term_id_taxonomy
    (term_id,taxonomy); ALTER TABLE WPPrefix_term_taxonomy ADD KEY
    taxonomy (taxonomy); ALTER TABLE WPPrefix_term_taxonomy MODIFY
    term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment;
    -- Starting with WPPrefix_term_relationships DELETE FROM WPPrefix_term_relationships WHERE object_id = 0; DELETE FROM
    WPPrefix_term_relationships WHERE term_taxonomy_id = 0; ALTER TABLE
    WPPrefix_term_relationships ADD PRIMARY KEY
    (object_id,term_taxonomy_id); ALTER TABLE
    WPPrefix_term_relationships ADD KEY term_taxonomy_id
    (term_taxonomy_id);
    -- Starting with WPPrefix_termmeta DELETE FROM WPPrefix_termmeta WHERE meta_id = 0; ALTER TABLE WPPrefix_termmeta ADD PRIMARY KEY
    (meta_id); ALTER TABLE WPPrefix_termmeta ADD KEY term_id (term_id);
    ALTER TABLE WPPrefix_termmeta ADD KEY meta_key (meta_key(191));
    ALTER TABLE WPPrefix_termmeta MODIFY meta_id bigint(20) unsigned NOT
    NULL auto_increment;
    -- Starting with WPPrefix_commentmeta DELETE FROM WPPrefix_commentmeta WHERE meta_id = 0; ALTER TABLE
    WPPrefix_commentmeta ADD PRIMARY KEY  (meta_id); ALTER TABLE
    WPPrefix_commentmeta ADD KEY comment_id (comment_id); ALTER TABLE
    WPPrefix_commentmeta ADD KEY meta_key (meta_key(191)); ALTER TABLE
    WPPrefix_commentmeta MODIFY meta_id bigint(20) unsigned NOT NULL
    auto_increment;

```

**SCRIPT 2**

Run the following to identify any tables set to the MyISAM storage engine:

SELECT TABLE\_NAME, ENGINE FROM information\_schema.TABLES WHERE TABLE\_SCHEMA = 'database' and ENGINE = 'myISAM'

Use the following to alter any tables identified in the above script:

ALTER TABLE WPPrefix\_comments ENGINE=InnoDB;

---

Finally, thank you everyone - I am loving CiviCRM etc and its all new to me! If I have stated anything incorrectly then I apologise because I am by no means an expert with any of this so please use any of my notes as may fit.

Thanks, Lee Cullinan
> 1 votes

# Answer
Do you have advanced logging (**Administer menu » System Settings » Misc**) on? If so can you turn it off before upgrading and try enabling it after upgrade?
> 2 votes

---
Tags: wordpress, upgrade
---


# Question
Title: Composer 2 and CiviCRM Installation Error
I am trying to install CiviCRM on a Drupal 8 site using Composer 2. I am getting the error below. Is this a Composer 2 issue? Do I need to revert to Composer 1? Is there a different fix? Thx.

=======================================

composer require civicrm/civicrm-{core,packages,drupal-8}:'~5.36'

./composer.json has been updated Running composer update civicrm/civicrm-core civicrm/civicrm-packages civicrm/civicrm-drupal-8 Loading composer repositories with package information Updating dependencies Your requirements could not be resolved to an installable set of packages.

Problem 1 \- civicrm/civicrm-core\[5.36.0, ..., 5.37.x-dev\] require symfony/finder ~3.0 || ~4.4 -\> found symfony/finder\[v3.0.0-BETA1, ..., 3.4.x-dev, v4.4.0-BETA1, ..., 4.4.x-dev\] but the package is fixed to v5.2.4 (lock file version) by a partial update and that version does not match. Make sure you list it as an argument for the update command. \- Root composer.json requires civicrm/civicrm-core ~5.36 -\> satisfiable by civicrm/civicrm-core\[5.36.0, 5.36.1, 5.36.x-dev, 5.37.x-dev\].

Use the option --with-all-dependencies (-W) to allow upgrades, downgrades and removals for packages currently locked to specific versions.

Installation failed, reverting ./composer.json and ./composer.lock to their original content.

# Answer
As per comments there were some leftover installed vendor files and removing composer.lock and the entire vendor folder and also web/modules/contrib/civicrm and then trying again clears it.
> 2 votes

---
Tags: drupal, composer
---


# Question
Title: configure URLs in system workflow messages
I've discovered that the URL that is created in the Pledge Acknowledgement and Reminder system workflow messages points to our admin site rather than the public site on our Joomla site. In other words, the url starts with "domain.org/administrator/" rather than "domain.org/index.org" This may be a configuration problem somewhere, or perhaps a bug in how Core handles Joomla urls. Any help on how I can fix this?

One fix that I've tried is to just remove the crmURL call in the smarty template, and replace it with text and simple smarty tokens, but I'm not sure how to access the pledge\_id in this case.

Joomla 3.9.24, Civi 5.33.2

# Answer
> 0 votes
It's entirely possible the stock template doesn't account for this. There are some cryptic parameters you can add to crmURL to force a frontend or backend url, respectively: `fe=1` or `fb=1`. (That's not a typo - fe=frontend, fb=forceBackend)

I don't use joomla but if that works for you can you either make a lab ticket at https://lab.civicrm.org/dev/joomla/-/issues/new or if you can make a PR against core.

---
Tags: smarty, system-workflow-messages, pledges
---


# Question
Title: Which tokens are available for activity?
I want to send a reminder to the assignee of an activity and I want to add in the message the phone number (preventing the assignee to log into CRM and spare clicks).

I found these tokens from here:

```
{activity.activity_id}
{activity.subject}
{activity.details}
{activity.activity_date_time}

```

and they work properly with CiviRules but they are not enough. It would be great to have extra tokens with e-mail, phone number and a few others.

Are they available right now or could they be available? If needed we could write an extension.

# Answer
You can use smarty to get contact details of the target of the activity.

https://civicrm.stackexchange.com/a/31516/8748

snippet:

```
{if $contact.activity_id}
  {crmAPI var='activityResult' entity='Activity' action='getsingle' return="target_contact_id" id=$contact.activity_id}
  {crmAPI var='contactResult' entity='Contact' action='getsingle' return="phone,email" id=$activityResult.target_contact_id}
  <tr>
    <td>Phone Number</td>
    <td>{$contactResult.phone}</td>
  </tr>
  <tr>
    <td>Email</td>
    <td>{$contactResult.email}</td>
  </tr>
{/if}

```
> 2 votes

# Answer
This extension could possibly help - https://civicrm.org/extensions/activity-tokens
> 2 votes

---
Tags: activities, tokens
---


# Question
Title: looking for a tutorial for installation of civiCRM for wordpress on NAS Synology
Iam looking for a tutorial for installation of civiCRM for wordpress on NAS Synology. Could you hep me. is it possible ? jf briois

# Answer
I'm not aware of any guides for installing on NAS Synology but general installation instructions are at https://docs.civicrm.org/installation/en/latest/

It looks like you could either try to install in the Synology Web Station or create a Linux VM.
> 1 votes

---
Tags: wordpress
---


title: Event Information Page

text:
# Question
Title: Event Information Page
I am trying to change a logo that appears on my event information page (https://integrativeonc.org/index.php?option=com\_civicrm&task=civicrm/event/register&reset=1&id=30). The area in the lower right that says "empowered by ADG creative" is what I am trying to edit. I cannot find where to edit this in Civi. Can someone assist?

Best regards, Erika

# Answer
i think ADG have swopped out the civicrm logo with their own. If so then you can disable this on your system as a whole by going to /civicrm/admin/setting/misc?reset=1 and turn off the 'empowered by civicrm' setting
> 2 votes

# Answer
Welcome to SE! I agree with Pete about how the logo is being added. If you want to disable it on all public forms then change the option to 'No'.

However, if you want to keep 'empowered by' but change the logo then find the file `templates/adgcreative/css/civicrm.css` and change the image file on line 3 that currently reads `url(../images/adg-creative-sm.png)`. Alternatively comment out that first rule to go back to the normal CiviCRM logo.
> 1 votes

---
Tags: civievent
---


title: Membership Payment

text:
# Question
Title: Membership Payment
I am looking into using CiviCRM for my organization, which matches babysitters and pet-sitters to healthcare workers. I noticed that members must pay to use CiviCRM. I am wondering whether our clients (healthcare workers) would need to sign up and pay for a membership if we were use to use CiviCRM for our organization. Thanks!

# Answer
> 2 votes
You can use CiviCRM without using the Membership module at all, or you can add free Memberships.

That said, I may be mis-reading your comment. CiviCRM is free open source software, so there is no 'you must pay to use civicrm' but there are often costs associated with 'freedom' namely hosting, specialist configuration, etc but there is no 'user fee' per se for CiviCRM. Sorry if i am misunderstanding.

---
Tags: civimember, payment
---


# Question
Title: Can you pull the emails collected through a "form" and dump them into a Civi CRM group?
If I create a form that will collect email addresses, can those email addresses be exported or added to a Civi CRM group for later mailings? I am trying to find a way to collect emails on the front end of our website then add them tour interest group mailing list on the back end without having to manually create a new contact that will then be able to be added to the group.

# Answer
> 3 votes
Welcome to SE! There are several ways to add email addresses to a mailing list. See this section in the User Guide.

What CMS are you using? On Drupal, you can use webforms. On WordPress you can use Caldera Forms or Contact Form 7

The GDPR extension also provides a page for people to choose which groups they want to belong to.

# Answer
> 3 votes
You can do all this with a profile. It has options in the advanced section to create a contact from the form and also to add the contact to a specific group. If you are wanting sign to multiple groups you will need to explore one of the other answers.

# Answer
> 2 votes
A couple of other ideas to add to Aidan's suggestions.

If you make your Group 'public' and then add the Group field to a profile, the Group will be listed for folk to tick. (it will list all 'public' groups, so if you only have one then you are sorted)

You can also just add a custom field eg 'newsletter sign up' = 'yes' and then build a Smart Group

---
Tags: civigroup
---


title: Event Report missing info

text:
# Question
Title: Event Report missing info
I set up an event and asked participants to answer a few questions. How do I pull a report for my event with the answers to the questions?

# Answer
If you used custom fields to store answers for the questions than you can use Event Participants List( CiviCRM \>\> Reports \>\> Event reports) report to get all the information gathered for the event.

Note: Make sure you have is searchable option turned on for all the custom fields you need to be available on report or search.
> 1 votes

---
Tags: civievent
---


# Question
Title: Change Log Tab Excrutiatingly Slow - Poorly Performing Query and Fix (from 8 minutes down to 4 seconds)
The page takes about 8 minutes to return which is absurdly slow for anything expected to be remotely interactive. It is so slow that my client is referring to it as the "Triangle of Doom".

I tracked it down to this query:

```
INSERT IGNORE INTO civicrm_tmp_e_logsummary_ffa3ac146126d178ade367f2a5d17bf5
SELECT activity_id,  IF (entity_log_civireport.log_action = 'Insert' AND extra_table.activity_type_id = 51 , GROUP_CONCAT(entity_log_civireport.contact_id), 1) , entity_log_civireport.log_action as log_civicrm_entity_log_action, 'log_civicrm_activity_contact' as log_civicrm_entity_log_type, entity_log_civireport.log_user_id as log_civicrm_entity_log_user_id, entity_log_civireport.log_date as log_civicrm_entity_log_date, modified_contact_civireport.display_name as log_civicrm_entity_altered_contact, modified_contact_civireport.id as log_civicrm_entity_altered_contact_id, entity_log_civireport.log_conn_id as log_civicrm_entity_log_conn_id, modified_contact_civireport.is_deleted as log_civicrm_entity_is_deleted, altered_by_contact_civireport.display_name as altered_by_contact_display_name

FROM            staging_civicrm.log_civicrm_activity_contact entity_log_civireport
JOIN            civicrm_contact modified_contact_civireport ON (entity_log_civireport.contact_id = modified_contact_civireport.id )
JOIN            staging_civicrm.log_civicrm_activity extra_table ON extra_table.id = entity_log_civireport.activity_id
LEFT JOIN       civicrm_contact altered_by_contact_civireport ON (entity_log_civireport.log_user_id = altered_by_contact_civireport.id)
WHERE           modified_contact_civireport.id = 338520 AND
                entity_log_civireport.log_action != 'Initialization'
GROUP BY        entity_log_civireport.log_conn_id,
                entity_log_civireport.log_user_id,
                EXTRACT(DAY_MICROSECOND FROM entity_log_civireport.log_date),
                entity_log_civireport.id
ORDER BY        entity_log_civireport.log_date DESC;

```

EXPLAIN shows:

```
+------+-------------+-------------------------------+--------+---------------+---------+---------+---------------------------------------------------+---------+-------------------------------------------------+
| id   | select_type | table                         | type   | possible_keys | key     | key_len | ref                                               | rows    | Extra                                           |
+------+-------------+-------------------------------+--------+---------------+---------+---------+---------------------------------------------------+---------+-------------------------------------------------+
|    1 | SIMPLE      | modified_contact_civireport   | const  | PRIMARY       | PRIMARY | 4       | const                                             | 1       | Using temporary; Using filesort                 |
|    1 | SIMPLE      | extra_table                   | ALL    | NULL          | NULL    | NULL    | NULL                                              | 3014020 |                                                 |
|    1 | SIMPLE      | entity_log_civireport         | ALL    | NULL          | NULL    | NULL    | NULL                                              | 5518537 | Using where; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | altered_by_contact_civireport | eq_ref | PRIMARY       | PRIMARY | 4       | staging_civicrm.entity_log_civireport.log_user_id | 1       | Using where                                     |
+------+-------------+-------------------------------+--------+---------------+---------+---------+---------------------------------------------------+---------+-------------------------------------------------+

```

# Answer
The fix passes:

1. First pass:

ALTER TABLE log\_civicrm\_activity\_contact ADD INDEX index\_activity\_id (activity\_id);

With no further changes, this alone makes the above query go from 8 minutes to 1m45s.

Explain plain:

```
+------+-------------+-------------------------------+--------+-------------------+-------------------+---------+---------------------------------------------------+---------+---------------------------------+
| id   | select_type | table                         | type   | possible_keys     | key               | key_len | ref                                               | rows    | Extra                           |
+------+-------------+-------------------------------+--------+-------------------+-------------------+---------+---------------------------------------------------+---------+---------------------------------+
|    1 | SIMPLE      | modified_contact_civireport   | const  | PRIMARY           | PRIMARY           | 4       | const                                             | 1       | Using temporary; Using filesort |
|    1 | SIMPLE      | extra_table                   | ALL    | NULL              | NULL              | NULL    | NULL                                              | 3014020 | Using where                     |
|    1 | SIMPLE      | entity_log_civireport         | ref    | index_activity_id | index_activity_id | 5       | staging_civicrm.extra_table.id                    | 1       | Using where                     |
|    1 | SIMPLE      | altered_by_contact_civireport | eq_ref | PRIMARY           | PRIMARY           | 4       | staging_civicrm.entity_log_civireport.log_user_id | 1       | Using where                     |
+------+-------------+-------------------------------+--------+-------------------+-------------------+---------+---------------------------------------------------+---------+---------------------------------+

```
2. Second pass:

ALTER TABLE log\_civicrm\_activity ADD INDEX index\_id (id);

Change the JOIN order explicitly and add a hint for the query optimizer to not re-order the JOINs:

```
SELECT STRAIGHT_JOIN activity_id,  IF (entity_log_civireport.log_action = 'Insert' AND extra_table.activity_type_id = 51 , GROUP_CONCAT(entity_log_civireport.contact_id), 1) , entity_log_civireport.log_action as log_civicrm_entity_log_action, 'log_civicrm_activity_contact' as log_civicrm_entity_log_type, entity_log_civireport.log_user_id as log_civicrm_entity_log_user_id, entity_log_civireport.log_date as log_civicrm_entity_log_date, modified_contact_civireport.display_name as log_civicrm_entity_altered_contact, modified_contact_civireport.id as log_civicrm_entity_altered_contact_id, entity_log_civireport.log_conn_id as log_civicrm_entity_log_conn_id, modified_contact_civireport.is_deleted as log_civicrm_entity_is_deleted, altered_by_contact_civireport.display_name as altered_by_contact_display_name

FROM            civicrm_contact modified_contact_civireport
JOIN            staging_civicrm.log_civicrm_activity_contact entity_log_civireport      ON entity_log_civireport.contact_id = modified_contact_civireport.id
JOIN            staging_civicrm.log_civicrm_activity extra_table                        ON extra_table.id = entity_log_civireport.activity_id

LEFT JOIN       civicrm_contact altered_by_contact_civireport                           ON entity_log_civireport.log_user_id = altered_by_contact_civireport.id
WHERE           modified_contact_civireport.id = 338520 AND
                entity_log_civireport.log_action != 'Initialization'
GROUP BY        entity_log_civireport.log_conn_id,
                entity_log_civireport.log_user_id,
                EXTRACT(DAY_MICROSECOND FROM entity_log_civireport.log_date),
                entity_log_civireport.id
ORDER BY        entity_log_civireport.log_date DESC;

```

New EXPLAIN:

```
+------+-------------+-------------------------------+--------+-------------------+----------+---------+---------------------------------------------------+---------+---------------------------------+
| id   | select_type | table                         | type   | possible_keys     | key      | key_len | ref                                               | rows    | Extra                           |
+------+-------------+-------------------------------+--------+-------------------+----------+---------+---------------------------------------------------+---------+---------------------------------+
|    1 | SIMPLE      | modified_contact_civireport   | const  | PRIMARY           | PRIMARY  | 4       | const                                             | 1       | Using temporary; Using filesort |
|    1 | SIMPLE      | entity_log_civireport         | ALL    | index_activity_id | NULL     | NULL    | NULL                                              | 5518537 | Using where; Using filesort     |
|    1 | SIMPLE      | extra_table                   | ref    | index_id          | index_id | 5       | staging_civicrm.entity_log_civireport.activity_id | 1       |                                 |
|    1 | SIMPLE      | altered_by_contact_civireport | eq_ref | PRIMARY           | PRIMARY  | 4       | staging_civicrm.entity_log_civireport.log_user_id | 1       | Using where                     |
+------+-------------+-------------------------------+--------+-------------------+----------+---------+---------------------------------------------------+---------+---------------------------------+

```

This gets it down to 4 seconds!

Since the first index we started with is no longer getting used in the final variant, we can just not add it.

Summary:

To fix "Change Log" tab taking forever to load, the following fix is needed:

1. Add index:

```
ALTER TABLE log_civicrm_activity ADD INDEX index_id (id);

```
2. Make the code emit the query modified as follows:

```
INSERT IGNORE INTO civicrm_tmp_e_logsummary_ffa3ac146126d178ade367f2a5d17bf5
SELECT STRAIGHT_JOIN activity_id,  IF (entity_log_civireport.log_action = 'Insert' AND extra_table.activity_type_id = 51 , GROUP_CONCAT(entity_log_civireport.contact_id), 1) , entity_log_civireport.log_action as log_civicrm_entity_log_action, 'log_civicrm_activity_contact' as log_civicrm_entity_log_type, entity_log_civireport.log_user_id as log_civicrm_entity_log_user_id, entity_log_civireport.log_date as log_civicrm_entity_log_date, modified_contact_civireport.display_name as log_civicrm_entity_altered_contact, modified_contact_civireport.id as log_civicrm_entity_altered_contact_id, entity_log_civireport.log_conn_id as log_civicrm_entity_log_conn_id, modified_contact_civireport.is_deleted as log_civicrm_entity_is_deleted, altered_by_contact_civireport.display_name as altered_by_contact_display_name

FROM            civicrm_contact modified_contact_civireport
JOIN            staging_civicrm.log_civicrm_activity_contact entity_log_civireport      ON entity_log_civireport.contact_id = modified_contact_civireport.id
JOIN            staging_civicrm.log_civicrm_activity extra_table                        ON extra_table.id = entity_log_civireport.activity_id

LEFT JOIN       civicrm_contact altered_by_contact_civireport                           ON entity_log_civireport.log_user_id = altered_by_contact_civireport.id
WHERE           modified_contact_civireport.id = 338520 AND
                entity_log_civireport.log_action != 'Initialization'
GROUP BY        entity_log_civireport.log_conn_id,
                entity_log_civireport.log_user_id,
                EXTRACT(DAY_MICROSECOND FROM entity_log_civireport.log_date),
                entity_log_civireport.id
ORDER BY        entity_log_civireport.log_date DESC;

```

Not only does this speed it up from 8 minutes down to 4 seconds, it also doesn't wreak havoc with row locking where every row scanned gets locked by the transaction engine, potentially resulting in a massive query pile-up in the database.
> 1 votes

---
Tags: database, mysql, issues, performance, mariadb
---


# Question
Title: Multiple Participants data entry on event not working
I have an event setup to allow registration of multiple participants but the participant number drop down does not show

The following html is within the page so ought to show

```
<select onchange="allowParticipant()" name="additional_participants" id="additional_participants" class="crm-form-select">
    <option value="">1</option>
    <option value="1">2</option>
    <option value="2">3</option>
</select>

```

I have tried on multiple themes

Php 7.3.27 Civicrm 5.36.0 Drupal 7.78

# Answer
> 2 votes
Eventually I discovered a {display:none} css style in a custom.css that was being applied to this field

# Answer
> 1 votes
Could you check if the field is hidden by doing inspect element on browser? It may be possible that the field is hidden by your custom css (`display : none`). If so you need to over-write the css to `display : block` or remove the line from css file

---
Tags: civievent
---


# Question
Title: For new Contributions entered manually, how do I add an "InMemoriam" section to the manual data entry form?
We occasionally receive donation cheques in memory of someone. For the public Contribution Page I've created a profile which asks for the particulars (name, rank, other info)of the deceased but I can't figure out how to display that profile when our volunteers are entering information **manually**. CiviCRM 5.35.0. on WordPress 5.7.1 | PHP 7.3.27 | Apache | MySQL 5.7.23-23

# Answer
If I understood your question correctly... Profiles that are assigned to Contributions display on the New Contribution input page but they are collapsed by default. We have a custom Profile "Other Details". My first screenshot shows it collapsed and the second one shows it opened.

If you do not see it there, then go into your Profile setting and make sure it is assigned to Contributions.
> 0 votes

---
Tags: civicontribute
---


# Question
Title: Cannot Require Once When Loading CiviCRM After Drupal Upgrade
I am trying to upgrade to the newest security release of Drupal 8 (8.9.14). After completing the update (Drupal is bootstrapping because the login page works) but CiviCRM stops being able to require specific files. The full error is:

```
PHP Fatal error:  require_once(): Failed opening required 'DB/DataObject.php' (include_path='.:/var/www/site_name/vendor/civicrm/civicrm-core:/var/www/site_name/vendor/civicrm/civicrm-core/packages:.:/var/www/site_name/vendor/civicrm/civicrm-core/:/var/www/site_name/vendor/civicrm/civicrm-core//packages:/var/www/site_name/vendor/civicrm/civicrm-core/vendor/tecnickcom:/var/www/site_name/vendor/pear/archive_tar:/var/www/site_name/vendor/pear/auth_sasl:/var/www/site_name/vendor/pear/console_getopt:/var/www/site_name/vendor/pear/db:/var/www/site_name/vendor/pear/log:/var/www/site_name/vendor/pear/mail:/var/www/site_name/vendor/pear/mail_mime:/var/www/site_name/vendor/pear/net_smtp:/var/www/site_name/vendor/pear/net_socket:/var/www/site_name/vendor/pear/pear-core-minimal/src:/var/www/site_name/vendor/pear/pear_exception:/var/www/site_name/vendor/pear/validate_finance_creditcard:.:/usr/share/pear:/usr/share/php') in /var/www/site_name/vendor/civicrm/civicrm-core/CRM/Core/DAO.php on line 26, referer: https://site.org/user/login

```

I tried adding the full relative path to the file (../../../civicrm-packages/DB/DataObject.php) and this does not stop the problem.

My CiviCRM root is,

```
$civicrm_paths['civicrm.root']['path'] = '/var/www/site_folder/vendor/civicrm/';

```

The variable is

```
$civicrm_root = '/var/www/site_folder/vendor/civicrm/civicrm-core/';

```

I tried changing the path for the array in \['civicrm.root'\]\['path'\] but this did not do anything.

I tried upgrading CiviCRM too. That did not work either.

I have figured out that the issue is not related to the Drupal upgrade itself but rather to the packages being upgraded with Drupal.

* symfony/debug (v4.4.20 =\> v4.4.22):
* symfony/var-dumper (v4.4.21 =\> v4.4.22):
* guzzlehttp/psr7 (1.8.1 =\> 1.8.2)
* doctrine/cache (1.10.2 =\> 1.11.0)
* doctrine/inflector (1.4.3 =\> 1.4.4)
* laminas/laminas-feed (2.14.0 =\> 2.14.1)

I have not narrowed down which library is the issue.

I appreciate any help figuring this out.

Thanks, Josh

# Answer
> 0 votes
The `$civicrm_paths['civicrm.root']['path']` should match with $civicrm\_root,

can you try by updating your `$civicrm_paths['civicrm.root']['path']` to

```
$civicrm_paths['civicrm.root']['path'] = '/var/www/networkpaao/vendor/civicrm/civicrm-core/';

```

---
Tags: upgrade, civicrm.settings.php
---


title: Search custom field by name

text:
# Question
Title: Search custom field by name
if I want to get a value of a custom field, now I have to use `custom_xx`, like this:

```
$result = civicrm_api3('Contact', 'getvalue', [
   'return' => "custom_11",
   'id' => 123,
]);

```

As `return` I'd like to use the name of field (`civicrm_custom_field.name`) instead of id. Is it possibile?

Thanks.

# Answer
You can use api 4 if you want to use as the name

```
$contacts = civicrm_api4('Contact', 'get', [
  'select' => [
    'constituent_information.Most_Important_Issue',
  ],
  'where' => [
    ['id', '=', 123],
  ],
]);

```
> 1 votes

---
Tags: custom-fields
---


# Question
Title: When unchecking VERP option I am getting an error "Boolean value required for track_civimail_replies"
When I go to Administer \> CiviMail \> CiviMail Component Settings. There is an option "Track replies using VERP in Reply-To header"

When I uncheck this box and hit Save I am getting an error message "Boolean value required for track\_civimail\_replies" Does anyone know what this means and how I can successfully turn of VERP?

I am using CiviCRM 5.36.1 Thanks

# Answer
> 2 votes
Not similar, but related issue is already logged on gitlab issue. enter link description here and has been fixed and will be available on 5.37 release.

You can either wait for 5.37 release or apply patch.

Alternatively you can add below line in civicrm.settings.php to turn off VERP setting.

```
global $civicrm_setting;
$civicrm_setting['Mailing Preferences']['track_civimail_replies'] = FALSE;

```

---
Tags: civimail
---