Sharepoint best practices

Posted on 2014-08-29
Last Modified: 2014-09-17
We got thrown into sharepoint as part of a company reorganization.  It was introduced as a filesharing solution.  We knew of it but not all the ins and outs and to be honest we have done the best we could with what we knew and what we could read to get it up and functional as fast as possible.

Now we have an issue we cant just work around.

We have 14 offices that share 28 spreadsheets.  They were able to make this work before with some options in excel and the fact that they were all on the same network.  However, now these spreadsheets are hosted in sharepoint and there is no way to co-author them.  I mean co-author in the literal sense of the word, multiple people entering data simultaneously.   The It department had very little knowledge of what they were actually doing until today and its too far to turn back.

So now we need to build a better solution quickly.  What we really have is about 6 true datapoints.  However, what they have done is build a spreadsheet for with a sheet for each month and a column for each day to track income for each line item for each day.

I was looking for a better way and started looking at custom lists and while they will give us what we need in the end what we really need is probably and info path form to collect the data and then something to report on the lists.  I could create an access database pretty quick but if we are really trying to get them off of file based solutions then this is really not the right answer.

Just looking for a good idea to move forward.  BTW we are tracking rental income. So a record would look like
company, location, start date, end date, 8/1, 8/2, 8/3.....
Question by:john15nlt
    LVL 9

    Assisted Solution

    As you are familiar with Access;
    The equivalent of an access table is a list. Unfortunately list are not relational.  Thus in Access you can have a parent child relationship and delete a parent and the children will automatically be deleted.  In a SharePoint list you can have a related list to be able to retrieve information but it does not work the same.  Although if you add workflows you can programmatically do it. Ultimately what this means is that you will most likely build a single list (table) that contains all the fields that you need.  You will also need to add others to control the form or processing. For example; using a list field (lets say status) you can control what your form looks like (using InfoPath) based on the status (New, In Process, Returned, etc). You can also build Calculated fields that use other fields to get their value. Once you have your data entry form and list you can add workflows to do more.  You can also build different views of the data and even control what views people see based on their department (you can put different people into SharePoint Groups and manage them that way).

    I would start by making a list with all the fields that are in you spreadsheet. Set the datatype, validations and Calculated fields.  Then work on how you would like the data entered (InfoPath).  How many views (forms) do you need (ie. New Entry Form, Update Form, Completed, etc). What fields should be visible and editable on each. Then determine what you need to report. Does it need to be grouped? Restrict view access to specific departments, etc.

    These kind of projects seem impossible at first, but if you take it apart piece by piece you will have a solution.
    I hope this helps some.

    Good Luck!
    LVL 33

    Expert Comment

    I think InfoPath has been deprecated so I would not build a new application using it.  Also, unless you have the latest version of SharePoint and Office 2013 or O365, I wouldn't build an Access web form since A2010 and earlier web forms have also been deprecated.

    If you have only old versions of SharePoint and/or Access, go with a list.  You can link the list to Excel or Access for reporting.
    LVL 9

    Accepted Solution


    As I reviewed your description of the problem and your experience with Access; It occured to me that when you have a spreadsheet that has more than one person editing it at the same time, this should be changed to an Access database.  You could initially create an access database to collect and update the data and then display the results in an Excel Web Part in SharePoint.  Once you have that process developed you can look at replacing the Access Database with SharePoint list, InfoPath Forms and workflows.

    So you could take a phased approach; Starting with a partial SharePoint solution and migrating to a full SharePoint solution as you gain more user input and requirements. This would allow you to use your experience in Access to build a strong back end and refine the requirements for the front end (SharePoint Groups, Permissions and different views).

    Finally, What version of SharePoint are you using? Do you know your SharePoint Architecture (1 Tier, 2 Tier or 3 Tier)? Do you have a separate App Server?
    LVL 1

    Author Closing Comment

    I think the overall consensus is this needs to be a database, and since we have sql server available I would lean in that direction rather than create an access database that would eventually need to be migrated to sql.
    I believe we currently have 2 tier architecture with the app and webserver running on the same box and database server on its own box.

    I appreciate all your help in this matter.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now