Answers.Body: <p>This is fixed as of <a href="https://download.civicrm.org/release/5.72.0" rel="nofollow noreferrer">CiviCRM 5.72.0</a>.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-12T16:01:36.463Z
Answers.LastActivityDate: 2024-04-12T16:01:36.463Z
Answers.UserDisplayName: Justin657
Body: <p><strong>The Ask</strong></p>
  <p>My client has to manually reconcile people records with an external system. This requirement calls for a report to be run on a periodic basis, say weekly, that shows changes to certain custom fields stored in Civicrm for people.</p>
  <p>I'm trying to understand what's feasible in Civi 5.65 on Drupal 10.</p>
  <p><strong>Problem: Civi's out-of-the-box Contact Logging Reports don't show custom field values</strong></p>
  <p>Civicrm Logging is turned ON (&quot;Logging&quot;=Yes at civicrm/admin/setting/misc)</p>
  <p>Using dummy data...</p>
  <p>The Contact Logging Report (Summary) shows an update was made to a person's record...</p>
  <p><a href="https://i.sstatic.net/GHmhh.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/GHmhh.png" alt="summary report" /></a></p>
  <p>...and the Detail report shows the custom fields that were changed...</p>
  <p><a href="https://i.sstatic.net/358qC.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/358qC.png" alt="detail report" /></a></p>
  <p>...but <em>the before and after values displayed for the custom field are the numeric option values</em>, whereas I want to display the option label (text).</p>
  <p>Ideally instead of reporting that a field changed from 11 to 9, it would report a change from &quot;Type 1&quot; to &quot;Type 2&quot; (taken from the civicrm_option_value db table).</p>
  <p>If I could get a report to show that, problem solved.</p>
  <p><strong>What I've tried</strong></p>
  <p>I've googled and searched this forum and tried different Civi extensions to no avail.</p>
  <ol>
  <li><strong>Contact Logging Report (summary and detail)</strong> is the closest to a solution (what you see above).</li>
  <li><strong><a href="https://civicrm.org/extensions/extended-reports" rel="nofollow noreferrer">Extended Reports</a> extension</strong> didn't offer a suitable report.</li>
  <li><strong><a href="https://civicrm.org/extensions/reportplus" rel="nofollow noreferrer">ReportPlus</a> extension</strong> also didn't offer a suitable report.</li>
  <li>These extensions showed initial promise but just throw errors... <a href="https://civicrm.org/extensions/extended-logging-report" rel="nofollow noreferrer">Extended Logging Report</a> and <a href="https://civicrm.org/extensions/viewdetailedlog" rel="nofollow noreferrer">View Detailed Log</a>.</li>
  <li><strong>SearchKit</strong>: I got as far as searching for Contacts with required Contact Logs, but that table join is based on the contact who made the change, not on the contact whose data was changed. Plus SK currently can't do the final join on the option value table to get me the text labels.</li>
  </ol>
  <p><strong>Possible Solutions</strong></p>
  <p>I can try writing a custom SQL query to join the specific tables (civicrm_log, log_civicrm_contact, log_civicrm_value_person_inform_8, and lastly the option value table to get the text labels) and display the results to screen, but ideally I'd like to find a way to do it within the CiviCRM UI.</p>
  <p>Appreciate any ideas!</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-21T00:36:30.360Z
Comments.Text: If you're happy with the Contact Logging Detail report, it probably wouldn't be much work to change that so it displays option values in core — and I think that would be of benefit to everyone. Ideally, this would be SearchKit, but I don't think the detailed logging (with the changed data) is even available at all through API4 at this point, so that's not really feasible currently.
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-21T03:12:27.137Z
Comments.Text: Well I don't care much for the master-detail back and forth between the Contact Logging Summary and Detail reports. I'd rather have it all in a single detail report across all the contacts who had changes. But I can appreciate there may be some inherent challenges in joining across all the possible log tables for changed custom field values. I'd certainly be thrilled if displaying option values makes it into core and helps others as well!
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-21T15:57:50.610Z
Comments.Text: Can you do a PR? I think it would be pretty simple.
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-21T16:03:24.173Z
Comments.Text: The other option would be to add detailed logging to API4 and then to SearchKit, which wouldn't be a huge job either, but definitely more than this. That would also be a good thing for everyone, I think.
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-23T06:51:36.210Z
Comments.Text: Not sure if that works for you but here is a different approach:
  
  Use the XCM extension (https://docs.civicrm.org/xcm/en/latest/) for importing the contact updates. It will create change activities instead of overwriting the contact values.
  
  Then use i3val extension (https://docs.civicrm.org/i3val/en/latest/) which provides a fancy interface to apply or discard those suggested changes.
Comments.UserDisplayName: Fabian
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-23T22:43:24.803Z
Comments.Text: Thanks, @Fabian. I'll consider XCM and i3val for any inbound data reconciliation. In this case my client is manually pushing changes from Civi to an external system.
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-23T22:47:58.700Z
Comments.Text: @LarsSG, I'd love to start contributing to core. Let me poke around the codebase and figure out where to start.
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-23T23:47:30.573Z
Comments.Text: Great! If it helps to get you starte, the relevant class is here: https://github.com/civicrm/civicrm-core/blob/master/CRM/Logging/ReportDetail.php and the function is convertDiffsToRows(). You can use CRM_Core_Pseudoconstant::getLabel() to get the label from the key.
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-01-16T23:29:03.867Z
Comments.Text: @LarsSG, I finally drafted a PR here: https://github.com/civicrm/civicrm-core/pull/29029
   I appreciate any guidance on it.
Comments.UserDisplayName: Justin657
Tags: <civireport><reports><searchkit><logs>
Title: How to generate a report of changes to a contact's custom fields (showing the before/after text labels) from Civi's detailed log?
ThreadText: # How to generate a report of changes to a contact's custom fields (showing the before/after text labels) from Civi's detailed log?
  
  (Asked by: username_0 on 2023-10-20)
  
  **The Ask**
  
  My client has to manually reconcile people records with an external system. This requirement calls for a report to be run on a periodic basis, say weekly, that shows changes to certain custom fields stored in Civicrm for people.
  
  I'm trying to understand what's feasible in Civi 5.65 on Drupal 10.
  
  **Problem: Civi's out-of-the-box Contact Logging Reports don't show custom field values**
  
  Civicrm Logging is turned ON ("Logging"=Yes at civicrm/admin/setting/misc)
  
  Using dummy data...
  
  The Contact Logging Report (Summary) shows an update was made to a person's record...
  
  [![summary report](https://i.sstatic.net/GHmhh.png)](https://i.sstatic.net/GHmhh.png)
  
  ...and the Detail report shows the custom fields that were changed...
  
  [![detail report](https://i.sstatic.net/358qC.png)](https://i.sstatic.net/358qC.png)
  
  ...but *the before and after values displayed for the custom field are the numeric option values*, whereas I want to display the option label (text).
  
  Ideally instead of reporting that a field changed from 11 to 9, it would report a change from "Type 1" to "Type 2" (taken from the civicrm\_option\_value db table).
  
  If I could get a report to show that, problem solved.
  
  **What I've tried**
  
  I've googled and searched this forum and tried different Civi extensions to no avail.
  
  1. **Contact Logging Report (summary and detail)** is the closest to a solution (what you see above).
  2. **[Extended Reports](https://civicrm.org/extensions/extended-reports) extension** didn't offer a suitable report.
  3. **[ReportPlus](https://civicrm.org/extensions/reportplus) extension** also didn't offer a suitable report.
  4. These extensions showed initial promise but just throw errors... [Extended Logging Report](https://civicrm.org/extensions/extended-logging-report) and [View Detailed Log](https://civicrm.org/extensions/viewdetailedlog).
  5. **SearchKit**: I got as far as searching for Contacts with required Contact Logs, but that table join is based on the contact who made the change, not on the contact whose data was changed. Plus SK currently can't do the final join on the option value table to get me the text labels.
  
  **Possible Solutions**
  
  I can try writing a custom SQL query to join the specific tables (civicrm\_log, log\_civicrm\_contact, log\_civicrm\_value\_person\_inform\_8, and lastly the option value table to get the text labels) and display the results to screen, but ideally I'd like to find a way to do it within the CiviCRM UI.
  
  Appreciate any ideas!
  
  **Tags:** civireport, reports, searchkit, logs
  
  > **Comments:**
  > **username_1**: If you're happy with the Contact Logging Detail report, it probably wouldn't be much work to change that so it displays option values in core — and I think that would be of benefit to everyone. Ideally, this would be SearchKit, but I don't think the detailed logging (with the changed data) is even available at all through API4 at this point, so that's not really feasible currently.  
  > **username_0**: Well I don't care much for the master-detail back and forth between the Contact Logging Summary and Detail reports. I'd rather have it all in a single detail report across all the contacts who had changes. But I can appreciate there may be some inherent challenges in joining across all the possible log tables for changed custom field values. I'd certainly be thrilled if displaying option values makes it into core and helps others as well!  
  > **username_1**: Can you do a PR? I think it would be pretty simple.  
  > **username_1**: The other option would be to add detailed logging to API4 and then to SearchKit, which wouldn't be a huge job either, but definitely more than this. That would also be a good thing for everyone, I think.  
  > **username_2**: Not sure if that works for you but here is a different approach:  Use the XCM extension (https://docs.civicrm.org/xcm/en/latest/) for importing the contact updates. It will create change activities instead of overwriting the contact values.  Then use i3val extension (https://docs.civicrm.org/i3val/en/latest/) which provides a fancy interface to apply or discard those suggested changes.  
  > **username_0**: Thanks, @username_2. I'll consider XCM and i3val for any inbound data reconciliation. In this case my client is manually pushing changes from Civi to an external system.  
  > **username_0**: @username_1, I'd love to start contributing to core. Let me poke around the codebase and figure out where to start.  
  > **username_1**: Great! If it helps to get you starte, the relevant class is here: https://github.com/civicrm/civicrm-core/blob/master/CRM/Logging/ReportDetail.php and the function is convertDiffsToRows(). You can use CRM_Core_Pseudoconstant::getLabel() to get the label from the key.  
  > **username_0**: @username_1, I finally drafted a PR here: https://github.com/civicrm/civicrm-core/pull/29029  I appreciate any guidance on it.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2024-04-12. Score: 1
  
  This is fixed as of [CiviCRM 5.72.0](https://download.civicrm.org/release/5.72.0).
  
  
  ---
  
  