Make "Modified By" and "Modified" specific to a column or set of columns / use SPListItem.SystemUpdate

Question

I have queries that I will schedule to run daily to update a sharepoint list linked to an access database. The problem is any time these queries update the list, it logs modified by and modified date as myself and whenever the auto updates run. I want it to ignore when I update rows based on this automatic job though, because frontend users will enter in their comments in a "User Research" field, but my update queries will overwrite the modified by and modified date. We want to track who last modified the specific column in our sharepoint list "User Research". The solutions I have come up with are:

1. Create a new column that acts like the default "Modified By" and "Modified" columns in Sharepoint, but only applies to specific columns I select. This would show me who and when columns X, Y and Z were modified, rather than the entire record. Before someone says versioning as the solution, I worked around with that and not quite what I want. It needs to be two columns in my already existing list, who modified column x, and when they modified column x.

2. I could figure out how to run my update queries as a systemupdate rather than just a straight update which apparently updates the modified and modified by columns. I have seen the SpListItem.SystemUpdate method, but I'm not sure how to specify this in access.

I don't know how to implement any of the theoretical solutions I have listed though. Assistance is appreciated.

Answer 1

1. Add ModifiedByUser yes/no field, Updated and UpdatedBy fields (acts like the default "Modified By" and "Modified" fields), X1, Y1, Z1 fields to the List.
2. Set ShowInDisplayForm, ShownInNewForm, ShownInEditForm to false for Updated, UpdatedBy, X1, Y1, Z1 or Turn on content type management on the list. Make Updated, UpdatedBy, X1, Y1, Z1 hidden for the main content type, it will not be shown in Display/New/Edit form but available in views.
3. Set ShowInDisplayForm for ModifiedByUser to false.
4. Using CSR/JSLink hide ModifiedByUser field in New/Edit form and set this checkbox to checked.
5. Create workflow on create/change list item with following conditions:

if (ModifiedByUser) { if (X != X1 or Y != Y1 or Z != Z1) { set Updated = Modified; set UpdatedBy = ModifiedBy; } } set X1 = X; set Y1 = Y; set Z1 = Z;