Reporting on when a Field is changed

hi Sara Foreman 
what about a simple BPM that triggers an email when that field changes, with both the before and after value, would that work for you?

Hi Vincent, thank you for your reply. Unfortunately this wouldn't work for what we are needing - there could be large numbers of changes each month so we need all the change information collated into one report...

OK, in that case sounds like you need a monthly report with all the changes on that field during the whole month rather than every time for each change?

If that is the case, then an advanced report should help with achieving this.

Hi Sara Foreman ,

instead of sending out an email as Vincent suggested, you could also create an entry in a specific module that you create for this.

We are doing this on Account level. We have built an extra module called "Field Updates", which gets a new entry via BPM whenever a certain field we want to monitor is changed. Per field, there can always only be one active entry, so if, let's say, the value of the "Expected Growth"-field changes, we set the current Field Update record for this field to the status "Past", write the current date in the "Date end" field of the Field Update and create a new Field Update record with the status "Current" and the runtime date as the "Date Start", (which will get set to past, when the field is changed again and so on and so on ...)

I hope you got the general idea ;) 

 Sara Foreman If you want to try Vincent Amari's suggestion, I believe one of the examples in A simple custom Query like a for quick summary. Great for use in Advanced Reports. may help give you a template to work from (you'll need to update the specifics, but it hopefully can give you a start):

Reviewing Case Status Changes:  

If your goal is to identify cases that have changed status most frequently in the past 90 days—perhaps to coach service agents on improving efficiency by minimizing back-and-forth interactions—you can use:

Fullscreen

1

2

3

4

5

6

SELECT parent_id a, COUNT(id) b

FROM cases_audit

WHERE field_name = 'status'

AND date_created >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)

GROUP BY a

ORDER BY b DESC;

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SELECT parent_id a, COUNT(id) b 
FROM cases_audit 
WHERE field_name = 'status' 
AND date_created >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
GROUP BY a 
ORDER BY b DESC;

Thank you Alex Nassi  and Vincent Amari, yes a monthly report will likely do what we want... we will have a look into the Advanced Reports, thank you for sharing the template.

Julia Weinhold this is really helpful, thank you for sharing the map - creating a module would help us track other fields as well - we will look into it! Excuse the question, I'm a very novice Sugar user(!), can you explain what you are building into the module - is it specifically the fields you want to see the changes of?