I'm looking for tips on implementing an activity stream in the style of—you guessed it—Facebook or Github or Salesforce, but in a FileMakery way.
It would be partly to capture changes like an audit log ("John changed due date to tomorrow"), but also to show other things not strictly in terms of change tracking ("Bryce liked Ed's call report").
I get the sense that people do this all the time, and the general approach seems intuitive, but I'm looking for any pitfalls, tricks, and general wisdom on implementing specifically in FileMaker Pro, and when one approach or another might be better or worse.
It comes to mind that the OnRecordCommit script trigger is a workhorse here, using a magic key to create a record in a log table related to a record being modified. The log table would have fields for user ID and timestamp, a description of the type of activity, and maybe one or more fields that changed and their old and new values.
And then a script to create a new event in the log with type of "Like" and a related record ID. Maybe using the same kind of magic key technique.
Are there ways of doing this with lightning speed? What happens when the activity log table starts getting huge?
It seems like a similar problem to audit logging, for which there's certainly quite a lot of discussion, but is an audit log really the right approach for generating a Facebook-style news feed?
I dunno. I'm playing around with different approaches, and I'd love to gain from anyone's experience on this. Thanks.