Forum Discussion
MarkShnier__You
Qrew Legend
3 years agoWhat I have done in the past is to create a child audit trail table and when any edits are made to the record, trigger a Webhook to copy the old and new values into the child table. Of course in most cases very few of the fields will be changed meaning that the old value and the new values will be the same.
So then, say each night, we would run a pipeline to delete all the records where there was no change to the particular field. ie, the old and new values were the same.
On any reports of the audit trail we would simply filter out where the old value and the new value is the same so from the users' perspective they would not be bothered by that extra clutter awaiting the nightly purge.
Message Header QUICKBASE-ACTION API_ImportFromCSV
Webhook #1 called Logger - multi field when added or modified of just logged fields changing
<qdbapi>
<usertoken>bxv4tx_bjix_8csgf2bk9smkgc5igppzvgv53r</usertoken>
<records_csv>
%RepeatOn%
"[Last Modified By]","Client Name","[old.Client Name]", "[Client Name]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Site Name","[old.Site Name]", "[Site Name]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Contact Name","[old.Contact Name]", "[Contact Name]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Contract Name","[old.Contract Name]", "[Contract Name]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Contract Type","[old.Contract Type]", "[Contract Type]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Industry","[old.Industry]", "[Industry]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Primary Project Manager","[old.Primary Project Manager]", "[Primary Project Manager]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Secondary Project Manager","[old.Secondary Project Manager]", "[Secondary Project Manager]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Large Contract Approver","[old.Large Contract Approver]", "[Large Contract Approver]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Estimator","[old.Senior Estimator]", "[Senior Estimator]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Projected Start Date","[old.Projected Start Date (not in use)]", "[Projected Start Date (not in use)]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Projected End Date","[old.Projected End Date (not in use)]", "[Projected End Date (not in use)]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Contact Status","[old.Contract Status]", "[Contract Status]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Award Date","[old.Award Date]", "[Award Date]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Customer PO","[old.Customer PO]", "[Customer PO]", "CO","[Record ID#]","[Record ID#]"
%RepeatOff%
</records_csv>
<clist>6.7.8.9.10.11.14</clist>
</qdbapi>
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
So then, say each night, we would run a pipeline to delete all the records where there was no change to the particular field. ie, the old and new values were the same.
On any reports of the audit trail we would simply filter out where the old value and the new value is the same so from the users' perspective they would not be bothered by that extra clutter awaiting the nightly purge.
Message Header QUICKBASE-ACTION API_ImportFromCSV
Webhook #1 called Logger - multi field when added or modified of just logged fields changing
<qdbapi>
<usertoken>bxv4tx_bjix_8csgf2bk9smkgc5igppzvgv53r</usertoken>
<records_csv>
%RepeatOn%
"[Last Modified By]","Client Name","[old.Client Name]", "[Client Name]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Site Name","[old.Site Name]", "[Site Name]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Contact Name","[old.Contact Name]", "[Contact Name]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Contract Name","[old.Contract Name]", "[Contract Name]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Contract Type","[old.Contract Type]", "[Contract Type]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Industry","[old.Industry]", "[Industry]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Primary Project Manager","[old.Primary Project Manager]", "[Primary Project Manager]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Secondary Project Manager","[old.Secondary Project Manager]", "[Secondary Project Manager]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Large Contract Approver","[old.Large Contract Approver]", "[Large Contract Approver]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Estimator","[old.Senior Estimator]", "[Senior Estimator]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Projected Start Date","[old.Projected Start Date (not in use)]", "[Projected Start Date (not in use)]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Projected End Date","[old.Projected End Date (not in use)]", "[Projected End Date (not in use)]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Contact Status","[old.Contract Status]", "[Contract Status]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Award Date","[old.Award Date]", "[Award Date]", "CO","[Record ID#]","[Record ID#]"
"[Last Modified By]","Customer PO","[old.Customer PO]", "[Customer PO]", "CO","[Record ID#]","[Record ID#]"
%RepeatOff%
</records_csv>
<clist>6.7.8.9.10.11.14</clist>
</qdbapi>
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
ChaohanLi
3 years agoQrew Member
Hello Mark,
Thank you for your information.
What I currently have in mind is this:
I currently have a few tables, then I created a child table that would log any changes made to the reports in these tables. In the log child table, I have a field named "Affected Field". Every webhook from each table should capture which field is being modified and through that field name to "Affected Field" in the log table. I am just not sure will webhook be able to do that? I don't think your solution will be a suitable one in this situation.
------------------------------
Chaohan Li
------------------------------
Thank you for your information.
What I currently have in mind is this:
I currently have a few tables, then I created a child table that would log any changes made to the reports in these tables. In the log child table, I have a field named "Affected Field". Every webhook from each table should capture which field is being modified and through that field name to "Affected Field" in the log table. I am just not sure will webhook be able to do that? I don't think your solution will be a suitable one in this situation.
------------------------------
Chaohan Li
------------------------------
- MarkShnier__You3 years ago
Qrew Legend
In this line here
"[Last Modified By]","Client Name","[old.Client Name]", "[Client Name]", "CO","[Record ID#]","[Record ID#]"
It will record who last modified, then the words "Client Name" which is the field affected, and then its old value and its new value.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------- ChaohanLi3 years agoQrew MemberI understand what you mean Mark, but by doing things your way, I think I would have to make hundreds of fields in the log table, and list every possible fields in the webhook. Is there simpler way, say using API_AddRecord? For example, with xml,
<field fid="15">[Record ID#]</field>
<field fid="16">%dbName%</field>
<field fid="17"> </field>
<field fid="10"> </field>
<field fid="11"> </field>
<field fid="12">%curuser%</field>
field 17 is where to through in the field name, field 10 for old value, and field 11 for new value. (Yet don't know which field is going to be modified by the user, so it can be any field, and there are at least 50 fields).
Thanks for your help.
------------------------------
Chaohan Li
------------------------------- MarkShnier__You3 years ago
Qrew Legend
Sorry but that is the only way I know.
I suggest that you use excel or word or some other tool to create the body of the webhook where you can use copy and paste.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------