1

How could I search for X activity type custom field and only return the last activity created if the record has more than 1 X activity type?

I envision creating a smart group out of this data.

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

  • Are you looking for a UI-only solution or is SQL ok? – Demerit Oct 9 at 22:57
  • The goal is to keep a mailing group accurate automatically. So probably UI. – Andy Burns Oct 9 at 22:59
  • Civirules doesn't expose custom fields of activities so doesn't look like that is an option – Andy Burns Oct 9 at 23:00
  • If keeping the old activities is about tracking/logging, then you could turn on detailed logging and instead of creating new activities always update the old one. I'm not sure though how to enforce only having one activity of that type per person (for regular activities outside of civicase) without some code. – Demerit Oct 9 at 23:07
  • Or conversely, create regular custom fields used for individuals and detailed logging would show changes to those fields? A detailed logging report might be able to show these changes? – Andy Burns Oct 9 at 23:12
1

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

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

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

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

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

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

This might or might not be an issue in your case

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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