I've been researching something that has yet to yield a clear understanding on how to proceed using SharePoint Online. And, if possible, do it without code (or as little as possible).

I have a list on a separate SharePoint site that I want to use as a data source for another list on another SharePoint site. The reason is that I want to protect the data source from being modified by users.

I've read that I can make the source list as a template, but doesn't that make a one-time copy of the data, too? If this is the case, I need to be able to update the source periodically and, of course, have the other list reflect those changes accordingly.

How should I proceed, please?


Yes, you are correct that saving a list (with data) as a template makes a one time copy of the original list.

If you need the copy list to reflect updates to the source list, I don't think there's any way you are going to get that done without some kind of "code" solution, although my suggestion involves Flow, so I don't know how much you consider Flow to be "code".

I've had to do a similar thing in the past (keep a related copy of an item in a different list updated according to the changes on a source item), and although I was working in SP 2013 using 2013 workflows, I'm sure the same idea could be implemented in SP Online using Flow.

Here's the general idea:

  • Add a number (integer) column to the source list called "Copy Item ID". Perhaps have it default to zero.
  • Create a workflow/Flow that runs on both item added and item updated
  • When the workflow runs, check the value of the "Copy Item ID" field. If it's zero (or blank), you know you haven't created the copy in the other list yet, so just go ahead and create the copy in the other list, but make sure to get that copied item's list ID, and save that in the source item's "Copy Item ID" field.
  • If the value of "Copy Item ID" is not zero (or not blank), you know you have created the copy already, so use the value of "Copy Item ID" to get the copied list item in the copy list, and update it. You won't know which field or fields were updated, so you'll have to update all the fields in the copied item to the values of the source item, essentially syncing it with the source item.

If you are expecting users to make changes to the items in the copy list, and you want to somehow preserve those changes while you make your updates from the source list, you are going to have to come up with a bunch of additional logic.

If you want to only update the fields that were changed in the source item, that can be done, but it involves creating hidden fields on the source item that correspond with every visible field to store the previous value of that field, so when the workflow runs you can check the current field value against the previous field value, and thus know which fields actually changed. Do-able, but a lot of work.

But really, the simpler way to prevent users from making changes to a list is to break permission inheritance on the list, make it read-only to most users, and have only a specific group have contribute permissions.

| improve this answer | |
  • Thanks Dylan. As you pointed out, it does sound like a lot of work to keep things updated. Fortunately, I have two things going for me: 1) I have a project ID, which should work as a substitute for the Copy Item ID you mentioned...that is, if I plan on keeping the two lists separate. 2) Users won't be updating anything I give them to use; this is only for them to view the status of projects. – rizingfenix Apr 10 at 17:16
  • I was thinking about giving users the ability to add their own comments to each record, which I presume can be saved off to a separate list just for that purpose. So if I associate those comments with the project ID, I should be able to call that up in a form to view the rest of the data. Do you have a suggestion on how that can be implemented? Based on your feedback, I'm now rethinking where the original source of data should be stored, and as you suggested, breaking the permissions so they can't change anything. – rizingfenix Apr 10 at 17:17
  • Well, that might work if your Project ID is guaranteed to be unique. In my example though, the "Copy Item ID" is meant to store the built-in SharePoint List Item ID of the item in the copy list. That is very reliable - SharePoint automatically generates those IDs and ensures that they are unique. As far as linking comments in another list back to your original items, if you set up a lookup field in the comment list that is a lookup to the source list, that should work to make the connection. You would have to choose which item you were commenting on when adding the comment. – Dylan Cristy Apr 10 at 18:48
  • Excellent. Thanks again, Dylan! – rizingfenix Apr 10 at 19:13

For your this scenario, the best solution would be first time create a replica of the source list to target list using the any of the below approaches :

  1. Using source list template with content or
  2. Migrate the source list to target using the migration tools like ShareGate, Metalogix or Microsoft migration tool. Or
  3. Using the PnP Provisioning template or
  4. Using the export list as excel in the source and create the list in target using the import excel.

The above any of the steps you can follow to replicate the source list in target for one time.

Now the question comes for the regular update, how can we do?

Well, this can be done in many ways like need develop a Microsoft Flow or SharePoint workflow or Nintex workflow (or even powershell windows task scheduler) which should be attached to the source list... then going forward for any new items, the workflow will automatically add the item to the target list.

| improve this answer | |
  • Much appreciated, Habibur. – rizingfenix Apr 13 at 13:56

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.