1

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.

1

Here is how I would approach this. (more likely choosing the workflow route)

Solution 1 : Create another column called XYZ or whatever "Manual Edit By:"...name is irrelevant.

Create a basic "Editing" triggered workflow.

make a condition that says IF (Modified By is not equal to AUTOMATED ACCOUNT) then -> Update the "Manual Edit By:" column to write the Modified by Users Name into the field.

This will make it so that only when an actual user edits the item it will write to the XYZ / Manual Edit Field.

I hope this makes sense to you. I have done this before and it works great ;) Let me know if I need to clarify more.

Solution 2: You could have a powershell script make a copy of the list and the last modified users prior to the SSIS script running, then after the SSIS script runs, overwrite the list with the "backed up" information via a mapping function triggered by the server to run from an event.

  • 1
    Thank you very much SP-Kyle. I understand what you mean in the Editing triggered workflow. :-) I was hoping to do this without a workflow if possible but looks like that might be the easiest way to do this. We already have a workflow on the list that gets triggered on each update so this will be an additional one. – Xperto Jul 25 at 16:34
  • I will try the workflow and post the results here. – Xperto Jul 25 at 16:34
  • If you already have a workflow on the list then just put this in that workflow as a step, no need to add an additional workflow if you don't want to... just stack it at the very top.... – SP-Kyle Jul 25 at 17:37
0

As I understand the problem, there are two solutions for this problem.

Solution 1: In case, if you dont need to track when the item got updated and who updated it and you are using SharePoint Farm solution to update the list item, then instead of using item.Update() use the method item.SystemUpdate(). The SystemUpdate method will update the item metadata without updating the context i.e. who updated it, and when it got update. This means it will retain the timestamp and editor information from the last modified user. Drawback of this is, you need to run code from the from the Site Collection admin account (or Application Pool account).

Solution 2: In case you want to track, when and what updated on item simply enable versioning on your list. So whenever the item get updated, a new version is created. You can enable moderation on top of it as well, so that approvers get notified when the content gets updated, review it and take action as required.

  • Thank you very much @ThinkB4Code. I do not have access Site Collection Admin account so can't follow Solution 1. I do not think versioning would work in my case because I really want the user name of the user making the change (other than script or admins) to be held in the item until someone else changes the same item, but I do not want the user name to be overwritten in the Modified column when the nightly script makes the change. With versioning, each night a new version will get created when the script changes the item, wiping out the user name of the user who made the previous change – Xperto Jul 25 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.