# Question
Title: Can't get a list of people whose birthday in current month
We want to see a list of people who have birthday next month. Advanced search doesn't suit us because it is trying to do search only for a current year. We use SYSTOPIA Birthdays for generating upcoming birthday report (this month). Then we configured scheduled job to send this monthly mail report. But somehow date when report sent is moving forward: one month it was sent at the first day of month, the other month at the second day etc. We need to send this report at the very first day of the month. If it sends a mail report at for example seventh day then we don't see people whose birthday was in the first days of the month. Is there any way to solve this problem?

CiviCRM version - 5.10.2

# Answer
> 1 votes
This extension should help with your requirement –

https://civicrm.org/extensions/birthdays-month

---
Tags: civireport, advanced-search
---


# Question
Title: Can you change multiple price set selections at one time?
Lets say for example - you wanted to change 30 people from one price set option to another. Similar to how you could change the amount for multiple participants. Could you do that - or would you have to manually change those selections.

# Answer
Probably the simplest approach would be to

1. find the participants you want to change
2. export those participants with the following fields: Event ID Participant ID Participant Status Id Fee level Fee Amount
3. Open the resulting CSV file and change the Fee Level column accordingly. You may need to experiment a bit if you have multiple fields in the price set, but if your selection is a simple radio button, just use the label for the selection you want to change it to. The export file will have the amount selected after the fee label (i.e. "Full Fee - 1"), but your changes should only use the label exactly as on the price set "Full Fee".
4. Also change the Fee Amount accordingly.
5. Save the file and Import Participants using the Update option for duplicates.

Note that this will not update any related contributions or payments.
> 2 votes

---
Tags: price-sets
---


# Question
Title: CiviEvent: Could not find valid value for id (Drupal 7.6.1)
Using CiviCRM 5.7.2, using Drupal 7.61.

I thought I had fixed this error but it looks like my fix has created another problem... or at least another error has decided to appear.

This is an intermittent error, have not been able to replicate yet but had it reported by a member of our staff.

Upon registering for an event with event registration turned on, and confirmation screen / emails turned on, and payment turned off, the user receives this error message.

```
Could not find valid value for id

```

This error seems different to other similar reported errors in that the registration **does not** go through. The user does not see the confirmation screen, they do not receive the confirmation email, and their registration does not feed into CiviCRM.

Went into the drupal error logs and saw the following errors:

```
$Fatal Error Details = Array ( [message] => Could not find valid value for id [code] => )

```

and

```
$backTrace = #0 /home2/ppe/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(381): CRM_Core_Error::backtrace("backTrace", TRUE) #1 /home2/ppe/public_html/sites/all/modules/civicrm/CRM/Utils/Request.php(121): CRM_Core_Error::fatal("Could not find valid value for id") #2 /home2/ppe/public_html/sites/all/modules/civicrm/CRM/Event/StateMachine/Registration.php(52): CRM_Utils_Request::retrieve("id", "Positive", Object(CRM_Event_Controller_Registration), TRUE) #3 /home2/ppe/public_html/sites/all/modules/civicrm/CRM/Event/Controller/Registration.php(47): CRM_Event_StateMachine_Registration->__construct(Object(CRM_Event_Controller_Registration), TRUE) #4 /home2/ppe/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(304): CRM_Event_Controller_Registration->__construct("Event Registration", TRUE, "null", NULL, "false") #5 /home2/ppe/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:15)) #6 /home2/ppe/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3)) #7 /home2/ppe/public_html/sites/all/modules/civicrm/drupal/civicrm.module(445): CRM_Core_Invoke::invoke((Array:3)) #8 /home2/ppe/public_html/includes/menu.inc(527): civicrm_invoke("event", "register") #9 /home2/ppe/public_html/index.php(21): menu_execute_active_handler() #10 {main}

```

(sorry, not sure how to put that code as a block rather than a massive horizontal scrolling box)

Another user has also reported receiving a 500 error, I'm not sure if it's related. This is what Drupal error log says about their 500 error:

```
Warning: in_array() expects parameter 2 to be array, null given in CRM_Contact_BAO_Contact::formatProfileContactParams() (line 2128 of /home2/ppe/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php).

```

Anyone encountered anything similar? For now I've just disabled confirmation emails altogether until I work this out.

Cheers,

# Answer
> 2 votes
After doing a lot of trial and error and closely analyzing the complaints we have received, I'm fairly sure that the cause of the problem is: you register one person, then click back on your browser and try to register a second. Your browser doesn't properly refresh the page and somehow the id of the event is lost in the process.

I'm not sure what the best fix is - but I'm fairly certain that is the cause.

# Answer
> 1 votes
This extension may help as it lets you set the 'redirect' page after a form is submitted. therefore it may help if you find folk are using the back page, ie set it so that after a submission the form redirects back to the original form (which would then have the id in)

a risk though might be that folk might worry their submission wasn't successful. not sure. long time since i used it.

# Answer
> 0 votes
@josh, before doing anything else, please upgrade your Drupal site: https://www.drupal.org/SA-CORE-2019-002

Your sysadmin may have applied the patch without running the upgrade.

---
Tags: civievent, drupal, error
---


# Question
Title: How do I change the Fee Amount display in CiviEvent registration?
The fee amount is displaying too many zeros. Every time I set the fee amount for an event, for example $55.00, a bunch of zeros gets added to the end ... $55.000000. I've reviewed all of the localization settings and everything appears to be set us as described for USD display. I've attached a screenshot of the issue.

# Answer
> 2 votes
I was having a similar problem with php 7.2, Wordpress 5.0.3 and Civicrm 5.9.1. I ended up adding a javascript function to my website's javascript file. The meat of the function is `number.toFixed(2)` This forces the number to have only two decimal places.

```
function formatPrices() {
        const price_spans = document.querySelectorAll('.crm-price-amount-amount');
        if (!price_spans) {
            return;
        }

        price_spans.forEach(function(node) {
            const price = node.innerText;
            const data = price.split(' ');
            const symbol = data[0];
            const num = +data[1];
            const new_price = symbol + num.toFixed(2);
            node.innerText = new_price;
        });
    }

```

---
Tags: civievent
---


# Question
Title: Why does Stripe 5.2 extension hang on payment?
After completing the payment screen and clicking 'Confirm Contribution' the screen hangs. The button is greyed out but nothing happens. But...if I just hit enter on the screen without first completing anything at all I get an error message about incomplete fields, and then I can complete the screen & proceed normally. But if I first complete all the details and then hit the 'Confirm Contribution' it hangs. I've tried this on Chrome, Firefox and on my Android 8.1 phone - all the same. I am using 5.2 of the Stripe extension, CiviCRM 5.7.3 running under Drupal. Using the second technique described above I've made a couple of payments so there's nothing wrong with the setup, it seems. Update: I have found some errors in the Stripe dashboard, here are screenshots and this

# Answer
The answer is that I had pasted one of the Stripe API keys with a trailing blank, so when I looked at Firefox's web developer console there was a message 'Your key is invalid as it contains whitespace' Working consistently now, but how I previous managed to make a couple of contributions successfully I don't understand!
> 3 votes

---
Tags: drupal, stripe
---


# Question
Title: How do I add the With Contact information to the email that gets sent to activity assignees?
In any activity that I create, I want to include the With Contact's Phone and Email address in the email that goes to the assignee. I can't do this right now and I need to enter the information manually.

Why do I need this? When I assign the activity to any person, that person might not have access to CiviCRM to look up the info. I don't want to enter the data manually. This is a serious waste of time.

An example of what I want to do is in the following diagram.

# Answer
> 1 votes
The template to edit is the one called "Cases - Send Copy of an Activity" under Administer - CiviMail- Message templates and then click the System Workflow tab. The name is historical - it isn't just for cases.

But it seems like it's not currently possible to get the With Contact information since even the activity id is not available in the template. There is minimal data to work with.

You could do this by customizing CRM\_Case\_BAO\_Case::sendActivityCopy() to include more information. I could see that being useful as a core improvement, to at least include the activity id and related contact ids.

Note that it's possible to have multiple With Contact's on an activity, so if there's more than one you'd need to handle that.

# Answer
> 0 votes
What I want is getting the target user information in the mail template. For example :
If I do contact.phone I will get the sender phone and not the selecterd user phone.
Something like selectedUser.phone or the email.

---
Tags: civimail, joomla, civicrm-home
---


# Question
Title: Expected one ParticipantPayment but found 0
Civicrm Version 5.4.0
On New Participant Registration, I get an error "Expected one ParticipantPayment but found 0" when I try to register for an event which is not a paid event.

CiviCRM\_API3\_Exception: "Expected one ParticipantPayment but found 0"

```
 #0 C:\sites\default\files\civicrm\ext\org.civicrm.multidomaininvoicing\multidomaininvoicing.php(316):
 civicrm_api3("ParticipantPayment", "getvalue", (Array:4))
 #1 C:\sites\all\modules\civicrm\CRM\Utils\Hook.php(274): multidomaininvoicing_civicrm_postProcess("CRM_Event_Form_Participant",
 Object(CRM_Event_Form_Participant))
 #2 C:\sites\all\modules\civicrm\CRM\Utils\Hook\DrupalBase.php(84): CRM_Utils_Hook->runHooks((Array:69), "civicrm_postProcess", 2,
 "CRM_Event_Form_Participant", Object(CRM_Event_Form_Participant),
 NULL, NULL, NULL, NULL)
 #3 C:\sites\all\modules\civicrm\Civi\Core\CiviEventDispatcher.php(90): CRM_Utils_Hook_DrupalBase->invokeViaUF(2,
 "CRM_Event_Form_Participant", Object(CRM_Event_Form_Participant),
 NULL, NULL, NULL, NULL, "civicrm_postProcess")
 #4 [internal function](): Civi\Core\CiviEventDispatcher::delegateToUF(Object(Civi\Core\Event\GenericHookEvent),
 "hook_civicrm_postProcess", Object(Civi\Core\CiviEventDispatcher))
 #5 C:\sites\all\modules\civicrm\vendor\symfony\event-dispatcher\Symfony\Component\EventDispatcher\EventDispatcher.php(164):
 call_user_func((Array:2), Object(Civi\Core\Event\GenericHookEvent),
 "hook_civicrm_postProcess", Object(Civi\Core\CiviEventDispatcher))
 #6 C:\sites\all\modules\civicrm\vendor\symfony\event-dispatcher\Symfony\Component\EventDispatcher\EventDispatcher.php(53): Symfony\Component\EventDispatcher\EventDispatcher->doDispatch((Array:1),
 "hook_civicrm_postProcess", Object(Civi\Core\Event\GenericHookEvent))
 #7 C:\sites\all\modules\civicrm\vendor\symfony\event-dispatcher\Symfony\Component\EventDispatcher\ContainerAwareEventDispatcher.php(167):
 Symfony\Component\EventDispatcher\EventDispatcher->dispatch("hook_civicrm_postProcess",
 Object(Civi\Core\Event\GenericHookEvent))
 #8 C:\sites\all\modules\civicrm\Civi\Core\CiviEventDispatcher.php(47): Symfony\Component\EventDispatcher\ContainerAwareEventDispatcher->dispatch("hook_civicrm_postProcess",
 Object(Civi\Core\Event\GenericHookEvent))
 #9 C:\sites\all\modules\civicrm\CRM\Utils\Hook.php(164): Civi\Core\CiviEventDispatcher->dispatch("hook_civicrm_postProcess",
 Object(Civi\Core\Event\GenericHookEvent))
 #10 C:\sites\all\modules\civicrm\CRM\Utils\Hook.php(444): CRM_Utils_Hook->invoke((Array:2), "CRM_Event_Form_Participant",
 Object(CRM_Event_Form_Participant), NULL, NULL, NULL, NULL,
 "civicrm_postProcess")
 #11 C:\sites\all\modules\civicrm\CRM\Core\Form.php(516): CRM_Utils_Hook::postProcess("CRM_Event_Form_Participant",
 Object(CRM_Event_Form_Participant))
 #12 C:\sites\all\modules\civicrm\CRM\Core\Form.php(490): CRM_Core_Form->postProcessHook()
 #13 C:\sites\all\modules\civicrm\CRM\Core\QuickForm\Action\Upload.php(169):
 CRM_Core_Form->mainProcess()
 #14 C:\sites\all\modules\civicrm\CRM\Core\QuickForm\Action\Upload.php(136):
CRM_Core_QuickForm_Action_Upload->realPerform(Object(CRM_Event_Form_Participant),
 "upload")
 #15 C:\sites\all\modules\civicrm\packages\HTML\QuickForm\Controller.php(203):
 CRM_Core_QuickForm_Action_Upload->perform(Object(CRM_Event_Form_Participant),
 "upload")
 #16 C:\sites\all\modules\civicrm\packages\HTML\QuickForm\Page.php(103):
 HTML_QuickForm_Controller->handle(Object(CRM_Event_Form_Participant),
 "upload")
 #17 C:\sites\all\modules\civicrm\CRM\Core\Controller.php(351): HTML_QuickForm_Page->handle("upload")
 #18 C:\sites\all\modules\civicrm\CRM\Event\Page\Tab.php(118): CRM_Core_Controller->run()
 #19 C:\sites\all\modules\civicrm\CRM\Event\Page\Tab.php(175): CRM_Event_Page_Tab->edit()
 #20 C:\sites\all\modules\civicrm\CRM\Core\Invoke.php(309): CRM_Event_Page_Tab->run((Array:3), NULL)
 #21 C:\sites\all\modules\civicrm\CRM\Core\Invoke.php(84): CRM_Core_Invoke::runItem((Array:15))
 #22 C:\sites\all\modules\civicrm\CRM\Core\Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
 #23 C:\sites\all\modules\civicrm\drupal\civicrm.module(445): CRM_Core_Invoke::invoke((Array:3))
 #24 C:\includes\menu.inc(527): civicrm_invoke("participant", "add")
 #25 C:\index.php(22): menu_execute_active_handler()
 #26 {main}

```

# Answer
> 3 votes
From the backtrace, you can see that the error is caused by a line in the `org.civicrm.multidomaininvoicing` extension. I assume this is your extension?

`getvalue` is only appropriate if you're sure that a value exists; in this case, you made an API call for a value that doesn't exist. If this is a possibility in your scenario, instead use `get` and search the `values` array for your result (with error handling to deal with a missing value.

---
Tags: civievent, participants
---


# Question
Title: How to install CiviCRM patch (5.7.2 to 5.7.5) on Drupal 7.64 - is my file directory writeable?
Currently running Drupal 7.64 and got a notification that I need to install a security patch on CiviCRM. Currently running CiviCRM 5.7.2 and need to install 5.7.5.

Apologies in advance if this question seems a bit unintelligent - but completely beyond my scope!

I read through the instructions in the CiviCRM Documentation and had one question in particular:

```
Ensure that your Drupal files directory is writeable by the webserver.

```

Is there a way of doing this through cPanel? I have no idea how to access anything command line-related...

I've updated Drupal before (e.g. from 7.61 to 7.64), not sure if that gives an indication of whether my file directory is writeable?

The rest of the instructions seem pretty straight forward... but is there anything else I need to be wary of? Always a bit hesitant with installing updates for the first time, so just making sure!

# Answer
For cpanel it's described in more detail at https://documentation.cpanel.net/display/76Docs/File+Manager#FileManager-Modifyfileorfolderpermissions

but briefly open the icon called File Manager and right-click on the folder and choose permissions.

In most shared hosting setups "writable by the webserver" means the user column has write access. Here "Drupal files" most likely means the "sites/default/files" subfolder.

But if you're upgrading and civi was working before it almost certainly has the right permissions already.

If you make backups first you'll usually be safe no matter what happens.
> 1 votes

---
Tags: drupal, upgrade
---


# Question
Title: How can I safely revert to earlier version of CiviCRM without reverting the database?
I am using Drupal 7.64. Everything was working fine on my site with CiviCRM 5.10.2.

I upgraded to 5.10.3 and two days later discovered that it broke an event registration page that relies on the extension Group-Based Pricing. Yes, I know that extension is not cleared for takeoff with this version of CiviCRM, but I had tested it carefully before deploying it live, and it has worked for a month. And now I absolutely need to keep using it for the next few months until the event is over.

I then upgraded to 5.10.4 thinking that might fix things. It didn't.

In the meantime, members have made other kinds of contributions, altered their profiles, etc. The database has been changed under 5.10.3 and 4.

So, while I can go back to the 5.10.2 CiviCRM code easily enough, I can't revert the database without losing the changes that were made in the past three days.

What should I do? Can I safely proceed with the upgraded and altered database as is? Is there something I can do to make it safe to use under 5.10.2?

Thanks.

# Answer
There are no database changes between the 5.10 versions so you can switch the code back & forth without issue - however you will lose the security patches, making your site vulnerable - which is not great since the SAs have been published - it might be you do that temporarily but prioritise getting the extension fixed. If you tighten the permission on accessing uploaded files it will mitigate the security issue - does the extension use uploaded files & images?
> 3 votes

---
Tags: drupal, database
---


# Question
Title: Custom field default values at database level, not form level?
When you set a default value for a custom field, it only exists at the form level. Is there a practical way to enforce them at the database level? For instance, by altering the SQL table definition?

I understand that a custom field record isn't created under every circumstance, but if I account for that (e.g. another custom field in that group is always populated) is altering the SQL practical?

# Answer
> 1 votes
A possible downside, although unlikely, is an upgrade might do some kind of batch modify on all custom fields that would wipe your default value.

What are you trying to do that can't be done with a hook or trigger?

# Answer
> 1 votes
Hang on, there is a default\_value column in the civicrm\_custom\_field table? I would assume changing that value would actually work....

---
Tags: custom-fields, developer, sql
---


# Question
Title: Error printing event badges after sorting by participant name
We need to print event badges in alphabetical order instead of the date registered order that you initially get from a participant search. The PDF creation completes successfully with the default sort, but after changing to alphabetical order, you get a yellow screen DB Error: unknown error. Is there a way around this? Thanks!

We are on CiviCRM 5.4.0 on Drupal 7.63

# Answer
I confirmed that this is caused by having a name badge layout with a spacer field and sorting the participants by name prior to generating name badges. My work-around for now is to instead of using a spacer in the badge layout, to insert an unused blank field, and I can now print alphabetically sorted badges.
> 2 votes

---
Tags: search-results, pdf, participants, printing
---


title: Can't see events

text:
# Question
Title: Can't see events
After recent update (I think to 5.10.2) events don't show up. Interestingly, I can see the pager (1 2 next last) but not the events themselves. I *can* see the events in the Event Dashboard.

When I try to `Edit View` in Drupal, I see the following error

> `SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM civicrm.civicrm_event civicrm_event LIMIT 20 OFFSET 0' at line 1`

and all fields show `Broken/missing handler`

I am pretty sure it could be a clue - but how can I see the actual SQL that is throwing the error? There is nothing in the log file

I update both Drupal and Civi to the latest versions (7.64 and 5.10.4) but no impact

# Answer
> 2 votes
To see the SQL query when in edit mode of the view, select the ‘Show the SQL query’ option here - /admin/structure/views/settings

This may assist –

1. Clear views’ cache - /admin/structure/views/settings/advanced
2. If that doesn’t fix it then update your settings.php file if you have separate databases - https://docs.civicrm.org/sysadmin/en/latest/integration/drupal/views/#drupal-7

Clear CiviCRM/Drupal caches.

---
Tags: civievent, drupal, drupal-views
---


# Question
Title: Mailing Bounces 100% with "authentication failure" or "failed to set sender"
I have some issues with sending out mailings (used to work, now it doesn't anymore). My mailing is failing 100% with a combination of the following errors:

1. `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 Ema`
2. `Failed to set sender: b.1139.33843.cb2d344bc94b0533@myserver.com [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 Admi`

Regarding the `Administer CiviCRM >> Global Settings >> Outbound Ema` settings, I have checked these copious times and have verified that they work using the "Save & Send Test Email" function. This for both the `mail()` and `SMTP` settings.

I have also tested the actual mailing settings by sending a test to one of my email addresses. This works 100% - which would indicate that the mailing test function uses a different approach to the actual mailing component (this does not make sense for me as it will result in inconsistent results).

Now more to the actual errors:

1. No idea what's wrong, as the tests pass.
2. I thought this was a part of the VERP option (server changes the email address) - the SMTP does not recognise the email address and does not allow sending.

When using `mail()`, the mailing is successfully sent, but most of the emails fall into the very dark hole of the internet - and the ones that get delivered are mostly sent to spam. This is because the reverse DNS look up for my domain is not the same as the IP of my server.

When using `SMTP` the SMTP server does not recognise the email address I am sending from. And yes, I have switched off the VERP function (I think).

Any ideas what else I can do to get closer to fixing this?

# Answer
I experienced this problem when I was running different versions of PHP in the website and my command line interface (CLI). You can check your PHP version for the web at **Administer menu » Administration Console » System Status**. You can find your CLI PHP version by running `php -v`. I was running 7.0 and 7.2 respectively.

Since PHP 7.2 has deprecated the `php-mcrypt` module, CiviCRM was expecting my mail password to be unencrypted when run from CLI, and encrypted when run from the web interface. So all web-based testing works - but CLI gets authentication errors. The solution is to match your PHP version on web and CLI.

Note that if you move from some lower version to PHP 7.2+, you'll need to re-enter your mail password for things to work.
> 5 votes

---
Tags: wordpress, error, mailing, smtp
---


# Question
Title: Relationships and Dashboard are broken with https
I recently tried forcing https in the htaccess file and noticed that the relationships in the main contact screen disappeared. They show up if I access them through a listing on the dashboard, but if I access the record through search they are simply not listed. Also the dashboard doesn't display properly and can't be configured. I haven't noticed other effects of https but they may exist too.

# Answer
The first thing I'd check is whether your `CIVICRM_UF_BASEURL` in `civicrm.settings.php` is set without the `https` and if so, change that. Then flush caches and test again.
> 3 votes

---
Tags: relationships, dashboard
---


# Question
Title: (How) can I add a footer fixed to the page of a message template for printing a pdf?
How can I fix a footer section at the bottom of the page of a Message Template? (no matter whether how much text the user is adding - the footer with our contact and donation account should always be at the very bottom of the page).

We would like to use Message Templates and the CiviCRM action "Print/Merge Document" to let our team write a letter to a contact within CiviCRM (getting address etc. filled through tokens), export it as a PDF and print it for postal mailing. Our previous CRM software had to option to generate a Word document prefilled with the contact information and including a footer element for this.

I found an old forum question about this but unfortunately it doesn't offer any solutions.

# Answer
All you need to do is create a table in the first place - Add header in the top row and calculate the number of rows can fit in the page and add the footer to the bottom of the table

Now when ever you add content to the page make sure you add to the table row by row and any empty row should be filled with ` `

By doing this you are always setting a set row and the footer remains in the same place

you need to create an extension for the same

I guess this helps !!!

If my suggestion is bit tricky - just wait for others to reply

Thanks
> 1 votes

# Answer
Use nested tables. The first table has a \< tr\> with a height to where you want your footer to start then nest a table within the \< td\> to hold your Smarty query. You can use valign="top" to put your data at the top of the row rather than centered. As long as your Smarty query results are not bigger than your row height, your footer will always be in the same place. Add table rows as needed to create your footer.

```
<table align="left" cellpadding="4" cellspacing="0" class="table" style="width: 875px;">
<tbody>
    <tr style="height:385px;">
        <td valign="top">
        <table align="left" cellpadding="4" cellspacing="0" class="table" style="width: 870px;border: 1px solid #281c1d;">
            <tbody>
                <tr style="padding: 8px; border: 1px solid #281c1d;">
                    <th style="padding: 8px;">Header1</th>
                    <th style="padding: 8px;">Header2</th>
                    <th style="padding: 8px;">Header3</th>
                    <th style="padding: 8px;">Header4</th>
                </tr>
                <!--
{Smarty}

{foreach from=$result.values}

-->
                <tr style="padding: 4px; border: 1px solid #281c1d;">
                    <td style="padding: 4px; border: 1px solid #281c1d;">{$value}</td>
                    <td style="padding: 4px; border: 1px solid #281c1d;">{$value2}</td>
                    <td style="padding: 4px; border: 1px solid #281c1d;">{$value3}</td>
                    <td style="text-align:right;padding: 4px; border: 1px solid #281c1d;">$ {$value4}</td>
                </tr>
                <!--
{/foreach}
-->
            </tbody>
        </table>
        </td>
    </tr>
    <tr>
        <td>Put Footer</td>
    </tr>
</tbody>
</table>

```

Add rows and columns to the footer as needed
> 1 votes

---
Tags: message-templates, pdf, printing
---


# Question
Title: Upgrade 5.2.2 to 5.10.4 fails with UPDATE civicrm_msg_template error
Wordpress 5.1

This is similar to Upgrade script (5.0.1 to 5.4.1) / DB Syntax error - UPDATE civicrm\_msg\_template SET msg\_text for which I don't understand the solution - if in fact one was provided.

Mine refers to contribution invoice receipt which I did have a custom version but I reverted to default before trying to upgrade. Is there perhaps somewhere else I need to delete the custom versions?

Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1, 1064 Additional Details: Array ( \[callback\] =\> Array ( \[0\] =\> CRM\_Core\_Error \[1\] =\> handle )

```
[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] =>
      UPDATE civicrm_msg_template SET msg_text = '{assign var=\"greeting\" value=\"{contact.email_greeting}\"}{if $greeting}{$greeting},{/if}\n{if $receipt_text}\n{$receipt_text}\n{/if}\n{if $is_pay_later}\n\n===========================================================\n{$pay_later_receipt}\n===========================================================\n{else}\n\n{ts}Please print this receipt for your records.{/ts}\n{/if}\n\n{if $amount}\n===========================================================\n{ts}Contribution Information{/ts}\n\n===========================================================\n{if $lineItem and $priceSetID and !$is_quick_config}\n{foreach from=$lineItem item=value key=priceset}\n---------------------------------------------------------\n{capture assign=ts_item}{ts}Item{/ts}{/capture}\n{capture assign=ts_qty}{ts}Qty{/ts}{/capture}\n{capture assign=ts_each}{ts}Each{/ts}{/capture}\n{if $dataArray}\n{capture assign=ts_subtotal}{ts}Subtotal{/ts}{/capture}\n{capture assign=ts_taxRate}{ts}Tax Rate{/ts}{/capture}\n{capture assign=ts_taxAmount}{ts}Tax Amount{/ts}{/capture}\n{/if}\n{capture assign=ts_total}{ts}Total{/ts}{/capture}\n{$ts_item|string_format:\"%-30s\"} {$ts_qty|string_format:\"%5s\"} {$ts_each|string_format:\"%10s\"} {if $dataArray} {$ts_subtotal|string_format:\"%10s\"} {$ts_taxRate} {$ts_taxAmount|string_format:\"%10s\"} {/if} {$ts_total|string_format:\"%10s\"}\n----------------------------------------------------------\n{foreach from=$value item=line}\n{capture assign=ts_item}{if $line.html_type eq \'Text\'}{$line.label}{else}{$line.field_title} - {$line.label}{/if} {if $line.description} {$line.description}{/if}{/capture}{$ts_item|truncate:30:\"...\"|string_format:\"%-30s\"} {$line.qty|string_format:\"%5s\"} {$line.unit_price|crmMoney:$currency|string_format:\"%10s\"} {if $dataArray}{$line.unit_price*$line.qty|crmMoney:$currency|string_format:\"%10s\"} {if $line.tax_rate != \"\" || $line.tax_amount != \"\"}  {$line.tax_rate|string_format:\"%.2f\"} %  {$line.tax_amount|crmMoney:$currency|string_format:\"%10s\"} {else}                  {/if}  {/if} {$line.line_total+$line.tax_amount|crmMoney:$currency|string_format:\"%10s\"}\n{/foreach}\n{/foreach}\n\n{if $dataArray}\n{ts}Amount before Tax{/ts}: {$amount-$totalTaxAmount|crmMoney:$currency}\n\n{foreach from=$dataArray item=value key=priceset}\n{if $priceset || $priceset == 0}\n{$taxTerm} {$priceset|string_format:\"%.2f\"}%: {$value|crmMoney:$currency}\n{else}\n{ts}No{/ts} {$taxTerm}: {$value|crmMoney:$currency}\n{/if}\n{/foreach}\n{/if}\n\n{if $totalTaxAmount}\n{ts}Total Tax Amount{/ts}: {$totalTaxAmount|crmMoney:$currency}\n{/if}\n\n{ts}Total Amount{/ts}: {$amount|crmMoney:$currency}\n{else}\n{ts}Amount{/ts}: {$amount|crmMoney:$currency} {if $amount_level } - {$amount_level} {/if}\n{/if}\n{/if}\n{if $receive_date}\n\n{ts}Date{/ts}: {$receive_date|crmDate}\n{/if}\n{if $is_monetary and $trxn_id}\n{ts}Transaction #{/ts}: {$trxn_id}\n{/if}\n\n{if $is_recur}\n{ts}This is a recurring contribution.{/ts}\n\n{if $cancelSubscriptionUrl}\n{ts}You can cancel future contributions at:{/ts}\n\n{$cancelSubscriptionUrl}\n\n{/if}\n\n{if $updateSubscriptionBillingUrl}\n{ts}You can update billing details for this recurring contribution at:{/ts}\n\n{$updateSubscriptionBillingUrl}\n\n{/if}\n\n{if $updateSubscriptionUrl}\n{ts}You can update recurring contribution amount or change the number of installments for this recurring contribution at:{/ts}\n\n{$updateSubscriptionUrl}\n\n{/if}\n{/if}\n\n{if $honor_block_is_active}\n===========================================================\n{$soft_credit_type}\n===========================================================\n{foreach from=$honoreeProfile item=value key=label}\n{$label}: {$value}\n{/foreach}\n{elseif $softCreditTypes and $softCredits}\n{foreach from=$softCreditTypes item=softCreditType key=n}\n===========================================================\n{$softCreditType}\n===========================================================\n{foreach from=$softCredits.$n item=value key=label}\n{$label}: {$value}\n{/foreach}\n{/foreach}\n{/if}\n{if $pcpBlock}\n===========================================================\n{ts}Personal Campaign Page{/ts}\n\n===========================================================\n{ts}Display In Honor Roll{/ts}: {if $pcp_display_in_roll}{ts}Yes{/ts}{else}{ts}No{/ts}{/if}\n\n{if $pcp_roll_nickname}{ts}Nickname{/ts}: {$pcp_roll_nickname}{/if}\n\n{if $pcp_personal_note}{ts}Personal Note{/ts}: {$pcp_personal_note}{/if}\n\n{/if}\n{if $onBehalfProfile}\n===========================================================\n{ts}On Behalf Of{/ts}\n\n===========================================================\n{foreach from=$onBehalfProfile item=onBehalfValue key=onBehalfName}\n{$onBehalfName}: {$onBehalfValue}\n{/foreach}\n{/if}\n\n{if !( $contributeMode eq \'notify\' OR $contributeMode eq \'directIPN\' ) and $is_monetary}\n{if $is_pay_later && !$isBillingAddressRequiredForPayLater}\n===========================================================\n{ts}Registered Email{/ts}\n\n===========================================================\n{$email}\n{elseif $amount GT 0}\n===========================================================\n{ts}Billing Name and Address{/ts}\n\n===========================================================\n{$billingName}\n{$address}\n\n{$email}\n{/if} {* End ! is_pay_later condition. *}\n{/if}\n{if $contributeMode eq \'direct\' AND !$is_pay_later AND $amount GT 0}\n\n===========================================================\n{ts}Credit Card Information{/ts}\n\n===========================================================\n{$credit_card_type}\n{$credit_card_number}\n{ts}Expires{/ts}: {$credit_card_exp_date|truncate:7:\'\'|crmDate}\n{/if}\n\n{if $selectPremium }\n===========================================================\n{ts}Premium Information{/ts}\n\n===========================================================\n{$product_name}\n{if $option}\n{ts}Option{/ts}: {$option}\n{/if}\n{if $sku}\n{ts}SKU{/ts}: {$sku}\n{/if}\n{if $start_date}\n{ts}Start Date{/ts}: {$start_date|crmDate}\n{/if}\n{if $end_date}\n{ts}End Date{/ts}: {$end_date|crmDate}\n{/if}\n{if $contact_email OR $contact_phone}\n\n{ts}For information about this premium, contact:{/ts}\n\n{if $contact_email}\n  {$contact_email}\n{/if}\n{if $contact_phone}\n  {$contact_phone}\n{/if}\n{/if}\n{if $is_deductible AND $price}\n\n{ts 1=$price|crmMoney:$currency}The value of this premium is %1. This may affect the amount of the tax deduction you can claim. Consult your tax advisor for more information.{/ts}{/if}\n{/if}\n\n{if $customPre}\n===========================================================\n{$customPre_grouptitle}\n\n===========================================================\n{foreach from=$customPre item=customValue key=customName}\n{if ( $trackingFields and ! in_array( $customName, $trackingFields ) ) or ! $trackingFields}\n {$customName}: {$customValue}\n{/if}\n{/foreach}\n{/if}\n\n\n{if $customPost}\n===========================================================\n{$customPost_grouptitle}\n\n===========================================================\n{foreach from=$customPost item=customValue key=customName}\n{if ( $trackingFields and ! in_array( $customName, $trackingFields ) ) or ! $trackingFields}\n {$customName}: {$customValue}\n{/if}\n{/foreach}\n{/if}\n' WHERE id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1]
[type] => DB_Error
[user_info] =>
      UPDATE civicrm_msg_template SET msg_text = '{assign var=\"greeting\" value=\"{contact.email_greeting}\"}{if $greeting}{$greeting},{/if}\n{if $receipt_text}\n{$receipt_text}\n{/if}\n{if $is_pay_later}\n\n===========================================================\n{$pay_later_receipt}\n===========================================================\n{else}\n\n{ts}Please print this receipt for your records.{/ts}\n{/if}\n\n{if $amount}\n===========================================================\n{ts}Contribution Information{/ts}\n\n===========================================================\n{if $lineItem and $priceSetID and !$is_quick_config}\n{foreach from=$lineItem item=value key=priceset}\n---------------------------------------------------------\n{capture assign=ts_item}{ts}Item{/ts}{/capture}\n{capture assign=ts_qty}{ts}Qty{/ts}{/capture}\n{capture assign=ts_each}{ts}Each{/ts}{/capture}\n{if $dataArray}\n{capture assign=ts_subtotal}{ts}Subtotal{/ts}{/capture}\n{capture assign=ts_taxRate}{ts}Tax Rate{/ts}{/capture}\n{capture assign=ts_taxAmount}{ts}Tax Amount{/ts}{/capture}\n{/if}\n{capture assign=ts_total}{ts}Total{/ts}{/capture}\n{$ts_item|string_format:\"%-30s\"} {$ts_qty|string_format:\"%5s\"} {$ts_each|string_format:\"%10s\"} {if $dataArray} {$ts_subtotal|string_format:\"%10s\"} {$ts_taxRate} {$ts_taxAmount|string_format:\"%10s\"} {/if} {$ts_total|string_format:\"%10s\"}\n----------------------------------------------------------\n{foreach from=$value item=line}\n{capture assign=ts_item}{if $line.html_type eq \'Text\'}{$line.label}{else}{$line.field_title} - {$line.label}{/if} {if $line.description} {$line.description}{/if}{/capture}{$ts_item|truncate:30:\"...\"|string_format:\"%-30s\"} {$line.qty|string_format:\"%5s\"} {$line.unit_price|crmMoney:$currency|string_format:\"%10s\"} {if $dataArray}{$line.unit_price*$line.qty|crmMoney:$currency|string_format:\"%10s\"} {if $line.tax_rate != \"\" || $line.tax_amount != \"\"}  {$line.tax_rate|string_format:\"%.2f\"} %  {$line.tax_amount|crmMoney:$currency|string_format:\"%10s\"} {else}                  {/if}  {/if} {$line.line_total+$line.tax_amount|crmMoney:$currency|string_format:\"%10s\"}\n{/foreach}\n{/foreach}\n\n{if $dataArray}\n{ts}Amount before Tax{/ts}: {$amount-$totalTaxAmount|crmMoney:$currency}\n\n{foreach from=$dataArray item=value key=priceset}\n{if $priceset || $priceset == 0}\n{$taxTerm} {$priceset|string_format:\"%.2f\"}%: {$value|crmMoney:$currency}\n{else}\n{ts}No{/ts} {$taxTerm}: {$value|crmMoney:$currency}\n{/if}\n{/foreach}\n{/if}\n\n{if $totalTaxAmount}\n{ts}Total Tax Amount{/ts}: {$totalTaxAmount|crmMoney:$currency}\n{/if}\n\n{ts}Total Amount{/ts}: {$amount|crmMoney:$currency}\n{else}\n{ts}Amount{/ts}: {$amount|crmMoney:$currency} {if $amount_level } - {$amount_level} {/if}\n{/if}\n{/if}\n{if $receive_date}\n\n{ts}Date{/ts}: {$receive_date|crmDate}\n{/if}\n{if $is_monetary and $trxn_id}\n{ts}Transaction #{/ts}: {$trxn_id}\n{/if}\n\n{if $is_recur}\n{ts}This is a recurring contribution.{/ts}\n\n{if $cancelSubscriptionUrl}\n{ts}You can cancel future contributions at:{/ts}\n\n{$cancelSubscriptionUrl}\n\n{/if}\n\n{if $updateSubscriptionBillingUrl}\n{ts}You can update billing details for this recurring contribution at:{/ts}\n\n{$updateSubscriptionBillingUrl}\n\n{/if}\n\n{if $updateSubscriptionUrl}\n{ts}You can update recurring contribution amount or change the number of installments for this recurring contribution at:{/ts}\n\n{$updateSubscriptionUrl}\n\n{/if}\n{/if}\n\n{if $honor_block_is_active}\n===========================================================\n{$soft_credit_type}\n===========================================================\n{foreach from=$honoreeProfile item=value key=label}\n{$label}: {$value}\n{/foreach}\n{elseif $softCreditTypes and $softCredits}\n{foreach from=$softCreditTypes item=softCreditType key=n}\n===========================================================\n{$softCreditType}\n===========================================================\n{foreach from=$softCredits.$n item=value key=label}\n{$label}: {$value}\n{/foreach}\n{/foreach}\n{/if}\n{if $pcpBlock}\n===========================================================\n{ts}Personal Campaign Page{/ts}\n\n===========================================================\n{ts}Display In Honor Roll{/ts}: {if $pcp_display_in_roll}{ts}Yes{/ts}{else}{ts}No{/ts}{/if}\n\n{if $pcp_roll_nickname}{ts}Nickname{/ts}: {$pcp_roll_nickname}{/if}\n\n{if $pcp_personal_note}{ts}Personal Note{/ts}: {$pcp_personal_note}{/if}\n\n{/if}\n{if $onBehalfProfile}\n===========================================================\n{ts}On Behalf Of{/ts}\n\n===========================================================\n{foreach from=$onBehalfProfile item=onBehalfValue key=onBehalfName}\n{$onBehalfName}: {$onBehalfValue}\n{/foreach}\n{/if}\n\n{if !( $contributeMode eq \'notify\' OR $contributeMode eq \'directIPN\' ) and $is_monetary}\n{if $is_pay_later && !$isBillingAddressRequiredForPayLater}\n===========================================================\n{ts}Registered Email{/ts}\n\n===========================================================\n{$email}\n{elseif $amount GT 0}\n===========================================================\n{ts}Billing Name and Address{/ts}\n\n===========================================================\n{$billingName}\n{$address}\n\n{$email}\n{/if} {* End ! is_pay_later condition. *}\n{/if}\n{if $contributeMode eq \'direct\' AND !$is_pay_later AND $amount GT 0}\n\n===========================================================\n{ts}Credit Card Information{/ts}\n\n===========================================================\n{$credit_card_type}\n{$credit_card_number}\n{ts}Expires{/ts}: {$credit_card_exp_date|truncate:7:\'\'|crmDate}\n{/if}\n\n{if $selectPremium }\n===========================================================\n{ts}Premium Information{/ts}\n\n===========================================================\n{$product_name}\n{if $option}\n{ts}Option{/ts}: {$option}\n{/if}\n{if $sku}\n{ts}SKU{/ts}: {$sku}\n{/if}\n{if $start_date}\n{ts}Start Date{/ts}: {$start_date|crmDate}\n{/if}\n{if $end_date}\n{ts}End Date{/ts}: {$end_date|crmDate}\n{/if}\n{if $contact_email OR $contact_phone}\n\n{ts}For information about this premium, contact:{/ts}\n\n{if $contact_email}\n  {$contact_email}\n{/if}\n{if $contact_phone}\n  {$contact_phone}\n{/if}\n{/if}\n{if $is_deductible AND $price}\n\n{ts 1=$price|crmMoney:$currency}The value of this premium is %1. This may affect the amount of the tax deduction you can claim. Consult your tax advisor for more information.{/ts}{/if}\n{/if}\n\n{if $customPre}\n===========================================================\n{$customPre_grouptitle}\n\n===========================================================\n{foreach from=$customPre item=customValue key=customName}\n{if ( $trackingFields and ! in_array( $customName, $trackingFields ) ) or ! $trackingFields}\n {$customName}: {$customValue}\n{/if}\n{/foreach}\n{/if}\n\n\n{if $customPost}\n===========================================================\n{$customPost_grouptitle}\n\n===========================================================\n{foreach from=$customPost item=customValue key=customName}\n{if ( $trackingFields and ! in_array( $customName, $trackingFields ) ) or ! $trackingFields}\n {$customName}: {$customValue}\n{/if}\n{/foreach}\n{/if}\n' WHERE id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1]
[to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
      UPDATE civicrm_msg_template SET msg_text = '{assign var=\"greeting\" value=\"{contact.email_greeting}\"}{if $greeting}{$greeting},{/if}\n{if $receipt_text}\n{$receipt_text}\n{/if}\n{if $is_pay_later}\n\n===========================================================\n{$pay_later_receipt}\n===========================================================\n{else}\n\n{ts}Please print this receipt for your records.{/ts}\n{/if}\n\n{if $amount}\n===========================================================\n{ts}Contribution Information{/ts}\n\n===========================================================\n{if $lineItem and $priceSetID and !$is_quick_config}\n{foreach from=$lineItem item=value key=priceset}\n---------------------------------------------------------\n{capture assign=ts_item}{ts}Item{/ts}{/capture}\n{capture assign=ts_qty}{ts}Qty{/ts}{/capture}\n{capture assign=ts_each}{ts}Each{/ts}{/capture}\n{if $dataArray}\n{capture assign=ts_subtotal}{ts}Subtotal{/ts}{/capture}\n{capture assign=ts_taxRate}{ts}Tax Rate{/ts}{/capture}\n{capture assign=ts_taxAmount}{ts}Tax Amount{/ts}{/capture}\n{/if}\n{capture assign=ts_total}{ts}Total{/ts}{/capture}\n{$ts_item|string_format:\"%-30s\"} {$ts_qty|string_format:\"%5s\"} {$ts_each|string_format:\"%10s\"} {if $dataArray} {$ts_subtotal|string_format:\"%10s\"} {$ts_taxRate} {$ts_taxAmount|string_format:\"%10s\"} {/if} {$ts_total|string_format:\"%10s\"}\n----------------------------------------------------------\n{foreach from=$value item=line}\n{capture assign=ts_item}{if $line.html_type eq \'Text\'}{$line.label}{else}{$line.field_ti

```

# Answer
From the error, it seems there are no templates which needs an update by civi on your site. To proceed you can try commenting this part from `CRM/Upgrade/Incremental/MessageTemplates.php` file.

I feel there should be an extra if condition added around this line to only execute the query if `$templatesToUpdate` is not an empty array value.
> 1 votes

---
Tags: upgrade
---


# Question
Title: Detail instructions on manually updating message templates (5.9.0 to 5.10.4)
We just updated our CiviCRM (Wordpress) from 5.9.0 to 5.10.4. Prior to the associated database upgrade, I got this message about having to upgrade our customized message templates. I screen copied the text but don't have the link to the "detailed instructions."

Any idea on where I would find those instructions?

# Answer
> 0 votes
It links to an old wiki page that redirects to https://docs.civicrm.org/user/en/latest/email/message-templates

Note it doesn't tell you what your customizations were before. You would need to know that separately or look at a backup.

# Answer
> 0 votes
Basically you need to figure out what changes you made to the original template, and then apply those to the updated template. A tool like Meld is useful to look at the differences and copy them. If you plan on changing these in the future, keeping a record of your changes in git also helps.

---
Tags: upgrade, message-templates
---


# Question
Title: How to overwrite template file using php?
I want to override predefined template file in my extension.How to do that using php?

# Answer
> 1 votes
Hardcoding cusomised templates into extensions is in breach of extension development standards and can break on upgrades. Prevention of errors is an important priority to most developers to take pride in their craft and care about their clients.

The answer in your extension is to include the modified file in the path form template/CRM/path/to/template.tpl.php - but please find a better way. Civi users deserve great products that they get from other services, not this.

# Answer
> 0 votes
You can use hook\_civicrm\_alterTemplateFile to select a different template file - but beware of the problems of overriding templates.

---
Tags: extension
---


# Question
Title: Can I upgrade a drupal 7 + civi site to drupal 8?
There is maybe a simpler path but this worked for me. Documenting this here since I don't know where else to put it.

# Answer
> 4 votes
**UPDATE**: Note this is now slightly out of date but the gist is correct, so follow the spirit if not the exact details. In particular if I was doing it again now there is a non-roundearth way to also install D8+civi, e.g. https://civicrm.stackexchange.com/a/35608/181 (official docs are in the works), and that method will set different path settings in civicrm.settings.php for you than described below, e.g.

```
$civicrm_paths['civicrm.files']['url'] = 'http://example.com/sites/default/files/civicrm';
$civicrm_paths['civicrm.files']['path'] = '/full/filesystem/path/to/sites/default/files/civicrm';

```

---

1. Start creating a new D8+civi site by using the installer at https://gitlab.com/roundearth/drupal-civicrm-project.
2. When doing the D8 install part, just install a minimal site and don't further configure it - just what's needed to get D8 installed.
3. Follow the standard drupal instructions to upgrade. https://www.drupal.org/docs/8/upgrade/upgrading-from-drupal-6-or-7-to-drupal-8. The short version is enable the Migrate UI module on the new site and then go to /upgrade.
4. Do not enable civicrm yet.
5. From your old site, export the civicrm\_xxx tables.
6. There is probably a better way to do this step but it worked for me to manually update any urls and paths in the export file (taking care with php-serialized strings) and then import the sql file into the new database. Note that any references to sites/all/modules/civicrm are now under vendor/civicrm/civicrm-core. URLs for sites/default/files/... can stay as is, but note that filepaths will now be web/sites/default/files. Also userFrameworkResourceURL is now libraries/civicrm.
7. Copy civicrm.settings.php to the new site under web/sites/default. Edit the file: `Change CIVICRM_UF from "Drupal" to "Drupal8".
Change the mysql connection strings.
Change paths and urls as above. Again note civicrm_root is now under vendor/civicrm/civicrm-core, and you need to put "web/" in front of filepaths to "sites/XXX".`
8. Enable the civicrm module.
9. Clear caches at admin/config/development/performance and also the civicrm caches at civicrm/admin/setting/updateConfigBackend?reset=1. I also ran /civicrm/menu/rebuild?reset=1 but I'm not sure it's necessary here.
10. The UI will look all messed up because it was a minimal drupal install but you can change the theme and set things as you normally would.
11. You may also need to set these settings in civicrm.settings.php: `$civicrm_setting['URL Preferences']['userFrameworkResourceURL'] = CIVICRM_UF_BASEURL . '/libraries/civicrm/';
$civicrm_paths['civicrm.root']['url'] = CIVICRM_UF_BASEURL . '/libraries/civicrm/';
$civicrm_setting['domain']['userFrameworkResourceURL'] = CIVICRM_UF_BASEURL . '/libraries/civicrm/';
$civicrm_paths['cms.root']['path'] = '/full/path/to/web'; // the "web" folder at the root of the install`

---
Tags: upgrade, drupal-8
---


# Question
Title: How to identify case activities in find activities or advanced search
In Activity Detail Reports I can set a filter to include or exclude case activities.

Is there a way of filtering activity searches in a similar fashion?

# Answer
> 1 votes
The custom search called Activity Search shouldn't be too hard to update. (See Find - Custom Searches - Activity Search. The file is CRM/Contact/Form/Search/Custom/ActivitySearch.php.) It already includes some partial case-awareness.

In fact, if you run it and then select all records, and then from the actions dropdown choose export contacts (yes, contacts), it will then export the activity records and there'll be an unnamed column that has the case id. There's 3 unnamed columns - the last one is case id, and it's blank if it's not a case activity.

---
Tags: civicase, activities, search
---


# Question
Title: Can you restrict Shoreditch from injecting CSS on Public pages with CiviCRM content?
As the title says - can you restrict Shoreditch from injecting CSS on Public pages with CiviCRM content?

For example - do a fresh install of Drupal, CiviCRM and Shoreditch - choose any public theme - create any public facing CiviCRM page (event, contribution, etc) - go to that public page - shoreditch will inject much of its CSS on the page. Fonts, body backgrounds, etc.

Whats the easiest way to restrict shoreditch from injecting itself on public pages?

# Answer
> 2 votes
This is a core issue (not specific to Shoreditch) and there is an issue with some discussion related to it here:

To tag on to your specific point about Shoreditch's CSS, I have found this to be problematic for the display reasons you mention (conflicts with CSS from the front end theme) but also for performance reasons. On one site I found Google Page Speed Insights showed a very low mobile score (2%) with Shoreditch enabled, and it jumped to a more normal level (87%) as soon as I disabled the Shoreditch CSS.

---
Tags: drupal, css, theme, shoreditch
---


# Question
Title: Mosaico: Where can I find the source code of my mailing?
we are using CiviCRM 5.4.0 and just got the Mosaico extension started to send our newsletter. Now I have run into the following problem: In the past I would take the source code of the mailing, create a .html file out of it and upload this to our homepage to back up a link people could click on - so for example it would say "January 2019 Newsletter", you could click on it and would be provided with an online version of the newsletter we had also send as an email.

Now, I am using Mosaico and don't know how to go about this situation anymore...I can not find the source code of the mailing to create the .html to then incorporate into our homepage...

**So my questions would be - how can I obtain either the source code or a clickable link I can include into our homepage?**

Thank you for any answers!

# Answer
I believe you can set the "Mailing Visibility" option (accessed by clicking the "Advanced Mailing Options" button on the 3. Options page of the Mosaico workflow) to "Public Pages" which will create a public HTML page that visitors can view, accessed by a tokenized link in the email itself. You should be able to get the HTML from this page and add it to your homepage... or just add a link to that public page.

> **Mailing Visibility**
>
> This option controls who can view the contents of this email on your website. The link can be included by inserting the "Mailing permalink" token (place it in a link when composing in HTML mode).
>
> The options for mailing visibility are:
>
> "Public Pages" will make the content of this mailing viewable everyone who has the permission "view public CiviMail content". Note that even people who did not receive the mailing would be able to find it via search engines. "User and User Admin Only" means that only users that received the mailing or administrators can view this email on your website; users must be logged in to view the message.
> 4 votes

# Answer
Do you get a copy of the mailing? As a workaround you can view the source code of the email in your email program (Outlook, webmail, etc) and use that.
> 0 votes

---
Tags: mosaico
---


# Question
Title: Help needed: Need to run reports to find information on number of members during a specific year
Good morning,

I'm a program assistant at the NH Public Health Association, and we need to determine the following data:

1. Number of current (as of a certain date within a year; e.g., February 26, 2019) members of NHPHA
2. Number of members at any time during a specific year (e.g., 2019)

We have tried to use the Reports function to determine these data, without success. Can anyone explain how to do this? Please let us know if there are specific filters we should be using. We appreciate any help anyone could give us. Thanks in advance!

Sincerely, Jess

# Answer
UPDATE: also try the nz.co.fuzion.historicmembershipdata extension

Try the Membership Churn extension otherwise

1/ I think you can get via

Adv Search

Set Start Date to \< Feb 26 2019

Set End Date to \> Feb 26 2019

2/ might be a bit trickier but I suggest using Search Builder and something like

Set Start Date \> Jan 1 2019 to Dec 31 2019

OR

Set End Date to \> Jan 1 2019 to Dec 31 2019

EDIT: you may need to also exclude Memberships that are 'old' ones which you may be able to do via Memb Status
> 1 votes

---
Tags: civimember
---


title: Email weekly reminder to group

text:
# Question
Title: Email weekly reminder to group
Is there a way to schedule the same email to a group each week? It's a reminder for a meeting we run.

Steve

# Answer
Could be done with scheduled reminders Adminster -\> Communications -\> Scheduled Reminders

Entity -\> Event Name, choose first meeting name.

Enable repetition Repeat every 7 days until 10 years after event start date

(or something like that).
> 4 votes

# Answer
The scheduledcommunication extension might help.

Once installed, you have to add a new Scheduled Reminder that uses the API call `Email2Each.Sendemails`, with parameters:

```
group=Name of the group
message_template=name_of_the_template

```

The group can also be a smartgroup (so people could update their preferences to change a value of a custom field to stop receiving the messages, for example).
> 1 votes

---
Tags: email, scheduled-reminders
---


# Question
Title: Mosaico 2.0, Drupal 7.64 - "Sorry an error occurred and your information was not saved"
Installed Mosaico 2.0-beta4 on Drupal 7.64 and CiviCRM 5.7.2. I followed all installation instructions and installed Shoreditch then Flexmailer then Mosaico.

I'm getting an error - when I try and edit a Mosaico template and drag a block in, it gives the error message of "Sorry an error occurred and your information was not saved".

This happens whether I upload an image into a block, or just drag an empty text box and add dummy text, or anything in between.

This also happens regardless of which base template I'm trying to edit.

If I just try and create a new mail using the base templates, I can't preview the email as HTML or send out a test email (again, regardless of how much or how little I customise the base template) and get the same error message.

I've checked error logs and haven't found anything. All modules and core up to date, any ideas on what I can do to fix this?

Thanks

EDIT: Some of the messages from Browser console. Not sure which of them are relevant:

```
The resource from “[default]/civicrm/css/?r=f1Bvs” was blocked due to MIME type mismatch (X-Content-Type-Options: nosniff).

```

.

```
Error while detaching the browsing context target front: Connection closed, pending request to server1.conn0.parentProcessTarget1, type detach failed

```

^ That one then has a "Request Stack" of around 20 lines.

```
Content Security Policy: The page’s settings blocked the loading of a resource at https://raw.githubusercontent.com/favicon.ico (“default-src”).

```

.

```
Loading mixed (insecure) display content “http://mosaico.io/srv/f-default/img?method=placeholder&params=166%2C160” on a secure page[Learn More] jquery.min.js:4:19318

unreachable code after return statement

```

EDIT:

Checked the error log in cpanel, this is an error message we're getting at the same time:

```
2019-02-27 12:11:55.672900 [INFO] [203.166.241.141:58741-Q:2699717862144599633-5] File not found [/(homedirectory)/public_html/403.shtml]

```

# Answer
> 1 votes
Have you tried asking your web host to see if you're throwing out a server based error, like a ModSecurity issue? I regularly run into those with CiviCRM when I'm doing emails and have to get the server techs to whitelist a process for me so that it doesn't give me an error. Other than a piece of bad formed code (like when someone has copied something from Word or whatever), it's always been this kind of issue for me.

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


# Question
Title: DB Error: unknown error when accessing any report menu after upgrade to 5.10.0
I have just updated CiviCRM under Joomla! from 4.7.6 to 5.10.0. The upgrade went well, with no issues. I have following the instructions on the page "Upgrading CiviCRM for Joomla". I can use CiviCRM except whenever i access any report menu i get :

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

Items on here say to look in the ConfigAndLog file which i think for Joomla should be at administrator/components/com\_civicrm/civicrm but i do not see it there.

Anyone have any suggestions on how this can be resolved?

thanks Dave

# Answer
On the Finish page of the CiviCRM 5.10.1 and 5.10.4 component installation, there is a little link called "Utility for Updating the Database structure" or something similar. Up til running that little link, we received the "DB Error: Field missing" error when running some reports.
> 2 votes

---
Tags: upgrade
---


title: Webform CiviCRM Case Tokens

text:
# Question
Title: Webform CiviCRM Case Tokens
Has anybody had success in using the CiviCRM Webform Case autocomplete tokens? We are using the cases for case management and would like to give users an option to update an existing case via a custom URL in a webform. It works for one user but does not work for another. Is there some permissions missing? Currently using ?case1=1 for Case ID one to prepopulate in the form. This works. But once I go to case1=4 or 5 it does not work.

# Answer
> 1 votes
> But once I go to case1=4 or 5 it does not work

The case loaded on the webform is specified by passing the id in the form of `case{fieldset}=<case_id>`.

For eg loading case id = 1 on fieldset 2 would be done by adding case2=1 in the URL.

Also make sure the contact loaded on the same fieldset is a client of the case. For eg - if contact A is attached to caseid = 2. Add this in the URL to load the case on the form =

```
webform_url?cid1=<contact_id>&case1=<case_id>

```

Similarly, if case is loaded on fieldset 2, the url would be -

```
webform_url?cid2=<contact_id>&case2=<case_id>

```

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


# Question
Title: Click to go to contact record from report
When i am viewing contacts in a civireport if i click on the persons contact name i am taken to the "view contact detail report" which is basically the Constituent Report (Detail). Is there a way to go the the persons contact record directly from the report and not have to be referred to the Constituent Report (Detail)?

this is the url it takes me to when i click on someone from the report that refers me to the constituent detail report: /civicrm/report/instance/2?reset=1&force=1&id\_op=eq&id\_value=6732

# Answer
This extension should help with your query –

https://civicrm.org/extensions/no-drill-down
> 2 votes

---
Tags: contacts, civireport
---


# Question
Title: Drupal webform billing fails: State/Province field is required
On Membership payment, using Drupal Webform, someone living in Singapore (for example) can not pay, as State/Province is required and Singapore has no State/Province defined. Gibraltar has the same.

This seems to be new. Do others have the same problem ? Is there a way around it ?

NB. State/province, where applicable, should indeed be required for Credit Card transactions. Just not for Singapore, Gibraltar, etc.

EDIT at the request of @Karing-semper-it , some screenshots

There is no difference in the gui before submitting:

However, after submitting, webform complains about a missing state/province, and civicrm does not

# Answer
> 4 votes
Singapore does not have States/Provinces but there are Regions. But these have not made the list. You can add the regions to the civicrm\_state\_province table via the API Explorer

In Civi go to Support \> Developer \> API Explorer

In Entity select StateProvince In Action select Create For Parameters: State = Central Region State Abbreviation = CR Country = Singapore

Scroll down and you will see an Execute button. Click that and confirm Write to Database

You can repeat this for each Region or you can do as Karin suggested and just create one state "Singapore"

# Answer
> 1 votes
I did this for Joomla. I added the district information in SQL Provincial Information for Turkey. But the county information is still not selected when the province.

enter link description here

---
Tags: webform-civicrm, payment, location
---


title: Syntax error from CiviMail

text:
# Question
Title: Syntax error from CiviMail
I have set up outbound mail and individual mails go fine. But I can't get the CiviMail working to a group. All the members bounce with a Syntax error. I am running CiviCRM 4.7.22 on WordPress 4.8.2. I've included addresses to recipients on different servers so don't think its a remote problem. The Syntax bounce is supposed to indicate an SMTP transaction error, but things work fine when its just a single email to one of the same contacts.

I have seen other SE posts that might relate, but they are with different configurations CiviMail not sending mails in 4.7.6 / Drupal 7 "DB Error: syntax error" and help on wordpress for send mailling

# Answer
As noted above the problem went away when I tried again some months later so its resolved but no idea why.
> 0 votes

# Answer
This is exactly like the problem I am having. I worked with Siteground support and they couldn't figure it out. They suggested to throttle the outbound to a very small number, which would work for small batches, but ultimately would fail. At the suggested throttle it would take 8 hours to send out an email to my 500 members.
> 1 votes

# Answer
civimail (for some reason) would not accept the syntax on newsletter bulk mail so I reset my outgoing email methods. I am using drupal 7.64 and civicrm 7.x-5.10.4 on siteground. I was sending LESS THAN 500 emails.

I switched my civicrm outbound email send option from -mail- to -smtp- with settings as follows (1) smtp server: ssl://smtp.gmail.com (2) port: 465 (3) authentication: yes (4) email: my gmail email address (5) password: my gmail password.

Then I had to go to gmail settings and add my siteground account as an alternate sender. In gmail account : (1) click on -settings- "cog"; (2) select -accounts and settings- tab; (3) select -check mail from other accounts- then (4) add your siteground email details: (a) email address; (b) password; (c) pop server (mail.your\_site.com); (d) port 110

worked for me to send unblocked bulk mails with free gmail service:-)
> 1 votes

# Answer
I have worked round this by installing an external mail provider SparkPost as suggested by a similar post Can't send or test bulk mail, but test mail succeeds in System Settings / Outbound email / SMTP . I don't see this as a full solution as it adds a lot of complexity. I don't think that my hosting provided (SiteGround) is particularly blocking it so still think that it must be some configuration error. Will come back to this when I have more time.
> 0 votes

# Answer
VERP is only applied to CiviMail emails and might lead to such behaviour.

Try disabling VERP if you are experiencing delivery issues with CiviMail but "regular" / transactional CiviCRM mails work OK.

> Administer \> CiviMail \> CiviMail Component Settings \> Track replies using VERP in Reply-To
> 0 votes

# Answer
I experienced this problem too, and it was a mismatch of PHP versions on command-line vs. web server: Mailing Bounces 100% with "authentication failure" or "failed to set sender"
> 0 votes

---
Tags: civimail
---


title: New > Individual Mandatory Phone

text:
# Question
Title: New > Individual Mandatory Phone
When creating an individual record, how do I make the phone number field a mandatory field. I mean, I don't want a record before the phone number is written.

`administrator/?option=com_civicrm&task=civicrm/contact/add&ct=Individual&reset=1`

# Answer
> 2 votes
I assume you are wanting to require a phone number when some contributes, becomes a member, or registers for an event.

Go to Admin \> Custom Data \> Profiles

Look for the Profile that you will be using in your Event/Contribution pages and to the right click Fields

You can edit the Phone field and make it Required

---
Tags: joomla, civicrm-home, phone
---


# Question
Title: Add confirmation notification after submit in civicrm form in Wordpress
this is a simple implementation of a CiviCRM form in Wordpress which I want to show success notification after submit.

```
<form  action="https://crm.mfc.org.pl/civicrm/profile/create?gid=25&reset=1" method="post" name="form" id="form33">
<input type="text" id="name" placeholder="First Name" required="required" /></div>
<input type="email" id="email" placeholder="Email" required="required" title="Please Provide A Valid Email Address !" />
<input value="SUBSCRIBE" type="submit"/>
</form>

```

For example after submit popup to says "Thank you for subscribing". Or the form to disappear and this text to stay on the form place. Now after submit nothing happens, just the page reload. Do you know how can I do it?

Thank you in advance!

# Answer
Welcome to StackExchange! It might help with answers if you clarified the purpose of the form. What will you then do with the collected Name and email?

It looks like the way you are attempting will require JS so maybe we should look at other options.

1. Depending on what you need the information for, you could use a Civi Membership or Event form, these would give you built in responses or redirection. You can create a profile that just uses Name and email and set the event/membership to No Fee.
2. Check out Caldera Forms or Gravity Forms. Both have CiviCRM integrations. I have used them both in the past. And both will give you the ability to create your own response or redirect.
> 1 votes

---
Tags: wordpress
---


title: sql username and password

text:
# Question
Title: sql username and password
I have installed civicrm bitnami stack on google cloud.

I need to connect sql database with tableau for data visualisation. How can i get the server, port, username and password for this connection.

# Answer
* Connect to Bitnami using SSH.
* Find `civicrm.settings.php` with `find . -name civicrm.settings.php`. You can probably speed things along by first navigating to `apps/civicrm/htdocs`.
* In the file, look for the line that defines `CIVICRM_DSN`, which can be interpreted as follows:

```
    define('CIVICRM_DSN', 'mysql://user:password@host/database?new_link=true');

```

According to the Bitnami CiviCRM docs the port is 3006. I assume that's correct and not a typo for "3306".
> 1 votes

---
Tags: civicrm.settings.php, mysql-5.7
---


# Question
Title: "This site can't be reached" error message when trying to
I got the above error message when trying to print mailing labels. I have here a screen cap of the entire message and I'd appreciate it if you could please help me resolve this issue.

Thanks

# Answer
This error is unfortunately not enough information to go on. I would suggest checking your CiviCRM log and perhaps your web server's error log as well.

If I had to hazard a guess though - it's possible that some element of your Joomla theme is adding text (perhaps a header or footer) to every page - and this is causing the PDF to be incorrectly formatted. If that's the case, you should see this same error when you print any PDF.
> 1 votes

---
Tags: civimail
---


# Question
Title: Where did the standard HTML window go on mailings with mosiaco installed?
I'm testing an upgrade to 5.9.1 and have installed mosaico (versions listed below) and have a few issues with mailings.

Try to create a new mailing. I don't see the standard HTML window with the wysiwyg editor. I see one for Design which has the mosiaco templates in it.

If I select a standard (non-mosaico) template, I don't have anyway to see it nor edit it.

If I select a mosaico template, I get that editor and can edit it no problem.

Another issue that may be a clue, is if I hit preview HTML either from the main screen or from within the mosaico editor, nothing happens. I get a db error with FK constraint on civicrm\_mailing\_trackable\_url. The sql is doing an insert and doesn't have the mail id. It is only trying to insert url.

Clearly this works else where so I must have something basic wrong.

Versions:

civicerm 5.9.1

mosaico 2.0-beta4.1528762072

FlexMailer 1.0-beta1

Shoreditch 0.1-alpha30 I could never get this work. It says it's optional so I skipped it. In playing with this, the not working could be related. It might work, in the mailing screens I wasn't getting the options for regular template and it looked like things weren't necessarily in the right place.

# Answer
If you want to open the traditional UI for mailings, you can find this under `Mailings -> New Mailing(Traditional)` nagivation menu.

If you don't see that menu item, try updating mosaico ext to latest OR simply visit -

```
http://site-name/civicrm/a/#/mailing/new/traditional

```
> 3 votes

# Answer
Am I thinking you want to be able to tweak the HTML of a Mosaico mailing? I don't think there is a direct way of doing that. My workaround was to send the test mailing and then cut and paste the HTML into a traditional mailing in CiviCRM.
> 1 votes

---
Tags: mosaico
---


title: CiviMobile not connecting in Joomla

text:
# Question
Title: CiviMobile not connecting in Joomla
Joomla 3.9.3 CiviCRM 5.10.3 CiviMobile 1.3.1 App on iPhone http://crm.mydomain.xx admin account

App always comes up with "unknow error" - demo works.

THX4HELP Frank

# Answer
> 0 votes
Make sure you are installing this extension

https://civicrm.org/extensions/civimobileapi/

---
Tags: joomla, mobile
---


title: multiple bulk email update 5.10.3

text:
# Question
Title: multiple bulk email update 5.10.3
We have updated our CiviCRM to the 5.10.3 but now we have trouble with bulk email.

The checkbox buttons behave like a radio button. So we can not have more the one bulk email address (not really useful)

For old contact the data are correct but we can not change it.

No one has this trouble? No one has an idea to resolve it?

Thanks

Nicolas

# Answer
You must enable the ‘Enable multiple bulk email address for a contact’ option from CiviMail component settings.

Administer \> CiviMail \> CiviMail component settings

/civicrm/admin/setting/preferences/mailing?reset=1
> 2 votes

---
Tags: contacts, email, xdebug
---


# Question
Title: Email greeting settings for contacts without names
Contacts have an 'email greeting' field which can be customised on a per-contact basis, and takes its default from a pattern like `Dear {contact.first_name}` docs link

Is it possible to enter a pattern that will show "Supporter" if there is no first name?

# Answer
> 3 votes
You can use Smarty conditionals in greetings in CiviCRM 4.5+.

So the correct pattern would be:

```
{capture assign=first_name}{contact.first_name}{/capture}Dear {$first_name|default:Supporter},

```

# Answer
> 1 votes
I don't think you can do just by configuring that pattern, but you could write your own token.

---
Tags: contacts
---


title: Can't update smart group counts

text:
# Question
Title: Can't update smart group counts
My smart groups show unknown counts. Debugging shows this when I click "update smart group counts".

Wordpress 5.0.3, Civi 5.9.0, PHP 7.2.14

```
#0 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(190): CRM_Core_Error::backtrace()
#1 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#3 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#4 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", "DB_Error", TRUE)
#5 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", "1292 ** Truncated incorrect DOUBLE value: ''")
#7 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#9 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2415): DB_common->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#10 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#11 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(438): DB_DataObject->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#12 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1413): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", TRUE)
#13 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(597): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#14 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3106): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
#15 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3037): CRM_Contact_BAO_Query->addGroupContactCache((Array:1), NULL, "contact_a", "IN")
#16 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(1822): CRM_Contact_BAO_Query->group((Array:5))
#17 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(2044): CRM_Contact_BAO_Query->whereClauseSingle((Array:5), NULL)
#18 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(531): CRM_Contact_BAO_Query->whereClause(NULL)
#19 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(491): CRM_Contact_BAO_Query->initialize(NULL)
#20 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4478): CRM_Contact_BAO_Query->__construct((Array:1), (Array:1), (Array:21), TRUE, FALSE, 1, TRUE, TRUE, FALSE, NULL, "AND", NULL, "1")
#21 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(226): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:1), NULL, NULL, 0, 0, FALSE)
#22 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(208): CRM_Contact_BAO_GroupContactCache::add((Array:3))
#23 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Group/Page/Group.php(129): CRM_Contact_BAO_GroupContactCache::loadAll()
#24 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(160): CRM_Group_Page_Group->browse(NULL, NULL)
#25 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(309): CRM_Core_Page_Basic->run((Array:2), NULL)
#26 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
#27 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:2))
#28 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm.php(1240): CRM_Core_Invoke::invoke((Array:2))

```

# Answer
> 4 votes
**Quick Note** The issue/solution I describe below only addresses broken Groups/Searches using the "on hold" email field. It's possible the Group/Search is broken through another field/value and the below info will only help specific cases.

**Another Quick Note** I solved a different Smart Group/Saved Search issue by switching "Empty" operators to "Null" operators in the Search criteria. StackOverflow post here: Search Builder Smart Group with Latitute, Street Address criteria 1292 Truncated DB Error

We are having a similar issue and I have traced the error down to this WHERE condition: `civicrm_email.on_hold IN ("")`. I have also opened up an issue with CiviCRM: https://lab.civicrm.org/dev/core/issues/731

~~I do not have a fix yet, and would have left this as a comment and not an answer, but alas I do not have enough reputation yet.~~

~~EDIT: Per Petednz's help and some investigation, it appears the process for generating these Smart Group queries for contact record was overhauled somewhere between 5.9 and 5.10 (specifically the CRM/Contact/BAO/Query.php went from 4000+ lines to ~100, where the "on\_hold" condition was hard-coded in). I have not confirmed an upgrade to 5.10.2 will fix it, however I am operating on the assumption it will and can confirm it once I have done so.~~

EDIT 2: Sterndata is right, I was looking at the wrong file. The existing offending code still exists in 5.10.2. A temporary workaround I have got working is to find this chunk of code CRM\Contact\BAO\Query.php (lines 1637-1642):

```
  elseif ($id == 'email_on_hold') {
    if ($onHoldValue = CRM_Utils_Array::value('email_on_hold', $formValues)) {
      $onHoldValue = (array) $onHoldValue;
      $params[] = array('on_hold', 'IN', $onHoldValue, 0, 0);
    }
  }

```

And then I add one line to check for and re-write the bogus value (the isset line):

```
  elseif ($id == 'email_on_hold') {
    if ($onHoldValue = CRM_Utils_Array::value('email_on_hold', $formValues)) {
      $onHoldValue = (array) $onHoldValue;
      if(isset($onHoldValue['on_hold']) && !$onHoldValue['on_hold']) $onHoldValue = array(0);
      $params[] = array('on_hold', 'IN', $onHoldValue, 0, 0);
    }
  }

```

As far as I can tell, this value is hard-coded into the form\_values column on the civicrm\_saved\_search table in a serialized array like so:

```
s:13:"email_on_hold";a:1:{s:7:"on_hold";s:0:"";}

```

The following code commit in 5.9.0 changed how the field works, by I believe, changing it to allow both a checkbox type OR a select list, depending on the values of what looks like a "Bulk Email" settings option: https://github.com/civicrm/civicrm-core/pull/12942/files#diff-1be8d2488755f023664ac3652a8d5de2R58

It doesn't appear there was a process for updating these values in the database to work with the new fields, but the upgrade did include a message stating that Smart Groups might be broken, and instructs to re-create the broken searches/smart groups.

**Hopefully Last Edit** I wrote some Drupal 7 specific PHP code (sorry sterndata I know you're on Wordpress) to find these saved searched rows in the table with the bad email\_on\_hold field data and update the data to work. I have tested it on my 5.9.1 site and it has worked fantastically for us. I ran it once and hopefully should never have to think about this again!

However, per best practice please modify the code to suit your needs and then run it on one record first, preferably in a test environment!

```
  $query = db_select('civicrm_saved_search', 'ss');
  $query->fields('ss');
  $query->condition('ss.form_values','%' . db_like('email_on_hold') . '%', 'LIKE');
  // this condition might be too specific
  // $bad_data_string = '{s:7:"on_hold";s:0:"";';
  // $query->condition('ss.form_values','%' . db_like($bad_data_string) . '%', 'LIKE');
  // uncomment to only alter one specific record at a time
  // $query->condition('ss.id', 47);
  // $query->range(0,1);
  $result = $query->execute()->fetchAllAssoc('id');
  if(!empty($result)) {
    foreach($result as $record_id => $record) {
      $form_values = unserialize($record->form_values);
      if(isset($form_values['email_on_hold']) && is_array($form_values['email_on_hold'])) {
        $email_on_hold = $form_values['email_on_hold'];
        if(isset($email_on_hold['on_hold']) && !$email_on_hold['on_hold']) $email_on_hold = array(0);
      }
      // update only if different
      if($email_on_hold != $form_values['email_on_hold']) {
        $form_values['email_on_hold'] = $email_on_hold;
        $new_form_values = serialize($form_values);
        $update_on_hold = db_update('civicrm_saved_search');
        $update_on_hold->fields(array(
          'form_values' => $new_form_values,
        ));
        $update_on_hold->condition('id', $record_id);
        $update_on_hold->execute();
      }
    }
  }

```

Lastly, if you do run these fixes on the database, the one line I had added to the Query.php file is no longer necessary.

# Answer
> 1 votes
The issue I was having, which I think is related to this same update, was that I couldn't go to a Smart Group and click "Contacts" and actually get the result. It gave an error:

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

This also didn't provide me with the usual "Edit Smart Group Criteria" link at the top of the page because it was a fatal error page. I did track down a way to do that, and update the smart group despite not actually changing anything, making it functional again without having to do anything directly in the database:

* Click "Settings" beside the Smart Group
* In the popup at the bottom you'll find "Edit Smart Group Criteria" and clicking on it will take you to the functional Smart Group page that lists the contacts/results.
* Select "All X results" and in the actions menu, choose "Group - Update Smart Group" (even though you haven't changed anything)
* Select "Update Smart Group" on the verification screen

Now when you go to the Groups page, the Smart Groups will work as expected.

---
Tags: error, smartgroups
---


# Question
Title: Membership Fixed period, early bird before membership year start
i am doing fixed period. membership start date is May 1, 2019 membership end date is April 30, 2020 early bird starts April 1st I have set the Fixed Period Start Day to be May 01 I have set the Fixed Period Rollover Day to be April 01

Have i got this right?

# Answer
rollover means if i join on April 2nd i get ~ 13 months in the first year. so if that is what you mean by early bird then yes you should be sorted
> 1 votes

---
Tags: civimember
---


title: AJAX error when viewing contact groups

text:
# Question
Title: AJAX error when viewing contact groups
Version 4.7.10 on Wordpress 4.6

**Error when selecting Contacts-\>Manage Groups:** DataTables warning: table id=DataTables\_Table\_0 - Ajax error. For more information about this error, please see http://datatables.net/tn/7

This is a fresh install. The error makes contact group unusable which makes the whole thing unusable.

**Comment from my system admin:** I've updated the nginx config without any success and also tried updating the PHP query string size limit, again without any success. I would say the next step would be to contact the civicrm maintainers and say that the query string is causing problems. Usually GET query parameters shouldn't exceed more than 255 characters, while the one above is closer to 1300. They should probably be using a POST request instead of GET.

**UPDATE**

Following the instructions from the datatables link above, I compared the demo site to my site.

-The suspect request URL is identical.

-Data transferred / size: demo site 0.05 kb. my site 0.19 kb. This is puzzling since the URL is identical!

-On the demo site request type is json. On my site, it says html.

-Both are GET requests. I also looked at Civi 4.6.21 on WP 4.5.3 and this same request is a POST request. So I guess they changed it to GET.

-Demo site runs Wordpress 4.5.3 and Civi 4.7.11. my site is Wordpress 4.6 and Civi 4.7.10

# Answer
> 1 votes
I have tested the above issue against WP 4.6.1 and CiviCRM 4.7.11. I am running Apache 2.4, php 5.6 and MySQL 5.6. The OS is CentOS 6.

I cannot reproduce this error nor the one mentioned Ajax Error when trying to view relationships tab

I commented in https://issues.civicrm.org/jira/browse/CRM-19331 about this as well.

I would suspect the issue here is due to either a plugin conflict or something specific to the Hosting Configuration. I am interested if these issues are specific to a host or a specific configuration.

I'd disable all plugins (except CiviCRM) and see if you can trace errors in you apache and php logs. I would also revert to a default theme (CiviCRM demo sites are tested with TwentyThirteen) I'd also look at the CiviCRM logs. The CiviCRM logins should be in the `/civicrm/ConfigAndLog` directory that is stored in the WP upload directory in 4.7

I'd be interested if these issues are specific to a host or a specific configuration.

# Answer
> 0 votes
I did made a test against Wordpress 4.5.3 and Civicrm 4.7.10 - Fresh Copy

I didn't get any error message as you have reported - may be you should try the same against demo to confirm

I guess this helps !!!

# Answer
> 0 votes
I had a similar issue on my CiviCRM fresh installation (CiviCRM 5.10.4) -\> IIS10 webserver.

Error used to happen on both Joomla (3.9.3) and WordPress (5.0.1) versions.

The problem was that we see any relationship or activities table (DataTables warning: table id=DataTables\_Table\_0)

So, what I did to fix this problem is to add the following values in the app web.config. Do not forget any of these or it won't work.

```
<system.web>
    <httpRuntime maxQueryStringLength = "12288" maxUrlLength="12288" />
</system.web>
<system.webServer>
    <security>
        <requestFiltering allowDoubleEscaping="false">
            <requestLimits maxAllowedContentLength="3000000000" maxUrl="4000000" maxQueryString="4000000" />
        </requestFiltering>
    </security>

    <httpErrors existingResponse="PassThrough"></httpErrors>
</system.webServer>

```

Hope this helps !

---
Tags: ajax
---


title: How to delete a membership report

text:
# Question
Title: How to delete a membership report
I have copied an existing report template for my own use with the intention to use it for my search options. I don't understand how I can name it as I wish. How to rename a report when copying? How to delete a copied report?

!\[Screenprint: \]

# Answer
When viewing the report, click on the Title and Format tab to rename it, from the actions drop down menu you have an option to delete –

Further documentation on reporting can be found here - https://docs.civicrm.org/user/en/latest/reporting/set-up/
> 1 votes

---
Tags: civireport
---


title: Calculate Contact age in a Drupal view

text:
# Question
Title: Calculate Contact age in a Drupal view
I'm exposing civi contact data in a drupal view. I can expose date of birth, but not the contact age field.

Anyone know of a module/ combination of modules that will allow me to calculate age in years within the view?

# Answer
> 11 votes
In your view, pull in the CiviCRM birth date field. Under date format, select Time ago. This will present the D.O.B. field as 40 years 7 months

To remove years and months from the output you can ‘Rewrite results’ in the view and ‘Trim this field to a maximum length’ and set ‘Maximum length’ to 2 characters

# Answer
> 1 votes
You need to add views php module

Add the dob into a view. In this example called birth\_date

Add a global php field.

Then put this code into the output code box in the global php field

```
<?php
$ageTime = $row->birth_date;
$t = time();
$age = $t-$ageTime;
$year = 60 * 60 * 24 * 365;
$ageYears = $age / $year;
print floor($ageYears);
?>

```

---
Tags: drupal-views
---


# Question
Title: Identifying contact voting district from KML data or from state website
we use civicrm for a political org, and I'm trying to find out how to add voter district information to contacts in database.

The state doesn't have a public API from what I can tell. They have this website: http://whosmy.virginiageneralassembly.gov/, but getting the info out of this site would require some python scripting, and I don't know how to automate the export and reimport of the contact records.

There are KML files available from the census (https://www.census.gov/geo/maps-data/data/kml/kml\_sld.html) that provide the boundaries for the districts. I'm wondering if anyone has advice on modules or approaches to take a contact's geocode address and determine which district boundary it falls in.

Thanks,

# Answer
> 3 votes
Google’s Civic Information API returns information about federal, state, local governments, school districts, etc. It takes as input an address string (not a lat/long pair). An API key is needed, but it’s free to obtain and use. In what little experimenting I’ve done recently, average latency has been ~350 ms.

# Answer
> 1 votes
I can share some experience from such a project a few years back. It required a fair bit of custom code implemented for that site, but ultimately your task would boil down to something like:

* Identify contacts who you want to look up (eg, *contacts with a state of VA and who don't already have custom data in custom field "District"*).
* For each contact, ensure they meet minimum criteria (eg try geocoding the address first, do what you need to handle contacts whose address does not)
* Once you have lat/long for the contact, look them up in a service capable of returning a value for the district. Options here vary - we used Koordinates originally (accepts KML, lets you query against it), but there are other options and that project eventually switched to an SQL approach.
* Store the results of this lookup against the contact.

Other considerations -

* How fast do you need to look up all the voters, and how performant is the lookup method? Do you need to invest time up front in mapping addresses to districts, or will a web service like Koordinates save you dev time/expense and let you work through the number of voter contacts required?
* Does every contact's default address map to their voter registered address / are there cases where you have an address on file that doesn't map to their voting district? How do you handle these?
* Do you want to permit "custom" values, eg "Bob is special, don't overwrite the district he's associated with because he chairs a neighbouring district" etc.
* What happens when district boundaries change?

This isn't a built-in functionality and I'm not aware of a published extension.

---
Tags: contacts
---


# Question
Title: Membership Fixed period, wrong start date
i am doing fixed period. membership start date is May 1, 2019 membership end date is April 30, 2020. Early bird registration starts March 1, 2019. I have set the Fixed Period Start Day to be May 01, anticipating May 1, 2019 I have set the Fixed Period Rollover Day to be March 01, anticipating March 1, 2019. I anticipate membership end date to be April 30, 2020, which it is. I anticipate membership start date to be May 1, 2019 which it is not, it gives me May 1, 2018.

# Answer
Yes this is an aspect of / issue with Rollover - ie when someone signs up, civimember effectively gives them the start date of the membership cycle on the day they sign up, and then adds on the extra year. so yes seeing 'start date' of 1 May 2018 for someone signing up on 5 March 2019 fits with the civimember logic.

i don't say i necessarily like what it does but the logic is consistent but means that you may need to pay more attention to the Join Date than the Start Date.

That is, assuming everything else looked right, ie End Date was 30 April 2020
> 0 votes

---
Tags: civimember
---


title: Managing collection tins and buckets

text:
# Question
Title: Managing collection tins and buckets
I am relatively new to civi and have an install that we are using for a new project. We are using it to manage our support groups and volunteers initially. Starting with a small part of the tasks that we will want to use it for we are just using civicore but will use other bits as we use it for more workflows.

One of the taks we want to use it for is managing collection tins that we allocated to fundraisers. Wondering if there is some method that people are already using for this sort of thing. Thought of an additional contact type and relationships or using CiviCase but wondering what others do?

TIA

# Answer
> 1 votes
In Civi we have a Contact named Cash that we use for this. This is an Individual contact type, no need for an additional Contact Type.

We use Price Sets (Line Items) to record Designations so we just have one "Cash" donor. Even if you use Campaigns, you could still get by with just one "Cash" donor.

---
Tags: fundraising
---