0

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

...and the Detail report shows the custom fields that were changed...

detail report

...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 extension didn't offer a suitable report.
  3. ReportPlus extension also didn't offer a suitable report.
  4. These extensions showed initial promise but just throw errors... Extended Logging Report and View Detailed Log.
  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!

Improve this question
9
  • 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.
    – Lars SG
    Commented Oct 21, 2023 at 0:36
  • 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!
    – Justin657
    Commented Oct 21, 2023 at 3:12
  • Can you do a PR? I think it would be pretty simple.
    – Lars SG
    Commented Oct 21, 2023 at 15:57
  • 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.
    – Lars SG
    Commented Oct 21, 2023 at 16:03
  • Not sure if that works for you but here is a different approach: Use the XCM extension (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 (docs.civicrm.org/i3val/en/latest) which provides a fancy interface to apply or discard those suggested changes.
    – Fabian
    Commented Oct 23, 2023 at 6:51

1 Answer 1

Reset to default
1

This is fixed as of CiviCRM 5.72.0.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.