I have a custom list in SharePoint 2010 that contains about a 1000 items. An SSIS script writes to this list every night and hence changes the “Modified” and “Modified By” columns at that time for all items. I want to store the value of “Modified” and “Modified By” columns in the list item if the item is updated by a regular user (not the user ID used by SSIS) or an admin. I tried using the “Me” technique to get the user name of the user making a change during the day but each night, as the “Modified By” column is updated, so will be the column using “Me”.
Just to explain the technique I used, I created a column called Me and another column called “Last Modified By”. Both were created as “Single line of text”. I then set the default value of the “Last Modified By” column to be calculated and put in the following formula: =IF(AND(Me<>”DOMAIN\ssis-user”,Me<>”DOMAIN\admin1″,Me<>”DOMAIN\admin2″,Me<>”DOMAIN\admin3″),Me,””). I then deleted the Me column so the “Me” in “Last Modified By” column now points to the default [Me] that gives the username of the person who modified the list item. In this way, I am able to get the user name of the user making an update to the list item, but it will be overwritten by the SSIS script user name overnight. I do not want it to be overwritten unless the user making the change is not the SSIS or one of the admins.
I have access to UI functionality or SharePoint Designer workflows to create this solution, although I would prefer to do this using without workflows if possible. All ideas are welcome.