# Question
Title: Is there a recommended way to customise the quicksearch?
I'm trying to customise the top-left quicksearch box so that it recognises some of our internal codes without selecting a specific option in the dropdown.

I used to adapt xavier's great Quick Contact Autocomplete extension, but this uses the crmAutocomplete action which I think went away in 4.5.

Is there a particular recommended way of doing this in 4.5+?

# Answer
For customizing the list of options, see this question.

To override the quicksearch behavior, you could try hook\_civicrm\_apiWrappers to modify the output of the contact.getquick api.
> 4 votes

# Answer
I found it quite hard to replace the customization done with hook\_civicrm\_contactListQuery (CiviCRM \<= 4.4) with the new hook\_civicrm\_apiWrappers (CiviCRM \>= 4.5).

So I am sharing my results here.

My goal is to have a way to search quickly either by contact name, or by a custom field that stores a member identifier (different from internal identifier and external identifier).

My old code looked like this :

```
function myext_civicrm_contactListQuery( &$query, $name, $context, $id ) {
  if ($context == 'navigation') {
    // if not name / email search, just keep the standard behavior
    if (!isset($_GET['tableName']) || $_GET['tableName'] != 'cc') return;

    // if the search is a number, try to look for the custom member id
    if (is_numeric($name)) {
      $query = "SELECT cc.id as id, CONCAT_WS( ' :: ', CONCAT(sort_name, ' (', im.num_ro_de_membre_37, ')') ) as data, sort_name
                FROM civicrm_contact cc
                LEFT JOIN civicrm_value_r_sum_de_membre_6 im ON ( cc.id = im.entity_id )
                WHERE im.num_ro_de_membre_37 = '$name'
                AND cc.is_deleted = 0
                LIMIT 5";

    } elseif (strlen($name) < 2) {
      $query = "";
    }
  }

}

```

And now :

```
function myext_civicrm_apiWrappers(&$wrappers, $apiRequest) {
  if ($apiRequest['entity'] == 'Contact' && $apiRequest['action'] == 'getquick') {

    // search by member id if the search is a numeric value
    if (is_numeric($apiRequest['params']['name'])) {
      $wrappers[] = new CRM_Myext_APIWrapper();
    }
  }
}

// i need to do this because there is no function civicrm_api3_contact_getlist
function emulate_civicrm_api3_contact_getList($params) {

  // not loaded by default
  include_once "api/v3/Generic/Getlist.php";
  include_once "api/v3/utils.php";

  $apiRequest = array(
    'entity' => 'Contact',
    'action' => 'getlist',
    'params' => $params,
  );
  $res = civicrm_api3_generic_getList($apiRequest);

  // reformat the output to look like getquick
  foreach ($res['values'] as $idx => $value) {
    $res['values'][$idx]['data'] = $value['extra']['sort_name'] . " ({$value['extra']['custom_37']})";
  }

  return $res;
}

```

And a new APIWrapper class in a dir CRM/Myext/APIWrapper.php :

```
class CRM_Myext_APIWrapper implements API_Wrapper {

  public function fromApiInput($apiRequest) {

    // change api call - getquick is deprecated and doesn't work with custom field
    $apiRequest['entity'] = 'Contact';
    $apiRequest['action'] = 'getlist';
    $apiRequest['function'] = 'emulate_civicrm_api3_contact_getList';

    // change name param to field custom_37 and remove useless params
    $apiRequest['params']['params']['custom_37'] = $apiRequest['params']['name'];
    unset($apiRequest['params']['name']);
    unset($apiRequest['params']['field_name']);
    unset($apiRequest['params']['table_name']);

    // restrict return data (getlist doesn't use return param)
    $apiRequest['params']['extra'] = array('sort_name','custom_37');

    return $apiRequest;
  }

}

```
> 7 votes

# Answer
Based on @samuelsov answer and samples I've developed an extension that could customize the quicksearch fields

More info here: https://civicrm.org/extensions/quicksearch
> 2 votes

---
Tags: search, quicksearch
---


title: Extend quick search box

text:
# Question
Title: Extend quick search box
We would like to extend the quick searchbox to include the fields standard civi field 'source' as well as potentially add some of our custom fields.

Any pointers to where in the base code and examples would be hugely appreciated!

# Answer
The base code for it is located at navigation.js.tpl where all the menus have been listed with their search inputs.

Adding source as a search field seems to just add another line with a value as `source`.

Note: Changing core file may not be recommended as you may lose them after upgradation.
> 2 votes

# Answer
I've developed an extension that could customize the quicksearch fields

More info here: https://civicrm.org/extensions/quicksearch
> 1 votes

---
Tags: quicksearch
---


# Question
Title: Amending relationship types has broken civi case
I was updating a site adding new relationships for cases, and relabelling old relationships to make more sense in case context.

What I've since found is that the updates to the relationship table don't automatically feed thru to the case relationships/ settings.

Fast forward a couple of months and it turns out that the case manager is no longer showing in the case dashboard.

I've either changed the designated case manager relationship, or relabelled the relationship and it is not being picked up by the dashboard.

Can anyone point me to where I'd find the case type setting file in the backend so i can start diagnosing what I've relabelled and what relationship the case expects to see as manager?

# Answer
Check the name\_a\_b and/or name\_b\_a fields in the database table civicrm\_relationship\_type. Check that they match the `<name>` entries in the `<CaseRoles>` section in the xml files in `<path_to_civicrm>/templates/CRM/Case/xml/configuration`. Depending on your version or upgrade history the xml definition might be in the civicrm\_case\_type table in the definition field.
> 1 votes

---
Tags: civicase, relationships, dashboard
---


title: Views not updating multiple records

text:
# Question
Title: Views not updating multiple records
I upgraded from Drupal CiviCRM 5.2 to 5.5 recently. Prior to the upgrade, I had a Views page that listed a table of custom fields (multiple records). The views was based on a "CiviCRM Contacts", and then I added each Civi field into the Views page.

What I noticed today is that if I deleted a record in the multiple records field in CiviCRM, the Views page still lists that record in the table. Similarly, if I added a new record in the multiple records, it shows up in CiviCRM but not in the Views. Very strange. Is anyone else having this problem?

I've cleanup caches, update paths (in CiviCRM), cleared the views cache, and also in Drupal (Run cron, Run a database update, flush all caches). The only error I see right now is below, but I'm not sure this is related to problem (I know this bug has already been reported):

```
Warning: array_combine(): Both parameters should have an equal number of elements in CRM_Contact_BAO_Relationship::buildRelationshipTypeOptions() (line 2222 of C:\xampp\htdocs\sites\all\modules\civicrm\CRM\Contact\BAO\Relationship.php).

```

# Answer
I realized that my Drupal database and CiviCRM were linked to different databases. I forgot to update the settings file during my testing.
> 1 votes

---
Tags: drupal-views
---


# Question
Title: After duplicating event, creating a new location updates the parent event location
I duplicated an event, and then, on that new event, clicked "Create New Location." The location saved successfully, but the old event and all events created from it had their locations updated as well. Why?

# Answer
> 1 votes
It looks like a fix will be included in 5.5.0

https://lab.civicrm.org/dev/core/issues/255

---
Tags: civievent, location
---


# Question
Title: How do I filter out memberships (by relationship)?
I have a report that generates a list of members who need to be called to renew, however I want to remove from the report all those with a Membership Type: Annual Member (by relationship). Any suggestions? Thanks!

Civi 5.3.1 on Drupal

# Answer
> 3 votes
You can use 'Membership Owner ID' filter under filters tab of report.

When you set 'Membership Owner ID' as 'is empty(NULL)' it will show all the primary membership owners that is membership not created by relationship

When you set 'Membership Owner ID' as 'is not empty(NULL)' it will show all the secondary membership owners that is membership created by relationship membership

PS: Don't miss to filter Membership type ;)

HTH

Pradeep

---
Tags: civimember, reports
---


# Question
Title: PxPay Recur Payment gets stuck in pending mode
CiviCRM 5.3.1 with Omnipay extension 2.5

Added a PaymentExpress PxPay processor. After setting up a recur contribution for a contact the first payment is done successfully without any errors.

The second payment run by the scheduled job `ProcessRecurring` only records a pending payment for the contact. The job log at `civicrm/admin/joblog?jid=18&reset=1` shows only the starting of the execution message. There is no `Finished` indication of the job so it seems it errored in between and aborted the execution.

There is a success message in the ConfigAndLog file at the same time when the above job was started at `00:41:45`-

```
Aug 10 00:41:46  [info] Contribution record updated successfully

Aug 10 00:41:46  [info] Success: Database updated

```

But, when I check the log tables, the contribution was never set to completed.

1. If I execute the job manually the recur payment is completed successfully.
2. If I manually run the `completetransation` API from the API explorer, it correctly completes the payment.

So there seems to be no way I can replicate this behaviour from my side. It is just that the automatic cron job fails to give the proper result. Shrug.

No error reporting in the system log table -

> 295 payment\_notification processor\_id=11 {"q":"civicrm/payment/ipn/761/11","result":"00001100039341640bfef9f4cb5d3c06","userid":"\****","IDS\_request\_uri":"/civicrm/payment/ipn/761/11?result=00001100039341640bfef9f4cb5d3c06&userid=\****","IDS\_user\_agent":"\***"}

Can anyone point out what is wrong happening here?

UPDATE: There is no system log entry for the recur payment. It seems the return URL is not triggered when the job is executed.

# Answer
> 1 votes
We contacted the payment express and asked them to setup the PxPost creds for the client and then the recur started to work properly. Seems it is a necessary config for recur to function correctly using the automatic cron execution. Below lines is from Rishabh(Payment Express team member)

> The PxPay 2.0 is only used to tokenized the card details and PxPost runs in the backend to rebill those tokens in automatic process.
>
> You will not be able to rebill the tokens automatically, until you have PxPost setup in the backend.

Thanks :-)

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


# Question
Title: Limit the groups exposed in Group profile field
I'd like to use the Group field in a profile so people can sign up for our mailing lists. However, the Group field exposes ALL of our public groups, and I want it to to expose only the public MAILING list groups. Is there a way to do that?

Using CiviCRM 5.3.1 with WordPress 4.9.6.

# Answer
One way is to change the visibility of the groups to `User and User Admin Only` to hide the group display from all the public pages or profile.

If you don't want to do that, you might want a developer to add a js file on the profile rendering page and hide extra group values using javascript.
> 1 votes

---
Tags: profile, groups
---


# Question
Title: As of 5.4.0, Activity Search gives an empty result set - patch is available
We just upgraded our CiviCRM sites to 5.4.0 and found out that Search | Activity Search always leads to an empty result set. We're on Joomla 3.8.11 but I see the same result on the demo civicrm sandbox (which is running 5.6 alpha 1 on Drupal). Activities are still in place - they show up on contact pages and in the search builder. Is there a change in functionality on the Activity Search page that we're not aware of or is this a bug? Anyone able to confirm?

# Answer
> 4 votes
I could replicate this issue on my local machine, this seems to be a core bug and have logged issue at https://lab.civicrm.org/dev/core/issues/353.

I think the search does provide results when we disable record type options

So in the mean time until the bug gets fixed you can rely on this option (hope it shoes correct count of results ;))

I have also submitted PR for this at https://github.com/civicrm/civicrm-core/pull/12713 if you can please try after applying patch and then comment on PR about your QA that would help to speedup the process of merging the code in next release.

---
Tags: activities
---


title: Logs in CiviCRM

text:
# Question
Title: Logs in CiviCRM
In our Drupal 8 - CiviCRM we use Redis for caching and some log management. We would like that CiviCRM also logs errors to Redis, or any other log system for that matter. Is it possible to configure/swap the log classes as is possible with cache classes?

In `civicrm-core/packages/Log` I see there are other classes available for logging but I don't know how they are used. How would a syslog for example be used for error logs?

Is it at all possible to use a different class for error logs or has this functionality yet to be build?

# Answer
> 1 votes
You'd have to write your own but under Administer -\> System Settings -\> Debugging and Error Handling you can set a classname that will handle fatal errors. However this only handles fatal errors.

Another method is to override the CRM/Core/Error.php class. The docs don't seem to exist anymore or at least I can't find them, and I don't see it in the extensions documentation either but I think you can do this with an extension, but you can do it standalone by going to Administer -\> System Settings -\> Directories and specify a custom php directory, and then create a CRM/Core/Error.php under that folder (can copy the original file). Then edit that new file.

The Log folder under packages I think is just a dependency of some of the 3rd party packages. I don't think core uses it.

# Answer
> 4 votes
I created an extension that writes all the logs to Redis: https://civicrm.org/extensions/eavesdropper Issues, questions or feature requests can be added on github.

---
Tags: logs, drupal-8
---


# Question
Title: How can I expose event creation fields on Drupal 8 front end without CiviCRM Entity?
A key feature and service of our Drupal 8 site (in development) is to allow users to create their own events. I want to expose some of the event creation fields to them on the front end. Is there a way to do this in Drupal 8?

When I research, I see solutions that involve CiviCRM Entity and Webforms, but Entity does not appear to have a stable Drupal 8 release (the latest is 8.x-3.x-dev, updated on August 14th).

If the event creation fields were available when creating a profile, I would do it that way. Since I don't have sufficient programming experience to properly build an extension, my backup is to style the admin interface and CiviEvent creation form to look as much like our public-facing site as possible, but that's a last resort.

I apologize if this question has been answered already, and I missed other proposed solutions. Thank you in advance for any help you can provide.

# Answer
If an extension isn't on the table, then I think that CiviCRM Entity is the way to go, despite the lack of a stable release.

For what it's worth, it's not terribly uncommon to have sites in production with modules that don't have stable releases. It means more testing ensuring it works for your use case - but that testing also helps build toward the common solution that in a few months will be the standard way of doing what you're describing.

You may also research the Form Processor extension \- but this relies on having a Drupal module like CiviMRF, which doesn't have a D8 version.

Finally - I would chat with the folks behind both of these solutions. In particular, https://chat.civicrm.org has a "CiviCRM Entity" channel where you can talk with the authors. They may also be open to a sponsorship to prioritize the features you need.
> 0 votes

---
Tags: civievent, drupal-8
---


# Question
Title: clickatell sms "Recipient phone number is invalid or recipient does not want to receive SMS"
wordpress 4.9.6 Clickatell 4.1 All was working fine until upgrade to Civi 5.22 and upgrade to SMS API 1.5 (yup in retrospect should have tested separately!)

phone is home-mobile-primary

# Answer
> 2 votes
Looks like `sites/all/modules/civicrm/CRM/Activity/BAO/Activity.php` is failing in function `function sendSMSMessage(` because `$doNotSms = TRUE;` on line 3154. Set it to false and you should be Ok. However, the check will not happen, so all numbers (even Do Not SMS #s) will get the SMS.

The other option is to comment out the following block and add an if block: `/*
 if ($smsProviderParams['To']) {
 // If phone number is specified use it
 $toPhoneNumber = trim($smsProviderParams['To']);
 Civi::log()->debug(print_r($smsProviderParams, true)."\ntoID: $toID\n");
 //echo "<pre>".print_r($smsProviderParams, true)."</pre>";
 $doNotSms = FALSE;
 }
 elseif ($toID) {
*/
 if ($toID) {
 // No phone number specified, so find a suitable one for the contact`

# Answer
> 1 votes
Have raised this as an issue with the extension maintainers:

https://github.com/veda-consulting/org.civicrm.sms.clickatell/issues/20

# Answer
> 0 votes
This is a Civi core bug. It happens with Twilio too: I tested in Civi 5.4.0 .

Seamus Lee has filed an issue against Civi core and submitted a fix.

---
Tags: sms, clickatell
---


title: Payment link in Civi invoice

text:
# Question
Title: Payment link in Civi invoice
Our organization hosts events and has memberships individuals sign up for on our website. If someone signs up for a membership or registers for an event online they have two options for payment. They can choose to "pay now" on the registration page or they can choose "invoice me". We do not currently utilize the invoice feature in Civi. Instead we have our accounting department send an invoice to the requesting party. We would like to begin using Civi Invoice. However, we noticed there is no online payment functionality (i.e. payment link) in the link or email.

Is this something we would need to enable or is there a plug in that would allow us to send invoices that contain a payment link in the invoice or email? If not, does anyone have a work around process for allowing members to pay for membership online via an invoice.

# Answer
> 2 votes
You can use "Default invoice payment page" setting which is used to set contribution page that can be used to pay pending or partially paid contribution using payment processor.

More about the setting and uses:

Wiki: https://wiki.civicrm.org/confluence/pages/viewpage.action?pageId=346750980

Here is how you can achieve this

1. Create a Contribution Page by enabling Other amount and payment processor.
2. Navigate to Administer \>\> CiviContribute \>\> CiviContribute Component Settings and select contribution page create at #1 for **Default invoice payment page**
3. Create a contribution for a contact with **pending** status.
4. On User Dashboard of contact (/civicrm/user?cid=\[contact\_id\]) under contribution section you will see pay now button against the pending contribution. When clicked on pay now it will redirect to online contribution page where one can pay pending contribution online using credit card.
5. You can also generate the link to pay pending contribution online `http://domainname.com/civicrm/contribute/transact?reset=1&id=[contribution_page_id]&ccid=[contribution_id]`

where contribution\_page\_id(id) is contribution page id created at #1 and contribution\_id(ccid) is contribution id created at #3. 6. You can use this link to send in invoice for people to pay online.

HTH

Pradeep

---
Tags: civievent, civicontribute
---


# Question
Title: Can we disable the "Record payment" button on contributions?
I've finally just now noticed the ability to record multiple separate payments on contributions in the back-office area, using the "record payment" button.

I didn't realize it, but according to https://github.com/civicrm/civicrm-core/blob/master/release-notes/4.7.16.md#features this has been around since version 4.7.16.

This is causing a couple of problems for some of my sites:

1. After we enter a partial payment against a contribution, there's no easy facility to find that payment (edit: I mean search for that payment, not just see it within the contribution record). I can search for contributions, but not payments.
2. A couple of my client's custom features weren't anticipating multiple payments, and so aren't correctly reflecting the payments they're entering against contributions.
3. I can't find much documentation about this online.

The above-referenced release notes indicate this was added as part of an effort to support accrual-based accounting. If that's so, is there perhaps a way to disable this functionality, and/or disable other accrual-accounting-related features?

# Answer
Once can find payments for contribution by clicking on Amount from Contribution result. The section which appears after clicking on amount shows all the payments against the contribution.

I believe its not possible to turn off 'Record Payment' link from contribution through UI. But you can implement hook\_links() in your extension and unset the record payment link. Here is the snippet on how you can remove the 'record payment link

```
function extension_civicrm_links($op, $objectName, $objectId, &$links, &$mask, &$values) {
  if ($objectName == 'Contribution' && 'contribution.selector.row' == $op) {
    foreach ($links as $key => $link) {
      if ($link['name'] == 'Record Payment') {
        unset($links[$key]);
        break;
      }
    }
  }
}

```

By default Accrual Accounting is disabled until you enable (Enable Deferred Revenue) them by navigating to Administer \>\> CiviContribute \>\> CiviContribute Component Setting. You can turn off the settings if its enabled in your site.

HTH

Pradeep
> 2 votes

---
Tags: accounting, partial-payments
---


# Question
Title: High-load situation produces "Deadlock found when trying to get lock; try restarting transaction"
Each year we go through an enrollment process, where we have groups of up to 60 users at a time registering for our site simultaneously on our one-CPU, 3GB virtual machine running the latest CiviCRM with Drupal.

We authenticate users using ADFS through SimpleSAMLphp - their user accounts are created in Drupal when, as part of the enrollment process, they click on a link that logs them in and redirects to a webform with the info we want to gather for Civi. *i.e. /saml\_login?destination=webform*

Last year was a laugh-and-a-half when Apache (with mod\_php) decided that was all far too much for it to handle, and killed MySQL hard in the process. A few times. Have since switched to Nginx with PHP-FPM and am now sleeping far better at night.

I have noted that about 10% of users are encountering a CiviCRM 'yellow box on white page' error - don't have the exact error message to hand, but to the effect of 'unknown error'.

Looked up the logs, and it's throwing a MySQL error:

> \[nativecode=1213 \** Deadlock found when trying to get lock; try restarting transaction\]

When attempting to run the query:

> INSERT INTO civicrm\_email (contact\_id , location\_type\_id , email , is\_primary , is\_billing , on\_hold ) VALUES .....

My cursory searching shows a few mentions of this kind of situation in JIRA, but there wasn't much to go on. If anyone has any suggestions of how I'd go about avoiding this happening in the future, I'd be glad to hear them!

**Edit:**

```
Aug 24 10:12:39  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary , is_billing , on_hold ) VALUES ( 69088 ,  1 , 'username@organisation.tld' ,  1 ,  0 ,  0 )  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary , is_billing , on_hold ) VALUES ( 69088 ,  1 , 'username@organisation.tld' ,  1 ,  0 ,  0 )  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary , is_billing , on_hold ) VALUES ( 69088 ,  1 , 'username@organisation.tld' ,  1 ,  0 ,  0 )  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]"]
)

Aug 24 10:12:39  [info] $backTrace = #0 /var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php(232): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/html/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#3 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#4 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...", "DB_Error", TRUE)
#5 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...", "1213 ** Deadlock found when trying to get lock; try restarting transaction")
#7 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#9 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#10 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(1040): DB_DataObject->_query("INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#11 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(571): DB_DataObject->insert()
#12 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/Email.php(102): CRM_Core_DAO->save()
#13 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/Block.php(348): CRM_Core_BAO_Email::add((Array:5))
#14 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/Location.php(66): CRM_Core_BAO_Block::create("email", (Array:12), NULL)
#15 /var/www/html/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(344): CRM_Core_BAO_Location::create((Array:12), TRUE)
#16 /var/www/html/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(2010): CRM_Contact_BAO_Contact::create((Array:12))
#17 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/UFMatch.php(298): CRM_Contact_BAO_Contact::createProfileContact((Array:4), (Array:0))
#18 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/UFMatch.php(118): CRM_Core_BAO_UFMatch::synchronizeUFMatch(Object(stdClass), "11667", "username@organisation.tld", "Drupal", NULL, "Individual", FALSE)
#19 /var/www/html/sites/all/modules/civicrm/drupal/civicrm_user.inc(51): CRM_Core_BAO_UFMatch::synchronize(Object(stdClass), FALSE, "Drupal", "Individual")
#20 /var/www/html/modules/user/user.module(101): civicrm_user_login((Array:2), Object(stdClass), NULL)
#21 /var/www/html/modules/user/user.module(2308): user_module_invoke("login", (Array:2), Object(stdClass))
#22 /var/www/html/modules/user/user.module(2321): user_login_finalize((Array:2))
#23 /var/www/html/modules/user/user.module(2351): user_login_submit((Array:0), (Array:2))
#24 /var/www/html/sites/all/modules/contrib/simplesamlphp_auth/simplesamlphp_auth.module(241): user_external_login_register("username@organisation.tld", "simplesamlphp_auth")
#25 /var/www/html/includes/module.inc(957): simplesamlphp_auth_init()
#26 /var/www/html/includes/common.inc(5314): module_invoke_all("init")
#27 /var/www/html/includes/bootstrap.inc(2538): _drupal_bootstrap_full()
#28 /var/www/html/index.php(20): drupal_bootstrap(7)
#29 {main}

```

# Answer
> 1 votes
Concurrency problems are tricky to reproduce, and therefore solve.

If you have the time and skill, writing a small scrapper that would automate submitting the form would be super helpful to create several users at the same time and hopefully reproduce your problem.

This being said, let's shoot from the hip, see if we can guess:

Are you creating several contacts from your webform? eg. creating both the individual and current employer, and setting emails addresses for both?

Can you get a calltrace of when the deadlock occurs? that would help narrowing down what triggers it

---
Tags: mysql
---


title: Civi Price Sets

text:
# Question
Title: Civi Price Sets
For an upcoming event, we are requiring a security deposit that everyone must pay. How do I add that to the price set so that it's always there and the user does not have to do anything with it and cannot remove it?

# Answer
> 1 votes
Try to add price\_set\_field for the same in the price\_set.

so, it remain always also make it as radio (mandate)

I guess this helps !!!

# Answer
> 0 votes
You can add price field to a price set used for an event. You can navigate to CiviCRM \>\> Administer \>\> CiviEvents \>\> Manage Price Sets.

Look for the price set you used in the event and Click on 'View and Edit Price Fields' besides it. (Price set used for event can be checked under fee section of event configuration page.)

You will be redirected to list of Price fields in price set. Click on Add Price Field button and add price field of type radio with single option in it and make it as required.

---
Tags: civievent, price-sets
---


# Question
Title: For all reports, CiviReport shows a page with no content
I'm getting a mostly blank screen when trying to run reports. I see the CiviCRM menu and page heading, then the page footer, with nothing in between.

I get the same thing for every report and every report template. The "Manage Templates" screen works, but if I click "Edit" on a template, I get a pop-up with no content.

I have no errors in the CiviCRM log file, no errors in my PHP log file, and no browser console errors. This is CiviCRM 4.6.26.

What steps can I take to further troubleshoot this problem?

# Answer
> 1 votes
I have just had this issue whilst upgrading from ver 5.3 to 5.4 (using Drupal 7 as the CMS). After a couple of frustrating days going through the debugger and finding nothing, I did a fresh 5.40 install on a VM and all was fine.

Going back over the steps I took upgrading, and retraced - one of the steps was to clear down the files/civicrm/templates\_c/en\_US directory. I cleared down everything I could in the en\_US directory and all was fine.

See Clear Cached file in the section

> Upgrade in Drupal 7

---
Tags: civireport, reports
---


title: Can't edit or save CiviCase types

text:
# Question
Title: Can't edit or save CiviCase types
I have a fresh install of CiviCRM v5.4.0 running on Wordpress. I wanted to modify the default CiviCase types, but the 'Save' button or the 'more' buttons are inactive, i.e. nothing happens when I click on them. Is this a known issue?

P.S. I'm running shoreditch theme 0.1-alpha22, but I have this issue regardless if I use the theme or not.

# Answer
> 2 votes
Yes, I think this is a core bug because of recent change. You can find temporary solution at CiviCRM 5.4, Unable to create case type (angular validation error)

HTH

Pradeep

---
Tags: wordpress, civicase
---


# Question
Title: User account fields missing from create user account profile
I've built two profiles to create user accounts 1) Volunteers Adds contacts to volunteers group to sync with volunteer role 2) Volunteer Managerss Adds contacts to volunteer managers group to sync with volunteer managers role

Both worked fine last week

Went to review the Vol manager profile today and none of the user acct fields (choose username, enterpassword etc) were visible on the profile. But the fields still showed in the volunteer profile.

Have been tinkering with profile settings, saving and unsaving. Can't make them reappear.

Both profiles have same settings: profiles set as standalone form. acct creation required checked. Site visitors can create own accounts set in drupal config. anon users can use profiles in create mode in drupal permissions.

Went to look at the volunteer profile a few minutes ago, and although it was showing user fields earlier, now the fields are missing from both profiles.

Gah!

Any help/ suggestion will be much appreciated!

drupal 7.59 civi 5.3.1

# Answer
> 3 votes
You will need to check few things

1. Check if the profile has create user account option enabled.
2. Check drupal settings if Anonymous user is allowed to create account(Configuration \>\> Account Settings).
3. Make sure your profile has email field and is primary or uses default location type

# Answer
> 1 votes
Thanks to Pradeep's intervention I've reinstated the user acct fields.

The site default location is home. So when first creating the profiles they defaulted to email location= home.

As I was making changes to the system I though semantically "Main" was a better location and updated the profile emails fields accordingly.

That's what broke the profiles.

re-editing the fields and setting location = home, gives me working user acct profiles.

---
Tags: profile, user-account
---


# Question
Title: How do I eliminate the error "Timestamp Mismatch"?
I see a lot of related questions, but none that quite handle the following:

System: Ubuntu/Wordpress 4.9.8 Civi 4.7.23

I'm about to upgrade Civi but the error about "Timestamp mismatch" is making me worry.

The MySQL time stamp is correct and matches "date" on the server.

The Wordpress time setting is correct and matches "date" on the server.

The PHP time setting as exposed in phpinfo() is correct although it of course doesn't spell out a time, just a timezone, which is correct.

So why is this error persisting?

# Answer
> 1 votes
\[UPDATE - IS FIXED\]

I fixed this by revisiting WordPress's settings and changing the Timezone setting from "UTC -5" to "Chicago". Both of these settings reflect the same time zone, so why these two settings are seen as different I can't say, but Civi no longer complains.

So I guess the lesson is: if you want to get rid of this error and you're showing no actual disagreement on timezone between the server, PHP or WordPress, go to WordPress and pick the timezone that is named for the city you're aiming at.

Hope that helps someone.

---
Tags: timestamp
---


title: What is 'listing mode' for Profiles?

text:
# Question
Title: What is 'listing mode' for Profiles?
Wordpress and civicrm 5.4

I've created a profile to allow our members to update some of their contact info.

It displays nicely in edit mode and if a member updates a field all is good and I get a nice success message.

If the user chooses not to update and presses the cancel button the page (I think) attempts to display the form in listings mode and I get an error message;

*No fields in this Profile have been configured as searchable. Ask the site administrator to check the Profile setup.*

The same error message is displayed if a field is changed and the "Back to Listing" button which appears at the bottom of the confirmation page is used.

a few questions: 1. What exactly is 'listing mode'? Do i understand correctly that is a publicly available / searchable page?
2. I'm guessing to avoid above error I need to configure at least some fields as searchable in the Profile set up? But I have tried making some fields searchable and the error perists. Have I misunderstood? 3. If I do succeed in making feilds searchable and thus presumably removing the error - who are the fields visible to exactly?

# Answer
Listing mode in profile enables the user to do a follow up search for other records which also have that same field value.

For example, you might set `City` to Public Pages and Listings. After the user conducts a search and views the details for a record they can search on the city value and look for other field values present in the record.

FWIW, If you don't want to navigate the user to listing page on hitting the cancel button, you can add a custom URL for this button in profile settings. It also provides a flexibility to simply remove the cancel button from profile.

The above config is present in `Advanced Settings` pane in the profile settings form.
> 2 votes

---
Tags: profile
---


# Question
Title: Database Error Code: Access denied for user 'civi'@'localhost' (using password: YES), 1045
I encounter a really weird issue. I'm moving my CiviCRM install from an Ubuntu 14.04 server to an Ubuntu 16.04 server. Mysql is now 5.7.21.

CiviCRM version is the latest 4.7.30

The default SQL mode only\_full\_group\_by is disabled.

If I connect using the terminal from the same localhost using the same username/password (I triple triple triple checked), it runs like a charm.

The CiviCRM page always shows a DB Error: unknown error (duh!). Enabling the backtrace gives me more info:

```
[callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] =>
SELECT  v.name as name ,v.value as value, v.grouping as grouping
FROM   civicrm_option_value_en_US v,
       civicrm_option_group_en_US g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'wysiwyg_presets'
  AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviMember","CiviMail","CiviReport")) )  ORDER BY v.weight [nativecode=1045 ** Access denied for user 'civietui'@'localhost' (using password: YES)]

```

Any help much appreciated

**Update**

If I deliberately use a wrong username or password, I have a different error:

Initialization Error

It shows that somehow, Civi connects to the DB up to a certain point

**update 2**

mysql\> show grants for username@localhost;

| Grants for username@localhost |

| GRANT USAGE ON *.* TO 'username'@'localhost' | | GRANT ALL PRIVILEGES ON `civicrm_DB`.\* TO 'username'@'localhost' WITH GRANT OPTION |

# Answer
MySQL 5.7 has "pluggable authentication", which means that you're not always logging in by password from your terminal. You'll want to ensure that the `mysql.user` table shows a value of `mysql_native_password` for all users in the `plugin` column.

If you're logging in to the terminal as the `root` user on Ubuntu 16.04, then by default you're not using `mysql_native_password`, you're using `auth_socket`. This could lead to misleading results where you can log in from the command line with what SEEMS like a good password! If this is your problem, you may need to install the `mysql_native_password` plugin before you can set the password.
> 1 votes

# Answer
Look at your imported SQL file any reference to the old site mysql user and replace it with your new site user.

CiviCRM 4.6.x has mysql user hardcoded, for exemple:

/\*!50013 DEFINER=`old_mysql_user`@`localhost` SQL SECURITY DEFINER */ /*!50003 CREATE\*/ /*!50017 DEFINER=`old_mysql_user`@`localhost`*/ /\*!50003 TRIGGER civicrm\_value\_autoritzacions\_16\_after\_update after update ON civicrm\_value\_autoritzacions\_16 FOR EACH ROW BEGIN

This also run in an import SQL error...

After cleaning this I get ride of your error...
> 0 votes

---
Tags: error, mysql
---


title: Civi Event discount

text:
# Question
Title: Civi Event discount
I need to use a discount that is activated when the person registering for an event checks a box on the registration form. How do I connect the checkbox on the form with the discount?

# Answer
I have done something similar using a custom template (.extra) and some jQuery. Here is how it worked:

* Created a custom field which is the trigger, in our case "are you a member"
* Setup a price field in a price set that has line items for the regular pricing and the discount pricing.
* Use jQuery to hide the price set initially.
* When user selects an option in the custom field it triggers the jQuery which shows the price set and the rows of the price field that are appropriate to the selection. So in our case if they select that they are a member it shows only member pricing if the select that they are not a member it shows only non member pricing.

This scenarios was created for an organization that didn't want to have members login to get the member discount so this put them on the honor system. We have used this method with various similar scenarios.

Hope this helps.
> 1 votes

# Answer
Don't think this is possible with some custom code addition in an extension.

You might try adding two similar events with read-only checkboxes -

* On one event, this checkbox will be enabled by default. Add discount to this event page only.
* Second event will have this checkbox disabled by default. No discount added to this page.
> 0 votes

---
Tags: civievent, cividiscount
---


# Question
Title: "participant listing options" how to add another option with more fields
The existing options are name only name and email name, status and register date

How do i add an option with more fields eg role, phone etc is it a template somewhere?

# Answer
> 2 votes
This is how i would do

1. Create Extension
2. Add install code via Managed hook to install new option value for option group 'participant\_listing'

snippet:

```
{
  "option_group_id": "participant_listing",
  "label": "Your Name",
  "value": "4",
  "name": "Your Name",
  "description": "CRM_Event_Page_ParticipantListing_YOURNAME",
  "is_active": "1"
}

```
3. Copy CRM/Event/Page/ParticipantListing/NameStatusAndDate.php and CRM/Event/Page/ParticipantListing/NameStatusAndDate.tpl into your extension folder and rename the files to YOURNAME.php and YOURNAME.tpl resp.
4. Change the code(in YOURNAME.php) in respective function like run(), preProcess(), orderBY() as per your need and you can also modify the tpl file as per your needs.
5. Test! Test! Test!

HTH

Pradeep

---
Tags: civievent
---


title: Price Set Total Amount not displaying

text:
# Question
Title: Price Set Total Amount not displaying
I have a WP civicrm install. I am using a price set on a membership contribution page, but the total amount is not displaying.

All works fine on test drive but on the live page no total amount is displayed at all, either initially or when any items from the price set are chosen.

It seems like the price total calculation is working as when contribution is confirmed the next pages shows the correct amounts.

# Answer
> 0 votes
fixed by disabling Wordpress Autoptimize plugin

---
Tags: price-sets
---


# Question
Title: Who is 'backup_Mk047aB'@'%' from the error 'The user specified as a definer ('backup_Mk047aB'@'%') does not exist'
While the back-end is working - e.g. I can add fields to profiles, etc. -, I see form data perfectly fine when calling pages, I cannot modify or add contacts resulting in

```
Sorry but we are not able to provide this at the moment. DB Error: unknown error

```

The log shows the following:

```
#6 /*/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysql.php(895): DB_common->raiseError(-1, NULL, NULL, NULL, "1449 ** The user specified as a definer ('backup_Mk047aB'@'%') does not exist")

```

Searching the CiviCRM database - I cannot find a reference to a user called "backup\_Mk047aB" (not anywhere in Wordpress databases either).

A previous post notes that

> You will get more details in civicrm log file (\*/file/civicrm/ConfigAndLog). Also the main reason of this type error is, we are using few fields that are disabled. check custom fields status that are using for contact. see here

I then tried using my webpage form and populated all available fields of all profiles and then get

```
Database Error Code: The user specified as a definer ('backup_Mk047aB'@'%') does not exist, 1449

Additional details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )
[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => INSERT INTO civicrm_value_eda_individual_characteristic_2  ( name_of_affiliation_10,address_line_1_11,address_line_2_12,entity_id ) VALUES ( '','Henriette','',2 ) ON DUPLICATE KEY UPDATE name_of_affiliation_10 = '',address_line_1_11 = 'Henriette',address_line_2_12 = '' [nativecode=1449 ** The user specified as a definer ('backup_Mk047aB'@'%') does not exist]
[type] => DB_Error
[user_info] => INSERT INTO civicrm_value_eda_individual_characteristic_2  ( name_of_affiliation_10,address_line_1_11,address_line_2_12,entity_id ) VALUES ( '','Henriette','',2 ) ON DUPLICATE KEY UPDATE name_of_affiliation_10 = '',address_line_1_11 = 'Henriette',address_line_2_12 = '' [nativecode=1449 ** The user specified as a definer ('backup_Mk047aB'@'%') does not exist]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_value_eda_individual_characteristic_2  ( name_of_affiliation_10,address_line_1_11,address_line_2_12,entity_id ) VALUES ( '','Henriette','',2 ) ON DUPLICATE KEY UPDATE name_of_affiliation_10 = '',address_line_1_11 = 'Henriette',address_line_2_12 = '' [nativecode=1449 ** The user specified as a definer ('backup_Mk047aB'@'%') does not exist]"]
)

```

Although I've been back-upping my site - this error just came along...

**I'm lost und would love to meet user:backup\_Mk047aB - any ideas!?**

# Answer
OK - This is really heinous!!! I've been trying all day - posted the query and after doing that read following set of instructions on how to do **Alternate Moving the database using phpMyAdmin** -\> see the link and look at the heading **Post Import Activities**. It reads

> Very Important: If you have custom data profiles set up in your civiCRM, they may be present but for some unknown reason not be active. Go to your Administer \>\> Customize \>\> Custom Data and select "View and Edit Custom Fields". Select "Edit Field" for any one of the custom fields in the group and save it without making any changes. This will make civiCRM aware of the custom data fields and everything should be back on track.

I've got two set of custom fields, did what I'd been tolded = didn't work; did it again with the second set = **and voila**, everything works again.

There's two things on my mind:

1.) Can any of the more tenured members comment on what the problem is - it must be a bug, it can't be by design!?

2.) Who the heck is user:backup\_Mk047aB???
> 1 votes

# Answer
Let me do a guess (the problem is already solved, so probably we will never find out what happened).

The problem is caused not by a CiviCRM bug but by a MySQL peculiarity. In MySQL it is possible to define a trigger on a table. That is a piece of code that executes when a row is added or updated. Custom tables have triggers (they update the timestamp of the related table). If you want to make a backup and export the table, the trigger is also exported. And MySQL also exports in the trigger code the name of the user that makes the export. Your first question was who is *backup\_Mk047aB*? Most likely the database user used for the backup.

In editing the definition of the custom field you force CiviCRM to recreate the trigger code. But now it is recreated with the correct user, so the error disappears.

This is, however, a complicated workaround. It is more convenient to force a rebuild by calling the url:

`http://example.org.org/wp-admin/admin.php?page=CiviCRM&q=civicrm/menu/rebuild&reset=1&triggerRebuild=1`

See https://docs.civicrm.org/sysadmin/en/latest/troubleshooting/#trigger-rebuild.
> 1 votes

---
Tags: wordpress, custom-fields, database, error
---


# Question
Title: Report with access "everyone (includes anonymous)" not displaying correctly
Civicrm 5.2.2 Wordpress 4.9

I have created a report and given access to "everyone (includes anonymous)" and also given view reports permission to anonymous user. This is the URL https://leep.ngo/civicrm/?page=CiviCRM&q=civicrm/report/instance/170&reset=1

Only part of the report appears - image below I have tried reverting to standard twenty sixteen theme with same result.

# Answer
> 0 votes
If you don't want ANONYMOUS USER to view the report then you need to set the permission in your ACL

Administer -\> Userss and Permissions -\> Permissions (Access Control)

where you will find "CiviReport: access CiviReport" remove for ANONYMOUS USER

Also for the display you need to make changes in your Theme

I Guess this helps !!! Please feel free to contact me if you need more help

Thanks

---
Tags: civireport
---


# Question
Title: Migration error: “The user specified as a definer does not exist”
I recently moved the site to a new server and changed from a remote to a local MySQL server with different credentials. Most things seem to be working fine, but even though I've flushed caches, editing phone numbers leads to an AJAX "Network Error" message and the following in my logs:

```
$Fatal Error Details = Array (
  [callback] => Array (
    [0] => CRM_Core_Error
    [1] => handle )
  [code] => -1
  [message] => DB Error: unknown error
  [mode] => 16
  [debug_info] => INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id )
    VALUES ( 200025 , 2 , 1 , 0 , '6464646464' , NULL , 1 )
    [nativecode=1449 ** The user specified as a definer ('OLDUSER'@'%') does not exist]
  [type] => DB_Error
  [user_info] => INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id )
    VALUES ( 200025 , 2 , 1 , 0 , '6464646464' , NULL , 1 )
    [nativecode=1449 ** The user specified as a definer ('OLDUSER'@'%') does not exist]
  [to_string] => [db_error: message="DB Error: unknown error"
    code=-1
    mode=callback
    callback=CRM_Core_Error::handle prefix=""
    info="INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id ) VALUES ( 200025 , 2 , 1 , 0 , '6464646464' , NULL , 1 ) [nativecode=1449 ** The user specified as a definer ('OLDUSER'@'%') does not exist]"]
)
```

OLDUSER is nowhere in my configs and I have run both `drush cc all` and `drush cvapi system.flush`. Any ideas?

# Answer
> 3 votes
Xavier, your answer would have worked if I had had the luxury of re-importing. It turns out that there is a single function in the CiviCRM DB that for some reason uses a hard-coded username and password. I went in to the DB, located the "civicrm\_strip\_non\_numeric" function and updated the username and password. Problem solved.

# Answer
> 6 votes
Maybe you do not have ssh access and MySQL access. But this can also done from the user interface. To rebuild the triggers use the url:

```
http://<<servername>/civicrm/menu/rebuild?reset=1&triggerRebuild=1

```

(This is documented in the system admin manual.)

Recreating the Case Views can be done by disabling the case module and enabling it again (Goto *Administer -\> System Settings -\> Components*)

# Answer
> 2 votes
did you dump the old site and restored it on the new server?

check that mysql dump file, it should contain the olduser string, replace it in that file with the new one before doing the restore and it should work fine.

# Answer
> 2 votes
Similar to the accepted answer, in my case there were two case views which contained the incorrect definer information. Performed the alter query on the view and it was fixed.

Those view names were - `civicrm_view_case_activity_recent` and `civicrm_view_case_activity_upcoming`. Steps performed -

* Execute this statement.

```
SHOW CREATE VIEW civicrm_view_case_activity_recent.

```
* Copy the `create view` syntax.
* replace incorrect username with the correct one.
* replace `CREATE` word with `ALTER`.
* Execute the query and repeat the steps for `civicrm_view_case_activity_upcoming`.

# Answer
> 1 votes
The database triggers and the views can be recreated by using some code from the installation procedure. You can execute them with `drush`. To rebuild the triggers do

```
drush cvapi System.flush triggers=1

```

This also rebuilds the `civicrm_strip_non_numeric`. Unfortunately this does not work for the views. But this can be done with:

```
drush eval 'civicrm_initialize();CRM_Case_BAO_Case::createCaseViews()'

```

On other CMS systems you can do the same with `cv`:

```
cv api3 System.flush triggers=1
cv ev 'CRM_Case_BAO_Case::createCaseViews()'

```

---
Tags: database, error, migration
---


# Question
Title: Getting errors when moving D7/CiviCRM install form localhost xampp portable to another location
I have xampp portable installed on Windows 10.

I run about a dozen Drupal, Wordpress and other CMS on there. I can move the whole stack to different drives and machines and it all works perfectly, only one that can't work is D7/CIVICrm install.

When I move it to another directory I get this error:

```
fatal error: Cannot redeclare class CRM_Core_ClassLoader in C:\Core\XAMPP_2018\htdocs\crm4\sites\all\modules\civicrm\CRM\Core\ClassLoader.php on line 36

```

Any idea how to make this work?

The files are absolutely identical from source to location, copy and paste whole Xampp portable folder...

# Answer
Had to change the path name in civicrm.settings.php from:

```
G:\XAMPP_2018\htdocs\crm4

```

To

```
C:\Core\XAMPP_2018\htdocs\crm4\

```

This is around LINE 180:

Code looks like this:

$civicrm\_root = 'G:/August25/XAMPP\_2018/htdocs/crm4/sites/all/modules/civicrm'; if (!defined('CIVICRM\_TEMPLATE\_COMPILEDIR')) { define( 'CIVICRM\_TEMPLATE\_COMPILEDIR', 'G:/August25/XAMPP\_2018/htdocs/crm4\sites\default\files\civicrm\templates\_c\\'); }

But the slashes also have to be forward like this "/", I searched from Windows path, that's why I couldn't find the code using find and replace!
> 1 votes

---
Tags: export
---


# Question
Title: Getting "DB Error: syntax error" When searching for Deleted cases
When I try and do any kind of search for cases and I select the box for "deleted cases" I get a page saying: *DB Error: syntax error*

I am using Civi 5.3.1 and Drupal 7.59.

The log gives the following error:

```
    Aug 26 11:16:52  [info] $backTrace = #0 /sites/all/modules/civicrm/CRM/Core/Error.php(232): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /sites/all/modules/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
#3 /sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: syntax error", -2, 16, (Array:2), "SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#4 /sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-2, 16, (Array:2), "SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -2, NULL, NULL, "SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...", "DB_Error", TRUE)
#6 /sites/all/modules/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))
#7 /sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#8 /sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -2, NULL, NULL, "SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...", "DB_Error", TRUE)
#9 /sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-2, NULL, NULL, NULL, "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#10 /sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#11 /sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#12 /sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#13 /sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#14 /sites/all/modules/civicrm/CRM/Core/DAO.php(415): DB_DataObject->query("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#15 /sites/all/modules/civicrm/CRM/Core/DAO.php(1371): CRM_Core_DAO->query("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...", TRUE)
#16 /sites/all/modules/civicrm/CRM/Case/BAO/Case.php(1591): CRM_Core_DAO::executeQuery("SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civi...")
#17 /sites/all/modules/civicrm/CRM/Case/Selector/Search.php(368): CRM_Case_BAO_Case::getNextScheduledActivity((Array:3), "upcoming")
#18 /sites/all/modules/civicrm/CRM/Core/Selector/Controller.php(420): CRM_Case_Selector_Search->getRows(4, 0, 50, Object(CRM_Utils_Sort), 1, NULL)
#19 /sites/all/modules/civicrm/CRM/Core/Selector/Controller.php(340): CRM_Core_Selector_Controller->getRows(Object(CRM_Core_Selector_Controller))
#20 /sites/all/modules/civicrm/CRM/Case/Form/Search.php(307): CRM_Core_Selector_Controller->run()
#21 /sites/all/modules/civicrm/CRM/Core/Form.php(466): CRM_Case_Form_Search->postProcess()
#22 /sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form->mainProcess()
#23 /sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Case_Form_Search), "refresh")
#24 /sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Case_Form_Search), "refresh")
#25 /sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("refresh")
#26 /sites/all/modules/civicrm/CRM/Core/Invoke.php(309): CRM_Core_Controller->run((Array:3), NULL)
#27 /sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
#28 /sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#29 /sites/all/modules/civicrm/drupal/civicrm.module(445): CRM_Core_Invoke::invoke((Array:3))
#30 [internal function](): civicrm_invoke("case", "search")
#31 /includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:2))
#32 /index.php(21): menu_execute_active_handler()
#33 {main}

```

EDIT:

Query that is causing the error is:

```
Aug 26 11:16:52  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id, c_type.title as case_type, civicrm_case.case_type_id as case_type_id, cov_status.label as case_status, cov_status.label as case_status_name, t_act.status_id, civicrm_case.start_date as case_start_date, case_relation_type.label_b_a as case_role, t_act.desired_date as case_scheduled_activity_date, t_act.id as case_scheduled_activity_id, t_act.act_type_name as case_scheduled_activity_type_name, t_act.act_type AS case_scheduled_activity_type  FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id  INNER JOIN
(
  SELECT case_id, act.id, activity_date_time AS desired_date, activity_type_id, status_id, aov.name AS act_type_name, aov.label AS act_type
  FROM (
    SELECT *
    FROM (
      SELECT *
      FROM civicrm_view_case_activity_upcoming
      ORDER BY activity_date_time ASC, id ASC
      ) AS upcomingOrdered
    ) AS act
  LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
  LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
) AS t_act

        ON t_act.case_id = civicrm_case.id
 LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
 LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 4197
      AND case_relationship.case_id = civicrm_case.id )

 LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )

 LEFT JOIN civicrm_case_type c_type
 ON civicrm_case.case_type_id = c_type.id

 LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

 LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
 WHERE (1) AND  civicrm_case_contact.contact_id IN( 1502 )
 AND civicrm_case.id IN( 41)
 AND civicrm_case.is_deleted     = 1  GROUP BY case_id  ORDER BY case_scheduled_activity_date ASC 1 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 39]
    [type] => DB_Error
    [user_info] => SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id, c_type.title as case_type, civicrm_case.case_type_id as case_type_id, cov_status.label as case_status, cov_status.label as case_status_name, t_act.status_id, civicrm_case.start_date as case_start_date, case_relation_type.label_b_a as case_role, t_act.desired_date as case_scheduled_activity_date, t_act.id as case_scheduled_activity_id, t_act.act_type_name as case_scheduled_activity_type_name, t_act.act_type AS case_scheduled_activity_type  FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id  INNER JOIN
(
  SELECT case_id, act.id, activity_date_time AS desired_date, activity_type_id, status_id, aov.name AS act_type_name, aov.label AS act_type
  FROM (
    SELECT *
    FROM (
      SELECT *
      FROM civicrm_view_case_activity_upcoming
      ORDER BY activity_date_time ASC, id ASC
      ) AS upcomingOrdered
    ) AS act
  LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
  LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
) AS t_act

        ON t_act.case_id = civicrm_case.id
 LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
 LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 4197
      AND case_relationship.case_id = civicrm_case.id )

 LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )

 LEFT JOIN civicrm_case_type c_type
 ON civicrm_case.case_type_id = c_type.id

 LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

 LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
 WHERE (1) AND  civicrm_case_contact.contact_id IN( 1502 )
 AND civicrm_case.id IN( 41)
 AND civicrm_case.is_deleted     = 1  GROUP BY case_id  ORDER BY case_scheduled_activity_date ASC 1 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 39]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id, c_type.title as case_type, civicrm_case.case_type_id as case_type_id, cov_status.label as case_status, cov_status.label as case_status_name, t_act.status_id, civicrm_case.start_date as case_start_date, case_relation_type.label_b_a as case_role, t_act.desired_date as case_scheduled_activity_date, t_act.id as case_scheduled_activity_id, t_act.act_type_name as case_scheduled_activity_type_name, t_act.act_type AS case_scheduled_activity_type  FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id  INNER JOIN
(
  SELECT case_id, act.id, activity_date_time AS desired_date, activity_type_id, status_id, aov.name AS act_type_name, aov.label AS act_type
  FROM (
    SELECT *
    FROM (
      SELECT *
      FROM civicrm_view_case_activity_upcoming
      ORDER BY activity_date_time ASC, id ASC
      ) AS upcomingOrdered
    ) AS act
  LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
  LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id )
) AS t_act

        ON t_act.case_id = civicrm_case.id
 LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
 LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 4197
      AND case_relationship.case_id = civicrm_case.id )

 LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )

 LEFT JOIN civicrm_case_type c_type
 ON civicrm_case.case_type_id = c_type.id

 LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

 LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
 WHERE (1) AND  civicrm_case_contact.contact_id IN( 1502 )
 AND civicrm_case.id IN( 41)
 AND civicrm_case.is_deleted     = 1  GROUP BY case_id  ORDER BY case_scheduled_activity_date ASC 1 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 39]"]
)

```

EDIT:

I have included a screenshot of the case search options I selected to help clarify:

# Answer
I could replicate this on 5.4.0. This is fixed at https://github.com/civicrm/civicrm-core/pull/12560

Can you please QA after applying the patch. And also please do post your QA results on PR.

Pradeep
> 3 votes

---
Tags: error, civicase, search
---


# Question
Title: How to remove User login box from public event registration?
I am creating a public event for my organization and is showing the User Login box on the left side. This is the box use to log in into CIVI so as long as attendee ignores it and use the form there's no problem. But I want to remove it as some people have got confuse with it. Any help?

# Answer
> 2 votes
Actually this is a Drupal question. The login of CiviCRM is handled by the CMS. Form the screenshot I assume it is Drupal. So the visibility of the login block must be configured in Drupal. You find the configuration with the following URL

```
http://<servername>/admin/structure/block/manage/user/login/configure

```

Here you can disable the login box for event pages in the following way:

---
Tags: civievent
---


# Question
Title: How to copy a Custom Field in a related Contact's Custom Field?
I use a Webform to register both Parent and related Children to an Event.

I would like them to **share** a common Participant Custom Field information : this does not seem possible, since each Participant has his own.

I think of different options, but I don't know if they are possible :

**Option 1 :** in the Webform : set Child field i to Parent field j Value : but, as far as I know, this is not possible with the standard Webform Conditionals ; is there any Webform extension to do that ?

**Option 2 :** independantly from Webform : create a Rule (?) (and a CRON Job ?) to copy Parent field j Value to his related Children's fields i. But I don't know wether this is possible, nor how to do that.

Any help or suggestion is welcome (I am not a developper)

# Answer
> 0 votes
The option of creating a rule is a probably an easy solution. There is a Drupal module to have a tirgger in civirules when a webform is submitted (https://lab.civicrm.org/partners/civicoop/webform\_civirules)

Why do you want to store the information twice in your system and only ask once?

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


title: Webform layout box not working?

text:
# Question
Title: Webform layout box not working?
Not sure if I'm missing a step here, I am trying to set up an event registration with webform. For previous set ups the layout box has worked to have two columns, but for this one it isn't. The setting doesn't seem to matter, it's showing the information as vertical instead of horizontal. I am using Drupal 7, CiviCRM 5.3.1, and webform version 7.x-4.17.

# Answer
> 2 votes
Ah that’s because your base theme is a bootstrap theme. Some CSS edits are required to mold the form into two columns.

Here's an example (if your node id=96:

```
@media only screen and (min-width : 767px) {
  #webform-client-form-96 fieldset {
    width: 46%;
  }
}

```

---
Tags: webform-civicrm
---


# Question
Title: How to use civicrm to achieve campaign requirements
I'm looking at using CiviCRM as part of a solution for a campaign. what we need is a solution that aids volunteers to go into the field, speak to and collect data from voters ( we do not have adequate voter records here, so we have to build ours). We'll be collecting names, phone numbers, gender and asking a couple of questions. Volunteers are split into areas to collect the data.

Our phone bank solution will use the data collected to reach and talk to potential voters. Is there a way to also manage call centre volunteers using civicrm?

Can civicrm be used to manage the data collected? Can we manage volunteers (those collecting data and manning the phones) with it and track volunteer efficiency as we are paying them? What are the best ways to achieve this? We'll be using the wordpress framework

# Answer
CiviCRM is very well capable of doing what you want to achieve. You can store contacts in CiviCRM, adds a custom fields for information specific to your situation. Also you can store an activity of who added the person to the database.

CiviCRM is also capable of importing data from Excel (CSV files) and exporting it to excel.

Have you thought of how your volunteers are collecting this data? Are they using an tablet? Do you get CSV files and import them later on?
> 1 votes

# Answer
Another option we have been working towards is being able to upload a csv to Drupal Webform - and from that import add the data to civi - the advantage of using webform is it lets you import more complex data arrangements (eg relationships, Activities, etc) in a single import
> 1 votes

---
Tags: wordpress, contacts, phone
---


# Question
Title: How to change the value of a custom radio button field programmatically?
I have a CiviCRM profile that hosts a custom field. The custom field is of type "Radio-Button" and hosts two options:

What I'm trying to achieve is changing the values of each option programmatically when the form gets built. According to the hook reference, the `buildForm` hook should be used for doing such changes.

I know it's possible to change default values for simple input fields, it can be done by calling the `$form->setDefaults( $defaults )` function. But how does one modify more complicated HTML form elements such as radio buttons?

I browsed the `CRM_Core_Form` PHP class reference and cannot find the appropriate functions.

# Answer
If the options that are shown on the profile are custom fields, then the radio options displayed might be the option values of some option groups.

To add/remove any options from the profile, you can make use of `fieldOpions` hook. Eg -

```
function hook_civicrm_fieldOptions($entity, $field, &$options, $params) {
  //replace 1 with your custom address field id
  if ($field == 'custom_1') {
    $options['my_option'] = 'My Custom Option';
  }
}

```

Check the doc for more info about the param, etc.
> 1 votes

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


# Question
Title: Can I customise the columns on the 'Find Participants' page?
I'd like to customise the columns on the 'Find Participants' page. I want to swap 'fee level' and 'amount' for 'employer' and 'job title'. Have I missed something obvious in Civi's customisation options? I feel like I've looked everywhere!

(We need to know this info to approve people for our events, and our current process is to export all in a spreadsheet, view the relevant info there, choose who gets approved, then load that info back into civicrm. It would be great if we could just do this all on one screen, without a separate spreadsheet.)

Details: Wordpress 4.9.8; Civicrm 5.4.0

Caveat: I'm not a (fluent/confident) coder or techie person...!

# Answer
> 2 votes
Yes, Its possible to alter the Columns in some search results of CiviCRM and definitely **YES** for participant search result.

You can create an extension and implement hook\_civicrm\_searchColumns hook. You can also find an example how to use the hook in the documentation.

Other alternate would be to create a report with same filters as Participant search but you won't be able to perform any action like how you can on participant search eg Update Participant status, Delete participants etc.

HTH

Pradeep

# Answer
> 1 votes
You can try adding a profile with your desired fields and then do an advanced search to result the columns according to your profile. Eg.

* Navigate to `Administer -> Customize Data and Screens -> Profiles`. Add a profile with `Search Views` enabled.
* Add your required fields to it.
* Navigate to Advanced Search and select this profile as a view of resulting columns.

---
Tags: civievent, registration, custom-search, participants
---


# Question
Title: Date created field displays erratically in views and webform Drupal7 integration
The native date created field for contacts shows up erratically in my views, some items it works, and some it doesn't.

The modified date field always displays however.

Additionally, there used to be a change log field on the bottom on contact creation, but now it's gone...!

Seems that some items that were created a while back show up, but some I did today don't, and I have no idea why some from a while back show and others don't!

# Answer
> 1 votes
You didn't specify what entity you'd like to add this custom field to - but you refer to "every new contact", so I assume you want it for contacts.

"Date created" and "date modified" already exist as core fields on many entities, including Contact entities. See screenshots below. "Modified Date" on contact is on the Change Log tab.

---
Tags: dates
---


title: BI/ Reporting Tool

text:
# Question
Title: BI/ Reporting Tool
Any recommendations on a Business Intelligence/ Reporting Tool? I have used Crystal Reports, IBM Cognos and Oracle Answers in my working life. I am now retired and helping a small community organization with their CiviCRM. I have heard about Reportico, Jasper etc. What do you folks use mostly - and pros and cons? Muchas Gracias in advance.

# Answer
> 1 votes
It depends on how well you know sql and civicrm schema and what kind of report you want

I've used a mix of metabase (awesome to explore with their query builder, and fairly powerful with the option to write your own query) and civisualize (more powerful, but you need to write your queries in sql and the viz in javascript)

I'd recommend installing metabase and see how far you can go with it

---
Tags: civireport, reports, custom-reports
---


# Question
Title: Deadlocked queries cause an instant error for end-users, but are retried in other circumstances
We have a lot of smart groups, and consequently a lot of deadlocks. There's already code in Civi that detects deadlock Exceptions, and then retries the query a few times. This is really helpful - I have extra logging on this, and I can see that it prevents a lot of errors. But this code doesn't kick in for people directly using the UI - they get the 'unknown error' yellow-screen-of-death immediately. This can happen on exports, or when opening complex groups. I dug into this and sort-of know why it's happening:

* When a deadlock happens, an exception is thrown if `$GLOBALS['_PEAR_default_error_options'][1]` is set to 'exceptionHandler'. This exception triggers the code which retries the query.
* An exception is **not** thrown if `$GLOBALS['_PEAR_default_error_options'][1]` is set to 'handle'.
* `$GLOBALS['_PEAR_default_error_options'][1]` is set to 'exceptionHandler' when you generate smart groups via the API, and in most other circumstances
* But `$GLOBALS['_PEAR_default_error_options'][1]` is set to 'handle' in the UI in general (?) - at least, it is on exports and when using 'Manage Groups'. So in this case no exception is raised. And if there's no exception, the try-catch blocks have nothing to work with and front-end users get 'unknown error' immediately.

I am happy to fund a fix for this, but I'm not sure what I'm getting myself into. Is this a bug, or is it an unfortunate consequence of how error handling needs to work for end users?

# Answer
> 1 votes
Instead of trying to "hide" the problem when it happens, it might be safer/wiser to try to prevent it.

I like smartgroups and how powerful they are, but I found that it works better if I cache the results for a while (eg. a day) and rebuild the smart groups via the jobs.

Would it be acceptable to keep a "stalled" version of your groups for a while and avoid rebuilding them all the time in your case?

Also, you mention having a lot of smartgroups, it might be worthwhile creating a custom search that would run the queries you want when you need to use them instead of creating lots of smartgroups. We found it more performant and easier to maintain.

X+

---
Tags: smartgroups, deadlocks, errorhandling
---


# Question
Title: how to change civicrm settings from command line?
There are some common settings to change for dev sites at mycivi.localhost/civicrm/admin/setting/debug. How can those be changed from the command line?

A better answer for the question How should I copy a site from production to staging? would include this information I think.

I see on https://docs.civicrm.org/sysadmin/en/latest/misc/staging-production/ that `$civicrm_setting['domain']['environment'] = 'Development';` can be placed in civicrm.settings.php, which I would expect would be sufficient(though it doesn't seem to affect what's displayed at /civicrm/admin/setting/debug)... but what about other settings like debugging and backtrace?

If I do `drush vget` I get a list of all kinds of Drupal settings that can be changed via `drush vset`. Does CiviCRM have a comparable utility? I tried `cv` and wondered if `cv vars:show` and `cv vars:fill` might be corresponding commands, but if they are I don't know how to use them to do anything useful.

# Answer
You can use drush cv to update the settings in db.

eg `drush cvapi Setting.create environment="Production"`

You can find various options in API explorer to set other settings and see for command in cv and drush section.
> 3 votes

---
Tags: settings, cli
---


# Question
Title: Importing membership custom field keeps failing
I'm having problems importing memberships with a custom field. I'm able to import a alphanumeric text field without an error, but a second custom field, set up as a checkbox, keeps being rejected.

The import CSV file originally had the option label in that field, but after changing this to the option value, the import is still rejected as invalid. What might I be missing here?

The original import error reads 'Invalid value for field(s) : Income; Income'

When I change the relevant fields to the correct value 'LowIncome' (having double checked that it's the value stored in the database), the import error reads:

'1' is not a valid option for field custom\_33

Which is technically correct, but none of the fields in the imported file contain '1', so I don't know where it's getting this from!

Because there's only the one option, I'm pretty sure I can swap this out for a Yes/No field and try it again, but I'd rather pin down why this is happening - I also have some activities to import with multi-option checkboxes, and I'd rather know they'll work...

ETA: I've tried adding in another field, where the option label, value and name are all the same ('Test'), and importing it - It gets flagged in the same way - '1' is not a valid option for field custom\_34

I also tried adding a yes/no field, and that imports okay, so I'm probably going to switch this particular field to a yes/no, but as I say, I'm concerned that it's going to reject other multichoice fields, and I'd still like to pin down the problem. I will try importing some of the activities, and update this when I know how it's behaving.

ETA2: This seems to be happening for any checkbox field, regardless of what the custom data set is used for, and isn't limited to fields with single checkboxes. I'm able to reproduce the problem in the master demo site, and importing from my new installation to the older version of CiviCRM also comes up with this error.

I've found a issue on the old bug tracker site that seems very similar in 4.7/4.7 - https://issues.civicrm.org/jira/browse/CRM-17493 \- and although it's marked as fixed, it looks a little as though the fix was never implemented in 4.7 - the last git pull request is marked as closed with the note 'Unclear if a fix is required or not': https://issues.civicrm.org/jira/browse/CRM-17493 (but it's also not entirely equivalent to the problem I've been having)

Failing any further ideas as to what I might be doing wrong, I'll raise it as a bug.

# Answer
> 1 votes
Try using the machine name instead of Label or Value. I believe for some cases it uses machine names rather value or label. You can find the machine name either using sql stmt or through api explorer.

Here is an example of some cases where value or label didn't work during import.

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


# Question
Title: how to troubleshoot template/ajax/caching/otherweirdness issue re: multilingual upgrade
Thanks for the help in solving the problem! Nice to have all the ajax working properly again.

Regarding the title of the question.... It seems like there's a (large?) gap between the errors I encountered and the solution for them. I'm wondering what I could have done to troubleshoot more...? maybe if I had gone to a non-english page I would have encountered different errors...? Is there anything that can be recommended regarding how I could've troubleshooted this more effectively? This issue seems completely mysterious to me, so while "go ask the friendly people online" can yield easy solutions sometimes as it did this time, I'd like to learn something a little deeper here if possible :)

---

After updating to 5.4.1 Contact pages are behaving oddly...

* Activities don't show up at all until a filter is toggled (for example, there are 10 activities it says, but it displays none of them, but then changing the filters to exclude any type of activity will cause all 10 of the activities to appear).
* Relationships don't show up at all (and they don't have any ajaxy toggle thing to magically make them appear).

In the browser's debugging console I see

* `Uncaught SyntaxError: Unexpected token <`
* `Refused to execute script from 'http://ourwebsite.org/civicrm/contact/undefinedjs/jquery/jquery.crmAjaxTable.js' because its MIME type ('text/html') is not executable, and strict MIME type checking is enabled.`

When Civi cache is flushed at /civicrm/admin/setting/updateConfigBackend a fatal error occurs followed by a backtrace. They mention 'ajax' a number of times (and that's all that stands out to me as meaningful. it says "DB Error: No such field" too, but I have no idea what field doesn't exist so maybe these errors are unrelated?)...

```
ID         :  6869
Date       :  28/Aug 19:48
Type       :  civicrm
Severity   :  debug
Message    :
              $Fatal Error Details = array(3) {
                ["message"]=>
                string(23) "DB Error: no such field"
                ["code"]=>
                NULL
                ["exception"]=>
                object(CiviCRM_API3_Exception)#181 (8) {
                  ["extraParams":"CiviCRM_API3_Exception":private]=>
                  array(4) {
                    ["error_code"]=>
                    string(13) "no such field"
                    ["tip"]=>
                    string(62) "add debug=1 to your API call to have more info about the error"
                    ["is_error"]=>
                    int(1)
                    ["error_message"]=>
                    string(23) "DB Error: no such field"
                  }
                  ["message":protected]=>
                  string(23) "DB Error: no such field"
                  ["string":"Exception":private]=>
                  string(0) ""
                  ["code":protected]=>
                  int(0)
                  ["file":protected]=>
                  string(65)
              "/home/milk/dev_html/docroot/sites/all/modules/civicrm/api/api.php"
                  ["line":protected]=>
                  int(45)
                  ["trace":"Exception":private]=>
                  array(10) {
                    [0]=>
                    array(4) {
                      ["file"]=>
                      string(78)
              "/home/milk/dev_html/docroot/sites/all/modules/civicrm/CRM/Core/BAO/UFGroup.php"
                      ["line"]=>
                      int(3340)
                      ["function"]=>
                      string(12) "civicrm_api3"
                      ["args"]=>
                      array(3) {
                        [0]=>
                        &string(8) "uf_group"
                        [1]=>
                        &string(3) "get"
                        [2]=>
                        &array(2) {
                          ["name"]=>
                          array(1) {
                            ["IN"]=>
                            array(3) {
                              [0]=>
                              string(14) "new_individual"
                              [1]=>
                              string(16) "new_organization"
                              [2]=>
                              string(13) "new_household"
                            }
                          }
                          ["is_active"]=>
                          int(1)
                        }
                      }
                    }
                    [1]=>
                    array(6) {
                      ["file"]=>
                      string(76)
              "/home/milk/dev_html/docroot/sites/all/modules/civicrm/CRM/Core/Resources.php"
                      ["line"]=>
                      int(689)
                      ["function"]=>
                      string(14) "getCreateLinks"
                      ["class"]=>
                      string(20) "CRM_Core_BAO_UFGroup"
                      ["type"]=>
                      string(2) "::"
                      ["args"]=>
                      array(0) {
                      }
                    }
                    [2]=>
                    array(4) {
                      ["function"]=>
                      string(20) "outputLocalizationJS"
                      ["class"]=>
                      string(18) "CRM_Core_Resources"
                      ["type"]=>
                      string(2) "::"
                      ["args"]=>
                      array(0) {
                      }
                    }
                    [3]=>
                    array(4) {
                      ["file"]=>
                      string(73)
              "/home/milk/dev_html/docroot/sites/all/modules/civicrm/CRM/Core/Invoke.php"
                      ["line"]=>
                      int(275)
                      ["function"]=>
                      string(14) "call_user_func"
                      ["args"]=>
                      array(1) {
                        [0]=>
                        &array(2) {
                          [0]=>
                          string(18) "CRM_Core_Resources"
                          [1]=>
                          string(20) "outputLocalizationJS"
                        }
                      }
                    }
                    [4]=>
                    array(6) {
                      ["file"]=>
                      string(73)
              "/home/milk/dev_html/docroot/sites/all/modules/civicrm/CRM/Core/Invoke.php"
                      ["line"]=>
                      int(84)
                      ["function"]=>
                      string(7) "runItem"
                      ["class"]=>
                      string(15) "CRM_Core_Invoke"
                      ["type"]=>
                      string(2) "::"
                      ["args"]=>
                      array(1) {
                        [0]=>
                        &array(12) {
                          ["id"]=>
                          string(3) "149"
                          ["domain_id"]=>
                          string(1) "1"
                          ["path"]=>
                          string(20) "civicrm/ajax/l10n-js"
                          ["access_callback"]=>
                          string(1) "1"
                          ["access_arguments"]=>
                          array(2) {
                            [0]=>
                            array(1) {
                              [0]=>
                              string(14) "access CiviCRM"
                            }
                            [1]=>
                            string(3) "and"
                          }
                          ["page_callback"]=>
                          array(2) {
                            [0]=>
                            string(18) "CRM_Core_Resources"
                            [1]=>
                            string(20) "outputLocalizationJS"
                          }
                          ["breadcrumb"]=>
                          array(1) {
                            [0]=>
                            array(2) {
                              ["title"]=>
                              string(7) "CiviCRM"
                              ["url"]=>
                              string(16) "/civicrm?reset=1"
                            }
                          }
                          ["is_ssl"]=>
                          string(1) "0"
                          ["weight"]=>
                          string(1) "1"
                          ["type"]=>
                          string(1) "1"
                          ["page_type"]=>
                          string(1) "0"
                          ["page_arguments"]=>
                          bool(false)
                        }
                      }
                    }
                    [5]=>
                    array(6) {
                      ["file"]=>
                      string(73)
              "/home/milk/dev_html/docroot/sites/all/modules/civicrm/CRM/Core/Invoke.php"
                      ["line"]=>
                      int(52)
                      ["function"]=>
                      string(7) "_invoke"
                      ["class"]=>
                      string(15) "CRM_Core_Invoke"
                      ["type"]=>
                      string(2) "::"
                      ["args"]=>
                      array(1) {
                        [0]=>
                        &array(4) {
                          [0]=>
                          string(7) "civicrm"
                          [1]=>
                          string(4) "ajax"
                          [2]=>
                          string(7) "l10n-js"
                          [3]=>
                          string(5) "en_US"
                        }
                      }
                    }
                    [6]=>
                    array(6) {
                      ["file"]=>
                      string(75)
              "/home/milk/dev_html/docroot/sites/all/modules/civicrm/drupal/civicrm.module"
                      ["line"]=>
                      int(445)
                      ["function"]=>
                      string(6) "invoke"
                      ["class"]=>
                      string(15) "CRM_Core_Invoke"
                      ["type"]=>
                      string(2) "::"
                      ["args"]=>
                      array(1) {
                        [0]=>
                        &array(4) {
                          [0]=>
                          string(7) "civicrm"
                          [1]=>
                          string(4) "ajax"
                          [2]=>
                          string(7) "l10n-js"
                          [3]=>
                          string(5) "en_US"
                        }
                      }
                    }
                    [7]=>
                    array(2) {
                      ["function"]=>
                      string(14) "civicrm_invoke"
                      ["args"]=>
                      array(3) {
                        [0]=>
                        &string(4) "ajax"
                        [1]=>
                        &string(7) "l10n-js"
                        [2]=>
                        &string(5) "en_US"
                      }
                    }
                    [8]=>
                    array(4) {
                      ["file"]=>
                      string(45) "/home/milk/dev_html/docroot/includes/menu.inc"
                      ["line"]=>
                      int(527)
                      ["function"]=>
                      string(20) "call_user_func_array"
                      ["args"]=>
                      array(2) {
                        [0]=>
                        &string(14) "civicrm_invoke"
                        [1]=>
                        &array(3) {
                          [0]=>
                          string(4) "ajax"
                          [1]=>
                          string(7) "l10n-js"
                          [2]=>
                          string(5) "en_US"
                        }
                      }
                    }
                    [9]=>
                    array(4) {
                      ["file"]=>
                      string(37) "/home/milk/dev_html/docroot/index.php"
                      ["line"]=>
                      int(21)
                      ["function"]=>
                      string(27) "menu_execute_active_handler"
                      ["args"]=>
                      array(0) {
                      }
                    }
                  }
                  ["previous":"Exception":private]=>
                  NULL
                }
              }

```

and the backtrace that followed:

```
ID         :  6870
Date       :  28/Aug 19:48
Type       :  civicrm
Severity   :  debug
Message    :  $backTrace = #0
              /home/milk/dev_html/docroot/sites/all/modules/civicrm/CRM/Core/Error.php(459):
              CRM_Core_Error::backtrace("backTrace", TRUE)
              #1 /home/milk/dev_html/docroot/sites/all/modules/civicrm/CRM/Core/Invoke.php(55):
              CRM_Core_Error::handleUnhandledException(Object(CiviCRM_API3_Exception))
              #2
              /home/milk/dev_html/docroot/sites/all/modules/civicrm/drupal/civicrm.module(445):
              CRM_Core_Invoke::invoke((Array:4))
              #3 [internal function](): civicrm_invoke("ajax", "l10n-js", "en_US")
              #4 /home/milk/dev_html/docroot/includes/menu.inc(527):
              call_user_func_array("civicrm_invoke", (Array:3))
              #5 /home/milk/dev_html/docroot/index.php(21): menu_execute_active_handler()
              #6 {main}

```

I don't know if those Drupal log entries are related to this problem, but I do suspect the javascript errors are...

Could the problem lie in jquery.crmAjaxTable.js? Doing `chmod +x jquery.crmAjaxTable.js` doesn't solve that.

But now after clearing the cache again have a couple more js errors, in addition to the two above are these:

* `Error: CRM.url called before initialization
CRM.console @ Common.js?pe6q8l:1605
CRM.url @ crm.ajax.js?pe6q8l:19
crmFormInline @ Summary.js?pe6q8l:23
(anonymous) @ Summary.js?pe6q8l:201
dispatch @ jquery.min.js?pe6q8l:3
r.handle @ jquery.min.js?pe6q8l:3`
* `crm.ajax.js?pe6q8l:26 Uncaught TypeError: Cannot read property 'back' of undefined
at Object.CRM.url (crm.ajax.js?pe6q8l:26)
at crmFormInline (Summary.js?pe6q8l:23)
at HTMLDivElement.<anonymous> (Summary.js?pe6q8l:201)
at HTMLDivElement.dispatch (jquery.min.js?pe6q8l:3)
at HTMLDivElement.r.handle (jquery.min.js?pe6q8l:3)
CRM.url @ crm.ajax.js?pe6q8l:26
crmFormInline @ Summary.js?pe6q8l:23
(anonymous) @ Summary.js?pe6q8l:201
dispatch @ jquery.min.js?pe6q8l:3
r.handle @ jquery.min.js?pe6q8l:3`

# Answer
> 0 votes
What stands out to me in your error report is the word "undefined" in `http://ourwebsite.org/civicrm/contact/`**undefined**`js/jquery/jquery.crmAjaxTable.js`. I think your resource url setting may have been messed up. Check **Administer -\> System Settings -\> Resource URLs**.

---
Tags: ajax, i18n, multilingual
---