Development issueshttps://lab.civicrm.org/groups/dev/-/issues2024-02-08T21:01:05Zhttps://lab.civicrm.org/dev/core/-/issues/4847Activity-based SearchKit results for 'restricted' users (via the Related Perm...2024-02-08T21:01:05ZpetednzActivity-based SearchKit results for 'restricted' users (via the Related Permissions Module) are no longer restricting results to only Activities of Contacts the user has access tohttps://chat.civicrm.org/civicrm/pl/wj4t3rrh7ir5uyz7t88c7fuj8o
We are using the "Related Permissions Module" https://civicrm.org/extensions/relationship-permissions-acls
Beyond that all we are doing is
- use relationship A to join X (...https://chat.civicrm.org/civicrm/pl/wj4t3rrh7ir5uyz7t88c7fuj8o
We are using the "Related Permissions Module" https://civicrm.org/extensions/relationship-permissions-acls
Beyond that all we are doing is
- use relationship A to join X (teacher) to Y (school)
- use relationship B to join Y (school) to Z (student)
- give X necessary permissions to see All Activities but not see All Contacts.
This means that in pure civicrm when X logs in they only see their Students and the relevant Activities.
- Add an SK to show My Contacts and confirm that X only sees their Students - PASS
- Add an SK to show My Activities (I can export but it is super simple) and confirm that X only sees Activities where their Students are the Target - FAIL - they now see all Students with specified Activity
This was the query from the above which worked up till last week when we ran a civi upgrade.
```
SELECT a.id AS id, a.subject AS subject, a.activity_type_id AS activity_type_id:label, Activity_ActivityContact_Contact_01.sort_name AS Activity_ActivityContact_Contact_01.sort_name, Activity_ActivityContact_Contact_01.id AS Activity_ActivityContact_Contact_01.id
FROM civicrm_activity a
INNER JOIN (civicrm_activity_contact Activity_ActivityContact_Contact_01_via_activitycontact INNER JOIN civicrm_contact Activity_ActivityContact_Contact_01 ON (Activity_ActivityContact_Contact_01_via_activitycontact.contact_id = Activity_ActivityContact_Contact_01.id)) ON Activity_ActivityContact_Contact_01_via_activitycontact.record_type_id = "3" AND Activity_ActivityContact_Contact_01_via_activitycontact.activity_id = a.id
LEFT JOIN civicrm_value_attendance_record_15 Attendance_record_1 ON a.id = Attendance_record_1.entity_id
WHERE (a.activity_type_id = "51")
AND (Attendance_record_1.week_commencing_124 BETWEEN "20231203" AND "20231209")
AND (a.is_test = "0")
AND (a.is_deleted = "0")
```colemanwcolemanwhttps://lab.civicrm.org/dev/core/-/issues/4580Assignee not exposed in activity import2023-09-14T13:02:53ZandyburnsAssignee not exposed in activity importThe record_type_id allows for source and target but not assignee? Wondering the history and reasoning behind this? https://forum.civicrm.org/index.php?topic=29394.0
It is possible to use the API CSV Import extension instead which previo...The record_type_id allows for source and target but not assignee? Wondering the history and reasoning behind this? https://forum.civicrm.org/index.php?topic=29394.0
It is possible to use the API CSV Import extension instead which previously may have hit issues with this: https://civicrm.stackexchange.com/questions/42834/issue-with-api-csv-importer-and-activity-imports/45547#45547.https://lab.civicrm.org/dev/core/-/issues/4511Allow multiple 'Added By' for an activity2023-09-08T20:35:49ZyashodhaAllow multiple 'Added By' for an activityThe way some clients are using the CRM, the 'Added By' person(s) is/are really the person(s) responsible for making the activity happen, and the 'Assigned To' are people that participate in the activity but are not directly responsible f...The way some clients are using the CRM, the 'Added By' person(s) is/are really the person(s) responsible for making the activity happen, and the 'Assigned To' are people that participate in the activity but are not directly responsible for it.
The issues is that the CRM only allows ONE Added By, so they rely on some custom contact reference fields 'Partnered With' to supplement the Added By. But this is not a good solution and has many limitations e,g not being able to see the activity in Partner tab etc.
Therefore the best way to move forward is to allow multiple 'Added By' for an activity. The database schema already allows for that with the _activity_contact_ table.
From technical perspective, I reckon it was an oversight to not allow multiple contacts for Contact Source as we had moved civicrm_activity.source_contact_id (single), civicrm_activity_target.contact_id, civicrm_activity_assignee.contact_id (dropped civicrm_activity_target/civicrm_activity_assignee tables) in favor of civicrm_activity_contact table.
Screens that need to be modified:
- Activity tab in the Contact screen
- Activity View screen
- Activity Edit screen
- Activities reports
- Activities resultyashodhayashodhahttps://lab.civicrm.org/dev/core/-/issues/4422Is there any reason to save both html and text versions of Inbound Emails?2023-07-13T17:33:27ZlarsssandergreenIs there any reason to save both html and text versions of Inbound Emails?For Email-to-Activity processing, when the email being processed has both html and text versions, both are saved to the details for the activity using the `-ALTERNATIVE ITEM 0-` ... `-ALTERNATIVE ITEM 1-` delimiters (depending on the det...For Email-to-Activity processing, when the email being processed has both html and text versions, both are saved to the details for the activity using the `-ALTERNATIVE ITEM 0-` ... `-ALTERNATIVE ITEM 1-` delimiters (depending on the details of the email, sometimes it seems to fail to recognize that both are present). This is handled while viewing activities (you only get the first version, which is not ideal as it is often the text version), but not in SearchKit.
I don't see any reason to keep two versions of the same email, so my proposal is just to keep the html version if both are present. Simple is better here unless there is some use case for having both.
If there is a need for the text version, perhaps we could add a setting for the mail account that would select text or html if both are present.https://lab.civicrm.org/dev/core/-/issues/4354Activities created via API should notify Assignees2023-08-03T12:38:52Zwil_SRQActivities created via API should notify AssigneesOverview
----------------------------------------
If the GUI (https://cc.unidosnow.org/civicrm/activity?action=add) would notify assignees when an activity is created. It'd be useful for the API to do so too. The GUI respects the setting...Overview
----------------------------------------
If the GUI (https://cc.unidosnow.org/civicrm/activity?action=add) would notify assignees when an activity is created. It'd be useful for the API to do so too. The GUI respects the setting in Administer > Customize Data and Screens > Display Preferences > Notify Activity Assignees and notification rules by Activity type. It'd be useful for the API to do so too.
Example use-case
----------------------------------------
1. Invoke civicrm_api3('Activity', 'create', []) or civicrm_api4('Activity', 'create', [])
2. Include assignees
Current behaviour
----------------------------------------
The Activity is created but the assignees are not notified, even in situations where creating the equivalent activity via the GUI would have issued notifications.
Proposed behaviour
----------------------------------------
Notify assignees using the same rules and notification format as the GUI.
Comments
----------------------------------------
See https://civicrm.stackexchange.com/q/45078/5446
Workaround is to call CRM_Activity_BAO_Activity::sendToAssignee() separatelyhttps://lab.civicrm.org/dev/core/-/issues/4314Many undefined array and deprecated warnings when adding a new activity for a...2023-11-15T20:11:28ZTobias KrauseMany undefined array and deprecated warnings when adding a new activity for a contact- Open a contact and go to the "Activities" tab
- Click on "+ New Activity"
In many case we see a lot of warnings appearing in watchdog - in total 21 but it seems that the following 3 messages are repeated 7 times:
```
Warning: Undefi...- Open a contact and go to the "Activities" tab
- Click on "+ New Activity"
In many case we see a lot of warnings appearing in watchdog - in total 21 but it seems that the following 3 messages are repeated 7 times:
```
Warning: Undefined array key "params" in include() (Zeile 5 in C:\wamp64\www\civicrm\httpdocs\sites\default\files\private\civicrm\templates_c\en_US\%%DF\DF0\DF0B0562%%RecurringEntity.hlp.php)
Warning: Trying to access array offset on value of type null in include() (Zeile 5 in C:\wamp64\www\civicrm\httpdocs\sites\default\files\private\civicrm\templates_c\en_US\%%DF\DF0\DF0B0562%%RecurringEntity.hlp.php)
Deprecated function: strtolower(): Passing null to parameter #1 ($string) of type string is deprecated in smarty_modifier_lower() (Zeile 23 in C:\wamp64\www\civicrm\vendor\civicrm\civicrm-packages\Smarty\plugins\modifier.lower.php)
```
For the last warning: I mentioned it already in https://lab.civicrm.org/dev/core/-/issues/4312 - maybe this warning might be an issue on it's ownhttps://lab.civicrm.org/dev/core/-/issues/4313Undefined array keys warnings when activities of a contact are listed2023-06-06T05:55:14ZTobias KrauseUndefined array keys warnings when activities of a contact are listedWhen you open a contact and click on the tab "Activities" we see the following warnings in watchdog regardless of how many activities are listed there.
```
Warning: Undefined array key "addAssigneeContact" in include() (Zeile 7 in C:\wa...When you open a contact and click on the tab "Activities" we see the following warnings in watchdog regardless of how many activities are listed there.
```
Warning: Undefined array key "addAssigneeContact" in include() (Zeile 7 in C:\wamp64\www\civicrm\httpdocs\sites\default\files\private\civicrm\templates_c\en_US\%%E3\E36\E366782B%%Tab.tpl.php)
Warning: Undefined array key "addTargetContact" in include() (Zeile 7 in C:\wamp64\www\civicrm\httpdocs\sites\default\files\private\civicrm\templates_c\en_US\%%E3\E36\E366782B%%Tab.tpl.php)
Warning: Undefined array key "hideRelativeLabel" in include() (Zeile 9 in C:\wamp64\www\civicrm\httpdocs\sites\default\files\private\civicrm\templates_c\en_US\%%48\480\480BECF5%%DatePickerRange.tpl.php)
```https://lab.civicrm.org/dev/core/-/issues/3427Activities: Not able to see activity if created by contact you don't have acc...2024-01-23T05:03:19Zben_fairlessActivities: Not able to see activity if created by contact you don't have access toOverview
----------------------------------------
We restrict access to contacts using Group Memberships and ACLs. Specific users are only able to see members of specific groups. We have discovered that users are not able to view an acti...Overview
----------------------------------------
We restrict access to contacts using Group Memberships and ACLs. Specific users are only able to see members of specific groups. We have discovered that users are not able to view an activity unless they have access to **both** the contact _and_ the creator of the activity (which may be in a group they don't have access to).
Reproduction steps
----------------------------------------
1. Sally and Glenn are members of "Group ABC" group.
2. Bob is a member of "Group XYZ" group.
3. A group "Access Group ABC" exists with ACL permissions to "Group ABC".
4. James is a Member of "Access Group ABC".
5. James can see contact record for Sally and Glenn.
6. If Glenn creates an activity on Sally's profile, James can view the activity without issue.
7. If Bob creates an activity on Sally's profile, James can see the activity in the list but cannot view it.
Current behaviour
----------------------------------------
You need to have access to **both** the contact _and_ the creator of the activity in order to view an Activity.
Expected behaviour
----------------------------------------
If you have access to the contact (and all other permissions are correct), you should have access to the activity.
Environment information
----------------------------------------
* __Browser:__ _Chrome Version 100.0.4896.127_
* __CiviCRM:__ _5.48.1_
* __PHP:__ _7.4.28 (Supports 64bit values)_
* __CMS:__ _WordPress 5.9.3_
* __Database:__ _10.2.43-MariaDB_
* __Web Server:__ _Apache_
Comments
----------------------------------------
This assumes that all other permissions (such as WordPress role permissions) are configured correctly. CiviCRM seems to be checking for access to both the creator of an activity as well as the contact it's assigned to to determine if you can view the contents of the activity. You are able to see the activity listed in the Activities tab without issue.https://lab.civicrm.org/dev/core/-/issues/2668MySQL max row size2023-08-28T05:03:23ZRob_SMySQL max row sizeA client has created a long questionnaire with around 110 questions. Everything is good up until question 107 but after that the MySQL row size was exceeded. It seems that when this happens, you can still add extra fields to a fieldset w...A client has created a long questionnaire with around 110 questions. Everything is good up until question 107 but after that the MySQL row size was exceeded. It seems that when this happens, you can still add extra fields to a fieldset without any warning from Civi, but the column does not actually get added to the table in the database. This resulted in a broken database. I'm not sure what happens when an end user completes the questionnaire, but the reason that we picked up on it is because when you try to view the activities of a contact, there is a JS error and they do not load.
The MySQL maximum row size limit is 65,535 bytes apparently, and fields of VARCHAR format use up a lot more memory than fields of TEXT format. CiviCRM creates the columns as VARCHAR format. I was able to fix the issue by working out the correct column names for the missing fields, and adding them manually using the TEXT format. I was also able to change a bunch of the existing columns from VARCHAR to TEXT in order to free up more memory, and this did not result in any data loss.
I think it would be a very good idea to add some extra validation when creating new fields, to check that the column has actually been created in MySQL.
Also, I see that fields of 'Select' type created in Civi are also given the VARCHAR format in MySQL. Looking at the data in these fields, I saw that they had values such as '1', or '2', so the VARCHAR format was using way more space than required. Perhaps it would make sense for Select field types to be created as TEXT format instead? Is it the key or the value that is stored in the database here? Another validator could be added to make sure that none of the keys / values provided were too large to fit in a TEXT field on the database.https://lab.civicrm.org/dev/core/-/issues/2655Incorrect date format displayed on custom activity field2023-08-27T05:03:15ZAndy ClarkIncorrect date format displayed on custom activity fieldA date and time was added as a custom field to an activity type. The format for dates is d MM y with a 12 hour format, which is how the activity date displays. However the custom date displays as YYYY-MM-DD HH-MM-SS. See [this SE](http...A date and time was added as a custom field to an activity type. The format for dates is d MM y with a 12 hour format, which is how the activity date displays. However the custom date displays as YYYY-MM-DD HH-MM-SS. See [this SE](https://civicrm.stackexchange.com/questions/39781/activity-custom-date-format-display-incorrect) problem. Civi release level is 5.37.2https://lab.civicrm.org/dev/core/-/issues/2610Bulk update on activities doesn't work incase checkbox custom field2023-08-12T05:03:23ZPradeep Nayakpradpnayak@gmail.comBulk update on activities doesn't work incase checkbox custom fieldReplicate:
1. Create a custom field against the activity of type checkbox
2. Create a profile and include the checkbox field created at step 1
3. Find Activities
4. Select activities to update and choose update multiple activities
5. Sel...Replicate:
1. Create a custom field against the activity of type checkbox
2. Create a profile and include the checkbox field created at step 1
3. Find Activities
4. Select activities to update and choose update multiple activities
5. Select the profile created from step 2
6. Click 'update activities' (make no changes)
Could replicate on https://dmaster.demo.civicrm.org/
Backtrace
```
$Fatal Error Details = array:3 [
"message" => "'' is not a valid option for field custom_52"
"code" => null
"exception" => CiviCRM_API3_Exception {#997
-extraParams: array:6 [
"error_field" => "custom_52"
"error_code" => 2001
"entity" => "Activity"
"action" => "create"
"is_error" => 1
"error_message" => "'' is not a valid option for field custom_52"
]
#message: "'' is not a valid option for field custom_52"
#code: 0
#file: "/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/api/api.php"
#line: 133
trace: {
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/api/api.php:133 {
› if (is_array($result) && !empty($result['is_error'])) {
› throw new CiviCRM_API3_Exception($result['error_message'], CRM_Utils_Array::value('error_code', $result, 'undefined'), $result);
› }
}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/CRM/Activity/Form/Task/Batch.php:234 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/CRM/Core/Form.php:526 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/CRM/Core/StateMachine.php:144 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php:43 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php:203 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php:103 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/CRM/Core/Controller.php:347 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/CRM/Core/Invoke.php:313 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/CRM/Core/Invoke.php:69 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/CRM/Core/Invoke.php:36 { …}
/Users/pradeep/Sites/drupal7/sites/all/modules/civicrm/drupal/civicrm.module:458 { …}
/Users/pradeep/Sites/drupal7/includes/menu.inc:527 { …}
/Users/pradeep/Sites/drupal7/index.php:21 { …}
}
}
]
```
Quick Fix
```
diff --git a/CRM/Activity/Form/Task/Batch.php b/CRM/Activity/Form/Task/Batch.php
index cfbe6ff238..ef5b8a7c16 100644
--- a/CRM/Activity/Form/Task/Batch.php
+++ b/CRM/Activity/Form/Task/Batch.php
@@ -231,7 +231,7 @@ class CRM_Activity_Form_Task_Batch extends CRM_Activity_Form_Task {
$value['subject'] = $value['activity_subject'];
}
- $activityId = civicrm_api3('activity', 'create', $value);
+ $activityId = CRM_Activity_BAO_Activity::create($value)->id;
// @todo this would be done by the api call above if the parames were passed through.
// @todo extract submit functions &
@@ -240,7 +240,7 @@ class CRM_Activity_Form_Task_Batch extends CRM_Activity_Form_Task {
if (!empty($value['custom']) &&
is_array($value['custom'])
) {
- CRM_Core_BAO_CustomValueTable::store($value['custom'], 'civicrm_activity', $activityId['id']);
+ CRM_Core_BAO_CustomValueTable::store($value['custom'], 'civicrm_activity', $activityId);
}
}
CRM_Core_Session::setStatus("", ts("Updates Saved"), "success");
```https://lab.civicrm.org/dev/core/-/issues/2576Trigger on civicrm_activity causing performance problems2023-09-11T05:03:23ZErikHommelTrigger on civicrm_activity causing performance problemsWhile looking for the root cause of a performance issue for a client, I tracked it down to a couple of triggers performing an inefficient UPDATE statement:
civicrm_activity_before_update civicrm_activity_before_delete
Original SQL (~4 ...While looking for the root cause of a performance issue for a client, I tracked it down to a couple of triggers performing an inefficient UPDATE statement:
civicrm_activity_before_update civicrm_activity_before_delete
Original SQL (~4 seconds):
`UPDATE civicrm_case SET modified_date = CURRENT_TIMESTAMP WHERE id IN (SELECT ca.case_id FROM civicrm_case_activity ca WHERE ca.activity_id = OLD.id);`
~1000x faster SQL to do the same thing:
`UPDATE civicrm_case, civicrm_case_activity SET civicrm_case.modified_date = CURRENT_TIMESTAMP WHERE civicrm_case.id = civicrm_case_activity.case_id AND civicrm_case_activity.activity_id = OLD.id;`
I tracked this down to the following PHP file: sites/all/modules/civicrm/Civi/Core/Container.php
The patch to fix the SQL payload in the file:
`--- Container.php.orig 2021-04-12 11:30:35.039270421 +0100
+++ Container.php 2021-04-12 11:32:00.485650901 +0100
@@ -286,7 +286,7 @@
'table' => 'civicrm_activity',
'when' => 'BEFORE',
'event' => ['UPDATE', 'DELETE'],
- 'sql' => "\nUPDATE civicrm_case SET modified_date = CURRENT_TIMESTAMP WHERE id IN (SELECT ca.case_id FROM civicrm_case_activity ca WHERE ca.activity_id = OLD.id);\n",
+ 'sql' => "\nUPDATE civicrm_case, civicrm_case_activity SET civicrm_case.modified_date = CURRENT_TIMESTAMP WHERE civicrm_case.id = civicrm_case_activity.case_id AND civicrm_case_activity.activity_id = OLD.id;\n",
],
],
]
`
The bug doesn't manifest if you rewrite the UPDATE as a SELECT, but in the UPDATE form it takes an absurdly long time when a sub-select is used. Tested on MariaDB 10.4 and 10.5.
(SE issue [Performance bug in trigger](https://civicrm.stackexchange.com/questions/39303/performance-bug-in-trigger-causing-1000x-slowdown))https://lab.civicrm.org/dev/core/-/issues/2442Proposal - adjust weights on activity contact record types2021-04-07T22:47:08ZeileenProposal - adjust weights on activity contact record typesThis came out of testing search kit - when you search for contacts and add in activity contacts the default record type is 'Assignees' - in search kit data and, I think, real world data I think 'Target' would be a better choice as assign...This came out of testing search kit - when you search for contacts and add in activity contacts the default record type is 'Assignees' - in search kit data and, I think, real world data I think 'Target' would be a better choice as assignees are often empty and I feel like people 'know what they are doing' when trying to find assignees whereas for target I think they are less deliberate about thinking which contact they want.
![image](/uploads/400685a613ddd35ef3190da22631b067/image.png)
Changing this would mean altering the default weights on the option values to re-order the 3 activity types. Personally I think it would be fine to make this change. If we are nervous we could do for new installs only.
I'm not sure the order of source & assignee matters - I flip flop on the order I'd choose there.https://lab.civicrm.org/dev/core/-/issues/2308Improve activity import to support updates2022-06-14T21:00:49ZeileenImprove activity import to support updatesAs I've delved through this code I don't think it makes sense for the checkbox about matching options to be used on the activity form. The reason for it on the contact form is the multiple possible options. For activity it is either an i...As I've delved through this code I don't think it makes sense for the checkbox about matching options to be used on the activity form. The reason for it on the contact form is the multiple possible options. For activity it is either an id match, if present, or nothing
I think ideally this would be dependent on activity id being mapped on the specified row - ie it could be possible to have a combination of with & without.
@DaveD tracking gl for this5.51.0https://lab.civicrm.org/dev/core/-/issues/2086Editing an activity always logs a backtrace about duplicate key if you don't ...2023-07-11T05:03:30ZDaveDEditing an activity always logs a backtrace about duplicate key if you don't change the With Contacts@eileen One thing I'm seeing as a result of https://github.com/civicrm/civicrm-core/pull/18566 is that every time you edit an activity and save it it silently logs a backtrace for the PEAR error since the target contact always already ex...@eileen One thing I'm seeing as a result of https://github.com/civicrm/civicrm-core/pull/18566 is that every time you edit an activity and save it it silently logs a backtrace for the PEAR error since the target contact always already exists if you haven't changed it. I suppose edits are less common than new activities, so maybe it's still a net win, but logging a backtrace maybe is worse than an extra query.
To think about...
I haven't marked it regression since it's not a bug exactly.https://lab.civicrm.org/dev/core/-/issues/1710Activity Detail report gives syntax error with force=1 if include case activi...2020-04-28T19:40:48ZelilisseckActivity Detail report gives syntax error with force=1 if include case activities is onDirectly related to: https://lab.civicrm.org/dev/core/-/issues/1552
Summary from 1552: On the Activity Details report, when the filter "Include Case Activities" is set to "Yes" the report can generate an SQL syntax error as follows:
``...Directly related to: https://lab.civicrm.org/dev/core/-/issues/1552
Summary from 1552: On the Activity Details report, when the filter "Include Case Activities" is set to "Yes" the report can generate an SQL syntax error as follows:
```
`[type] => DB_Error
[user_info] => CREATE TEMPORARY TABLE civicrm_tmp_e_dflt_607d7436413c499f88a03a05edf1fd9c ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT civicrm_contact_target.sort_name as civicrm_contact_contact_target, civicrm_contact_target.id as civicrm_contact_contact_target_id, activity_civireport.id as civicrm_activity_id, activity_civireport.source_record_id as civicrm_activity_source_record_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.subject as civicrm_activity_activity_subject, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_civireport.status_id as civicrm_activity_status_id, address_civireport.street_name as civicrm_address_street_name, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_address as civicrm_address_street_address, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code
FROM civicrm_activity activity_civireport
INNER JOIN civicrm_activity_contact activity_contact_civireport
ON activity_civireport.id = activity_contact_civireport.activity_id AND
activity_contact_civireport.record_type_id = 3
INNER JOIN civicrm_contact civicrm_contact_target
ON activity_contact_civireport.contact_id = civicrm_contact_target.id
LEFT JOIN civicrm_case_activity
ON .activity_id = activity_civireport.id
LEFT JOIN civicrm_address address_civireport
ON (civicrm_contact_target.id =
address_civireport.contact_id) AND
address_civireport.is_primary = 1
WHERE activity_civireport.is_test = 0 AND
activity_civireport.is_deleted = 0 AND
activity_civireport.is_current_revision = 1 AND ( activity_civireport.activity_date_time >= 20200401000000) AND ( activity_civireport.activity_date_time <= 20200430235959) AND ( activity_civireport.activity_type_id IN (66,27,20,19,37,29,18,16,53,26,15,33,35,36,48,52,51,6,42,49,43,3,50,5,41,54,14,12,45,62,55,25,64,34,56,1,8,17,7,58,23,13,4,46,32,2,30,10,11,22,24,47,40,21,44,60,28,9,39,38,67,68,31) ) [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 '= activity_civireport.id
LEFT JOIN civicrm_address address_civi' at line 10]`
```
The exact same syntax error still exists in a demo install of 5.26.alpha1 but only when the direct "View Results" link is used without first viewing report criteria i.e. the report form is viewed with a param like "force=1" or "output=result" as it generally would be in a direct menu link to that report.
Further, it seems the following notices appear only when the Activity Detail report result are accessed directly. These notices to not appear when one views criteria first and clicks "view results":
`Notice: Undefined index: civicrm_case_activity in CRM_Report_Form_Activity->buildFrom() (line 1157 of /home/eli/buildkit/build/drupal526/web/sites/all/modules/civicrm/CRM/Report/Form/Activity.php).`
`Notice: Undefined offset: 3 in CRM_Report_Utils_Report::getInstanceID() (line 307 of /home/eli/buildkit/build/drupal526/web/sites/all/modules/civicrm/CRM/Report/Utils/Report.php).`
It appears `{$this->_aliases['civicrm_case_activity']}` is only populated when the report criteria is accessed before results.5.25.0https://lab.civicrm.org/dev/core/-/issues/1634Evaluate if any indexed fields are unused2022-12-04T23:07:09ZeileenEvaluate if any indexed fields are unused
**Proposal** (note this is being updated based on discussion & comments may refer to an earlier version)
1. Remove the following columns from the xml from civicrm_activity
* phone_id
* phone_number
* relationship_id
1. Remove th...
**Proposal** (note this is being updated based on discussion & comments may refer to an earlier version)
1. Remove the following columns from the xml from civicrm_activity
* phone_id
* phone_number
* relationship_id
1. Remove the index from the xml on
* medium_id
* is_deleted
1. During upgrade we drop the above columns, if empty.
**Follow ups to consider**
2. There are other columns in the civicrm_activity table that are case specific - we might consider indexing is_current_revision & original_id only when CiviCase is enabled
3. I've been doing some tests on searches and found that searching is faster if I DROP the contribution_status_id index - it might be interesting to test the activity_type_id index although I suspect it has a much greater cardinality & is more useful
**Impact of the above**
1. Data would not be lost but api fields would no longer access those fields
2. Developers who might be using them outside of core could be impacted - we can mitigate by communicating on the dev list & perhaps putting checks & deprecation notices onto sites with data in the fields for a few months before making any changes.
3. DB size would be reduced. Note that empty fields contribute notably to table size IF they are indexed
4. Dev confusion & efficiency is improved by not having unused stuff in core.
**Background**
Obviously that's not something we should rush into so I'll have to ping the dev list etc
Looking at our civicrm_activity table it appears that each index has a base size - of around a half a gig. From there, the index size increases based on how much data is in the table. So an index on an empty field is around 57% of the size of our largest index.
There are 5 fields that are indexed + empty in our database for the civicrm_activity table (
```
Original id used for CiviCase
Medium id
Phone id
relationship_id
is_deleted
```
Plus - is_current_revision is effectively null
So my first question is are these all used in other databases - e.g when civicase is in use.
I couldn't spot references to phone_id and it feels 'wrong' to me anyway as I think you would want to either link to the contact or have a hard reference. I wonder if some of these fields are quietly obsolete?
It's very unsafe to drop core fields. However, I'm pondering dropping the indexes on these fields
@DaveD I'd appreciate your thoughts....https://lab.civicrm.org/dev/core/-/issues/1619Activity Summary civireport gives fatal error when grouping activity date by ...2020-02-27T23:22:50ZDaveDActivity Summary civireport gives fatal error when grouping activity date by quarterNot working since at least 5.20. Haven't checked farther back.
```
INSERT INTO civicrm_tmp_e_dflt_c96bd73d5a616c0a85f8b776fdaf8a13 ( civicrm_contact_id,civicrm_activity_activity_date_time_start,civicrm_activity_activity_date_time_interv...Not working since at least 5.20. Haven't checked farther back.
```
INSERT INTO civicrm_tmp_e_dflt_c96bd73d5a616c0a85f8b776fdaf8a13 ( civicrm_contact_id,civicrm_activity_activity_date_time_start,civicrm_activity_activity_date_time_interval,civicrm_activity_activity_date_time_subtotal,civicrm_activity_activity_type_id,civicrm_activity_duration,civicrm_activity_id_count )
SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, STR_TO_DATE(CONCAT( 3 * QUARTER( activity_civireport.activity_date_time ) -2 , '/', '1', '/', YEAR( activity_civireport.activity_date_time ) ), '%m/%d/%Y') AS civicrm_activity_activity_date_time_start, QUARTER(activity_civireport.activity_date_time) AS civicrm_activity_activity_date_time_subtotal, QUARTER(activity_civireport.activity_date_time) AS civicrm_activity_activity_date_time_interval, GROUP_CONCAT(DISTINCT activity_civireport.activity_type_id ORDER BY activity_civireport.activity_type_id ) as civicrm_activity_activity_type_id, activity_civireport.duration as civicrm_activity_duration, COUNT(DISTINCT(activity_civireport.id)) as civicrm_activity_id_count
FROM civicrm_activity activity_civireport
LEFT JOIN civicrm_activity_contact target_activity
ON activity_civireport.id = target_activity.activity_id AND
target_activity.record_type_id = 3
LEFT JOIN civicrm_activity_contact assignment_activity
ON activity_civireport.id = assignment_activity.activity_id AND
assignment_activity.record_type_id = 1
LEFT JOIN civicrm_activity_contact source_activity
ON activity_civireport.id = source_activity.activity_id AND
source_activity.record_type_id = 2
LEFT JOIN civicrm_contact contact_civireport
ON target_activity.contact_id = contact_civireport.id
LEFT JOIN civicrm_contact civicrm_contact_assignee
ON assignment_activity.contact_id = civicrm_contact_assignee.id
LEFT JOIN civicrm_contact civicrm_contact_source
ON source_activity.contact_id = civicrm_contact_source.id
LEFT JOIN civicrm_option_value
ON ( activity_civireport.activity_type_id = civicrm_option_value.value )
LEFT JOIN civicrm_option_group
ON civicrm_option_group.id = civicrm_option_value.option_group_id
LEFT JOIN civicrm_case_activity
ON civicrm_case_activity.activity_id = activity_civireport.id
LEFT JOIN civicrm_case
ON civicrm_case_activity.case_id = civicrm_case.id
LEFT JOIN civicrm_case_contact
ON civicrm_case_contact.case_id = civicrm_case.id WHERE civicrm_option_group.name = "activity_type" AND
activity_civireport.is_test = 0 AND
activity_civireport.is_deleted = 0 AND
activity_civireport.is_current_revision = 1 AND ((`activity_civireport`.`activity_type_id` IS NULL OR (`activity_civireport`.`activity_type_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 53, 33, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 55, 56, 58, 60, 62, 64, 66))) OR civicrm_contact_source.is_deleted=0 OR civicrm_contact_assignee.is_deleted=0) GROUP BY YEAR(activity_civireport.activity_date_time), QUARTER(activity_civireport.activity_date_time), contact_civireport.id, STR_TO_DATE(CONCAT( 3 * QUARTER( activity_civireport.activity_date_time ) -2 , '/', '1', '/', YEAR( activity_civireport.activity_date_time ) ), '%m/%d/%Y'), QUARTER(activity_civireport.activity_date_time), activity_civireport.duration ORDER BY activity_civireport.activity_type_id ASC LIMIT 0, 50 [nativecode=1055 ** Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'core16560724hxcivi_syiur.activity_civireport.activity_type_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
```5.24.0https://lab.civicrm.org/dev/core/-/issues/1616Activity Search doesn't display activities with no "With Contact"2023-02-16T15:15:35ZJonGoldActivity Search doesn't display activities with no "With Contact"To replicate:
* Create a new activity without anyone listed as "With Contact".
* Search for the activity from **Find Activities**.
I hadn't considered the use case of an activity with no "With Contact", but apparently my client has - an...To replicate:
* Create a new activity without anyone listed as "With Contact".
* Search for the activity from **Find Activities**.
I hadn't considered the use case of an activity with no "With Contact", but apparently my client has - and it's not a required field. So it seems that these activities should show up in search.https://lab.civicrm.org/dev/core/-/issues/1526civiCase, Contributions and activities have no record locking!2023-01-29T05:03:15ZsqwciviCase, Contributions and activities have no record locking!To Reproduce
1) Open 2 windows on the same Case
2) Edit the details in both windows - use different data in each
3) Save record in both windows
4) The later saved record will be the only date recorded
There is no user warning either bef...To Reproduce
1) Open 2 windows on the same Case
2) Edit the details in both windows - use different data in each
3) Save record in both windows
4) The later saved record will be the only date recorded
There is no user warning either before or after editing or saving the data.
The same is reproducible for activities and contributions.
This may apply elsewhere.
To my mind this is a fundamental show stopper since it can cause loss of data at any time without warning or notice.