title: Scheduled Reminders no longer sending

text:
# Question
Title: Scheduled Reminders no longer sending
Scheduled email reminders were working fine on my Drupal/CiviCRM server, then suddenly stopped working, with the last successful mail being sent on 2/11. I don't recall making any significant changes around that time. This is not a cron issue, as the job is executing regularly, it's just returning the following error when it does run.

```
Mar 24 13:12:33  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => SELECT *
FROM civicrm_event r
INNER JOIN civicrm_event r ON e.event_id = r.id
INNER JOIN civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0
WHERE (r.id IN ("456")) AND (r.is_active = 1) AND (r.is_template = 0) AND ("20210324131233" <= DATE_SUB(start_date, INTERVAL 11 hour))
LIMIT 1
OFFSET 0
 [nativecode=1066 ** Not unique table/alias: 'r']
    [type] => DB_Error
    [user_info] => SELECT *
FROM civicrm_event r
INNER JOIN civicrm_event r ON e.event_id = r.id
INNER JOIN civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0
WHERE (r.id IN ("456")) AND (r.is_active = 1) AND (r.is_template = 0) AND ("20210324131233" <= DATE_SUB(start_date, INTERVAL 11 hour))
LIMIT 1
OFFSET 0
 [nativecode=1066 ** Not unique table/alias: 'r']
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT *
FROM civicrm_event r
INNER JOIN civicrm_event r ON e.event_id = r.id
INNER JOIN civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0
WHERE (r.id IN ("456")) AND (r.is_active = 1) AND (r.is_template = 0) AND ("20210324131233" <= DATE_SUB(start_date, INTERVAL 11 hour))
LIMIT 1
OFFSET 0
 [nativecode=1066 ** Not unique table/alias: 'r']"]
)

Mar 24 13:12:33  [debug] $backTrace = #0 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/Error.php(942): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB.php(997): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "SELECT *\nFROM civicrm_event r\nINN\
ER JOIN civicrm_event r ON e.event_id = r....")
#3 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "SELECT *\nFROM civicrm_event r\nINNER JOIN \
civicrm_event r ON e.event_id = r....")
#4 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR::_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "SELECT *\nFROM civicrm\
_event r\nINNER JOIN civicrm_event r ON e.event_id = r....", "DB_Error", TRUE)
#5 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1928): PEAR->__call("raiseError", (Array:7))
#6 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(936): DB_common->raiseError(-1, NULL, NULL, "SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r O\
N e.event_id = r....", "1066 ** Not unique table/alias: 'r'")
#7 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /home/vectorspace/www/www/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1234): DB_mysqli->simpleQuery("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id =\
 r....")
#9 /home/vectorspace/www/www/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common->query("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id = r....")
#10 /home/vectorspace/www/www/sites/all/modules/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id = r\
....")
#11 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/DAO.php(457): DB_DataObject->query("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id = r....")
#12 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/DAO.php(1564): CRM_Core_DAO->query("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r ON e.event_id = r....", TRUE)
#13 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/ActionSchedule/RecipientBuilder.php(251): CRM_Core_DAO::executeQuery("SELECT *\nFROM civicrm_event r\nINNER JOIN civicrm_event r\
 ON e.event_id = r....")
#14 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/ActionSchedule/RecipientBuilder.php(150): Civi\ActionSchedule\RecipientBuilder->buildAddlRepeatPass()
#15 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/BAO/ActionSchedule.php(353): Civi\ActionSchedule\RecipientBuilder->build()
#16 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/BAO/ActionSchedule.php(371): CRM_Core_BAO_ActionSchedule::buildRecipientContacts("3", "20210324131233", (Array:2))
#17 /home/vectorspace/www/www/sites/all/modules/civicrm/api/v3/Job.php(207): CRM_Core_BAO_ActionSchedule::processQueue("20210324131233", (Array:2))
#18 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_job_send_reminder((Array:2))
#19 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Kernel.php(150): Civi\API\Provider\MagicFunctionProvider->invoke((Array:8))
#20 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Kernel.php(81): Civi\API\Kernel->runRequest((Array:8))
#21 /home/vectorspace/www/www/sites/all/modules/civicrm/api/api.php(22): Civi\API\Kernel->runSafe("Job", "send_reminder", (Array:1))
#22 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/JobManager.php(134): civicrm_api("Job", "send_reminder", (Array:1))
#23 /home/vectorspace/www/www/sites/all/modules/civicrm/CRM/Core/JobManager.php(66): CRM_Core_JobManager->executeJob(Object(CRM_Core_ScheduledJob))
#24 /home/vectorspace/www/www/sites/all/modules/civicrm/api/v3/Job.php(118): CRM_Core_JobManager->execute(FALSE)
#25 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_job_execute((Array:1))
#26 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Kernel.php(150): Civi\API\Provider\MagicFunctionProvider->invoke((Array:8))
#27 /home/vectorspace/www/www/sites/all/modules/civicrm/Civi/API/Kernel.php(81): Civi\API\Kernel->runRequest((Array:8))
#28 /home/vectorspace/www/www/sites/all/modules/civicrm/api/api.php(22): Civi\API\Kernel->runSafe("job", "execute", (Array:1))
#29 phar:///home/vectorspace/private/bin/cv/src/Command/ApiCommand.php(74): civicrm_api("job", "execute", (Array:1))
#30 phar:///home/vectorspace/private/bin/cv/vendor/symfony/console/Command/Command.php(257): Civi\Cv\Command\ApiCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Sy\
mfony\Component\Console\Output\ConsoleOutput))
#31 phar:///home/vectorspace/private/bin/cv/vendor/symfony/console/Application.php(850): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Ob\
ject(Symfony\Component\Console\Output\ConsoleOutput))
#32 phar:///home/vectorspace/private/bin/cv/vendor/symfony/console/Application.php(193): Symfony\Component\Console\Application->doRunCommand(Object(Civi\Cv\Command\ApiCommand), Object(Symfo\
ny\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#33 phar:///home/vectorspace/private/bin/cv/src/Application.php(46): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\
\Console\Output\ConsoleOutput))
#34 phar:///home/vectorspace/private/bin/cv/vendor/symfony/console/Application.php(124): Civi\Cv\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Compone\
nt\Console\Output\ConsoleOutput))
#35 phar:///home/vectorspace/private/bin/cv/src/Application.php(15): Symfony\Component\Console\Application->run()
#36 phar:///home/vectorspace/private/bin/cv/bin/cv(27): Civi\Cv\Application::main("phar:///home/vectorspace/private/bin/cv/bin")
#37 /home/vectorspace/private/bin/cv(14): require("phar:///home/vectorspace/private/bin/cv/bin/cv")
#38 {main}

```

When I disable event 455 and manually execute the scheduled reminders, the same type of error occurs but for event 454, so I don't believe it has to do with event 455. Is it something to do with cv? I installed cv many more months ago and haven't made any changes to it. Your help is appreciated!

# Answer
This appears when you have schedule reminder with is repeat ON and 'Also Include' Recipient set. Try applying below patch

```
diff --git a/CRM/Event/ActionMapping.php b/CRM/Event/ActionMapping.php
index 9c8aaae7a5..0874099eb0 100644
--- a/CRM/Event/ActionMapping.php
+++ b/CRM/Event/ActionMapping.php
@@ -136,7 +136,7 @@ class CRM_Event_ActionMapping extends \Civi\ActionSchedule\Mapping {
     $selectedStatuses = (array) \CRM_Utils_Array::explodePadded($schedule->entity_status);

     $query = \CRM_Utils_SQL_Select::from("{$this->entity} e")->param($defaultParams);
-    $query['casAddlCheckFrom'] = 'civicrm_event r';
+    $query['casAddlCheckFrom'] = 'civicrm_participant e';
     $query['casContactIdField'] = 'e.contact_id';
     $query['casEntityIdField'] = 'e.id';
     $query['casContactTableAlias'] = NULL;

```
> 3 votes

---
Tags: scheduled-reminders, cv
---


# Question
Title: How do I eliminate the "civi Dashboard" tab
So far, I've used civiCRM to build my database. I'm about to go public on registration for an event and I don't want registrants to see the "civi Dashboard" tab.

I have my civiCRM on Drupal and I was told the secret lies in Drupal configuration but I can't find the right switch to turn it off.

# Answer
May be turning off 'CiviCRM: access Contact Dashboard' permission for user role should help
> 2 votes

---
Tags: dashboard
---


# Question
Title: Getting an error while viewing an activity. How to resolve it?
> \`

Blockquote

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. You are not authorized to access this page. \`

# Answer
The logged in user doen't have permission to view the activity. Does the activity type belong to Case? Or Non-Contact component? Do the user have edit all contact permission?
> 4 votes

---
Tags: drupal, activities
---


# Question
Title: Wordpress install fails : Fatal Error: Uncaught
I'm having an issue installing civicrm against a WordPress install. Plugin is installed. wp-content\>uploads\>civicrm is present, writable, and empty. I have specified a separate database to use. However, I was encountering the same issue when I tried to install against the WP database. This is the error I am receiving:

---

## Fatal error: Uncaught Civi\Setup\Exception\SqlException: 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: Specified key was too long; max key length is 767 bytes in /var/www/vhosts/******.org/httpdocs/wp-content/plugins/ in /var/www/vhosts/******.org/httpdocs/wp-content/plugins/civicrm/civicrm/setup/src/Setup/DbUtil.php on line 204

Any thoughts on where to start? Happy to provide any info needed to help troubleshoot. Thanks in advance.

# Answer
> 1 votes
Welcome to SE CiviCRM

You will need the following MySQL server configuration:

```
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

```

add above configuration in your my.cnf file.

See mysql requirement for CiviCRM

---
Tags: wordpress
---


# Question
Title: Can one show participantID on event reminder?
We're intending to use an event's participant ID as a pseudo-registration code, i.e. a quick way for us to correlate who shows up to events. Because of that, we are asking our event participants to know their participant ID when they arrive.

So far, we've adapted the `ThankYou.tpl` event registration confirmation page to show the user their participant ID by adding the following:

```
<p><strong>{ts 1=$participantIDs.0}Your registration number is: %1{/ts}</strong></p>

```

and we've adjusted the template for the event registration email to show `$participantID` as well (inside `Mailings -> Message Templates -> System Workflow Messages -> Events - Registration Confirmation and Receipt (on-line)`).

Now, we'd like to add that value on to a reminder message. We've set up the reminder template under `Mailings -> Message Templates -> User driven messages` and are able to get the reminders to send correctly, but it's not clear how to get the participant ID to show up. It's certainly not listed within the available tokens.

Is there a way to add a participant id to a template there? Can I link participant\_id to the available tokens in some way?

# Answer
> 1 votes
Unless writing an extension implementing token processor its not possible to fetch the participant id in reminders.

If you familiar with smarty and the contact will have only one participant for a event than you can try below snippet in your reminder message template

```
{capture assign=eventID}{event.event_id}{/capture}
{capture assign=contactId}{contact.contact_id}{/capture}

{crmAPI var='result' entity='Participant' action='getvalue' sequential=0 return="id" event_id=$eventID contact_id=$contactId}
{foreach from=$result.values item=participant}
 Participant Id - {$participant.id}
{/foreach}

```

---
Tags: civievent, scheduled-reminders, templates
---


# Question
Title: Best practice for sending a notice about an upcoming recurring contribution
We have a contribution page that creates automated recurring contributions on a yearly basis.

Because 12 months is a long time to remember that you have a recurring contribution set up, we want to send out a courtesy email 2 weeks before to let people know that we are going to take a payment and give them the opportunity to cancel it if they want to.

Is anyone else doing this? How do you do it? A scheduled reminder based on `civicrm_contribution_recur.next_sched_contribution_date` would be nice but I know that isn't possible at the mo. Are there any other ways people are doing this?

PS. I know that we could use membership end date if they were members but we don't want to go down that route - we want to tie it to the recurring contribution.

# Answer
CiviRules + emailapi extension. Scheduled reminders is too inflexible as you can't control the criteria - with CiviRules you can build in whatever conditions you like. Looks like you'd need to add a new "CRON trigger" to CiviRules for recurring contributions but we already have that for activities and for memberships so would be pretty easy to do and will help a lot of people.

With that you can build "reminders" that can target specific paymentprocessors, specific contact types etc.
> 4 votes

# Answer
I feel it should be doable using reminder screen via extension by implementing token hooks and Civi listener.
> 1 votes

---
Tags: recurring-contributions, scheduled-reminders
---


# Question
Title: How to add/remove search fields from the search box dropdown in the navbar?
In the CiviCRM global menu there is a nice search box on the left. This box allows for targeting specific fields via radio buttons in a dropdown, like this:

Is it possible to remove or change the fields that appear here?

Can custom contact fields be added to these options?

# Answer
The quick answer is "no."

For some back-story, that feature was contributed (circa 4.3) by a volunteer from the community who was enough of a coder to be able to get it to work, but not really a CiviCRM architect. His implementation was done by hard-coding that list of fields directly into the template.

The result of his good-faith effort is that:

* We have the feature (which IMO is a step up from previously not having it).
* It's not flexible or extensible.

I've been thinking that it would be a great little project for someone to add a setting to the "display preferences" screen that would allow you to enable/disable fields from that list. Searchable custom fields could be included.

If anyone is interested in taking this on, let me know in the comments and I'm more than happy to help you get started.
> 7 votes

# Answer
As Coleman said, no, for adding fields, but we removed certain fields for our setup with jQuery so our users wouldn't mis-click.

EDIT: I went back to look at the code, and I remembered that we tried removing the fields with simple jQuery .hide() or .remove(), but realized that the standard classes we were trying to use were used elsewhere so it did more damage than good. In trying to use something like a nth-child selector on the li, we ran into trouble as well, though I can't remember what exactly.

We ended up going the custom tpl route by creating a copy of templates/CRM/common/navigation.js.tpl and placing it in our custom templates directory and commenting out of the "li"'s that we didn't want. Beginning around line 40:

```
<!-- <li><label class="crm-quickSearchField"><input type="radio" data-tablename="cc" value="contact_id" name="quickSearchField">{ts}CiviCRM ID{/ts}</label></li> -->
<!-- <li><label class="crm-quickSearchField"><input type="radio" data-tablename="cc" value="external_identifier" name="quickSearchField">{ts}External ID{/ts}</label></li> -->

```

Looking back at it now, it would have been better to slog through and find the right combination of selectors with jQuery. Perhaps something like this:

```
CRM.$(function($) {
    $('#civicrm-menu #crm-qsearch ul li:nth-child(3)').hide();
});

```
> 6 votes

# Answer
Since CiviCRM 5.8 this feature is in core. Before you could use this extension:

https://lab.civicrm.org/extensions/quicksearch

To manage it:

```
Menu Administer / Customize Data and Screens / Search Preferences

/civicrm/admin/setting/search?reset=1

```
> 4 votes

---
Tags: search, global-menu
---


# Question
Title: How to export contact list that includes total contributions to date?
I can only **either** export my contacts, or contributions. Is there a way to export an amalgamated list of contacts including all contributions made by each contact, either that year/per year/within date rage, or total to date?

I tried to use the advanced search, to find contacts within a certain group + with contributions of certain $ amount. When I try to export that list, there is no available field to export "contributions". Please advise!

# Answer
If you add the Summary Fields extension then you can choose to have a field for 'total amount', total this financial year, count this financial year, etc etc, which then makes exporting much easier as it is 'just' another custom field
> 1 votes

# Answer
When you run the advanced search, change the option at the very top called "Display results as" from contact to contributions. That should allow you to export contribution information for the search results. (There should also be basic contact information available when exporting contribution information.)
> 0 votes

---
Tags: civicontribute, contacts, contributions, export
---


# Question
Title: How to update "wpLoadPhp" setting for CiviCRM WordPress when moving/migrating installation?
On my Civi WordPress multisite installation, I just discovered that there is a `wpLoadPhp` setting in the CiviCRM settings *for each Civi domain* that contains the absolute filesystem path to `wp-load.php` (for example: `/home/myusername/sites/mydomain.org/wp-load.php`). So this setting needs to be updated if I move WordPress+Civi to a new location, or move a copy of the Civi database to a development or test installation.

I discovered this setting when the Civi `cv` CLI tool misbehaved—it tried to load settings from the wrong WordPress installation for a `cv api setting.create ...` command. (However, using the WP-CLI `wp cv api setting.create ...` worked correctly. It must override the Civi `wpLoadPhp` setting.)

What is the correct/best way to update the `wpLoadPhp` setting—preferably via a script for automation? Is `wp cv api setting.create ...` best? It seems to work OK: e.g. `wp cv api setting.create wpLoadPhp=/home/myusername/sites/mydomain.org/wp-load.php`.

# Answer
> 2 votes
You can safely ignore this setting unless you're hitting scripts in `civicrm/extern` or `civicrm/bin` or using `cv` directly. In that case I'd recommend switching to routes that go via WordPress e.g. the REST API or `wp cv` instead - they are much more reliable in WordPress.

---
Tags: wordpress, settings, multisite, migration
---


# Question
Title: Civicrm - Expense extension [unable to edit expenses]
Having trouble to figure out whats wrong with the edit expense function as it always return a blank page. What could be the issue?

I extracted the error code from the error log.

My setup environment: Wordpress Civicrm 5.35.1

# Answer
This sounds like it might be a bug in the expenses extension. It's hitting line 121 in CRM/Expenses/Form/Expense.php but `$this->_expense` is a string instead of an array at that point.

It's not clear why it's a string - it gets set at line 46 \- if you add something like `CRM_Core_Error::debug_log_message(var_export($this->_expense, true));` either at line 48 or at line 120 and then check in the ConfigAndLog file it will tell you what it looks like.
> 1 votes

---
Tags: civicase, administer
---


# Question
Title: Self-service cancellation from a registered event seems to be broken
The following error is reproduceable on a fresh install of CiviCRM 5.35.1 on WordPress 5.7. If self-service cancellation is enabled for a simple event, the cancellation or transfer request results in the following error:

CRM\_Core\_Exception: getFieldValue failed in /home/bbcadmin2016/sigstage57.net/wp/wp-content/plugins/civicrm/civicrm/CRM/Event/Form/SelfSvcUpdate.php on line 132

The stack trace indicates this:

CRM\_Core\_DAO::getFieldValue('CRM\_Event\_DAO\_Ev…', null, 'title') /home/bbcadmin2016/sigstage57.net/wp/wp-content/plugins/civicrm/civicrm/CRM/Event/Form/SelfSvcUpdate.php:132

I am reporting this again in the hope that it can be verified by someone else as a bug - something that the core team requests before submitting the issue.

# Answer
> 2 votes
Can you try reproducing on https://wpmaster.demo.civicrm.org/ (I did try and wasn't able to).

Looks like its a environment issue. Could you please check your htaccess or server config? Or WP permlinks?

# Answer
> 1 votes
Turns out the culprit was ModSecurity

---
Tags: civievent, error
---


title: Option Group vs Custom field

text:
# Question
Title: Option Group vs Custom field
New install (civicrm 5.35.1) and I can't find this answer.

We have 22,000 alumni that I've imported. One of the fields is cast year there are about 120 of these so I want to make an option group (it would be taxonomy in other systems I guess). There are multiple uses of this in a contact.

For example, everyone of our alumni has a primary cast. They could also have multiple "secondary" casts where they were staff or several other participants.

I'm trying to map these to a contact through a custom field.

Data sample:

```
1965A
1965B
1988E

```

etc.

Question 1: I can import the data into the database directly if needed, but I'm not sure of all the fields that are listed in the option group table.

Question 2: Assuming I get these entered as an option group, how can I create a custom field that will use this for lookup? And secondarily, how to choose which cast type it is (i.e. primary, secondary, etc.)

In answer to the 2nd part of question 2, I know I can also create an option group of cast\_type, but again, I'm not finding an easy way to have this show and also allow me to import it. It would be similar to phone or address I imagine.

Thanks and please let me know if you have an questions.

# Answer
> 2 votes
First create a custom field of type select or multi-select with empty options. From civicrm\_custom\_field table grab the option\_group\_id for the field.

You can use Option value importer extension to import all the options using csv or have a insert query to insert records in civicrm\_option\_value table using option\_group\_id from civicrm\_custom\_field.option\_group\_id

---
Tags: custom-fields, option
---


# Question
Title: Why doesn't custom search enable "Actions" items for selected records after upgrade to 4.6.3?
I have a custom search extension and it was working fine in 4.4.14. Since I have upgraded to 4.6.3, "Actions" items only enabled for All records. But if I select certain records from result it does not enable "Actions" items. My custom PHP & a screenshot is shown below.

```
    <?php

/**
 * A custom contact search
 */
class CRM_Guestsearch_Form_Search_Guestsearch extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {

  function __construct(&$formValues) {
    parent::__construct($formValues);
  }

  /**
   * Prepare a set of search fields
   *
   * @param CRM_Core_Form $form modifiable
   * @return void
   */
  function buildForm(&$form) {
    CRM_Utils_System::setTitle(ts('Registered Guest (Unassigned)'));

  /**
    $form->add('text', 'name', ts('Name'), TRUE
    );

    $form->setDefaults(array(
      'name' => '',
    ));
     */
    /**
     * if you are using the standard template, this array tells the template what elements
     * are part of the search criteria
     */
    $form->assign('elements', array('name'));
  }

  /**
   * Get a list of summary data points
   *
   * @return mixed; NULL or array with keys:
   *  - summary: string
   *  - total: numeric
   */
  function summary() {
    return NULL;
    // return array(
    //   'summary' => 'This is a summary',
    //   'total' => 50.0,
    // );
  }

  /**
   * Get a list of displayable columns
   *
   * @return array, keys are printable column headers and values are SQL column names
   */
  function &columns() {
    // return by reference
    $columns = array(
      ts('Contact Source') => 'source',
      ts('Name') => 'sort_name',
      ts('Phone') => 'phone',
      ts('Email') => 'email',
      ts('Date of visit') => CIVICRM_GUESTSEARCH_CUSTOM_COLUMN_NAME,
    );
    return $columns;
  }

  /**
   * Construct a full SQL query which returns one page worth of results
   *
   * @param int $offset
   * @param int $rowcount
   * @param null $sort
   * @param bool $includeContactIDs
   * @param bool $justIDs
   * @return string, sql
   */
  function all($offset = 0, $rowcount = 0, $sort = NULL, $includeContactIDs = FALSE, $justIDs = FALSE) {

    return $this->sql($this->select(), $offset, $rowcount, null, $includeContactIDs, NULL);
  }

  /**
   * Construct a SQL SELECT clause
   *
   * @return string, sql fragment with SELECT arguments
   */
  function select() {
    return "
      contact_a.id                  as contact_id,
      contact_a.source              as source,
      contact_a.contact_sub_type    as contact_sub_type,
      contact_a.sort_name           as sort_name,
      phone.phone ,
      email.email  ,
      date.
    " . CIVICRM_GUESTSEARCH_CUSTOM_COLUMN_NAME;
  }

  /**
   * Construct a SQL FROM clause
   *
   * @return string, sql fragment with FROM and JOIN clauses
   */
  function from() {
    return "
      FROM      civicrm_contact contact_a
      LEFT JOIN civicrm_activity_contact a ON (a.contact_id = contact_a.id)
      LEFT JOIN civicrm_phone phone ON (phone.contact_id = contact_a.id)
      LEFT JOIN civicrm_email email ON (email.contact_id = contact_a.id)
      LEFT JOIN " . CIVICRM_GUESTSEARCH_CUSTOM_TABLE_NAME . " date ON (date.entity_id = contact_a.id)
    ";
  }

  /**
   * Construct a SQL WHERE clause
   *
   * @param bool $includeContactIDs
   * @return string, sql fragment with conditional expressions
   */
  function where($includeContactIDs = false) {
    $params = array();

    $where = "contact_a.contact_sub_type = 'Guest' AND contact_a.source = 'Guest Greeter Signup' AND activity_id IS NULL ORDER BY date." . CIVICRM_GUESTSEARCH_CUSTOM_COLUMN_NAME . " ASC ";

    return $where;

  }

  /**
   * Determine the Smarty template for the search screen
   *
   * @return string, template path (findable through Smarty template path)
   */
  function templateFile() {
    return 'CRM/Contact/Form/Search/Custom.tpl';
  }

  /**
   * Modify the content of each row
   *
   * @param array $row modifiable SQL result row
   * @return void
   */

}

```

# Answer
Since the number of selected records hasn't updated when you check the box, that suggests it's possibly a Javascript error. Try the steps in Troubleshooting JavaScript in CiviCRM and post back any new diagnostic info you find.
> 3 votes

# Answer
In 2021 is the same problem. In custom search you can't enable actions. But I find solution. I looked to the js file which count checkboxes and I found out that you just need remove "crm-ajax-selection-form" class from your search form. You can add to your tpl file somethink like this: `<script> {literal} CRM.$(".crm-ajax-selection-form").removeClass("crm-ajax-selection-form"); {/literal} </script>` 2 hours reverse engineering was worth it. :D
> 2 votes

# Answer
I am running into exactly the same problem (ie selecting some records does not activate the "Actions" button. It is happening with a custom search that was generated by "civix". (I only changed the title)

Did anyone find a solution?

Source code is: \`

> /\** * A custom contact search \*/ class CRM\_Financialsummaries\_Form\_Search\_preparestatements extends CRM\_Contact\_Form\_Search\_Custom\_Base implements CRM\_Contact\_Form\_Search\_Interface { function \__construct(&$formValues) { parent::\__construct($formValues); }
>
> /\** * Prepare a set of search fields * * @param CRM\_Core\_Form $form modifiable * @return void \*/ function buildForm(&$form) { CRM\_Utils\_System::setTitle(ts('Prepare Statements'));
>
> ```
> $form->add('text',
>   'household_name',
>   ts('Household Name'),
>   TRUE
> );
>
> $stateProvince = array('' => ts('- any state/province -')) + CRM_Core_PseudoConstant::stateProvince();
> $form->addElement('select', 'state_province_id', ts('State/Province'), $stateProvince);
>
> // Optionally define default search values
> $form->setDefaults(array(
>   'household_name' => '',
>   'state_province_id' => NULL,
> ));
>
> /**
>  * if you are using the standard template, this array tells the template what elements
>  * are part of the search criteria
>  */
> $form->assign('elements', array('household_name', 'state_province_id'));   }
>
> ```
>
> /\** * Get a list of summary data points * * @return mixed; NULL or array with keys: * - summary: string * - total: numeric \*/ function summary() { return NULL; // return array( // 'summary' =\> 'This is a summary', // 'total' =\> 50.0, // ); }
>
> /\** * Get a list of displayable columns * * @return array, keys are printable column headers and values are SQL column names
> \*/ function &columns() { // return by reference $columns = array( ts('Contact Id') =\> 'contact\_id', ts('Contact Type') =\> 'contact\_type', ts('Name') =\> 'sort\_name', ts('State') =\> 'state\_province', ); return $columns; }
>
> /\** * Construct a full SQL query which returns one page worth of results * * @param int $offset * @param int $rowcount * @param null $sort * @param bool $includeContactIDs * @param bool $justIDs * @return string, sql \*/ function all($offset = 0, $rowcount = 0, $sort = NULL, $includeContactIDs = FALSE, $justIDs = FALSE) { // delegate to $this-\>sql(), $this-\>select(), $this-\>from(), $this-\>where(), etc. return $this-\>sql($this-\>select(), $offset, $rowcount, $sort, $includeContactIDs, NULL); }
>
> /\** * Construct a SQL SELECT clause * * @return string, sql fragment with SELECT arguments \*/ function select() { return " contact\_a.id as contact\_id , contact\_a.contact\_type as contact\_type, contact\_a.sort\_name as sort\_name, state\_province.name as state\_province "; }
>
> /\** * Construct a SQL FROM clause * * @return string, sql fragment with FROM and JOIN clauses \*/ function from() { return " FROM civicrm\_contact contact\_a LEFT JOIN civicrm\_address address ON ( address.contact\_id = contact\_a.id AND address.is\_primary = 1 ) LEFT JOIN civicrm\_email ON ( civicrm\_email.contact\_id = contact\_a.id AND civicrm\_email.is\_primary = 1 ) LEFT JOIN civicrm\_state\_province state\_province ON state\_province.id = address.state\_province\_id "; }
>
> /\** * Construct a SQL WHERE clause * * @param bool $includeContactIDs * @return string, sql fragment with conditional expressions \*/ function where($includeContactIDs = FALSE) { $params = array(); $where = "contact\_a.contact\_type = 'Household'";
>
> ```
> $count  = 1;
> $clause = array();
> $name   = CRM_Utils_Array::value('household_name',
>   $this->_formValues
> );
> if ($name != NULL) {
>   if (strpos($name, '%') === FALSE) {
>     $name = "%{$name}%";
>   }
>   $params[$count] = array($name, 'String');
>   $clause[] = "contact_a.household_name LIKE %{$count}";
>   $count++;
> }
>
> $state = CRM_Utils_Array::value('state_province_id',
>   $this->_formValues
> );
> if (!$state &&
>   $this->_stateID
> ) {
>   $state = $this->_stateID;
> }
>
> if ($state) {
>   $params[$count] = array($state, 'Integer');
>   $clause[] = "state_province.id = %{$count}";
> }
>
> if (!empty($clause)) {
>   $where .= ' AND ' . implode(' AND ', $clause);
> }
>
> return $this->whereClause($where, $params);   }
>
> ```
>
> /\** * Determine the Smarty template for the search screen *
> * @return string, template path (findable through Smarty template path) \*/ function templateFile() { return 'CRM/Contact/Form/Search/Custom.tpl'; }
>
> /\** * Modify the content of each row * * @param array $row modifiable SQL result row * @return void \*/ function alterRow(&$row) { $row\['sort\_name'\] .= ' ( altered )'; } }\`
> 0 votes

---
Tags: custom-search
---


# Question
Title: Crash in upgrade to 5.35.1 on task core-issue#365 - Add created_date to civicrm_action_schedule
While upgrading several of my sites to 5.35.1 (from 5.28 or later) I experienced a nasty database crash in the step called *core-issue#365 - Add created\_date to civicrm\_action\_schedule*

```
$Fatal Error Details = Array (
 [callback] => Array ( [0] => CRM_Core_Error [1] => exceptionHandler )
 [code] => -1
 [message] => DB Error: unknown error
 [mode] => 16
 [debug_info] =>
   ALTER TABLE `civicrm_action_schedule`
   ADD COLUMN `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP
               COMMENT 'When was the schedule reminder created.'
   [nativecode=1118 ** Row size too large. The maximum row size for the used
   table type, not counting BLOBs, is 8126. This includes storage overhead,
   check the manual. You have to change some columns to TEXT or BLOBs]

```

What can I do?

(Note in my case this is not related to Upgrade fails with Row size too large error)

# Answer
> 3 votes
Of course

* you ran this on a development copy of the site first, so you're not panicking, right?
* you took a backup before your production upgrade, and can roll back to that?
* you wished you had?

## If you can, revert your database to a pre-upgrade backup

Then run this SQL on the CiviCRM database: `OPTIMIZE TABLE civicrm_action_schedule;`

Then repeat the upgrade.

## If you didn't have a backup

Then run this SQL on the CiviCRM database: `OPTIMIZE TABLE civicrm_action_schedule;`

Then try to resume the upgrade procedure. If you're on the command line you can use `cv upgrade:db` which will probably suggest you run `cv upgrade:db --retry`.

Hopefully it runs through clean for you now.

# Answer
> 3 votes
This was reported for 5.34 and there was PR for the fix but was rejected in favour of environment issue.

Solution was, Before upgrading to 5.34 or later to convert all the tables in civicrm to make ROW\_FORMAT=DYNAMIC.

Include the below config in mysql config file

```
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

```

# Answer
> 0 votes
I do have a backup via drush ard.

However I tried the second of artfulrobot's options, as the steps for the restore are a bit convoluted.

> drush civicrm-upgrade-db

ran with no output.

> drush cache-clear civicrm

It appears things are ok.

---
Tags: upgrade
---


title: I have deleted a contact by mistake

text:
# Question
Title: I have deleted a contact by mistake
I have deleted a contact by mistake. Please explain how I can restore the contact to the database ?

# Answer
> 4 votes
You must have permission to restore deleted contacts. If you don't, please ask someone who does.

* Go to **Search menu \>\> Advanced Search**.
* Check the **Search in Trash (deleted contacts)** box. It's near the top on the right.
* Enter the name for the person in the name field if you know it, otherwise just press **Search**.
* You should see a list of deleted contacts. Check the box next to the deleted contact you want to restore.
* From the *Actions* drop-down that appears above the list of names, select **Restore contacts from trash**.
* Click **Restore contact(s)**.

---
Tags: deleted
---


# Question
Title: When enabling logging/auditing for multisite (multi-domain), does it need to be enabled for each site/domain?
When enabling "detailed logging" for a multisite (multi-domain) installation, does logging actually need to be enabled for each domain ID? Or is it sufficient to enable logging only on the "master" site (with Civi domain ID 1)?

I've noticed that in the `civicrm_setting` table, the `logging` setting is stored with the `domain_id`. And, in the CiviCRM admin UI, when logging is "enabled" on the site with domain ID 1, it still shows as "disabled" on other sites unless I explicitly "enable" it.

Since logging works via insert/update/delete triggers on each DB table, I cannot see how logging could be "enabled" for one domain ID but not enabled for all other domain IDs. Nonetheless, the fact that the setting appears to be independent for each site (based on both the UI and on the `domain_id` value in the `civicrm_setting` table) is confusing, and I have not seen this documented elsewhere.

I'm running WordPress multisite (v.5.4.4) and Civi 5.35.1.

---

**Edit:** Out of curiosity, I dug deeper and inspected the database triggers on the `civicrm_contact` table after first enabling logging on both the main (domain ID 1) site and a child site, and then again after disabling logging on **only** the child site.

After enabling logging, there was a `TRIGGER civicrm_contact_after_insert after insert ON civicrm_contact FOR EACH ROW BEGIN IF ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) THEN INSERT INTO ...` defined on the `civicrm_contact` table. And after disabling logging on **only** the child site, that trigger disappeared from the `civicrm_contact` table. So this seems to confirm that logging is **not** specific to a domain ID. But, I don't know whether or how the codebase uses the `@civicrm_disable_logging` variable. Through that variable, it looks like it would be possible to disable/bypass logging on a per-site basis!

For the record, I enabled and disabled logging via the `wp cv` CLI rather than the UI using (e.g.):

```
wp cv api setting.create logging=1 --url=main.mydomain.org
wp cv api setting.create logging=1 --url=childsite1.mydomain.org
wp cv api setting.create logging=0 --url=childsite1.mydomain.org

```

I did not test to see if changing settings in the UI behaves differently.

# Answer
You're correct that logging is at the database level. It seems like a bit of a loophole that it can be enabled-disabled on a per-domain basis. I would set it to `TRUE` on all domains to avoid unexpected behavior, but my guess is you'll only have a problem if you disable it on one site and expect it to continue working on others.
> 1 votes

---
Tags: multisite, logging
---


# Question
Title: Combine or report on individual and household donations
So I have two issues that I am trying to overcome.

Like others, I have a need to aggregate donations up to the Household level in a quick and easy way. Ultimately, it would be great if we could transfer donations already made to the household level but not sure that it is possible (I couldn't find a way to move donations entered to another account).

I have read the posts of Soft Credit and about the extension titled "AGGREGATE HOUSEHOLD CONTRIBUTIONS REPORT".

We are currently on version 5.25.0 so I am not clear of the extension mentioned will work for us or not.

Anyone with advice on this subject - please reply.

Thanks,

E

# Answer
> 2 votes
hi and excuse the slight whinge but... providing links to things like the extension, makes it quicker for other busy folk (we all are) to help. And explaining 'why' you think it might not work also saves folk time looking stuff up.

AGGREGATE HOUSEHOLD CONTRIBUTIONS REPORT has its last release as 4.6. Best I can suggest is give it a spin and report back. That way if it works the maintainer of it may update the details

it can also be worth checking the git page and checking the Issues to see if anyone else is commenting about it working on more recent.

NOTE: if an extension says it is good for 4.7 then in theory it is good for 5.x

And Bingo! there is an issue about 4.7 compatability

and

so chances are good to very good. and if you report back on that issue (it is easy to do so) then every chance Allen (twomice) will take the hint and update.

Better still if you are keen (and welcome to this diy community) submit a PR on the info.xml and you will have made Allen's life at least one-click easier :-)

# Answer
> 1 votes
To respond to the part of the question about moving a contribution, you can use the Move Contributions extension.

---
Tags: contributions, households, donations, individual
---


# Question
Title: The "cmsBaseUrl" is not set according to the information in civicrm.settings.php
**Error:**

When I check the installation requirements (`cv core:check-req`) I get an error message that says:

> The "cmsBaseUrl" (http://localhost/usr/local/bin/usr/local/bin/) is unavailable or malformed. Consider setting it explicitly.

Despite this message, there have never been any errors related to the base url when operating the site. I only noticed it when I ran the above check.

**Setup:**

I installed CiviCRM on Drupal8 with the following cv command:

```
cv core:install --cms-base-url="https://example.org" --lang="de_DE" -m loadGenerated=1 -v

```

And looking in `civicrm.settings.php` I can see that the base url is set with:

```
if (!defined('CIVICRM_UF_BASEURL')) {
  define( 'CIVICRM_UF_BASEURL'      , 'https://example.org');
}

```

**Troubleshooting attempts:**

In `civicrm.settings.php` it says:

> IMPORTANT: Trailing slashes should be used on all URL settings.

Accordingly, I changed `https://example.org` to `https://example.org/` in that file.

I then cleaned up the Drupal and CiviCRM cashes via the admin interface. The error message remains.

# Answer
Did you run check-req with the `--cms-base-url` argument? It needs to be specified there too.
> 2 votes

---
Tags: drupal, drupal-8, civicrm.settings.php, cv
---


title: Where to Download 5.13.7 ESR

text:
# Question
Title: Where to Download 5.13.7 ESR
Where/how do I get the tarball for CiviCRM 5.13.7 ESR Drupal 7 edition.

I have two different $20/month membership subscriptions to CiviCRM. Logged in to my civicrm.org account I have twice filled out a form asking for my email address and User name.

I see nothing in my email and no messages on my account page.

How do I get it?

# Answer
Thank you for supporting CiviCRM! You can use this ESR form for members to request a download key.

There is more information in the FAQ on the ESR page.

When we launched Extended Security Releases (ESR) as a service, we were not sure how many people would sign up, so we started handling the requests manually. It seems like it's here to stay, so we are currently working on automating the process.
> 3 votes

# Answer
The URL to the ESR Releases is:

https://lab.civicrm.org/esr/core/-/releases
> 0 votes

---
Tags: civimember, developer, release, esr
---


title: Upgrade or Replace CiviCRM 4.7.31

text:
# Question
Title: Upgrade or Replace CiviCRM 4.7.31
We are using Wordpress 5.7. Our current CiviCRM version is 4.7.31. As of August 2020, CiviCRM event registration stopped working due to the Wordpress update. Now, we can not seem to process memberships through CiviCRM either. As of now, I am sending people directly to PayPal, but for membership registration should upgrade, update, or replace our current CiviCRM version as it no longer working compatibly with our Wordpress site

# Answer
I think you can upgrade - but you want to first back up your database! It might be that you need to upgrade in a few steps (backing up between each one) - you can get older CiviCRM releases from sourceforge so you could upgrade to 5.10.0 or something & then on to the next hop

We do actually test that upgrades work from 4.7 in our unit tests but real world sites can throw up gotchas
> 1 votes

# Answer
I think you're asking whether to try upgrading or just start with a fresh install. It is definitely possible to upgrade from 4.7.31. You'll want to update php too since you're probably still using php 5 and that won't work with the latest civi.
> 0 votes

# Answer
Since you are on WP 5.7, its mandatory to upgrade CiviCRM to 5.28 or more. Also there is a latest Civi security release i.e 5.35.1, so its worth upgrading Civi to 5.35.1.
> 0 votes

---
Tags: wordpress, civicrm.settings.php, version
---


# Question
Title: How do I setup a webform so that an existing activity in a case is updated?
Here's the scenario:

A user submits a webform that creates a case.

An administrator assigns a role in that case to a second user, and edits a specific activity (activity ID = xxx) in that case to assign that activity to the same user named for the role.

A Drupal View is used to present to that second user a link to a different webform. The link is of the form http://example.org/path/to/form?aid=xxx My understanding is that because I have appended the activity ID to the url the form submission will update the activity with that ID.

However, when I submit the form a new activity gets created and the activity with ID = xxx is not updated or affected in any way. It doesn't seem to matter which options I set for the activity within the webform. Where am I going wrong?

As a supplementary, I've noticed that for the activity that does get created (with ID = yyy), if I file that activity on the case where it should be, then the filed activity has a different ID (yyy+1), which is concerning. Surely an activity should retain the same ID number regardless of its status?

# Answer
What I have found is that to update an existing activity that is part of an existing case, it is insufficient to pass just the activity ID into the form via the URL. Which sort of makes sense as I can't see where that data would be stored in the webform submission (maybe it somehow gets passed into CiviCRM via the url?)

However, if I pass in the contact ID of the case client as well, and use an additional hidden existing contact field in the webform for this ID, as explained by @jaap-jansma in the comments elsewhere on this page, then this is enough data for CiviCRM to be able to identify the correct target activity in the correct case, and update the activity accordingly.

Maybe we don't need to pass in the activity ID at all, given that we can target the correct case, and use the filters available in webform\_civicrm to focus on updating the correct activity? If I have time I'll test that option, and would welcome comments from others on this.

We used a Drupal View to construct and present the link to the user.
> 1 votes

# Answer
This is possible at the option update existing case and update existing activity. This option is available after you have enabled case and activity processing.
> 1 votes

# Answer
I don't think you can pass case id in the url.

I sort of asked a similar question here: http://forum.civicrm.org/index.php/topic,36136
> 1 votes

# Answer
I added a case to the webform and passed the case1 id via the URL.

I made sure the Case Type was "User Select" so CiviCRM does not expect to have case roles added, except for the case client. This was critical. When I selected a case type, the activity kept getting added instead of updated.

I passed the case client CID as a hidden contact on the webform,

I made sure the activity was File on Case 'Case1'

I made sure all the target, source and assignees bits were filled out on the activity.

I passed the activity id in the URL to populate the activity.

I passed CIDs for the target, source and assignee to populate the activity.

This magic sauce prevents an activity being added, when you want to update an existing one.
> 1 votes

---
Tags: activities, civicase, webform-civicrm
---


title: Drupal Views and Case Relationships

text:
# Question
Title: Drupal Views and Case Relationships
I'm setting up a vew of cases of type "x" and using views relationships to pull in cases relationships such as "Case Worker for" Client- as well as client details.

If, I have a client who has had more than one case with the organisation. With a number of "Case Workers". how can I filter the view to only show the case relationship for the listed case?

Currently will now show duplicate lines or each case, 1 per case worker.

If I use views aggregator plus module, I can compress the view down to one line per case- but it is normally the first added case worker shows not the one for the specific case.

See screenshot for example- 1 client, 1 case, but 5 caseworker relationships to the client

# Answer
> 4 votes
Ok, once again I should have looked more carefully at the suggested answers

If contact has multiple cases, how do I filter a view to show only case roles associated to that case type

Start with a relationship view Then pull in the case id from the views relationships and filter on the case type, relationship type and is the relationship active

And then bring in the related contacts

Thanks @pmoz and @ehommel

---
Tags: civicase, drupal-views
---


title: Trapped by MySQL 5.6

text:
# Question
Title: Trapped by MySQL 5.6
HostGator has frozen their shared platforms at MySQL 5.6 with no plans to offer MySQL 5.7 to the shared packages, even though 5.6 went EOL on February 5th, 2021. They're pushing people to use their Virtual Private Server which is about 10x the cost of shared. So, if you're installing CiviCRM, DON'T host with HostGator. We'd like to stay up-to-date on CiviCRM, at least the security fixes, but the only option seems to be to pay for Extended Security Release. Are there any alternatives for those who have been locked out by their hosting platforms? cheers ... :) gn

# Answer
> 3 votes
Geoff - although I do want to encourage you to upgrade your mysql you are still able to install the free version of CiviCRM on mysql 5.6 at this stage. We are discussing when to stop supporting it over here https://lab.civicrm.org/dev/core/-/issues/2481

# Answer
> 1 votes
I just wanted to add this in here...I think in terms of safety, it would probably be better to upgrade to the VPS or your own dedicated server. While expensive, I'm not sure I'd be OK with having constituent data on a shared hosting environment. It would also give you a bit more flexibility in terms of upgrades.

---
Tags: mysql, hosting, hosting-providers
---


title: Editing custom group settings

text:
# Question
Title: Editing custom group settings
I'm trying to edit the settings for an existing custom fields group. When I make the changes and save it, I get this:

`Please correct the following errors in the form fields below:`

`Custom group 'Training Information' already exists in Database.`

# Answer
> 0 votes
Quick guess. Did you double click the save button?

Or, it's suggesting there's already a "Training Information" Dataset on your system. Maybe for a different entity type e.g activity data not contact data.

---
Tags: custom-fields
---


# Question
Title: Specified return fields in contact get do not get returned
If I do a api3 contact 'get' with no return fields specified, it obvs returns all the fields including address fields.

What I want to do is only retrieve certain fields for my needs, just to reduce overhead.

If I specify to return only 'state\_province\_name' or 'geofield1', they are not returned.

Is there a way to do this in a single call?

# Answer
If include 'state\_province' in return param it should return you state\_province\_id, state\_province\_name and state\_province.

For geofield1 you need to use 'geo\_code\_1'.

If you search for primary address in return field drop down under api explorer you should see the exact name for fields to retrieve for address.

```
$result = civicrm_api3('Contact', 'get', [
  'sequential' => 1,
  'return' => ["address_id", "city", "postal_code", "state_province", "geo_code_1", "country"],
  'state_province_name' => ['IS NOT NULL' => 1],
]);

```
> 2 votes

---
Tags: api
---


# Question
Title: How to create a WP account for an user already in CiviCRM?
We have contacts in CiviCRM and we want them to create a WP account to access some specific front-end content. We're using a caldera form to let them register themselves. But if I use a test contact I get `This email address is already in use.`

How could I proceed? Is this possible? (CiviCRM 5.35.1 on WP 5.7)

# Answer
Are you using Caldera add on plugin to create wp user if yes than i guess its a question to ask Caldera support team.

Also can you check the email address you used already has wp user account or there is an entry in civicrm\_uf\_match table under uf\_name column
> 1 votes

---
Tags: user-account, caldera-forms
---


title: How secure is CiviCRM

text:
# Question
Title: How secure is CiviCRM
I have been using CiviCRM for over five years. I am trying to encourage a local charity who provides care to vulnerable people to move to CiviCRM. I have been asked by the project manager the following "I am looking at the security of CiviCRM and wonder if there has been any security testing of the product that you are aware of e.g. hacking of the website or the database? Has there been any penetration of the site performed?" I would like to respond to them and would be grateful for any help, or advice on this.

Regards Graham Clarkson

# Answer
*Adapted from my response on MM:*

In terms of penetration testing that's host specific, the point of penetration testing is to test the entire system, it's as much about your local/server configuration as the PHP code. The CiviCRM codebase has benefitted from several third party audits for security which have resulted in security releases but, and this is important. It's only as secure as your server is configured to be.

We could have the best, most secure PHP code in the world - but a minor misconfiguration on your hosting risks negating it entirely.

The security of CiviCRM, like any other application also relies on regular updates to maintain security - for example any version less than 5.35.1 or the latest ESR (5.33.3?) has known security holes unless manually patched (or updated to one of those supported versions)

Some organisations choose to maintain additional security by separating CiviCRM onto a separate host/server from their website or having CiviCRM behind their firewall in their LAN.

There is some information on our security processes and releases here: https://civicrm.org/security
> 3 votes

---
Tags: security
---


title: Using 'Date Deceased' in CiviReport

text:
# Question
Title: Using 'Date Deceased' in CiviReport
I'm attempting to create a basic Contact Summary CiviReport: A list of deceased contacts sorted by Date Deceased. The field 'Date Deceased' from the Contact Summary doesn't appear to be an available column in CiviReport nor is it sortable.

Am I missing something? Thanks!

# Answer
Another option is to use Data processor extension, you can output the result as search or report or dashlet.
> 2 votes

# Answer
You're correct, however you can do this with the new Search Kit. It's currently an optional extension under Admin - System Settings - Extensions. After enabling there's a new option under the Search menu.
> 1 votes

---
Tags: civireport
---


title: SMS not showing up in the actions menu

text:
# Question
Title: SMS not showing up in the actions menu
I have installed and set up the SMS Clickatell extensions and activated the provider on CiviCRM, but SMS doesn't show up in the actions menu of a contact and the batch SMS isn't working. Do you have any ideas? Thanks in advance.

# Answer
> 2 votes
I just had to set the contact telephone to "Mobile". Thanks @Demerit.

---
Tags: sms, clickatell
---


title: Members are being charged early

text:
# Question
Title: Members are being charged early
CiviCRM 5.35.1 WordPress 5.7

We noticed last week that our members who set up recurring payments via Credit Card with the iATS processor are being double charged or even triple charged in some cases. All of sudden 100s of recurring transactions are being triggered at the same time when they should not be triggered. For instance, someone who should only be recurring once a year, was charged twice We have no idea what is going on. This just recently started happening. Please see screenshot below for example. As you can see, all these transactions are happening at the same time on the same day. This is not normal and people are being double charged.

This screenshot shows contributions for an ANNUAL membership that is not due to be renewed until January 30th 2022, but it was renewed on March 30th. This is just one example. We have hundreds of instances of this happening over the last 5 or 6 days.

# Answer
> 1 votes
In the screenshot above -\> I don't see any double charges. Every Contribution in the screenshot belongs to a different recurring series (the id = numbers are different).

As far as at the same time on the same day -\> if cron has not run for some time on your site -\> then this is exactly what you would see. Every recurring series that has a next\_scheduled\_date in the past would be due as soon as `iATS Payments Recurring Contributions (Daily)` is triggered by your system cron.

As far as double charges -\> there are safe guards in place for that -\> it can really only happen if that cron job is triggered by two crons at the same time and/or if you have a production and a development set up and you have cron running on both.

PS - Make sure you are on the latest iATS Extension update (1.7.4)

On timing -\> which recurring contribution series should create a new contribution to be submitted for processing to iATS -\> is solely governed by the Next Scheduled Contribution Date in the Recurring Series Record.

In this report -\> /civicrm/report/com.iatspayments.com/recur?reset=1 you can filter Next Scheduled Contribution Date -\> next week so see what's going to happen next week. For this client -\> they will have two series transact on April 09 and one on April 10.

It sounds like your recurring series definitions/dates are not in line with your membership dates.

Also very important - do not switch ON Recurring within your iATSpayments.com account itself as then you have both civicrm and iats trying to control the schedule of your payments.

So go into one of your recurring series records (for example id = 578 you have in your second screenshot) -\> and view all variables re: time -\> i.e. next scheduled date + frequency

---
Tags: civimember, recurring-contributions, iats
---


# Question
Title: missing config_backend in civicrm_domain table, templates_c directory and Config.IDS.ini after migration
I didn't do the database dumps or file backups for this migration of CiviCRM on wordpress, so could it be a problem I can solve without going back to the old host?

# Answer
> 0 votes
What is the problem the site is having?

In later versions there is no config\_backend field, and templates\_c and config.ids.ini will recreate themselves if the folder permissions are correct.

---
Tags: database, missing
---


# Question
Title: What's the right syntax for the API3 "replace" action for the "WordReplacement" entity?
I'm trying to call the `WordReplacement` API v3 with the `replace` action. The APIv3 Explorer tells me the right PHP syntax is as show below. (I previously called `WordReplacement` `get` to get the correct `id` value.)

```
$result = civicrm_api3('WordReplacement', 'replace', [
  'id' => 2,
  'find_word' => "Contribute",
  'replace_word' => "Give Now",
  'domain_id' => "my-domain-name-here",
  'match_type' => "exactMatch",
  'is_active' => 1
]);

```

But executing that in the API Explorer returns the error:

```
{
    "is_error": 1,
    "error_message": "Mandatory key(s) missing from params array: values"
}

```

I figured out this is calling the "generic" `replace` API, and by digging through the API examples (for `Email` and `UFField`), it appears the correct syntax is something like:

```
$result = civicrm_api3('WordReplacement', 'replace', [
  'some_key' => 'some_value',
  'values' => [
    'id' => 2, // maybe this ID isn't needed here?
    'find_word' => "Contribute",
    'replace_word' => "Give Soon",
    'domain_id' => "my-domain-name-here",
    'match_type' => "exactMatch",
    'is_active' => 1
  ]
]);

```

I tried a few different guesses for `some_key` and `some_value` (and also tried omitting those entirely), but always got a new error `Type error: Argument 3 passed to civicrm_api() must be of the type array, null given, called in /longpathgoeshere/wordpress/wp-content/plugins/civicrm/civicrm/api/v3/utils.php on line 1813`.

I also looked at some of the API definition files like `civicrm\civicrm\api\v3\UFField.php`, `Email.php` and `WordReplacement.php`, as well as the API `getfields` output, but nothing helped me to infer what the correct syntax should be. So I'd also be interested in hints on how to understand and learn from those as a developer.

I'm running WordPress v.5.4.4 and Civi v.5.35.1.

# Answer
**Updated!** based on Coleman's answer, and additional testing.

First of all, for `WordReplacement`, if you want to update a single record it's best and most appropriate to use the `get` action to get the record ID (`civicrm_word_replacement.id`). Then (as Coleman noted) use the `create` action and include the `id` parameter to update just that record. (Without the `id` parameter, `create` throws an error complaining the record already exists. With it, `create` performs an update.)

For the `replace` action, the `values` parameter must be an array of arrays. But, **crucially**, in addition to the `values` array, you **must** provide "*scope*" parameters *outside* of the `values` array.

So for example, with `id` as the "scope" parameter, the following will update only the record matching the `id` (equivalently—but more awkwardly—than `create`):

```
$result = civicrm_api3('WordReplacement', 'replace', [
  'values' => [
    'id' => 2, // "scope" parameter
    [
      'find_word' => "Contribute",
      'replace_word' => "Give Now",
      'domain_id' => "my-domain-name-here",
      'match_type' => "exactMatch",
      'is_active' => 1
    ]
  ]
]);

```

The API also supports, and is in fact intended for, multiple replacements. But in this case the `values` array must contain **all** of the records that match the "scope" parameter(s). In other words, it replaces the complete set of records that match the "scope" parameter(s). For `WordReplacement`, if you provide only the `domain_id` as the "scope" parameter, the `replace` action will match all of the records for that domain, so your `values` array must contain the complete set of `WordReplacement` records for that domain. If you have 3 replacement words, this might look like the following:

```
$result = civicrm_api3('WordReplacement', 'replace', [
  'values' => [
    'domain_id' => "my-domain-name-here", // "scope" parameter
    [
      'id' => 2,
      'find_word' => "Contribute",
      'replace_word' => "Give Soon",
      'match_type' => "exactMatch",
      'is_active' => 1
    ],
    [
      'id' => 3,
      'find_word' => "Find this",
      'replace_word' => "Replace with that",
      'match_type' => "exactMatch",
      'is_active' => 1
    ],
    [
      'id' => 7,
      'find_word' => "Find this too",
      'replace_word' => "And replace it with this",
      'match_type' => "wildcardMatch",
      'is_active' => 1
    ],
  ]
]);

```

If you had four `WordReplacement` records for *"my-domain-name-here"*, you'll end up with only the three above after calling `replace`. The fourth record will be deleted. Conversely you can add new records by omitting the `id` parameter (provided the new records have no "*already exists*" conflicts).

So the `replace` API action is powerful but potentially dangerous. Test carefully in a development environment before using.
> 2 votes

# Answer
I think there may be some misunderstanding about the purpose of the `replace` action. To clarify, this is a specialized api action intended to perform a simultaneous create/update/delete on a bunch of records. It's typical use would be to replace all of a contact's email addresses with a new set of email addresses.

Perhaps the confusion arose from similarity of `replace` and `WordReplacement`, but I do not recommend using that action with this entity, chiefly because `replace` is supposed to operate within some bounds (e.g. replace emails just for this one contact). Without those bounds it will delete every existing record in the database. Danger!

I think you are looking for the `create` action, which uses the syntax you were expecting. It will create a new `WordReplacement` record if you omit `id` from the params, or if `id` is included it will update that record.
> 1 votes

---
Tags: api, word-replacements
---


# Question
Title: Awaiting approval participant status message template
When requiring participant approval, it sends this email message. I would have thought it would have been in the system workflow messages under message templates. It is not. So where do I find this in core?

Also while configuring the online registration, it let's you customize the approval message, but I do not see where this is actually showing to end users.

# Answer
It is within the `Events - Registration Confirmation and Receipt (on-line)` template.
> 3 votes

---
Tags: civievent, system-workflow-messages, waitlist
---


# Question
Title: CiviCRM Entity and editing or viewing activites
I'm just curious if anyone has done work with CiviCRM Entity and activities. I was considering whether it makes sense (or is even possible) to have people edit certain activities from Drupal rather than from CiviCRM.

We have a lot of custom activity types with a lot of custom data (total about 600 fields) - it is only appropriate to view/add/edit the custom data related to the appropriate activity type.

* For viewing activity info, I found that I could use either the Module View Mode Page or create a view with some contextual filters and a rendered entity that uses a custom view mode (or simply a customized view).
* For editing an activity - is there any way to limit what is on the form? Rather than showing the long list of all activity custom data?
* Depending on the answer to the question directly above, how can I avoid loading all 600 fields?

(Drupal 7.78, CiviCRM Entity 7.x-2.2, CiviCRM 5.33.4)

# Answer
> 1 votes
With CiviCRM Entity, there is a Drupal form for editing activities /civicrm-activity/\[activity-id\]/edit

Unfortunately the form does not limit to only those custom fields that are appropriate for the activity type.

In the past, we had done hook\_form\_alter() implementation in custom module to hide custom fields depending on activity type. This feature to only show custom fields appropriate for activity type is on our radar for future improvements.

---
Tags: activities, civicrm-entity
---


title: URL "civicrm/a/#" breaks the UI

text:
# Question
Title: URL "civicrm/a/#" breaks the UI
Hello dear colleagues.

Yesterday I updated a Drupal 8 site to 5.35.1. Afterwards many of menu URLs become replaced/injected with a "civicrm/a/#" string, for example domain.tld/civicrm/a/#/mailing/new or domain.tld/civicrm/a#/status; and the UI returns a blank page, not a white screen, but a page with no CiviCRM body content (see attached image).

Once this occurs the CiviCRM menu system is no longer active (clicking on any other CiviCRM menu does nothing).

I have also now done a net new composer based install (new o/s path and new DB) this time on Drupal 9 CiviCRM 5.35.1, and precisely the same problem occurs.

This is a CPanel Centos VPS server.

Your kind help is most appreciated.

# Answer
I have repeated the new install, this time including this:

`composer config extra.enable-patching true`

The site now completely renders UI contents, such as the new mailing page or the status page.

So then, going to the troubled upgrade site, having first configured the install with these composer configurations:

`composer config extra.enable-patching true`

`composer config minimum-stability dev`

`composer config prefer-stable true`

`composer require civicrm/civicrm-asset-plugin`

`composer require civicrm/civicrm-{core,packages,drupal-8}`

and then again:

`composer update`

`composer civicrm:publish`

The upgrade site is now working.
> 3 votes

# Answer
This typically helps with angular caching issues -\> \[it's a cache clear of sorts\] Administer -\> System Settings -\> Asset Caching -\> Disable

If that worked -\> toggle back to -\> Auto

Sometimes I have to re-run composer civicrm:publish (some bower assets - only some - most were there) - were just not there post upgrade.
> 0 votes

---
Tags: drupal, upgrade
---


# Question
Title: PayPal recurring payments delayed by days
We've been using recurring payments with the 'PayPal - Website Payments Pro' processor for several years now. In the past we'd see an occasional delay between the user's submitting the payment on a CiviContribute page and the transaction being recorded as "Completed" in CiviCRM. The delay would be minutes or hours.

Recently (say, last 2-3 months), we've seen delays as much as several days; two recent examples: the contributions were initiated (and thus the recurring subscriptions were created in PayPal) on 4/1, but PayPal only actually processed the first payment on 4/6.

Found the following, poking around PayPal's knowledge base: https://www.paypal.com/us/smarthelp/article/why-are-some-of-my-initial-payments-delayed-when-i-use-my-recurring-payments-profile-faq3682

Anyone else experiencing this? Any way around it?

(Reason this is important to us is we provide discounts on events for members, and folks often sign up for membership right before registering for an event, and they don't receive their discount if the payment processing is delayed.) Thanks for any tips.

# Answer
If it's a 5-day delay, it's almost certainly because their card was declined the first time. 5 days is the default delay when a card is declined on a recurring payment. It then tries again 5 days later, at which point the payment isn't retried. That used to cancel the subscription, but now it goes into a "missed payments" field on the subscription.

You should be able to check the subscription for an explanation of what happened in the period between the initial attempt and the successful one.
> 1 votes

---
Tags: payment-processor, paypal, recurring-contributions
---


# Question
Title: How can I change the currency of my sandbox authorize net merchant?
I'd like to change my currency at my sandbox authorize net merchant. I can only see one currency on my sandbox virtual terminal. What should I do?

# Answer
Welcome to SE CiviCRM, The question you raised is basically for Authorize.net support team instead here. You will need to contact Authorize.net support team to change the currency for your merchant account.
> 1 votes

---
Tags: payment
---


# Question
Title: Where is the link between a CiviCRM contact and Wordpress user stored in the database?
I am migrating my CiviCRM database to a new server with a fresh install of Wordpress and CiviCRM. I would like to know where in the database the link between CiviCRM contacts and Wordpress Users are, so if needs be I can manually correct them (there are not many contacts linked to users in my database).

Maybe Civi will correct the link between users and contacts if I were to use the 'Synchronize Users to Contacts' feature again in the new system, does anyone have experience of this?

Regardless I would be interested to know about where this link is stored to improve my familiarity with how Civi works. I looked in the civi\_contact table but could not see any field there that might store the linked user id from Wordpress.

# Answer
> 7 votes
Great question!

The matching between the CMS user and the CiviCRM contacts relies, like most "relationships" between entities in CiviCRM, on an additional table that maps the IDs between the two entity types.

For the Contact to CMS User match you're looking for the `civicrm_uf_match` table. This table has the following columns:

* ID - *this is the Unique ID of the match record.*
* DOMAIN\_ID - *this is the Unique ID of the CiviCRM domain in a multisite setup.*
* UF\_ID - *this is the User's ID in the "UserFramework (UF)" (this is what CiviCRM calls the CMS).*
* UF\_NAME - *this is the User's Username or Email address in the UserFramework.*
* CONTACT\_ID - *this is the Unique ID of the CiviCRM contact that matches to the User.*

Hope this helps!

# Answer
> 5 votes
In the civicrm\_uf\_match mysql table

---
Tags: wordpress, contacts, migration
---


title: One database table very large

text:
# Question
Title: One database table very large
Database table Table: civicrm\_contact\_push\_notification\_messages

Is huge. 51GB to be precise.

The main field is %display\_name has created activity.

Is this the table that shows up what emails have been sent under a user's activity tab? Or something else.

Does anyone know?

Thakns! Caroline

# Answer
Thats from Civimobile extension and You can find quick solution at https://lab.civicrm.org/extensions/civimobileapi/-/issues/57#note\_54817

> One thing I have found is editing the scheduled job Civimobile clean old push notification messages and setting a command / API parameter count\_of\_day=1 which means keep only 1 days worth of notification messages can help
> 4 votes

---
Tags: database, mysql
---


# Question
Title: User Dashboard and Edit Profile for Organization
We have set up a User dashboard for individuals, and a profile so individuals can update their address, phone, email, etc. It looks like this:

We would like to set up something similar for an Organization, so permissioned users can see the organization's membership expiration date, and update the contact info for the organization.

I understand that the way to do this is to set a permissioned relationship, so the individual can view and edit the Organization. However, if I click the Organization, it doesn't take me to a user dashboard for the Organization, but a contact view as if an administrator were logged in. Membership information is not visible. The edit function is not a profile with limited fields, but all the fields an administrator could edit (see below photos):

Am I missing something? Is there a way to see a user dashboard for an organization, like there is for an individual? Can I limit the organizational fields that can be edited?

I am using CiviCRM 5.31.0 with WordPress.

Access control: Subscriber (the user) can: view my contact, edit my contact, access contact dashboard

Relationship permission: set to view and update

EDIT pd added image showing what dmaster shows for comparison

# Answer
> 2 votes
We have used contact layout editor extension to show only necessary fields on view contact section. Using contact layout editor you can customize the view contact page. You can include profiles with limited fields and hide or remove the edit option via css or JS.

Or other option is write extension to redirect Org contact to /civicrm/contact/dashboard?cid=123&reset=1

AFAIK having Relationship permission: set to view and update will only allow user to updated the related contact details but not Payments or Membership. Try giving 'Access civi membership' permission to user role

# Answer
> 2 votes
If you want a user to only edit specific fields for their organisation you can create a profile and add the organisation fields you want to give them access to. From the CiviCRM profiles page:

> CiviCRM Profile(s) allow you to aggregate groups of fields and include them in your site as input forms, contact display pages, and search and listings features. They provide a powerful set of tools for you to collect information from constituents and selectively share contact information.

Profiles can be used to list contacts, create new contacts or edit contacts. For your requirement, the profile would be a standalone form profile for editing. When you create a link to the profile edit form, you'll need to make sure you pass the organisaton contact ID in the URL as one of the parameters. For instance, if your profile form had an ID of 6, and the organisation contact ID was 18 then your URL would be:

`www.mydomain.com/civicrm/profile/edit?gid=6&id=18&reset=1`

Note that the query parameter label for the contact ID is `id`, not `cid`. The `gid` query parameter refers to the id of the profile form.

To ensure the correct permissions to allow access to the profile, you may need to update the civicrm ACL permissions at `/civicrm/acl?reset=1`.

**Note**: if you do not pass the organisation ID parameter correctly in the URL then you'll get a warning message saying:

> This profile is configured for contact type 'Organization'. It cannot be used to edit contacts of other types.

---
Tags: permissions, user-dashboard, organization
---


# Question
Title: How can you remove or disable some US states from showing up in profile?
How can you remove or disable some states from showing up in profile?

We really only want a few showing up - Latest CiviCRM - not using webform - this is for profiles.

# Answer
If you want to show only few US states across whole CiviCRM form than you can set civicrm\_state\_province.is\_active = 0 for the ones to not show for country\_id = 1228
> 2 votes

---
Tags: profile, localisation
---


# Question
Title: Why are emails not being sent when a contribution is made?
We are using CiviCRM 5.35.0, but I have tracked this issue down to the installation of 5.34.0 (updated from 5.33.2). Our CMS is Drupal 7.78. Our PHP is 7.3.27. Our outbound mail setting in CiviCRM is "mail()" and this continues to work for most mailings.

However, starting right after I installed 5.34, when a contribution is made, CiviCRM does not seem to actually send the expected emails (receipt AND profile submission notifications). In the contact's contribution record, it says that the email receipt was sent, but neither the contact nor myself (I am cc'd on those receipts - setting on the contribution page) gets an email.

Our Payment processor is Stripe and we are using the Stripe Payment Processor extension version 6.5.7. All extensions are up to date. Stripe continues to send its receipts directly, so at least our users are getting those.

As far as I know, this issue is only occurring when emails are triggered by a contribution. Otherwise, the system's emails seem to be going out as expected.

Anybody have any ideas what might be causing this?

Thanks.

# Answer
I narrowed this down to one particular contribution page. Others were working as expected. I don't know what the problem was, but I worked around it by simply creating a replacement for that contribution page. Thanks to everyone who gave this some thought.
> 2 votes

---
Tags: drupal, civicontribute, email, receipts
---


# Question
Title: Using fields of referenced contacts in message
When I have two contacts that have an employer/employee relationship in CiviCRM I can use the name of the employer in message templates when I sent messages with the `{contact.current_employer}` tag.

However I am currently seeing no way to get the name of a contact referenced through a custom relationship (say for example sibling) to use that in a letter. Is there any way to accomplish this in CiviCRM? Maybe something like `{relationship_123.display_name}`?

# Answer
> 4 votes
Welcome to SE CiviCRM, you can install Fuzion tokens extension. It provides lots of tokens for contacts like relationships, payments etc

Details can be found here.

---
Tags: relationships, message-templates
---


# Question
Title: Why is CiviRules not firing when the trigger is adding a tag?
Using CiviCRM 5.35.1 with Drupal 7. Just installed the new CiviRules extension 2.23. I've been waiting for this because CiviRules has not been working with Tags for a couple of months and it has totally messed up our membership workflow. :-(

Just tested the new version of the CiviRules extension (2.23) and some things are fixed, but not others. When **changing membership status** is the trigger, CiviRules does add the tags it should to the contact and does add the contact to the groups it should. :-)

However, when **adding a tag** is the trigger, it still does not add contacts to groups or remove them from groups as it used to do. :-(

Is anyone else seeing this?

Thanks.

# Answer
Thanks for sharing your problem.

As you can see there are a few outstanding issues with tags: https://lab.civicrm.org/extensions/civirules/-/issues?scope=all&utf8=%E2%9C%93&state=opened&search=tag

As you are probably aware CiviRules is open source like the rest of CiviCRM. That means that issues will be fixed in a number of ways:

* organizations run into bugs and get their own staff to fix them
* organizations run into bugs and pay a CiviCRM partner/developer to fix them

So if you are in a position to either fix the problem or fund someone else fixing it that would be great! Most of the times issues do not get fixed without someone bearing the cost at some point, the power of open source is that the solution is then shared freely.

On top of this CiviCooP sponsors a yearly 2 day sprint to fix as many as the issues as we can in 2 days. But there is no guarantee that the issue that is bugging you is on top of the list (although in this case it probably will be....). This sprint is going to be in the week of 10 May. Any sponsorship for an additional day will be welcome :-)
> 2 votes

---
Tags: civirules, tags
---


# Question
Title: The view past events button has disappeared from manage events. How do I view/copy from past events?
Using civiCRM-5.35.1-wordpress, and since updating the view past events button under Manage Events has disappeared. Does anyone know where this has gone? Is it now accessible somewhere else? Thanks for your help!

# Answer
> 1 votes
You can find the past events by doing a search based on dates

---
Tags: wordpress, civievent
---


title: How to view SearchKit's API and SQL

text:
# Question
Title: How to view SearchKit's API and SQL
I was following along the 1st video of the SearchKit playlist on Youtube. And approximately 38 min in, the presenter shows how one can see the underlying API code or SQL code for a given search. It looks like he simply expanded on a tab, Query Info. However this is not available for me on my Buildkit site and I'm wondered how to enable this.

# Answer
> 2 votes
I'm pretty sure you need "CiviCRM: view debug output" permission in the CMS.

Also if it's not obvious, you have to run the search first before it appears.

---
Tags: searchkit
---


title: Smarty template tokens not translating

text:
# Question
Title: Smarty template tokens not translating
We are running civicrm 5.33.1, wordpress 5.7, php 7.4.

The following test template only translates the contact.email greeting, the others stay as they are as do all other tokens in our templates.

Dear {contact.email\_greeting}

{domain.address}

{domain.email}

{domain.phone}

# Answer
> 1 votes
Thank you Demerit. I misunderstood how tokens work and was trying to use system workflow tokens in a user template.

---
Tags: smarty
---


# Question
Title: Is there a way to sell numbered tickets, e.g. for a raffle?
We would like to sell numbered tickets for a raffle. According to the regulations we need to meet, we have to sell tickets that have unique numbers from a predetermined set (e.g. we have tickets numbered 1-500). How can we sell these tickets with email receipts that go to the buyer, including the ticket numbers?

Ideally, it would be possible to sell more than one ticket in a transaction and send a receipt to a buyer saying that they bought tickets x, y and z or failing that three emails listing the ticket numbers.

We've used a Drupal webform with the submission id as the ticket number in the past, but this only allows us to sell one ticket per transaction. It seems like we might be able use Civirules to send receipts with ticket numbers, but I'm not seeing a way to do that without custom code.

# Answer
> 1 votes
My solution to this isn't elegant, but it gets the job done. I simply added a little code with hook\_webform\_submission\_insert that takes the original webform submission data and resubmits the essential details to a second webform as many times as the user bought tickets. That second webform serves as the official list of tickets sold and sends an email for each submission to the buyer (so the buyer gets three emails with different ticket numbers if they bought three tickets). This works fine as long as your ticket numbers are sequential and increasing. You can add more tickets in a higher range by adding higher tickets numbers by changing the next submission value for the webform, but you can't add tickets with a lower number.

```
function wildsight_raffle_tickets_webform_submission_insert($node, $submission) {
  if ($submission->nid == 210) {

  $node_out = node_load('211');
  global $user;

  $data = array(
    'first_name' => $submission->data[3][0],
    'last_name' => $submission->data[4][0],
    'email' => $submission->data[14][0],
  );

  module_load_include('inc', 'webform', 'webform.module');
  module_load_include('inc', 'webform', 'includes/webform.submissions');

  $number_of_tickets = $submission->data[24][0];

  // This methods will arrange $data in the right way
  $data_out = _webform_client_form_submit_flatten($node_out, $data);
  $data_out = webform_submission_data($node_out, $data_out);

  while ($number_of_tickets > 0) {

  $submission_out = (object) array(
    'nid' => '211',
    'uid' => $user->uid,
    'sid' => NULL,
    'submitted' => REQUEST_TIME,
    'completed' => REQUEST_TIME,
    'remote_addr' => ip_address(),
    'is_draft' => FALSE,
    'data' => $data_out,
  );

    webform_submission_insert($node_out, $submission_out);
    webform_submission_send_mail($node_out, $submission_out);
    $number_of_tickets -= 1;
  }
}
}

```

---
Tags: webform-civicrm, civirules
---


# Question
Title: Using the LIKE operator with a wildcard using API v3's REST interface
I am trying to do a simple `GET` request using a `LIKE` operator with a wildcard. It's easy to set up the explorer and works as expected. I'm using a demo civi environment using the example data included on install. There are two Heidis in the database.

The rest URL generated in the explorer is:
`https://civi.site.com/wp-content/plugins/civicrm/civicrm/extern/rest.php ?entity=Contact &action=get&api_key=userkey&key=sitekey&json={"sequential":1,"first_name":{"LIKE":"Heid%"}}`

As is, I know that the URL will generate the error: `Unable to decode supplied JSON.`

I have reformatted it to:
`https://civi.site.com/wp-content/plugins/civicrm/civicrm/extern/rest.php?key=sitekey&api_key=userkey&entity=Contact&sequential=1&json=1&action=get&first_name=Heidi`

That works as expected using the exact match on `Heidi`. I tried using the `LIKE` operator using array syntax:
`https://civi.site.com/wp-content/plugins/civicrm/civicrm/extern/rest.php?key=sitekey&api_key=userkey&entity=Contact&sequential=1&json=1&action=get&first_name[like]=Heidi`

I get the expected response, but this uses the exact value and no wildcard characters.

I've tried three different options for wildcards:

1. Like array no wildcard:
`https://civi.site.com/wp-content/plugins/civicrm/civicrm/extern/rest.php?key=sitekey&api_key=userkey&entity=Contact&sequential=1&json=1&action=get&first_name[like]=Heid`
2. Like array with wildcard:
`https://civi.site.com/wp-content/plugins/civicrm/civicrm/extern/rest.php?key=sitekey&api_key=userkey&entity=Contact&sequential=1&json=1&action=get&first_name[like]=Heid%`
3. Like array with encoded wildcard:
`https://civi.site.com/wp-content/plugins/civicrm/civicrm/extern/rest.php?key=sitekey&api_key=userkey&entity=Contact&sequential=1&json=1&action=get&first_name[like]=Heid%25`

None of the three wildcard attempts produce an error, but they return 0 results. I've looked through the docs and have done multiple Google searches but I can't find a workable solution. Any help would be greatly appreciated.

# Answer
the correct syntax is the one at the top:

"first\_name": {"LIKE":"hei%"}

to work around the encoding issues (that might be tricky indeed), don't try to put all the params in the url (as http get) but use a http post (the api works with both)
> 3 votes

---
Tags: api, rest
---


# Question
Title: CiviMail report showing bulk emails have sent when they haven't
On two Civi 5.35.1 & Joomla sites, I've got the same issue of bulk emails not being sent, but the CiviMail report stating every email has been sent (0 bounces or replies). The 0 bounces bit of the Mail report is what alerted us to something being wrong. Other Civi emails are working fine. On checking, another Civi 5.35.1 & Joomla site using a different host and DNS provider also worked fine for bulk email.

Eventually by looking at the server's mail delivery logs I realised that the emails had never sent, with an error that looks like

```
587 I=[185.181.117.20]:48996 X=TLS1.2:AES128-GCM-SHA256:128 CV=yes DN="/CN=*.filteredmx.net": SMTP error from remote mail server after MAIL FROM:<b.1231.77517.036450515a4f21ca@qwerty.org> SIZE=3092: 550-Verification failed for <b.1231.77517.036450515a4f21ca@qwerty.org>\n550-Called: 185.181.117.37\n550-Sent: RCPT TO:<b.1231.77517.036450515a4f21ca@qwerty.org>\n550-Response: 550 No Such User Here"\n550 Invalid sender <b.1231.77517.036450515a4f21ca@qwerty.org> DT=2.353s

```

With help from here - Mosaico mailings not sending \- I saw this means bounce processsing had stopped working.

I tried with a different email account from an external domain (ie another email address I have) - and it all worked again. So it seems there's two issues here:

* a Civi error notice issue, because Civi appears to be telling the user mails have been sent when they haven't. It took quite a bit of digging (e.g. turning on MailLog in civicrm.settings.php - gave no insight) before I eventually checked the 'Email Deliverability' screen in Cpanel where I found these errors.
* and the 'something has changed in the last few weeks' issue - that might have nothing to do with Civi 5.35.x and be more about Cloudflare or Cpanel or our webhosts. What's especially odd is both sites will let me use an external Dreamhost-hosted email for bounce processing, but each of them if I use a correctly setup email account for the same domain the site is hosted at don't work - even tho one is using Gsuite for mail processing and one is using Cpanel.

None of this is urgent any more as we've got CiviMail working again - but still not sure what the wider issue might be (perhaps nothing to do with Civi at all), and thought it useful to track this here because it happened to me on two sites so maybe happens to someone else.

# Answer
> 0 votes
Glad you got it working back.

smtp protocol is made to be very resilient, but it means as well it's really hard to know for sure when/what is going wrong.

I'm not sure I understood the details of your configuration, but a somewhat common problem is when you have a local satellite mail server (that will always tell civi the mails are sent as it accepts them) and that isn't able to send it forward for whatever reason... and report that issue in a mailbox that isn't properly read by civi (for another problem).

So from civi point of view, it's all working fine, and in that specific case, it's very difficult to identify there is a problem (or two, as one hides the other), unless you add human brains that is able to identify that it shouldn't have zero bounces and investigate further.

Beside recommending to validate the bounce process and checks that it works on a regular basis, not quite sure how civi can do better on its own.

---
Tags: civimail
---


# Question
Title: What does 'Number of Items to Process For Each Queue Item' mean when using the API Importer extension?
I'm using Civi 5.35 and Drupal 7. We've installed the API CSV Import extension. At ~/civicrm/index.php?q=civicrm/csvimporter/import , I see a place to enter "Number Of Items To Process For Each Queue Item" followed by "This is the number of rows to process in each queue item. (Eg. 10)"

What is a "queue item"? Do I enter a 'Number of Items' value of, say, 2,000, if I want to 'process'/import 2,000 records at a time, but my entire import file can contain say 50,000 or 500,000 records, as long as it does not exceed 3 megabytes?

Thanks!

# Answer
> 3 votes
API CSV Import extension uses Job Scheduler to import the records in Civi. The job scheduler breaks down the whole csv into small batches(also known as queue item) to import to avoid timeout issue, memory excess and various reasons.

'Number of Items' is used to define how many records you want to process in single single queue. If you specify 2000 than there will be 25 queue item lined up in job scheduler to process 50k records with 2k in each queue. The 'Number of Items' you specify should respect the max time out and memory size of the server or it will always result in error. I prefer to set to 500 per batch for better results.

HTH

Pradeep

---
Tags: api, import, data-import, csv
---


title: Unwanted CiviRule email sent to Admin

text:
# Question
Title: Unwanted CiviRule email sent to Admin
I have a client with a simple set-up. When A assigns an activity to B with C, then B and C get notified per e-mail with a special template. But also D (me i.e Admin) and I'm not mentioned in any of the civirule and this address doesn't either show up in any parameter for e-mails.

Is there a way to prevent this?

# Answer
> 1 votes
There is no strange behaviour, just lack of information ;-) A trainee was using this e-mail address to create 'Activity'

# Answer
> 0 votes
It will be configured somewhere, CiviRules does not automatically send unspecified email in my experience:-) Is there something else, a Scheduled Reminder or a configuration somewhere?

---
Tags: civirules
---


# Question
Title: Does the system status check and upgrade version check only list available updates for in-app extensions?
It seems like extensions which are listed in the extension directory but not available for in-app distribution are not included in update checks. They also don't appear in the sites/default/files/civicrm/upload/cache/extensions.json file which seems to be the list of available updates it retrieves. I've run the version update job and "refresh" button on the extensions page manually and can see it downloads a new one.

So for example on the status page it says all extensions are up to date but I know they are not.

Is this a local config issue or is this just how it works?

# Answer
> 4 votes
So I discovered what you can do is put this in civicrm.settings.php:

`$civicrm_setting['domain']['ext_repo_url'] = 'https://civicrm.org/extdir/ver={ver}|cms={uf}|ready=';`

Note the `ready=` at the end (the default is without it).

This then properly shows you pending upgrades for your installed extensions that are in the extension directory but not approved for automatic distribution. The downside is it now lists all the available extensions for your civi version on the Add New tab on the Extensions page as if they were approved for automatic distribution.

An alternative is I've made an extension that adds a status check to show you available updates for your extensions that are not automatically distributed: https://civicrm.org/extensions/notify-about-upgrades-unapproved-extensions

---
Tags: extension
---