As you are probably aware, in SharePoint Server 2013 we can utilise external data thanks to Business Connectivity Services (BCS).
We can create External Content Types in SharePoint Designer, define what we want to do with our data (all CRUD options available) and display that data in an External List.
One would say it is brilliant. We can use SharePoint to CRUD data in another LOB systems e.g. another SQL database. It is good but not perfect. If you ever worked with SharePoint External List, you had noticed that we cannot utilise SharePoint core functionality available in native SharePoint lists. These are the features which are currently not supported:
- Per-location view settings
- Metadata navigation settings
- Custom columns
- IMP settings
- Export to Excel (this may work arguably)
Yes, instead of an External List you can just create External Columns an use them in your native SP lists to have above features available. This however was not an option for me as lookup columns will not take into account any new items that have been created in an external system.
I have decided to replicate my External List to a native SharePoint list and create a PowerShell script to automate the process. Now, many scripts I have looked at do the similar thing but still it was not quite what I was looking for. The scripts I found on the internet were exporting External List into a CSV file and importing the values into a native SP List. One script was checking if an item exists in a SP List and if not, it created a new one. Some of the scripts were also updating ALL items instead of the ones that have changed.
My requirements were:
1. Export an External List into a CSV file (always up to date info)
2. Import a CSV file into a SP Native List
a) Check if an item exists, if not - create it
b) Check if an update is needed, if yes- update JUST the items that changed (needed for
3. Run the script on a scheduled basis for automation
4. Utilise SharePoint functionality (alerts, metadata etc. in a Native List)
Here is my script to accomplish points 1 and 2 above
Thank you Dan Christian for ideas:
You can accomplish Point 3 from my requirements with a Windows Task Scheduler and run this script how often you want to keep your External and Native SharePoint lists in sync.
Once in your Native SharePoint list, you may add metadata and other columns, version control, alerts etc. you know what you can do.
You will soon start to notice advantages of this approach as thanks to it, you can work on external data in a much more flexible way.