My attempt at a Audit Trail Log. Fully Working + a Few Question for ZOHO Employees.

My attempt at a Audit Trail Log. Fully Working + a Few Question for ZOHO Employees.

First part of the post is a script for an Audit Trail, feel free to copy it if you want. 

Second part, are questions i have for some advanced user or employee, please help me. 

I wrote an Audit Trail Log for my application, i needed a simple approach, and a function that i only need to write once and copy/paste it without changes to my forms. This is what i did. 


I used the function getFieldNames() and getFieldValue() to get all the fields and values in any given form without having to specify each field, so you can insert this code in any form and it will work seamlessly.
 
I created a Form called AuditTrailHistory, with only 2 fields Single Line field called "FieldID" and multiline field called "History"


Copy the following code to "On Add -> On success" and "On Edit -> On success"


  2. HistoryData = "";
  3. for each field in getFieldNames()
  4. {
  5. //I don't want to save all of these system fields, because i have my own embedded in my form, so this part tells the script not to get the values from those fields. 
  6.     if ((((((field  ==  "Modified_User_IP_Address")  ||  (field  ==  "Added_User_IP_Address"))  ||  (field  ==  "Added_User"))  ||  (field  ==  "Added_Time"))  ||  (field  ==  "Modified_User"))  ||  (field  ==  "Modified_Time"))
  7.     {
  8.         HistoryData = "-";
  9.     }
  10.     else
  11.     {
  12.         HistoryData = HistoryData + "\n" + field + ":" + getFieldValue(field);
  13.     }
  14. }
  15. insert into AuditTrailHistory
  16. [
  17.     Added_User = zoho.loginuser
  18.     FieldID = input.ID
  19.     History = " Created by: " + zoho.loginuser + "\n " + " Creation Time: " + zoho.currenttime + "\n" + HistoryData + " "
  20. ]
I chose to insert a new record everytime the record gets updated, instead of just updating the field. 


Then on my report i created a custom action that displays on the menu for each record, this is the code for it. 

  2. void Global.AuditRecord(int ID)
  3. {
  4.     Record  =  AuditTrailHistory  [FieldID == input.ID];
  5.     if (Record.ID  !=  null)
  6.     {
  7. //If a log exits for this record it will open a popup with the Audit Trail, make sure this report has no edit or delete permissions
  8.         openUrl("#View:AuditTrailHistory_Report?FieldID=" + input.ID + "&zc_Header=false", "Popup Window");
  9.     }
  10.     else
  11.     {
  12. //this is a simple page which says a log for this record was not found
  13.         openUrl("#View:AlertNoRecord?zc_LoadIn=dialog&zc_Header=false&zc_BdrClr=transparent", "Popup Window");
  14.     }
  15. }
The code is working perfectly, except for:

Now, these are the issues I'm having with my code. 

1.- in some of my forms i have dynamically populated dropdowns, and i am not getting those fields values using getFieldValues(), instead it just gets them as "Null"

2.- Is  there a way i can just save in the Audit Trail just the fields which have changed, and not all the fields submitted?  i can try using old.xxx but as i said, i don't want to write the name of each field manually, is there a way in which i can use something like old.getFieldValues() and do a loop?   Or in another case, how can i compare the 2 strings and decide which part has changed, and then just save that to the new history?

This is the application I'm using it in  https://creator.zoho.com/developer3/boatpartyms/view-embed/Pending_Applications

and this is the trail report  https://creator.zoho.com/developer3/boatpartyms/view-embed/AuditTrailHistory_Report

Any ideas?