0

My experience with SharePoint is not really sophisticated so please bear with me.

What I want to do is setting up a live-sync between a SharePoint list and a database table. This live-sync should be updating both ways, so if I change something in the list it should write back the changes into the database table and vice versa.

In addition the SharePoint list shouldn't show every column of the database table, but just 3 or 4 of them (like if I'd do a SELECT on specific columns and not a SELECT *).

The actual use-case for this would be a custom dictionary that I made. People can submit their own suggestions, but as they need to be approved first I had to think of something to flag those submissions. I decided to go with a "bit" column in MSSQL that is either "0" for not approved yet or "1" for approved.

As I don't want to have anyone screwing around in the actual database, I wanted to go for that SharePoint list. The guys who approve suggestions just get to see the important columns (original word, translated word, status) and can work it from there. If they decide to rephrase a suggestion or correct a typo or something they can just do it on the fly and after they've finished they set the status to 1 and everything is back in the Database and ready to go instantly (as the DB is queryied by an AJAX Livesearch).

Is such a thing possible with SharePoint? I'm happy over any suggestion you guys can offer me, so fire away ;-)

best regards, daZza

Improve this question

2 Answers 2

Reset to default
1

daZza,

Yes Business Connectivity Services is the key here, and you have set up External Content Type and External List that is perfect solution to sync with SQL Server table!

Now what you can do is create a View in your database and write a SQL Query to provide a column as StatusForSharePoint or something and using CASES in T-SQL send information like "Translation approved" or "Pending Approval", something like this:

SELECT CASE WHEN Status = 0 THEN 'Pending Approval' WHEN Status = 1 THEN 'Translation Approved' END FROM TableName

Now in your external content type create a Read List operation using this View and rest of the forms Create/Delete/Update with the actual table!

Let me know if you stuck somewhere! Hope this helps...

Improve this answer
2
  • Sounds good. Thanks for that suggestion. I also thought about using a calculated column, but I think the SQL Solution might be easier to realize / maintain. Also thanks for showing me the CASE statement in SQL :D Actually didn't know something like that existed and it seems quite useful in many situations :)
    – daZza
    Oct 5, 2012 at 14:27
  • daZza, no problem - here to help the community.. Please mark as answer if that helped!
    – Arsalan Adam Khatri
    Oct 5, 2012 at 15:18
0

I think you can use SharePoint timer job for this.

Improve this answer
1
  • By now I seem to have actually managed to get an external list done which is syncing with the database and found an option to create a view of the list in which I could select which colums I want to have shown in the list. So most of my problems are solved now. However, one problem still remains. My "status" column is, as I said earlier, either "0" or "1". Is it possible to get an if clause or something else into that list that converts "1" to "translation approved" and "0" to "pending approval"?
    – daZza
    Oct 5, 2012 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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