Forum Discussion

KellyLyons1's avatar
KellyLyons1
Qrew Cadet
2 years ago

Admin/system generated logging help

I am trying to figure out how to set up a "system-generated" account log  (system generated account activity such as when a user clicks a button that generates a document) for recording certain actions.  For example when anyone adds any type of child records, edits a record, emails a specific invoice, changes the status of child records (ie changes the status of a record, authorizes and schedules a patient for something), 

I want to have this type of activity documented separately from any data entry/notes that they enter directly.  And even when they add a note directly, some type of table to keep track of all activity related to the parent record.

I would like this documented as "system admin" and not by the actual user since this is already in another place.  

What is the best way to tackle something like this?

Would I want to create a new user - System Admin - and then create some type of pipeline to capture these changes as the admin?   

Would use audit logs or similar work???

I have attached a screenshot of how our previos database did this for reference.

Thanks for any ideas



------------------------------
Kelly Lyons
------------------------------
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    2 years ago

    A lot of it probably depends on what you're targeting to do with this data. Quickbase supports tracking data changes by enabling certain apps and fields for logging.

    This will have some advantages and disadvantages though: 

    1. You're limited to 100 which is a great number for most use cases
    2. Only Admins with access to the admin console can view the logs
    3. You'll be unable to report effectively about what specifically changed or by who easily

    The alternative is what you said and to use pipelines. You would setup the pipeline under a SVC account like you mentioned, and you would have the trigger from whatever tables on add/edit that you want to target. You would move the changes into a 'logs' table that you set up with fields like what field changed, who changed it, when it happened etc. 

    The advantages and disadvantages are though: 

    1. You would need to have a pipeline for each table you'd be targeting. So if you want to track changes on 10 tables, you'd need at least 10 pipelines that triggers on adds and edits (and deletes if your role allows)
    2. If you to track the actual changes to 'each field' - you'll have to get really creative with how you setup your pipeline. If its a small table of like 10 fields you can probably do IF conditions to see if the current and previous value changed and if so then log out that Field A was changed by User X and they changed it from A to B. 
      1. If you have a lot of fields though, you'll need to get creative with Jinja to merge all of your fields into a request where you are using Jinja to check previous versus current to identify what changed. 
      2. This method is tough though in that you have to constantly keep updating the Pipeline to keep your fields in line. 
        1. There might be a way to do a loop in Jinja for all the key / value pairs and log that way but would take some digging
    3. On the good side though, you would get some really good data to know in very easy to report ways how your data is changing. Since this is all native to a single table and if you break out the fields such as Table, Field that changed, who changed it, old value, new value, when it changed (6 total fields for example) you can run reports like how many times did someone lower a specific $ field  or how many times did someone update a particular invoice and what were they changing etc etc. 

    I personally have gone the latter route and done the more manual audit logs given the ease of reporting and auditability by more users. I prefer having the ability to look and say show me all the audit logs for this particular record and trace that history but its a lot harder to manage. This method also allows you to use things like summary if you wanted to track or alert when certain fields change in certain ways etc etc.

    The native QB logs are much easier to put in place and manage what fields to audit but you lose some of the reporting and access directly in the app itself given that they're an admin console/realm feature and not specific to a single app. 



    ------------------------------
    Chayce Duncan
    ------------------------------