Tracking User Activity
Is it possible to build a table to count individual user actions in the database as a whole?
I want to implement a fun rank and title system, similar to the one on this forum (Apprentice, Ninja, Monk, etc) based on how often each user is interacting with the database.
I'm assuming it would just be a matter of counting the number of times each user appears in the database history, but I don't know the syntax to access history (if it exists).
10 replies
-
Anybody?
-
Bringing this up again...
-
This depends on what you want to track. You can use the trigger "onOpen" to write a timestamp and the user to a seperate table.
Birger
-
Thanks, Birger. That would just capture how often they are opening the database, correct?
I'm assuming I could do the same thing with "Trigger on create" to capture userName() and then use a formula to count the number of times each name appears. Do you think it would slow down the database to capture user info on every "on create"?
-
Hi Bill,
It should not slow down your database, but it will only count how often a user is creating a new record.
Best, Jörg
-
Correct. That's what I want to capture. Looking forward to implementing this soon.
Thanks for your help!
-
Update on this. I've implemented an Activity Monitor table in my database that utilizes the Trigger After Update function to create entries in the Activity Monitor table:
(create 'Activity Monitor').['A#' := maxId + 1, User := user(), Where := "TableName", 'XP for Action' := 1]
My question now is: Is there a "get field name" function so that I can identify what was changed?
Also, one short-coming of the above code is that it is not capturing record deletions. Suggestions?
-
Glad to know about this kind of database which can easily track individuals activity.Thank you and glad to be a part here.
-
@Bill
If you want field level tracking into your Activity table.. you will need to put that logic in the Trigger after update of EACH field. While that sounds cumbersome (and it is)... put the core of the logic in a global function and call that global function and pass in the field name.
As for deletions.. You are correct. So far, there is no trigger before/after delete. To address that, you will need "Trigger after open" logic that scans your activity table(s) and then determines if the row still exists on the main table. The way I have addressed this in some applications is to create a "history" table that mirrors the structure of the main table... the main tables trigger after update end dates the most current history table row.. and then copies out the current row.. So the most current row on the main table and the history table "should" always match. When the main table row is "deleted" .. I have a copy of what it looked like on the history table. Then.. on open.. if that logical key is no longer on the main table .. I end date the history row.
There are several drawbacks to this approach..
1. The tables "Trigger after update" fires EACH TIME a FIELD is updated.. which may / may not be what you want, and the history table can grow rather large. You can get around this by moving the history logic into trigger after update per field.. then write logic to collapse the rows were fields are null.
2. There is no trigger after delete.. so the Trigger after Open will identify that a row no longer exists, but it will not tell you WHO or WHEN deleted it.. If that is SUPER important to you, @Maarten Thiebou .. one of the Ninox partners, developed a nice little html / css hack that hides the delete menu button.. thus you can create your own button for delete and have full control.. If using the Ninox Cloud version, you could also get creative adding roles so only certain people can delete.
-
Mconneen,
Thanks for feedback. This is a big help; however, I haven't set up a global function before and I'm struggling with the syntax. How should I go about modifying my existing code to create and call a global function?
Content aside
- 4 yrs agoLast active
- 10Replies
- 3120Views