# Question
Title: Override CiviCRM contact import timeout using PHP-fpm and Nginx
I'm attempting to override the fpm time out from nginx just for the import. We are importing very large files, and it might take up to a couple hours to complete, so I need to set:

```
fastcgi_send_timeout 0;
fastcgi_read_timeout 0;

```

I'm having difficulties understanding what exactly happens during the import in which location I should add this override to.

There seems to be a lot happening during the import, ajax, POST requests etc, and I have tried adding this override to just certain paths, all of which didn't fix the problem.

Our installation is at `/vendor/civicrm/civicrm-core`, and adding the following location block didn't work.

```
location ^~ /vendor/civicrm/civicrm-core/CRM/Contact/Import {
        try_files $uri /index.php?$query_string;
        location ~ \.php$ {
            include snippets/fastcgi-common.conf;
            fastcgi_send_timeout 0;
            fastcgi_read_timeout 0;
            fastcgi_pass unix:/run/php/php7.2-fpm.sock;
        }
}

```

I have tried `/civicrm/import/contact`, but it still times out with:

```
upstream timed out (110: Connection timed out) while reading response header from upstream, client: ::ffff:REDACTED, server: REDACTED.com, request: "POST /civicrm/import/contact HTTP/2.0", upstream: "fastcgi://unix:/run/php/php7.2-fpm.sock", host: "REDACTED.com", referrer: "https://REDACTED.com/civicrm/import/contact"

```

---

Trying to use an invalid fastcgi socket seems to imply it's not being matched since it doesn't error out:

```
location ^~ /civicrm/import/contact {
    ## Standard site protection
    try_files $uri /index.php?$query_string;
    location ~ \.php$ {
        include snippets/fastcgi-common.conf;
        fastcgi_send_timeout 0;
        fastcgi_read_timeout 0;
        fastcgi_pass unix:/tmp/for_sure_will_500_and_doesnt_exist.sock;
    }
}

location ~ \.php$ {
    include snippets/fastcgi-common.conf;
    fastcgi_pass unix:/run/php/php7.2-fpm.sock;
}

```

The debug log also shows its matching `~ \.php$`, why would it not match the import path first?

# Answer
The import do generate a lot of requests indeed, the one you should care about is the POST one (that does the import). The others are mostly cosmetic to update the counter of how many contacts have been processed so far.

What matters for your rewrite isn't where the code is, but what's the url of that post. It depends of your install (check your log or the console on your browser), but it's likely not including the full path (vendor/civicrm/civicrm-core), eg something like:

```
location ^~ /civicrm/import/contact { ...

```

one trick I use to see if the location nginx rule is the correct one is to put an invalid fastcgi\_pass, 50x errors are easier to see than waiting and trying to guess if the timeout worked or not ;)

This being said, if you have a lot of contacts to import, it's probably safer/easier to write a php script that you run from the CLI and that uses civicrm api to create the contacts (you don't have to deal with timeout, you have more memory, easier to do exactly the tests you want and create the data you need the way you want it.... and that's way easier to re-run if something breaks because some input is invalid)

X+
> 1 votes

---
Tags: php, nginx
---


# Question
Title: Event registration times out with invoicing enabled
Environment: shared hosting (LAMP), CiviCRM 5.18.2, Backdrop 1.14, PHP 7.2.23 When invoicing is enabled (pay later, no payment processor), event registration times out after confirmation screen is confirmed by user. No final confirmation/thank you screen shown, or email sent with pdf invoice. PDF extension selected in CPanel PHP Selector \> Extensions though throws same error if not selected; PHP timeout extended to 120 seconds, though this does not seem to override host settings (cannot restart Apache after changing). Error message: "\[09-Oct-2019 23:57:10 Europe/London\] PHP Fatal error: Maximum execution time of 30 seconds exceeded in ~/modules/civicrm/vendor/dompdf/dompdf/src/FrameDecorator/Block.php on line 193" Any thoughts gratefully received. Disabling invoicing allows registration, and confirmation email sent with pdf invoice with no invoice number, just no contribution record or ability to generate copy invoice etc.

# Answer
Generating a pdf can take a lot of memory and time. If you can turn of the PDF option, that should skip the problem, as Laryn suggested try to turn if off, at least to be sure it's the root problem.

But ff it really takes a lot of time (300 sec is a lot) it might be that you changed the layout of the pdf and/or include too many heavy things (like a huge heavy image for your logo?)

If you do need complex/heavy pdf, one workaround is to let civi delegate the hard work of generating that pdf to an external program. Check if your hosting provider can install wkhtmltopdf and under Admin/system settings/misc put the path of that program into "Path to wkhtmltopdf executable"

I use it for instances that need to generate loads of complex pdfs, the default civi way should work fine for "simple" pdf, it's likely you have something in your setting that is an issue (either faulty pdf layout if you customised it, non optimised images or something)

X+
> 1 votes

---
Tags: pdf, invoice, backdrop
---


title: Search only the last activity created

text:
# Question
Title: Search only the last activity created
How could I search for X activity type custom field and only return the last activity created if the record has more than 1 X activity type?

I envision creating a smart group out of this data.

My use case is tracking user preferences and if they do a 180 degree on their preference, I would only want to consider their last preference change. I could use a static custom field but that will not track longitudinally. The worst case is I could store data in both and send data to both custom fields...

# Answer
I don't see a way of doing it without writing custom code

One path would be to create a custom search that has the right sql to contain only the most recent one. This is closest to what you want, but might have a performance impact (eg if you have loads of records/contacts) when civi builds the smart group

Another one is to denormalise and create the same custom fields you have on the activity but add them (as read only) on the contact instead of the activity

and then you write a hook so everytime a new activity is created with these custom fields, it would automatically duplicate them to the contact... or skip the duplicate custom fields on contact and directly add them to the right group if it's your only use case.

So the activities contain all the history, but the latest version is always stored in the contact (or belong to the right group)

This might get a little bit more tricky if you have to handle edge cases (eg what happens if you delete an activity? if you merge two contacts that have activities?)

This might or might not be an issue in your case

Another way to skin that cat would be to improve civirule to allow new triggers on custom fields.

Good luck, writing an extension isn't that complicated if you know php/sql X+
> 1 votes

---
Tags: activity
---


# Question
Title: How can I create mosaico block with 2 button?
I would need a mosaico block with 2 button, is it possible to create? Thanks

# Answer
> 2 votes
This is not an answer for how to create a new block, but if you need something quick and dirty with two buttons side-by-side, hold your nose for this horrible hack...

* Drag over a "Two-Columns Block".
* Change to Block Level Style so you don't mess up other blocks.
* Change the Paragraph Size to the smallest (or use the instructions in the next section).
* Disable "Show Title" and "Show Image".
* Remove the text from both columns.

If you want to minimize the blank text areas:

* Select one of the text areas.
* Choose the Source Code option ("\<\>").
* Substitute a placeholder: `<p style="color:transparent;font-size:1px;"> </p>`
* Repeat for the other text area.

Unfortunately, there are no options for aligning the buttons or making them different colors, but at least you'll have a two button block to butt up against other blocks.

Notes:

1. I'm assuming you are using the Versafix-1 template.
2. I found that I had to use something other than an actual space character (or ` `) for the text areas. Otherwise, the source code block wasn't saved the next time I tried to edit it. I could leave some transparent text, but I don't want to impact someone using a screen reader.
3. You might be able to use "font-size:0" to completely eliminate the text area but I doubt you'll be able to select it again since there is no substance left for the editor to represent.

---
Tags: civimail, mosaico, templates, custom-templates, flexmailer
---


# Question
Title: "Failed to load resource" error when trying to view fees under manage events
I am trying to add fees to a new event in a fairly new installation of CiviCRM on a Drupal CMS. When I click on the fees tab I get the following error displayed in the browser:

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

and this in the console window:

*jquery.js?pxiki7:10255 GET https://\[DOMAIN NAME\]/civicrm/event/manage/fee?reset=1&action=update&id=2&component=event&snippet=json 500 (Internal Server Error)*

I was also getting a "Failed to load resource" error in the console window too although that seems to have stopped.

also, if I hover over the fees tab is says *fees (disabled)*.

I am fairly new to civiCRM and Drupal so any help would be much appreciated.

# Answer
> 2 votes
I came accross this article: Event Fees - DB Error: syntax error

It turns out that if you disable the CiviContribute component it causes errors with Event fees!

---
Tags: civievent, drupal
---


title: Auto Renew Membership

text:
# Question
Title: Auto Renew Membership
Brand new to CiviCRM. First thing I am trying to setup is memberships. Our members dues are automatically deducted from paychecks. We receive 26 paychecks per year but only 24 dues payments per year. So no dues are collected from the 3rd paycheck within a calendar month (happens twice a year) Trying to figure out the best term to use and if there is a way to just allow the payments to be automatically recorded in CiviCRM based on the schedule dates for paychecks or to just simplify it to 2 payments per month... No payments actually need to be processed, just simply recorded in CiviCRM.

Initially it seemed that it might just be easier to not track actual payments and just override the members status and just manually expire the member when they resign but trying to use CiviCRM to its fullest extent.

# Answer
> 3 votes
Welcome to CiviCRM SE.

I think you have quite a complicated membership processing structure there (very frequent payments), but the basic requirement seems to be process membership update structure from an external file (I presume you get the paycheck info as a CSV file) is quite common. If you don't need to record the payments at all I think you can do a membership import to update the membership expiry dates (you would need to do a bit of processing on the file first to indicate the appropriate expiry date.

If you want to record the payments (as I do) then its a bit more difficult and I haven't yet found a good solution (How to bulk renew membership is similar).

---
Tags: civimember
---


# Question
Title: Error in WordPress affecting site editing
When I try to edit pages in my WordPress site, I get a message on the editing page: The site is experiencing technical difficulties. Please check your site admin email inbox for instructions.

The email I receive about the error states:

# Error Details

An error of type E\_PARSE was caused in line 1 of the file /home/susquehanna/ssorchestra.org/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(207) : eval()'d code. Error message: syntax error, unexpected 'clone' (T\_CLONE), expecting '('

If I deactivate CiviCRM, I can edit my site like normal, if it's activated, I get the error messages. How can I fix this?

# Answer
> 1 votes
CiviCRM 4.2.4 is a very old version - it's from 2012. It's not compatible with newer versions of PHP - I'd be surprised if it worked with PHP 5.6, let alone 7+. You can downgrade PHP or upgrade CiviCRM - but since you're upgrading from such an old version, be sure to a) take a backup of the database before beginning, b) upgrade a few versions at a time (I'd go to the last version of 4.4, 4.5, 4.6, 4.7 before going to 5.18), c) if you DO have to restore the database, be sure to delete the existing database before restoring the backup.

---
Tags: wordpress, extension, error
---


# Question
Title: How best to resolve Wordpress theme / CiviCRM conflicts
I need help where we cannot upgrade CiviCRM 5.12.4 to any higher version because of a theme conflict with a paid, premium theme, Benevolent Pro. Details are in CiviCRM 5.17.5 and WordPress 5.2.3 just won't work.

We have logged the problem with Benevolent support. Their initial response is "this is a CiviCRM error." In this case, I believe "error" is the wrong word, but rather there is some kind of conflict, because CiviCRM 5.17.5 works just fine with the stock Wordpress "2019" theme. With the stock "2019" theme, we have also removed any CiviCRM conflicts with any other plugins we use (there was one). Who is to say who is at fault in this case, and how do I diagnose? I'm including a screenshot showing javascript errors on one important contribution page for reference. My main question, though, is to whom do I register this conflict? It seems like discussions on this nice forum are not where I am going to find an answer. I am still pursuing with Benevolent support, but just looking for advice how we can ever upgrade Civi again. Btw, we quite love Benevolent Pro. As a paid, supported theme it is guaranteed to be stable as Wordpress evolution continues, something we had difficulty with a previously customized hard-coded theme that was continuing to paint us into a corner with either Wordpress or our plugin upgrades.

Thanks for any advice here!  **\*Edit added ~midnight 10/11/2019 *** Adding more details from a local xampp clone of our site. We have 2 instances, all Wordpress data identical and Civi data identical except one is at CiviCRM 5.12.4 and the broken one is at CiviCRM 5.18.2. In both cases, the URL in the address bar is http://{clone\_site}.webdev/civicrm/?page=CiviCRM&q=civicrm/event/info&reset=1&id=168

In this screenshot for CiviCRM 5.12.4, note civicrm under Sources in the inspect panel shows http://sdbikecoalition.webdev/civicrm/?page=CiviCRM&q=civicrm%2Fajax%2Fl10n-js%2Fen\_US&cid=&r=aMJXH and the event page (event id=168) displays correctly.

In this next screenshot for CiviCRM 5.18.2, note the civicrm under Sources in the inspect panel shows http://test.sdcbcdream.webdev/civicrm/?page=CiviCRM&q=civicrm/event/info&reset=1&id=168 and the event page (event id=168) does NOT display.

Note the difference in the Sources in the inspect panel is the Ajax injection when it works correctly. Makes no sense to me, but I'm not javascript fluent at all.

And finally, here is a screenshot of Event id=168 for CiviCRM 5.18.2 with the stock Wordpress 2019 theme. It displays correctly and note there are 2 entries under civicrm under Sources in the inspect panel, one with the event URL as in the address bar and the other with the Ajax injection.

# Answer
> 3 votes
Karl has already posted my solution to this issue, but for those who want to update their CiviCRM install with immediate effect, the patch can be found on the GitHub repo for the CiviCRM-WordPress plugin.

# Answer
> 3 votes
After inserting some debug code per recommendation at chat.civicrm.org, I discovered an even more focused repair is to just change a single line in civicrm\_in\_wordpress\_set() within civicrm.php. Replace

> $page = get\_query\_var( 'page' );

with

> $page = $\_GET\['page'\];

and all problems we have noted disappear. The get\_query\_var call involves calls to our theme (looking at the backtrace at that point) that evidently get in the way. Obviously a theme interaction, but how would I even notify Benevolent support of the conflict? It seems so simple to make this one change on the CiviCRM side, but I need to understand unintended consequences. Why was get\_query\_var added in the first place - it first appeared in Civi 5.13.0?

# Answer
> 2 votes
Here is a better answer provided by @haystack at chat.civicrm.org. Note that it works and looks to be something we want to add permanently into the CiviCRM Wordpress distribution. I might add that this knowledge and experience is way beyond what I knew previously about Wordpress under the hood and how themes interact. I learned a little along the way and am indebted to @haystack.

> @nc3man I get why this change (using $\_GET('page') directly) works for you, however the problem with this approach is that Clean URLs will no longer work (for those who have enabled them) if we access $\_GET directly.
>
> I have a different approach which I'd like you to try. It looks like Benevolent is making a sub-query via the benevolent\_pro\_customize\_register\_header\_misc() function, so it may be that register\_hooks\_front\_end() is being called before it actually should be. Could you amend the top of register\_hooks\_front\_end() to read like this:
>
> ```
>  /**
>   * Register hooks for the front end.
>   *
>   * @since 5.6
>   *
>   * @param WP_Query $query The WP_Query instance (passed by reference).
>   */
>  public function register_hooks_front_end( $query ) {
>
>    // Bail if $query is not the main loop.
>    if ( ! $query->is_main_query() ) {
>      return;
>    }
>
>    // Bail if filters are suppressed on this query.
>    if ( true == $query->get( 'suppress_filters' ) ) {
>      return;
>    }
>
>    // Prevent multiple calls
>    static $alreadyRegistered = FALSE;
>    if ( $alreadyRegistered ) {
>      return;
>    }
>    $alreadyRegistered = TRUE;
>
> ```
>
> FWIW, the new code is:
>
> * the addition of $query as a parameter to the function
> * the sanity-checking code before the line that reads `// Prevent multiple calls.` If you then re-check your problematic page, we can see if this helps.

# Answer
> 1 votes
This is a suggested answer, not complete and I don't know if it has any (many) unintended consequences. It does solve the issues with the screenshots shown in the question above for both the Join page and a stand-alone call to display a CiviCRM event.

1. The Join page shows NO javascript console errors
2. The Event pages now display correctly and contain the expected Ajax insertions when looking at civicrm under Sources in the inspection panel

The culprit was easy to locate, the top level function wp-content/plugins/civicrm/civicrm.php. In looking at the differences between civi 5.12.4 and 5.13.1 (5.13.x was when this conflict first occurred) I noticed differences on lines that were wordpress specific, even mentioning Ajax and an additional function to clean URLs'. See images below for these 2 code differences. There are others, mostly in the wordpress context, so I'm not sure which are most significant. I also diffed civicrm.php between 5.13.1 and the latest stable 5.18.2 and those differences look mostly to be to verify the correct PHP version. I know it's foolish just to replace civicrm.php all the time with the 5.12.4 version which is the solution that works here, but I hope someone who understands why those changes were made in the first place can help me resolve this. You'll get your bounty ;-)

---
Tags: wordpress, theme
---


# Question
Title: CSS and JS Files aren't loaded - CiviCRM 5.18.2 & WordPress 5.2.2
Fresh install of CiviCRM on a WordPress multisite yet no interface loads on the status page - seems that all the assets throw a 404 error causing this.

Tried updating from 5.18.1 to 5.18.2 but no success.

# Answer
I'm with Demerit - it looks like there's a mismatch between your Resource URL and your base directory. Check your `$civicrm_root` in `civicrm.settings.php`. Ensure that it is a) correct, b) doesn't contain a symlink. If it contains a symlink, rewrite the path to be whatever the symlink(s) resolve to.
> 2 votes

# Answer
So after looking at this closer and working with a colleague, we found that disabling cache for the assets worked – Status page angular load error
> 2 votes

---
Tags: wordpress, error
---


title: Ability to add International Addresses

text:
# Question
Title: Ability to add International Addresses
Currently, I'm unable to add anything except for U.S. addresses. I'mm using Civi 5.2.3. How can I add international addresses to existing contacts? I've tried Administer menu » Localization » Address Settings but am not able to add a country from there or on the edit page of each contact. Thanks!

# Answer
You have to set the available countries in Administer\>Localization\>Language and Currency.
> 3 votes

---
Tags: addresses
---


# Question
Title: I modified a template file and now can't remember how
Yes I know - very foolish but hoping someone will take pity on me!

This is the my screen when entering a contribution or registering for an event while logged in. I customised it to include the text 'PLEASE PAUSE FOR A MOMENT AND READ THIS - If you are entering a session record, please click on "WANT TO DO THIS FOR A DIFFERENT PERSON" Now I want to revert the default but can't remember the file I edited I've searched all the files in my custom templates directory - heaven forfend I edited core! (although then I guess an upgrade could fix my problem) Thanks all

# Answer
This could potentially be done via Word Replacement - so check there too.
> 4 votes

# Answer
do a search on your text "PLEASE PAUSE FOR A MOMENT" on your website/civicrm folder, it should tell you which file it is

I would strongly recommend you to write custom extension for these kind of manipulation, it's way safer than using custom template (as in, less issue when you upgrade), and way way safer to modify the core ;)
> 2 votes

# Answer
An upgrade would indeed fix your problem:-) What operating system and browser are you using? If you are using Firefox then you could right click on the page and view the source code of the page. If you then search for .tpl you should find the starting template of the page? You probably either modified that one or one of the templates it includes with the {include}.
> 0 votes

---
Tags: custom-templates
---


# Question
Title: Block locations for events and check conflicting location bookings
Is it possible in CiviEvent to prevent a location from being booked? Such as:

1. location isn't available
2. location is already booked for a different event
3. holidays

Would it also be possible to notify the users of this?

# Answer
Ren, welcome to SE! I think you may need to be clearer about your use case. In particular you say user twice, when I suspect the first may be an admin and the other a general user.

There isn't a mechanism to stop a venue being bookable, but you can make individual events not active in Manage Events \>\> edit specific event \>\> location tab. Does that do what you want. You can also allow booking only for a specific time in the online booking tab which may also do what you need. Making some assumptions here of course without more detail.

UPDATE based on comments below. There is currently no facility in CiviCRM to manage allocation of locations. It just holds location as a simple list and does not hold any time information against locations so cannot manage availability. You could write and extension, but it would be quite substantial as you would be doing a room booking system. If you have an external room booking system you might be able to do an integration as an extension but again quite a bit of work. I would suggest something external to CiviCRM as being the easiest option. Maybe a shared calendar.
> 1 votes

---
Tags: civievent
---


# Question
Title: What is the easiest way to set up cron for CiviCRM on a Drupal 7 installation?
I have created a test instance of CiviCRM that uses Drupal 7 as the CMS. It's running inside a Ubuntu Desktop virtual machine that has a LAMP stack installed on it (Apache2, php7.2 and mySQL).

I am very new to Linux and would like to set up a cron job to run CiviCRM's scheduled tasks. I have looked at https://docs.civicrm.org/sysadmin/en/latest/setup/jobs/, but I confess that I find it confusing. I haven't set up any cron jobs for Drupal, either. Can anyone help me, please?

Many thanks in advance,

Daniel

# Answer
> 4 votes
Welcome to SE! Setting up cron can be confusing since there are many variations and it touches several aspects: OS users & permissions, CMS users & permissions, other tools, cron - and the details depend on your hosting environment.

I've just re-read the page you linked to. It has the information you need, but it also has other options that you don't need, so work through that step by step. (I've also submitted a few clarifications.)

So, following the 'Preparing' section and assuming a typical Ubuntu installation:

1. Choose a command: I prefer the cv method
2. OS User: Ubuntu typically uses 'www-data' to run Apache. (Adjust below if different.)
3. CMS User: Create a Drupal user (eg 'cronuser') with the listed permissions

If you don't have cv installed already, go ahead and install it.

Check that you can run the command manually:

```
$ sudo -u www-data cv api job.execute --user=cronuser --cwd=/var/www/html

```

(If your installation is not under /var/www/html adjust accordingly)

Then add it to the crontab for `www-data`:

```
$ sudo crontab -e -u www-data

```

---
Tags: drupal, installation, cron
---


title: CiviCRM Relationship error

text:
# Question
Title: CiviCRM Relationship error
I am getting an error on my development site and i have tried everything to fix it but had no Luck.

```
***ERROR***
message:            unknown relationship create error Argument <em class="placeholder">civicrm_relationship</em> is missing.
code:
exception:          CRM_Core_Exception Object
(
    [errorData:CRM_Core_Exception:private] => Array
        (
            [error_code] => 0
        )

    [cause:protected] =>
    [_trace:PEAR_Exception:private] =>
    [message:protected] => unknown relationship create error Argument <em class="placeholder">civicrm_relationship</em> is missing.
    [string:Exception:private] =>
    [code:protected] => 0
    [file:protected] => /var/www/vhosts/site.co.uk/httpdocs/sites/all/modules/civicrm/CRM/Contact/BAO/Relationship.php
    [line:protected] => 139
    [trace:Exception:private] => Array
        (
            [0] => Array
                (
                    [file] => /var/www/vhosts/site.co.uk/httpdocs/sites/all/modules/civicrm/CRM/Contact/Form/Relationship.php
                    [line] => 565
                    [function] => createMultiple
                    [class] => CRM_Contact_BAO_Relationship
                    [type] => ::
                    [args] => Array
                        (
                            [0] => Array
                                (
                                    [hidden_custom] => 1
                                    [hidden_custom_group_count] => Array
                                        (
                                            [] => 1
                                        )

                                    [qfKey] => bf18a1feddecedf9bb2d0998921e2ecb_7909
                                    [entryURL] => https://site.co.uk/civicrm/contact/view/rel?cid=140&action=add&reset=1
                                    [relationship_type_id] => 27
                                    [related_contact_id] => 137
                                    [is_current_employer] => 1
                                    [start_date] =>
                                    [end_date] =>
                                    [is_active] => 1
                                    [is_permission_a_b] => 0
                                   ...

```

I have deleted the relationships re-added them but have had no Luck.

Thanks

# Answer
The issue was i had rules setup with the wrong values hence why it wasn't working. After disabling the rules i am now able to add relationships.

I don't know how it worked but it has.
> 2 votes

---
Tags: relationships
---


# Question
Title: Changing CiviCRM events partcipant status
After all good things said about civicrm my question is: Is it any possibility to allow participant after an event registration to change status or other information related to that event? I'm using drupal and civicrm. Help will be appreciate. Tanks

# Answer
> 3 votes
Normally it would be a civicrm user/ administrator that would update participant details after an event. (Rather than the participant themselves).

As Pete said you can configure a Drupal Webform to update participation details.

The answers to this question How do I get Retrospective feedback for an event using a Webform (participant Custom Data)?

Shows how someone else has configured a webform, and the webform url (including event id and contact id) for use in a schedued reminder.

---
Tags: civievent
---


# Question
Title: CiviCRM Scheduled Jobs Timezone Confusion
I am running a CiviCRM deployment in a GNU\Linux environment. When comparing the times reported by https://example.com/civicrm/admin/job (Settings - Scheduled Jobs) with https://example.com/civicrm/admin/joblog (Settings - Scheduled Jobs Log), the times reported are different.

In the first, the jobs overview, times seem to be reported in Drupal's timezone. In the second, Scheduled Jobs Log, times seem to be reported in the user's timezone. Is this intentional or a bug?

PHP version 7.3.8 Drupal version 7.67 CiviCRM version 5.18.2

The server's time zone is CEST The Drupal time zone is Europe/London My user's time zone is Europe/Berlin

I have tried to reproduce this in demo systems, but the ones I tried, understandably, restrict admin settings.

Any thoughts?

# Answer
> 2 votes
This question is now resolved. It is not a bug, but a server misconfiguration. I am adding the resolution here for posterity.

It turns out the issue was caused by the server being set to Europe/Berlin, but PHP-FPM defaulting to UTC.

Setting PHP-FPM to Europe/Berlin through its configuration file solved the issue.

---
Tags: cron, scheduled-job, timezone
---


# Question
Title: How do I access Mosaico on CiviCRM Spark instance?
My client paid the upgraded fee for CiviCRM Spark Hosting with CiviMail and Mosaico. CiviMail is working, but Mosaico isn't under Mailings as it is in other CiviCRM instances I work with. I also can't find anywhere to enable it.

# Answer
> 3 votes
It is an Extension and you would need to 'install' it via the Extension page. Administer \> System Settings \> Extension

# Answer
> 1 votes
Unfortunately, it appears Spark users can't install additional or upgrade Extensions, yet. However, if the site was upgraded to add Mosaico, that should appear as an Enabled extension at Administer -\> System Settings -\> Extensions and at Mailings -\> Mosaico Templates. If it does not, please contact the Spark folks at chat.civicrm.org in the ~spark channel. You may also want to run Administer -\> System Settings -\> Cleanup Caches.

---
Tags: mosaico, civicrm-spark
---


title: Created and Last Change Data

text:
# Question
Title: Created and Last Change Data
Created and where is the Last Change data held?

Can I get information about the table location in SQL.

**System:**

* Joomla! 3.9.10
* CiviCRM 5.18.2

# Answer
> 1 votes
It's in civicrm\_log. Entity\_id matches civicrm\_contact.id and entity\_table='civicrm\_contact'. Modified\_id is the contact id of who modified it.

Or if you have detailed logging turned on there's also entries in the log\_civicrm\_xxx tables.

# Answer
> 1 votes
It's in the civicrm\_contact table

---
Tags: joomla, database, datatables
---


title: Translation pt_BR

text:
# Question
Title: Translation pt_BR
Where can I find the translation for pt\_BR? I logged in and signed up to Transifex, but couldn't download the language. I also did not find where and how to install the translation. Can anyone inform me?

# Answer
There are some instructions here: https://wiki.civicrm.org/confluence/display/CRMDOC/i18n+Administrator%27s+Guide%3A+Using+CiviCRM+in+your+own+language. The download is in the dropdown at https://civicrm.org/download as a bundle that includes all the languages.

Transifex is for entering the translations.
> 2 votes

---
Tags: language
---


title: Can CiviEvent widget show thumbnails?

text:
# Question
Title: Can CiviEvent widget show thumbnails?
I'm in the process of switching from Events Manager to CiviEvents so my client can integrate event registrations and payments into Civi.  However, as far as I can see there's no facility for the CiviEvents widget to show event thumbnails, which is a shame. Attached is a screenshot of the current set-up, where the Events Manager widget formatting matches the recent posts widget.

I can show an image by including it in the CiviEvent widget description field, but that's far from ideal.

# Answer
> 1 votes
For those using Drupal -\> you can use Drupal Views to create such displays - here's an example of a CiviCRM Event Display - using a Bootstrap Grid (responsive display):

# Answer
> 1 votes
CiviCRM Event Organiser can do this, but requires the Event Organiser plugin for display of events via the usual templating methods in WordPress. I don't know if there's a way to migrate your existing events from Events Manager to Event Organiser, but if your events are held in CiviCRM, then that won't matter.

Here's an example of upcoming events, a tag archive and the out-of-the-box calendar. Event Organiser also provides a couple of configurable widgets, IIRC.

---
Tags: wordpress, civievent, widget
---


# Question
Title: How to remove an activity from a case or file an activity on several cases
I have an Email activity that I sent to several contacts. Each of those contacts has an active case open. My intention was to file that email on each contact's case so that I see it when I review the case history. When I open the email activity, I see one email with 7 recipients.

After filing the e-mail on the first contact's case, it has disappeared from the activity view of all the other contacts.

My first inclination was to try to "un-file" it from the first contact's case. But I don't see an un-file button when looking at the case.

So...questions:

1. Is it possible to un-file an activity from a case? How?
2. Is it possible to file the same activity record against multiple cases?

Thank you!

# Answer
You USED to be able to do this but the system was changed a little over a year ago so that it now removes the original from the non-case side - see Items not showing up on activity log.

There is the copy-to-case action for most activities on a case but it's not available for Emails/Inbound Emails. The reason might be that historically the "spirit" of CiviCase was to prevent something that might be challenged legally, i.e. if someone sends you an email it shouldn't be altered, to preserve the record, and that got applied broadly since internally it's similar code to other actions. You could try temporarily removing this line in CRM/Case/BAO/Case.php and see what breaks: https://github.com/civicrm/civicrm-core/blob/5.18.3/CRM/Case/BAO/Case.php#L2621. You might also need to grant the "edit inbound email basic information" permission in your CMS's permissions to see the action link if you aren't the admin user, but I don't think it's needed.

If you're comfortable with editing the database directly, find the internal activity id (hover over the view link on manage case and it's the number after the `aid=`), and then you can do `DELETE FROM civicrm_case_activity WHERE case_id=1 AND activity_id=2`, where you replace 1 and 2 with the actual case\_id and activity\_id. That should unfile it.
> 1 votes

# Answer
I do not think there is a way in the user interface to unlink an activity from a case. AFAIK you can also not record a single activity in many cases.
> 0 votes

---
Tags: civicase
---


title: Error printing to pdf

text:
# Question
Title: Error printing to pdf
We are on CiviCRM 5.15.1

In trying to pdf print a member list ('Print to PDF') we get the error below.

An error of type E\_ERROR was caused in line 274 of the file /home/.../public\_html/wp-content/plugins/civicrm/civicrm/vendor/dompdf/dompdf/src/Dompdf.php. Error message: Uncaught Error: Call to undefined function Dompdf\mb\_internal\_encoding() in /home/.../public\_html/wp-content/plugins/civicrm/civicrm/vendor/dompdf/dompdf/src/Dompdf.php:274

Stack trace:

-0 /home/.../public\_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PDF/Utils.php(200): Dompdf\Dompdf-\>\__construct(Object(Dompdf\Options))

-1 /home/.../public\_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PDF/Utils.php(125): CRM\_Utils\_PDF\_Utils::\_html2pdf\_dompdf(Array, 'landscape', '\n\n

# Answer
> 2 votes
Based on replies here this is the solution. Hosting support assured me: Package alt-php72-xml-7.2.20-2.el6.x86\_64 already installed and latest version

Then I enabled wordpress debugging, and found that the package 'GD' was required, so I enabled that in cPanel. PDF printing is now working!

# Answer
> 1 votes
You probably don't have php-mbstring installed, or it's not enabled in php.ini. See for example https://stackoverflow.com/a/1216284/8332458 and the comment underneath it for if you're using ubuntu/debian (apt install php-mbstring). The package name might be slightly different depending on exactly what system you're on.

# Answer
> 0 votes
You could try using wkhtmltopdf instead as a workaround to get your pdf's printed - that is provided by some shared hosting setups. See eg https://civicrm.stackexchange.com/a/10903/225

# Answer
> 0 votes
Here is another error that just happened with 'Print to PDF'. It's like the wrong files were distributed, or there is a problem with pdf\_mbstring. Our shared hosting support is probably out of their depth on this one.

Fatal error: Uncaught Error: Class 'DOMDocument' not found in /home/.../public\_html/wp-content/plugins/civicrm/civicrm/vendor/dompdf/dompdf/src/Dompdf.php:488

Stack trace:

-0 /home/.../public\_html/wp-content/plugins/civicrm/civicrm/vendor/dompdf/dompdf/src/Dompdf.php(406): Dompdf\Dompdf-\>loadHtml('\n\n

-1 /home/.../public\_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PDF/Utils.php(202): Dompdf\Dompdf-\>load\_html('\n\n

-2 /home/.../public\_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PDF/Utils.php(125): CRM\_Utils\_PDF\_Utils::\_html2pdf\_dompdf(Array, 'landscape', '\n\n

-3 /home/.../public\_html/wp-content/plugins/civicrm/civicrm/CRM/Report/Form.php(3472): CRM\_Utils\_PDF\_Utils::html2pdf('\r\n

-4 /home/.../public\_html/wp-content/plugins/civicrm/civicrm/CRM/Report/Form.php(3562): CRM\_Report\_Form-\>endPostProcess(Array)

-5 /home/.../public\_html/wp-content/ in /home/.../public\_html/wp-content/plugins/civicrm/civicrm/vendor/dompdf/dompdf/src/Dompdf.php on line 488

---
Tags: pdf, printing
---


# Question
Title: How can members change their data on their own?
we have CiviCRM installed in WP. We want, that members change there data on their own. Data could be: bank information, email adr, etc. Is there a solution in place? Thanks Regards Edgar

# Answer
If Members are also WP users then let them log in and have access to either a CiviCRM Profile (https://docs.civicrm.org/user/en/latest/organising-your-data/profiles/) or a Caldera Form and you should be able to set up the fields you want them to have access to via the form

If Members are NOT WP users then you can send out a Mailing with a checksum link to a Profile so they can make their changes without logging in
> 2 votes

---
Tags: civimember
---


# Question
Title: How can I create a list of the contacts in a household?
How can I create a list of the contacts in a household? What I want to see is which contacts are currently in a household. I also cannot see how I can add a person to an already established household.

# Answer
> 1 votes
I assume you are using Relationships to connect individuals to their Household. So if you start from the H/h or from the Individual you should be able to go to the Relationship Tab and click Add Relationship.

On the Rel. Tab of the H/h you should also see any Individuals already related to it.

---
Tags: households
---


# Question
Title: How to show paidby and check_number on Contribution - Receipt (on-line)
CiviCRM 5.16.3 on Drupal 7. I would like to show payment menthods of contribution in template Contribution - Receipt (on-line). I tried to insert $formValues.paidby or $paidby but they show nothing. (I saw those variables at off-line version of receipt and copied the them without even knowing if they are populated in the on-line workflow.)

How can I show payment methods and check numbers like those in off-line version?

Thank you.

# Answer
> 0 votes
I figured out myself.

Put this API call somewhere upfront in the template. This retrieve contribution payment method by ID. (Get the whole record if removing "return" parameter.)

```
     {crmAPI var='apiResult' entity='Contribution' action='get' return="payment_instrument_id" contribution_id=$contributionID}

```

At where it should be displayed:

```
     {foreach from=$apiResult.values item=contribution}

         {$contribution.payment_instrument}

     {/foreach}

```

---
Tags: civicontribute, smarty, templates
---


title: Attachments in Message Templates

text:
# Question
Title: Attachments in Message Templates
How do I attach documents to a message template? Am I overlooking an "attach" button?

# Answer
I believe that's not possible yet (it would be a good feature to add to CiviCRM). Adding attachments is only possible sending an email or bulk email, not when you create/edit a message template.

You could upload the file/attachment to your server/site and include a link that points to it in the message template so recipients can download it.
> 2 votes

# Answer
I just stumbled on an extension for this: https://civicrm.org/extensions/e-mail-template-attachments

Although, I'm having a little trouble with it maybe you'll have more luck.
> 2 votes

# Answer
When you do Actions\> Phone Call, there is the Attachment(s) section at the bottom of the screen. You can add files here. You can then send an e-mail with the file attachment to the person or mail group you assign to Assigned to. Your Phone Call will go as a message template.
> 0 votes

---
Tags: message-templates, attachments
---


# Question
Title: Where and how do I 'check Drupal permissions' or 'set Drupal permissions'?
I find that I am often asked to 'check Drupal permissions' or 'set Drupal permissions' when, for example, I have added a new Component, like Civi Campaigns, Civi Cases, Civi Grants, or, have installed and enabled a new Extension.

But, if I am just getting started with Civi and Drupal, or have not changed my settings in awhile, I may not be sure what that means.

So, where and how do I check Drupal permissions or set Drupal permissions?

# Answer
> 2 votes
In CiviCRM , go to Administer -\> Users and Permissions -\> Permissions (Access Control) . Then , select Drupal Access Control .

You will see a long list of various options, and check boxes to enable permissions for Components, Extensions, etc for various Roles.

Scroll down the list until you see the Component, Extension etc that you have installed and enabled in the list. Then, select the checkbox to add a permission to a Role.

In the screenshot below, I've added permissions for the Administrator role for the ReportPlus extension I just added.

Save your changes, et voila! You should now see the new permissions for the Administrator role or for whichever role to which you have added new permissions.

---
Tags: drupal
---


# Question
Title: CiviCase - Edit Actions with permission: "CiviCase: access my cases and activities"
Im Configurating CiviCase and I have seen that if a contact has the "CiviCase: access my cases and activities" permission, he cannot use the actions of the assigned roles. I understand that this permission has certain limitations, but I think this should be available, maybe I am wrong, but I think it would be interesting to be able to eliminate the assigned roles or change the person from the role since the case is created by the user who edits it.

# Answer
It was done deliberately in 2010 and it's been that way ever since. While it's not stated in the JIRA link below, if I had to guess the reason it's because it might allow you to give your buddy access to a case they shouldn't see.

See
> 2 votes

---
Tags: civicase, permissions
---


# Question
Title: Show CiviEvent registration details via dashboard or view?
How would one approach letting a user login and see their registration details for an event? We find that many users lose their initial email and we'd like to streamline the process. Looking for any good ideas/support! The contact dashboard seems to show the event one has signed up for, but I did not see it display the actual price set/registration details of the event. Did I just miss something? I'm thinking about making a View of some sort to do this, so we can attach to their user page or something.

CiviMobile seems like a cool way to do this: https://civicrm.org/blog/skornien/event-participants-management-in-the-new-version-of-civimobile we'll consider it for 2020, but it's too late for our current event.

Most recent Civi / Drupal

# Answer
I was able to make a View for Drupal 7 / CiviCRM 5.x that produces a block which can be placed on the user page (/users/) via Panels or Panelizer.

The limitation I see currently is that it needs to specify the price set ID of the event in a filter to show the appropriate information. We are going to use it for our yearly conference so someone can login and see what they purchased.

It will only show the items, sorted by price field weight (in order), if the participant has a status that is registered, pay later, etc. This should all be adjustable (it's Views)

Views export below:

https://pastebin.com/iLSNYP4W
> 1 votes

# Answer
You can use for it CiviMobile if a user who registered has a CiviCRM account. Only CiviCRM users can login in CiviMobile. So they will see the list of events where he is registered and their tickets on these events with all information (role, price, name, status).
> 3 votes

---
Tags: civievent
---


title: Merging contacts not working

text:
# Question
Title: Merging contacts not working
I am trying to merge 2 contacts (individuals). I search for the contact, select both contacts and select merge. Go through the options. A message saying: Contacts Merged, Name has been updated. Contact ID 1951 has been deleted.

The contacts are not merged and no contacts are deleted.

The log file in ConfigAndLog has this message:

\[error\] $Fatal Error Details = Array ( \[callback\] =\> Array ( \[0\] =\> CRM\_Core\_Error \[1\] =\> exceptionHandler )

```
[code] => -5
[message] => DB Error: already exists
[mode] => 16
[debug_info] => UPDATE  civicrm_account_contact  SET contact_id = 1951 , accounts_contact_id = '2bd64d12-13b0-4188-a708-430aa0bb8f5b' , accounts_display_name = 'Neil T - 1501' , last_sync_date = 20191018151509 , accounts_modified_date = 20191018000000 , accounts_data = 'Array' , accounts_needs_update = 0 , connector_id = 0 , plugin = 'xero' , do_not_sync = 0   WHERE (  civicrm_account_contact.id = 2598 )   [nativecode=1062 ** Duplicate entry '1951-0-xero' for key 'contact_id_plugin']
[type] => DB_Error
[user_info] => UPDATE  civicrm_account_contact  SET contact_id = 1951 , accounts_contact_id = '2bd64d12-13b0-4188-a708-430aa0bb8f5b' , accounts_display_name = 'Neil T - 1501' , last_sync_date = 20191018151509 , accounts_modified_date = 20191018000000 , accounts_data = 'Array' , accounts_needs_update = 0 , connector_id = 0 , plugin = 'xero' , do_not_sync = 0   WHERE (  civicrm_account_contact.id = 2598 )   [nativecode=1062 ** Duplicate entry '1951-0-xero' for key 'contact_id_plugin']
[to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="UPDATE  civicrm_account_contact  SET contact_id = 1951 , accounts_contact_id = '2bd64d12-13b0-4188-a708-430aa0bb8f5b' , accounts_display_name = 'Neil T - 1501' , last_sync_date = 20191018151509 , accounts_modified_date = 20191018000000 , accounts_data = 'Array' , accounts_needs_update = 0 , connector_id = 0 , plugin = 'xero' , do_not_sync = 0   WHERE (  civicrm_account_contact.id = 2598 )   [nativecode=1062 ** Duplicate entry '1951-0-xero' for key 'contact_id_plugin']"]

```

)

Using latest version of Drupal + Civi

# Answer
It seems to be an issue with xero (I'm assuming it's an extension/plugin that you have enabled on your site)?

If you are using fuzion's extension, you might want to check if it's already logged as an issue or if there is a solution already

https://github.com/eileenmcnaughton/nz.co.fuzion.civixero
> 2 votes

---
Tags: duplicate-contacts, civixero
---


# Question
Title: Webform CiviCRM 'Registration Status' for Event not Updating each subission
We're using the latest on D7 to have a nice RSVP form for an event. We made a custom status 'Not Attending' and of course have 'Registered' as the other status. The form has 4 contacts on it, cloned, all with the same stuff. No prices, just name/email/phone for 4 contacts with the RSVP field (civicrm\_1\_participant\_1\_participant\_status\_id)

When they first submit the form their choice is saved, works great.

But, when they return to the form, and submit again with a change in RSVP, the update is not reflected on their civicrm participant record.

Is this by design, or an error I have made? I assumed it would be cool to let people change their mind!

# Answer
> 2 votes
I'm not sure from your description how exactly your form is configured. For the update participant status you may need a second form and specific url to reach it which includes the user's contact id and the event id.

See the responses to this question for more (and clearer) details: How do I get Retrospective feedback for an event using a Webform (participant Custom Data)?

---
Tags: civievent, webform-civicrm
---


title: Message Templates Font Color Change

text:
# Question
Title: Message Templates Font Color Change
Message Templates I want to specify the color of the text "Status - Send Copy of an Activity" for incoming mail as color code 1f497d.

So I want to replace the posts in the table with 1f497d color, not black. How can I do that.

# Answer
In the message template there's two lines near the top that look like this:

```
{capture assign=labelStyle }style="padding: 4px; border-bottom: 1px solid #999; background-color: #f7f7f7;"{/capture}
{capture assign=valueStyle }style="padding: 4px; border-bottom: 1px solid #999;"{/capture}

```

If you change them to add a `color: #1f497d;` to look like this it should work:

```
{capture assign=labelStyle }style="padding: 4px; border-bottom: 1px solid #999; background-color: #f7f7f7; color: #1f497d;"{/capture}
{capture assign=valueStyle }style="padding: 4px; border-bottom: 1px solid #999; color: #1f497d;"{/capture}

```
> 2 votes

# Answer
I'd like to ask you one more thing. When more content (words, etc.) is entered in the Details section, the side headers field starts to narrow. So column 1 is almost narrow enough to fall into one word and it looks very ugly in this mail. Is it possible to ensure that there is no such contraction in the table structure?
> 0 votes

---
Tags: message-templates
---


# Question
Title: Update DB Query fails sporadically when saving contact; nativecode=1205 ** Lock wait timeout exceeded
The exact query and issue is below.

```
UPDATE  civicrm_contact  SET contact_type = 'Individual' , contact_sub_type
= NULL , sort_name = 'XXXXXX XXXXXX' , display_name = 'XXXXXX XXXXXX'
, first_name = 'XXXXXX' , last_name = 'XXXXXX' , email_greeting_id = 6 ,
postal_greeting_id = 1 , addressee_id = 1   WHERE (  civicrm_contact.id =
27636 )   [nativecode=1205 ** Lock wait timeout exceeded; try restarting
transaction]

```

There are also a number of other "database deadlock" errors cropping up in the CiviCRM log.

Essentially, the person using the CRM is complaining that randomly they'll be editing a contact and it will fail to save, instead simply timing out (I haven't reproduced the behavior but I trust it).

After some research/reading, it appears CiviCRM has a contact\_cache table in regards to Smart Groups it refreshes periodically via CRON. This specific CiviCRM install has thousands of contacts, and many large Smart Groups. So what I assume is happening is:

1. The site issues a "job" for refreshing the smart groups and data in the contact\_cache table.
2. While the "job" is running, every single contact record affected by the smart group is "locked" in the database, and cannot be altered until the "job" finishes.
3. The user does not know the job is happening in the background, saves a contact, and produces an UPDATE query for the contact.
4. If the contact record is locked, it waits a certain specified amount of time (innodb\_wait\_timeout I believe) for the row to be unlocked, before failing the query altogether.

My problem is, if the above is true, I don't know how I can resolve the problem. Upping the timeout from 50 seconds to 120 seconds for example, would only increase the amount of time they wait for the request to fail, because the Smart Groups take over 120 seconds to be refreshed.

Other relevant info: Drupal 7, CiviCRM 5.9.1, PHP 7.0.33

Thanks!

# Answer
> 5 votes
We have seen this on large instances. Things to do:

* upgrade to 5.13.5 (this also puts you close to the next ESR - a paid for service - which will be released on Nov20).

There have been performance improvements - this will help.

* cull your smart groups; do you really still need all of them.
* reschedule the cron job -\> ask yourself how up to date your smart groups need to be.

# Answer
> 4 votes
Karin's answer is a good one. However, not all smart groups are created equal! Culling the *right* smart groups is what will fix your deadlocks.

* Identify your slow smart groups. Here's a script (rough, could use improvement) that you can run that will show you how long it takes to build each smart group. The latest version is available on my Github:

```
#!/usr/bin/php
<?php
// Use this code to see how long it takes to generate all of your smart groups to find slow ones.
eval(`cv php:boot`);
//Force a refresh of the cache
$sql = 'UPDATE civicrm_group SET cache_date = NULL, refresh_date = NULL';
CRM_Core_DAO::executeQuery($sql);
// Get a list of smart group IDs.
$smartGroups = civicrm_api3('Group', 'get', [
  'return' => ["id", "title"],
  'options' => ['limit' => 0],
  'saved_search_id.id' => ['IS NOT NULL' => 1],
  'is_active' => 1,
  'is_hidden' => 0,
])['values'];

foreach ($smartGroups as $group) {
  $time_start = microtime(true);
  echo "{$group['title']} ({$group['id']}): ";
  CRM_Contact_BAO_GroupContactCache::loadAll($group['id']);
  $time_end = microtime(true);
  $executionTime = ($time_end - $time_start);
  echo "$executionTime seconds\n";
}

```

Running this on a site of mine, the vast majority of smart groups run in under .3 seconds. However, some run for as long as 211 seconds. Those are the ones causing deadlocks!

If you can't cull a particular smart group, here are some strategies you can use instead:

* Don't nest your smart groups - that is, don't make a smart group "Vermont residents", a smart group "New Hampshire residents", then a smart group "New England Residents" whose members are the folks in the previous 2 (and more) smart groups. Instead, rewrite the "New England residents" group to include Vermont and New Hampshire residents directly, instead of relying on the nested smart groups.
* *Especially* don't make a smart group made up of members of at least one smart group and one static group! For reasons too esoteric to discuss, this will completely kill performance.
* Consider whether you can use an extension like CiviRules or SQL Tasks to replace the smart group. For instance, if you have a "Major Donors" smart group, defined as "Everyone who ever gave $500+ in a single donation", consider replacing it with a static group, and a CiviRule that adds donors to it when their donation exceeds your threshold.
* The SQL command CiviCRM comes up with to determine a smart group is sometimes inefficient. Previously I wrote custom search extensions to optimize the SQL, but I've largely deprecated this approach in favor of SQL Tasks. Custom searches can still be helpful if end users need to be able to make variations on a particular query on their own.

Hopefully this will give you tools to best find and fix your slow smart groups and kill deadlocks for good!

---
Tags: mysql, deadlocks
---


# Question
Title: SYNTAX ERROR in Pledge Report when Balance Due is selected (ver. 5.18.2)
CiviCRM ver. 5.18.2 (or 5.14.2) on Wordpress.

Hi all.

When **Balance Due** is selected in **Pledge Report \> Details** we get a **syntax error**. Here is the error log:

```
 [error] $Fatal Error Details = Array
(
[callback] => Array
    (
        [0] => CRM_Core_Error
        [1] => handle
    )
[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, pledge_civireport.id as civicrm_pledge_id, pledge_civireport.contact_id as civicrm_pledge_contact_id, pledge_civireport.amount as civicrm_pledge_amount, pledge_civireport.currency as civicrm_pledge_currency, pledge_civireport.status_id as civicrm_pledge_status_id, COALESCE(sum(pledge_payment_civireport.actual_amount), 0) as civicrm_pledge_payment_total_paid, IF(pledge_civireport.status_id IN(3, ), 0, COALESCE(pledge_civireport.amount, 0) - COALESCE(sum(pledge_payment_civireport.actual_amount),0)) as civicrm_pledge_payment_balance_due, (address_civireport.street_number % 2) as civicrm_address_address_odd_street_number
        FROM civicrm_pledge pledge_civireport
             LEFT JOIN civicrm_contact contact_civireport
                  ON (contact_civireport.id =
                      pledge_civireport.contact_id )

    LEFT JOIN civicrm_pledge_payment pledge_payment_civireport ON
      pledge_civireport.id = pledge_payment_civireport.pledge_id
      AND pledge_payment_civireport.status_id = 1

             LEFT JOIN civicrm_address address_civireport
                       ON (contact_civireport.id =
                           address_civireport.contact_id)  AND
                           address_civireport.is_primary = 1
WHERE (pledge_civireport.is_test=0 )   GROUP BY pledge_civireport.id, pledge_civireport.currency  ORDER BY contact_civireport.sort_name, contact_civireport.id  LIMIT 0, 50 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '), 0, COALESCE(pledge_civireport.amount, 0) - COALESCE(sum(pledge_payment_civire' at line 1]
[type] => DB_Error
[user_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, pledge_civireport.id as civicrm_pledge_id, pledge_civireport.contact_id as civicrm_pledge_contact_id, pledge_civireport.amount as civicrm_pledge_amount, pledge_civireport.currency as civicrm_pledge_currency, pledge_civireport.status_id as civicrm_pledge_status_id, COALESCE(sum(pledge_payment_civireport.actual_amount), 0) as civicrm_pledge_payment_total_paid, IF(pledge_civireport.status_id IN(3, ), 0, COALESCE(pledge_civireport.amount, 0) - COALESCE(sum(pledge_payment_civireport.actual_amount),0)) as civicrm_pledge_payment_balance_due, (address_civireport.street_number % 2) as civicrm_address_address_odd_street_number
        FROM civicrm_pledge pledge_civireport
             LEFT JOIN civicrm_contact contact_civireport
                  ON (contact_civireport.id =
                      pledge_civireport.contact_id )

    LEFT JOIN civicrm_pledge_payment pledge_payment_civireport ON
      pledge_civireport.id = pledge_payment_civireport.pledge_id
      AND pledge_payment_civireport.status_id = 1

             LEFT JOIN civicrm_address address_civireport
                       ON (contact_civireport.id =
                           address_civireport.contact_id)  AND
                           address_civireport.is_primary = 1
WHERE (pledge_civireport.is_test=0 )   GROUP BY pledge_civireport.id, pledge_civireport.currency  ORDER BY contact_civireport.sort_name, contact_civireport.id  LIMIT 0, 50 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '), 0, COALESCE(pledge_civireport.amount, 0) - COALESCE(sum(pledge_payment_civire' at line 1]"]

```

Is this a bug? or is it the result of errors in setting the fields?

Thx&Bye.

# Answer
> 2 votes
This is a bug. It only happens when you have modified the name of the pledge status "Completed" or "Cancelled". In your case, "Completed" has been modified.

While you could change that label back, the report shouldn't crash regardless. It was easy to fix, so I reported the issue then made the fix.

You literally need to change one word in one file, so if you feel comfortable changing the contents of a file in your CiviCRM directory, you can look at the fix I made and make the change on your site. If you do, please report back on Github that it works - that will speed up the process of getting this fixed merged into CiviCRM for everyone.

---
Tags: reports, pledges
---


# Question
Title: Can I sync CiviCRM data with Raiser's Edge?
We currently use Raiser's Edge. We're thinking about handling our e-communications via Mailchimp and wondered whether we can then use CiviCRM as the back end to our online functionality and then sync it to Raiser's Edge.

Is this possible?

Thanks

# Answer
CiviCRM has a fully-featured "API", which makes it relatively easy to get data in and out of it via a sync. Raiser's Edge has an API, but it's an additional add-on.

If you had the API add-on for Raiser's Edge, this would be possible - though the more data you want to sync the more intensive the process would be.
> 2 votes

# Answer
Yes, it is possible to sync or migrate data from Raiser's Edge into CiviCRM. Here are some useful posts that might help you

How to migrate Raisers edge data to CiviCRM?

https://civicrm.org/blog/dharmatech/migrating-raisers-edge-civicrm
> 1 votes

# Answer
We recently got ImportOmatic to move data into Raiser's Edge from CanadaHelps (an external fundraising site) and I'm working now on using that tool to run regular imports and exports between Raiser's Edge and CiviCRM (we're just starting to use CiviCRM for some things that would be harder or more expensive to do in RE, while maintaining that system for our Fundraisers who love it).
> 1 votes

---
Tags: integration, mailchimp
---


# Question
Title: Why are Username and Password are no longer accepted for Outbound GMail?
We use GMail (G-Suite) for outbound mail. Three days ago, we started getting:

authentication failure \[SMTP: Invalid response code received from SMTP server while sending email. This is often caused by a misconfiguration in Outbound Email settings. Please verify the settings at Administer CiviCRM \>\> Global Settings \>\> Outbound Email (SMTP). (code: 535, response: 5.7.8 Username and Password not accepted. Learn more at 5.7.8 https://support.google.com/mail/?p=BadCredentials n3sm11135782pff.102 - gsmtp)\]

We've verified (ad nauseam) that the Username and Password are correct. We can use them to send and receive through GMail just fine. However, we can no longer send with Outlook, either.

How can I find out why the Username and Password are no longer accepted?

# Answer
> 3 votes
This link seems to fix things, if only temporarily: https://accounts.google.com/DisplayUnlockCaptcha

---
Tags: email
---


# Question
Title: Is there a way to connect the civisepa backend to the profiling in wordpress?
I'm trying to connect the payment attributes of my wordpress/civi profiles with civisepa, so that mandates will automatically be updated if a user changes his IBAN, etc. Is there any way to achieve this?

Thanks in advance!

# Answer
> 1 votes
Welcome to CiviCRM Stack Exchange alicedd!

Afaik, CiviSEPA mandate information cannot easily be made available in Profiles. CiviSEPA can be made available as a payment processor on CiviCRM Contribution pages though. CiviSEPA would then create a mandate when someone uses the page

If you want to allow constituents updating a mandate you will need to create a custom form / function.

---
Tags: profile, civisepa, civibanking
---


# Question
Title: Organization Membership Payment page not recording memberships
As background information, all of our client's members are organizations. I see that if I select "on behalf of organization" and "membership section enabled" I get two copies of the profile on my page. So we won't do that.

However, neither option by itself records a membership in the membership dashboard when the profile is filled out in our tests. I have all the key fields; organization name, email, contact address and phone. I get no errors when saving the contribution page. I have no errors when submitting a live form. What am I missing?

# Answer
After much frustration and subsequent soul searching, contemplating the abyss of total consumption by the digital realm, a singular epiphany presented itself to me at the gates of Valhalla. It whispered in my ear, "you're using the wrong damn profile, you dimwit!"

I had built a custom profile where I should have used the default "on behalf of organization" profile. Much confusion ensued, until I figured out, what is now obvious.

Many thanks for all the help offered and time invested by those who guided this spirit quest.
> 3 votes

---
Tags: civicontribute, civimember, profile
---


# Question
Title: Contacts age as a search criteria/smart group?
I would like to create 2 smart groups. 1 for members under 18 and one for members over 18. There is no search criteria for age rather only a static date. Any idea?

# Answer
There is a way with the CiviRules extension. It's a similar concept as noted here: https://civicrm.stackexchange.com/a/4873/105

Create two tags: Under 18 and 18+.

Create two Rules:

1. A generic **trigger** such as "Individual is changed" or "Individual is added". CiviRules has a **condition** called "Age Comparison". You can set it to less than 18. Then, for the **action**, 'add Tag to contact' using Under 18.
2. Same trigger, Age Comparison condition - greater than or equal to 18. But here have two actions: 'add Tag to contact 18+' as well as 'remove Tag from contact - Under 18' (to catch the people after they have turned 18).

Create two smart group that looks for your respective tags. Any time a contact is updated in some way (if you go the 'Individual is changed' route), the Rules will fire and lead to updated smart groups.
> 2 votes

# Answer
Although it doesn't help you right now, search by age will be in 4.7 - CRM-16160. You can try it in on the demo: http://dmaster.demo.civicrm.org/
> 5 votes

# Answer
This is now available in the UI (as noted by @Aidan above).

To find it, use the **Advanced Search** and scroll down to **Demographics**:

Once you've configured your search you can save it as a **Smart Group** in the usual way to make the search results available very quickly and easily.
> 1 votes

# Answer
Not doable via UI afaik, and I don't recall anyone adding a custom search for this but there was a wiki page about that once upon a time - ah here which may give you or someone else a head start - though it may need updating of course.
> 0 votes

---
Tags: search
---


# Question
Title: CiviCase not strictly adhering to permissions?
I have a use case where 'volunteers' deal with casework on an area basis, with a 'supervisor' for each area. Volunteers can add contacts, and view their own cases. Supervisors can view all cases.

Both roles' access to their area's contacts is limited by Civi's own ACL through each service user being assigned to an area group.

This works up to a point, in that neither group has permission to view cases outside their area, but supervisors' dashboards and dashlets show all cases, regardless of whether those contacts are visible.

Is this expected behaviour, or have I went wrong somewhere?

**Drupal permission:**

> CiviCase: access all cases and activities *View and edit all cases (for visible contacts)*

# Answer
You might be right. The dashboard as far as I can remember has never used ACL's it just uses "my" or "all". The feature where ACL support for case was introduced might have been for a different need and doesn't seem to have included the dashboard (https://issues.civicrm.org/jira/browse/CRM-17645).

So yes and no to your question?
> 1 votes

---
Tags: civicase, permissions
---


# Question
Title: A contribution batch containing transactions of more than one currencies causes fatal error
My setup: CiviCRM 5.18.3 on Drupal 7.

I created a Contribution batch. At transactions I chose two transactions of difference currencies e.g. HKD and USD and Assign them to a batch. Then I no longer be able to retrieve the list of batches because of fatal error: Dialog: DataTables warning: table id=crm-batch-selector-2 - Ajax error. For more information about this error, please see http://datatables.net/tn/7

In ConfigAndLog there are lines produced e.g. CRM\_Core\_Exception .... Invalid currency "HKD,USD" blah blah.....

Even I cancel the offending transaction it does not help. I had to delete the transaction in that batch to revive the batch's functioning.

Please kindly advise.

Best Regards,

```
2019-10-17 (too long to add to comments).
I copied this from ConfigAndLog, because no on-screen backtrace is shown even I enabled backtrace at System setting.
Oct 17 09:53:08  [debug] $backTrace = #0
/var/www/c53/web/sites/all/modules/civicrm/CRM/Core/Error.php(463):
 CRM_Core_Error::backtrace("backTrace", TRUE)

#1 /var/www/c53/web/sites/all/modules/civicrm/CRM/Core/Invoke.php(55): CRM_Core_Error::handleUnhandledException(Object(CRM_Core_Exception))

#2 /var/www/c53/web/sites/all/modules/civicrm/drupal/civicrm.module(444): CRM_Core_Invoke::invoke((Array:3))

#3 /var/www/c53/web/includes/menu.inc(527): civicrm_invoke("ajax", "batchlist")

#4 /var/www/c53/web/index.php(21): menu_execute_active_handler()

#5 {main}

```

# Answer
I believe the standard workflow for accounting requires a single currency per batch. At least that was all that we aimed to support. Please create an issue at lab.civicrm.org indicating that there should be a 'friendly' error message preventing transactions that have a different currency from ones already in the batch from being added. This should include an error in case of an attempt to simultaneously add transactions in more than one currency.
> 1 votes

---
Tags: civicontribute, ajax, batches, currency
---


# Question
Title: Upgrading old CiviCRM/Drupal and switching hosts
I have a CiviCRM 4.1.6/Drupal 6.30 site that I haven't touched in years. I would like to switch to a new host and bring both packages up to the most current versions. To make it even more complex, I'll need to change the domain name. For example, it would go from something like xyz.example.com to xyz.com.

Can anyone recommend a strategy for doing this as easily as possible with the least number of issues? Do I have to upgrade in place and then port to the new host, or is there some kind of shortcut? And what's the best way to handle the domain name change?

Thanks for any tips large or small!

# Answer
> 5 votes
I'd recommend something like this:

* Take a snapshot of the filesystem and database(s) and get the current site running on the new host and new domain name. See: https://docs.civicrm.org/sysadmin/en/latest/misc/switch-servers/
* Upgrade the filesystem to Drupal 7 (updating the CiviCRM package to CiviCRM 4.1.6 for Drupal 7 in the process of this upgrade so it is compatible but not functionality different beyond that).
* Once Drupal 7 is running smoothly, you can upgrade CiviCRM to the most recent version. Backup databases often before attempting next steps.
* Then, once CiviCRM is running smoothly on the latest version, you could either stay on Drupal 7 for the next few years OR upgrade to Backdrop CMS or Drupal 8 if you still have some energy.

# Answer
> 2 votes
There is no clear strategy for those kind of upgrades. What also migh be an idea is to start with fresh civicrm installation and then import your data from your old civicrm? This is probably quite a bit of work but probably the least risky one (as long as you keep your old civicrm up and running for a while because then you could still lookup old/missing data).

Another strategy is to follow the following steps: 1. Update Drupal 6 to Drupal 7 2. Move hosts 3. Update CiviCRM to the latest version

(Dont forget to create backups).

---
Tags: drupal, upgrade
---


title: Can i use CiviToken in Custom Fields

text:
# Question
Title: Can i use CiviToken in Custom Fields
Can i use CiviToken (https://github.com/eileenmcnaughton/nz.co.fuzion.civitoken) as Default Values in CiviCRM Custom Fields the same way Drupal lets you use it.

Thanks

# Answer
> 2 votes
No that is not possible. The tokens in CiviCRM are used in letters/emails. So they are used for communication with the contacts in your database.

Also note that a token in CiviCRM is always related to a contact.

---
Tags: custom-fields, tokens
---


# Question
Title: Where do I change the wording "membership fee total?"
I'm already in the automated workflow templates, but I don't know which variable I need to tinker about with so that I change the wording. I have a recurring recertification, that I'm processing as a membership fee, but the client doesn't want the receipt to read "membership fee." So, what am I looking for? I'd like to simply call it Fee Total instead of Membership Fee Total.

# Answer
> 1 votes
It should work with Word Replacements, even for receipts. Under Administer - customize - Word Replacements.

UPDATE: Under Administer - CiviMail - Message Templates, then on the System Workflow tab, there's two receipts for memberships. You can edit it in there. If it's hard to find the wording use CTRL-F and search for Membership Fee. Once in a while an upgrade will warn you that the template has changed and you'll need to merge the changes, but doesn't happen too often.

---
Tags: civicontribute, message-templates, system-workflow-messages
---


# Question
Title: How do I simplify multiple contributions?
Our client is association which only has organizational members. There are no individual memberships. However, member organizations have related contacts who require annual recertification. I want a contribution page, which works like an event registration, allowing one individual to register multiple contacts at the same time, but for an annual recertification fee. It's not an event and each contact should receive their annual reminder that certification will expire in a year.

I don't see this option for recurring contributions or memberships. Did I miss something or does someone have a clever solution for our unusual situation?

# Answer
This can be done with Webform-CiviCRM integration, and probably with Caldera Form as well. You can create multiple contats on a single form, specify a single fee, create relationships between the organization and individual. The annual reminder can be sent out in a number of ways; if you're using Drupal, I'd just use the Webform email functionality to send the email to all the individuals.
> 1 votes

---
Tags: civicontribute, contacts, contributions, recurring-contributions
---


title: Reorder Report Columns

text:
# Question
Title: Reorder Report Columns
I'm creating a basic custom report using constituent details. I'm hoping to find a way to reorder the columns in the report. For instance, I have a custom field that I'd like to go first, and then put the address second - to match a previous report format we used.

Is there a non coding option for doing this? If I have to code, where can I go to learn how to build it?

# Answer
> 2 votes
welcome to the community!

First up, I would *not* recommend learning to code by editing CiviCRM Reports.

The thing to exhaust before reaching for your Dial-a-Coder is the CiviCRM Profile option for an Advanced Search. You can get the information that you need from Civi 99% of the time using cleverly created Profiles, and then using them as a "Search View" on an Advanced Search.

If (and Only If!) you've exhausted that, then it may be best to ask a CiviCRM Partner organisation to help you. They can be found here: https://civicrm.org/partners-contributors.

Good luck!

JohnFF

# Answer
> 2 votes
This is now possible with the Extended Reports extension, which you can install from the in-app extension explorer. You'll need to rebuild your report in the corresponding Extended Report template. Click the **Advanced Report Configuration** link to reorder columns.

It's also possible with the ReportPlus extension, which I haven't used yet.

# Answer
> 1 votes
One option is to run the report and export to CSV (there is a button at the top of the report results). You can then open it in Excel and order the columns as you wish.

This is the quick way with no coding necessary.

# Answer
> 0 votes
You've certainly stumbled on to a weak point of CiviCRM. In addition to both of the answers already submitted, if you need better reporting you can also hook a reporting/business intelligence tool directly into CiviCRM. I know CiviCRM folks using JasperReports, Pentaho BI, and Tableau. I've also experimented with Google Data Studio. They're all a bit tricky to set up, but you have a LOT more reporting power.

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


# Question
Title: Change the permissions to access Volunteer tab in CiviEvent
I have found out that the Volunteer tab within CiviEvent needs 'edit\_all\_events'. However, I do not want people editing events they did not create.

Where in the code could I find and adjust this permission? I'm looking here https://github.com/civicrm/org.civicrm.volunteer/blob/master/volunteer.php but have not found it.

I do have permissions set so this same user role can create and manage civivolunteer projects but it would be best if they were created with the event as the associated entity.

Relevant CiviEvent Permissions:

* CiviEvent: access CiviEvent
* CiviEvent: edit event participants

Relevant CiviVolunteer Permissions

* CiviVolunteer: edit own volunteer projects
* CiviVolunteer: create volunteer projects
* CiviVolunteer: edit volunteer project relationships

With these, the user get permission denied:

# Answer
> 2 votes
The two places "Edit All Events" is defined as necessary is in the page callbacks in the router XML on lines 19 and 27.

You should be able to change those, then do a cache clear with menu rebuild to change the permissions.

Since CiviVolunteer is so AJAX-heavy, I'm pretty sure that's the right answer. You can probably just remove the "Edit All Events" permission. However, the better long-term answer is to do an event-level permission check in the PHP, because it's not uncommon for someone to grant permission to edit only some events via an extension.

---
Tags: civievent, civivolunteer
---


title: Removing elements for non-admin users

text:
# Question
Title: Removing elements for non-admin users
There are several instances where I want to remove some buttons, inputs, tabs, etc on various screens for non-admin users. For instance, on the Event Search screen in this template

`CRM/Event/Form/Search.extra.tpl` I have

```
<script>
{literal}
//remove smart group creation
CRM.$(function remove_smart_group_creation($) {
 $('#task option[value=12]').remove();
});

//remove group add contacts
CRM.$(function remove_group_add_contacts($) {
 $('#task option[value=2]').remove();
});

//remove delete participants
CRM.$(function remove_delete_participants($) {
 $('#task option[value=4]').remove();
});
</script>
{/literal}

```

How could I exempt the administrator role?

# Answer
Are they in the administrators group? Since then you might be able to do something like (untested):

```
CRM.api3('GroupContact', 'get', {
  "contact_id": "user_contact_id", // use this exact string to mean current user
  "group_id": "Administrators"
}).then(function(result) {
  if (result.count == 0) {
    // Not in the admin group
    // ** do your removal stuff here **
  }
});

```

There is also CRM.checkPerm() to check a CMS permission. See e.g. https://github.com/civicrm/civicrm-core/blob/5.18.4/templates/CRM/Tag/Page/Tag.tpl#L123, e.g. `CRM.checkPerm('administer reserved tags')`

```
if (!CRM.checkPerm("administer CiviCRM")) {
  // do your removal stuff
}

```
> 2 votes

---
Tags: custom-templates, jquery
---


# Question
Title: Event signup - email address verification?
Is there a way - with CiviCRM on WordPress - when an unauthenticated user registers for an event, it'll send an email with a link they need to click on to confirm their registratin?

# Answer
> 1 votes
You might want to consider using Caldera Form Wordpress plugin, then add the CIVICRM Caldera Form as well (https://github.com/mecachisenros/cf-civicrm). This provides a more powerful way to register various forms with CIVICRM including events. Though i have not done what you asked here on email verification as part of registration, i saw that Caldera has a free plugin with registration (Verify Email for Caldera Forms) which does what you mentioned. I think it is worth a try.

# Answer
> 0 votes
If you wish them to be authenticated users, you'll probably need a wordpress plugin such as Profile Builder, to get email confirmation activated. I dont think Wordpress offers a default process to do this for new users.

In your relevant civievent template and the online registration tab, it will indicate the profile that is being used. Go to Administer/Customize data and screens/Profiles and select settings for the appropriate profile title. In the advanced settings there is a section called "WordPress user account registration option?" with an option for "Account creation required". Together with the aforementioned plugin or something similar, this should ensure that new users get an authorisation email from wordpress to register as a user.

In the civievent template online registration tab, there are settings where you can switch on "Send Confirmation Email".

Hope this helps.

---
Tags: wordpress, civievent, email
---


title: Problem with civicrm tab in webform

text:
# Question
Title: Problem with civicrm tab in webform
Hello everyone i've a very weird problem. All tutorials show webform civicrm integration installation normaly and easyly done. I've done installation but in webform i do not see the civicrm tab displayed. Do anybody know what happened ? I only have these tabs

# Answer
> 2 votes
I finally solve the problem by doing this: 1- desable properly webform module and uninstall it. 2- desable properly webform civicrm integration and uninstall it. And i reisntall the both module and flush all caches. And i have the civicrm tab now

Thanks everybody

# Answer
> 2 votes
Is the webform\_civicrm module enabled as well as being installed? Go to your Modules page in Drupal (`/admin/modules`) and check it is enabled.

Also trying flushing the Drupal caches (`/admin/config/development/performance`)

---
Tags: webform-civicrm
---


# Question
Title: Contribution Status Edit in Options Group
The set of Contribution Status are available in the Options Group and allowed for Edit, though it seems that it is just for the names of the respective Contribution Status. What should be considered when changing these names ? For example, if I need a contribution status of "Waived", can i just rename the existing status of "Cancelled" ? I looked around and there seems to be concerns in adding/changing of contribution status as they are connected to account rules of the contribution. Thanks.

# Answer
Just to be sure I would add a new contribution status rather then meddle with the existing ones. But maybe that is just being to careful :-)
> 0 votes

---
Tags: contributions
---


# Question
Title: CiviMail does not use the editor set in 'Display Preferences'
I'm on 5.17.4 on drupal8 and civimail is working, html editing and all. But I don't know why. CKeditor loads and it's clearly not the drupal one (minimal), but it also doesn't fully match the editor config under Display Preferences. There are no console, server or civi log errors. It does take part of the preferences into account though; I can break the editor by choosing a skin or plugin that's not available.

The pressing problem is that the toolbar does not contain the link buttons and there appears to be filtering involved, so even in the source view, adding anchor elements manually, eventually just filters them out, replacing them with the tag's content. So no way to add html links.

So where is this third config coming from? Would this be the default CKEditor ships with and is somehow used instead of the civi provided one? What are the expected lookup paths?

EDIT: I can even use `'CKEditorCustomConfig' => "adasdada"` in Resources.php and it changes nothing. I also tried hacking away what I found of ACF code in the drupal plugin part of CKEditor, but that didn't have an effect either.

# Answer
> 1 votes
You didn't mention the path to the `config.js` that you're editing, which I think will help people answer your question.

However, my guess is that you are installing Civi using the Roundearth plugin, because that would account for you having two different `config.js` files. I'm also guessing you're editing the file at `<projectroot>/vendor/civicrm/civicrm-core/bower_components/ckeditor`. There's a second `config.js` at `<projectroot>/web/libraries/civicrm/bower_components/ckeditor`. Hopefully this is enough to help you solve your problem.

# Answer
> 1 votes
With the help of the answer to How to disable or fix the wysiwyg editor on civimail I managed to get it to not strip links by adding this line to the config:

```
config.extraAllowedContent = 'html;head;body;span;ul;li;table;td;style;a;a[*];*[id];*(*);*{*}';

```

It's different from the linked answer in that it explicitly reallows `a` and all its attributes.

So while this provides a workaround to the problem, this file will get overwritten if I ever touch civi ckeditor settings and it's still not at all clear why this is needed in the first place. And more importantly, the toolbar is still missing the buttons.

---
Tags: civimail, ckeditor, drupal-8
---


title: Set Default Value for Custom Field

text:
# Question
Title: Set Default Value for Custom Field
I am trying to set custom fields in a Custom Group. Here is my code:

```
function dfi_civicrm_buildForm($formName, &$form) {
  if ($formName == 'CustomData') {
    if ($form->getAction() == CRM_Core_Action::ADD) {
      echo 'Hello World';
      $defaults['custom_160_-5'] = mt_rand();
      $defaults['custom_70_-5'] = '23/10/2019';
      $form->setDefaults($defaults);
    }
  }
}

```

I have added it as an extension. It is not working even the Hello World isn't showing.

Thanks

# Answer
Moe's answer is technically workable - but oof ouch my performance!

Instead, something like this:

```
function getElementNameFromFieldName($fieldName, $elementIndex) {
  // This function could be further optimized so it's only called once, but meh.
  $elementNames = array_keys($elementIndex);
  foreach ($fieldNames as $fieldName) {
    if (strpos($elementName, $fieldName) === 0) {
      return $elementName;
    }
  }
}

if ( $formName == 'CRM_Contact_Form_CustomData' || $form == CRM_Core_Action::ADD) {
  $custom198ElementName = getElementNameFromFieldName('custom_198', $elementIndex);
  $custom197ElementName = getElementNameFromFieldName('custom_197', $elementIndex);
  $custom160ElementName = getElementNameFromFieldName('custom_160', $elementIndex);

  $form->updateElementAttr($custom198ElementName, ['readonly']);
  $form->updateElementAttr($custom197ElementName, ['readonly']);
  $form->updateElementAttr($custom160ElementName, ['readonly']);
}

if ($form == CRM_Core_Action::ADD) {
  $customid = rand (1,10000);
  $currentDate = date('d/m/Y');

  $defaults[$custom160ElementName] = $customid;
  $defaults[$custom197ElementName] = $currentDate;
  $defaults[$custom198ElementName] = $currentDate;
  $form->setDefaults($defaults);
}

```

Additionally - your code (and mine) as written will set defaults on EVERY form's `ADD` action. You probably want to limit it to those that have these custom fields.
> 2 votes

# Answer
With the help of JaapJansma-CiviCooP

I was able to make this work using the below code:

```
function dfi_civicrm_buildForm($formName, &$form) {
    if ( $formName == 'CRM_Contact_Form_CustomData' ) {
        for ($i = 1; $i < 10000; $i++) {
            $form->updateElementAttr('custom_198_'. $i,array('readonly'));
            $form->updateElementAttr('custom_197_'. $i,array('readonly'));
            $form->updateElementAttr('custom_160_'. $i,array('readonly'));
        }
     if ($form == CRM_Core_Action::ADD) {
        $customid = rand (1,10000);
        $currentDate = date('d/m/Y');
        for ($i = 1; $i < 10000; $i++) {
            $defaults['custom_160_-' . $i] = $customid;
            $defaults['custom_197_-' . $i] = $currentDate;
            $defaults['custom_198_-' . $i] = $currentDate;
            $form->updateElementAttr('custom_198_-'. $i,array('readonly'));
            $form->updateElementAttr('custom_197_-'. $i,array('readonly'));
            $form->updateElementAttr('custom_160_-'. $i,array('readonly'));
        }
        $form->setDefaults($defaults);
       }
    }
}

```

Thanks
> 1 votes

---
Tags: custom-fields
---


title: Using Google Analytics to track events

text:
# Question
Title: Using Google Analytics to track events
We'd like to set up goals inside CiviCRM regarding people signing up for events. How would you go about doing that? I'm not sure what URL we would use for them.

Our events use price sets; take payments via Paypal or pay later (send check or call in with credit card info).

Drupal CMS. Currently it's on Civi 4.7.22, but we'll be upgrading to the lastest version soon.

UPDATE: Has there been any progress on this? It really seems odd that there doesn't seem to be a way to track conversions in CiviCRM. The only thing I've been pointed to is something that is 3 years old and therefore unlikely to work with the current CiviCRM.

# Answer
> 1 votes
There was some work done on CiviCRM integration with Google Analytics as a part of Google Summer of Code program. This should be a good base. Check https://civicrm.org/blog/vishalagarwal/gsoc-completion-report-on-integrating-google-analytics-with-civicrm

# Answer
> 1 votes
It would help if to know what platforms you use (what cms and version of Civi) and how precise you need your tracking to be - however I can share that on one of my sites - we use wordpress and Civi. On Wordpress we use the plugin Google Analytics Dashboard for WP (GADWP) which embeds analytics all over our site. (you do not need this plugin to have analytics on your site - we just found it super easy) When visiting analytics.google.com we are able to see all the analytics it offers for all of our CiviCRM pages, including events. We are able to see how many people visited an event page, then went to registration page, then went to confirmation page, etc. However there is no way to see who exactly did what and google analytics will not track users that are blocking cookies, though that is a small amount of people.

Here is an example of a small event and the analytics we have from it. Is this the type of stuff you are looking for?

# Answer
> 0 votes
The Google Summer of Code project mentioned in Kurund's answer has been updated and improved upon by members of the community, it's now called the Webtracking extension.

---
Tags: civievent
---


# Question
Title: New fields failed to get created properly, resulting in DB errors
In adding new custom fields to an existing set, things recently went wrong. New entries were added in the civicrm\_custom\_field table, but the corresponding columns were not added to that field set's table. This resulted in `DB Error: no such field` breaking the UI display of existing cases. Deleting the rows from the custom\_field table solved the errors so that existing cases can be viewed.

Now new cases cannot be created. When the new case form is submitted the case gets partially added to the db (but is not visible via the UI) and a blank error screen with message `DB Constraint Violation - case_type_id should possibly be marked as mandatory for Case,get API. If so, please raise a bug report.` appears. Creating a new case (or trying to) adds 83000 new lines to the log file and near the top of that entry is `[nativecode=1054 ** Unknown column 'a_to_civicrm_value_special_case_fields_8._name_of_deleted_field_157' in 'field list']`. That seems confusing since "a\_to\_" is not actually part of the table's name, and "\_name\_of\_deleted\_field\_157" isn't in the civicrm\_custom\_field table. Are custom fields recorded/stored anywhere aside from civicrm\_custom\_field and the fieldset they're contained by?

Also, the field and case id's seem to have their incrementing messed up... the id#s appear to increment by 1 but there are several numbers skipped. Is that a problem? With the fields I deleted the row and column back to the last increment, 148, but when adding a new field its id was 155. Can missing id's (149-154 in this case) be problematic?

Is there anything you can suggest or point me towards in getting this working?

---

*edits:*

* 131 rows in civicrm\_custom\_field table
* CiviCRM 5.13.4

# Answer
> 1 votes
As Demerit mentioned, you have the same issues I reported. I don't think it's about the number of fields that's the issue, but the combination of data types. If you are receiving that error, be careful if your site is live because some of your cases will hit an error and you won't be able to view the case page.

Here's what I did:

If possible, you will want to delete all the custom fields and the custom group itself. Do it in phpmyadmin because it's faster. Once you have cleared the data, split the 65 fields into two custom groups. The error should go away.

If you can't delete the custom group because you already have data, delete the custom fields and then create a new custom group to split off some of those fields.

---
Tags: error, db-logging
---


title: Deleting and Adding Custom Fields

text:
# Question
Title: Deleting and Adding Custom Fields
I'm having trouble updating custom fields in CiviCRM 5.15.1/Drupal.

**UPDATE**: I believe this is occurring because I have too many custom fields in a custom group\_id. I was able to reproduce this error twice: created a custom group, added custom fields to it. Once the number of custom fields reaches 93, I can no longer edit new or existing fields. When editing custom fields, I get the error:

```
DB Constraint Violation - custom_group_id should possibly be marked as mandatory for CustomField,create API. If so, please raise a bug report.

```

I cannot delete the custom field through civicrm (only phpmyadmin). When trying to edit the custom field, the logo spins forever. Also, when I try to access certain cases, I get an error "case\_id is not valid : 1."

I know the issue is related to the custom fields/custom group because if I disable the custom group, I no longer have the case\_id issue. Any ideas what is going on?

# Answer
> 1 votes
It appears to be a bug, but temporary workaround fixes the issue (split the fields into two custom field groups).

Bug report can be found here: https://lab.civicrm.org/dev/core/issues/1330

# Answer
> 2 votes
I do not think it is because you have too many custom fields but it might well be that something is not totally right in your data? Ideally you should compare the values in the civicrm\_custom\_group and civicrm\_custom\_field tables with the actual data holding tables (in your example civicrm\_value\_care\_init\_69) and check if that is all correct.

The error also suggest that there is a constraint, so you probably use a field type of contact entity reference somewhere? And perhaps that is not entirely correctly configured? It is hard to be sure with only the error message to go by :-)

---
Tags: custom-fields
---


# Question
Title: Sign up for multiple events, get a discount
What is the best way to set it up so that people can register for multiple events all at once and get a discount for doing so? Say they are taking 5 trainings that are normally $100/ea and you want to allow them to sign up for all 5 at once and it only be $450?

Using Drupal 7, Civi 5.17.5

# Answer
Since you are on Drupal, you can create a webform. (You'll need the webform\_civicrm module) Enable 5 event options. Configure each event to be one of your events. Enable the participant fee field and set the price to $100. Create a conditional If event 1 and event 2 and event 3 and 4 and 5 are selected then price fields are set to 90. Enable a contribution form for online payment.
> 3 votes

---
Tags: civievent
---


# Question
Title: CiviCRM theme not loading after URL change
I've changed the URL of our site from beta.example.net to example.net and now CiviCRM is loading with the Drupal frontend theme instead of it's own.

I've enforced the change by updating the line in .htaccess that's enforcing https. It now reads as

```
RewriteCond %{HTTPS} off
RewriteRule ^(.*)$ https://example.net/$1 [R=301,L]

```

I've also updated the base URL in Drupal's settings.php to

```
 $base_url = 'https://example.net';

```

and the site URL in civicrm.settings.php with

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

```

I've also tried setting the CMS root in civicrm.settings.php with both

```
define('CIVICRM_CMSDIR', 'https://example.net/');

```

and

```
define('CIVICRM_CMSDIR', '/var/sites/c/example/public_html/');

```

but neither seems to make any difference.

My best guess was that I'd broken something with Shoreditch, trying at some point in the past to define a relative URL so that CiviCRM wouldn't constantly display an error. But I've gone to https://example.net/civicrm/admin/extensions (I can't navigate to it through the current theme) and disabled Shoreditch and the error is still occurring.

With Shoreditch enabled, this is how the resource URLs screen currently looks:

Screenshot of the current state of the extensions page:

And, just to confirm I cleared the cache:

Drupal's own backend loads as expected (though somewhat slowly). Drupal 7.67. CiviCRM 5.18.2. Shoreditch 0.1-alpha34.

# Answer
I updated CiviCRM, and all seems fine now!
> 1 votes

---
Tags: drupal, urls, resource-urls, shoreditch
---