Custom SharePoint Application with Mobile, Offline Requirements

Technical Requirements:  needed to design a web app  with the following Technical Requirements: 

1) A Web App that can be used to perform CRUD operations on data, which could consist of several tables with parent-child relationships and some independent tables such as Master tables that drive data types used in the parent-child tables OR, the same hierarchy could be replicated to SharePoint lists. That was one decision to be made as we though through the design (Decision #1).

2) The app must support offline editing of data through iPads, because many aspects of data entry are done in places with no connectivity to the Internet and the iPad camera needs to be leveraged for taking pictures and uploading them as attachments to data records. This required us to make a decision on which third party app to use for offline support or build our own mobile app (Decision #2).

3) Users must be able to generate Reports and Dashboards. We needed to determine the Reporting platform for this product (Decision #3).


4) Users must be able to set Alerts on the data.

5) The solution needs to be delivered quickly (4-8 weeks).

6) The data volume is not expected to be very large ( probably range between few thousand to ten thousand records).

Solutions Considered: 

1) ASP.NET Web App with a Relational Database: One way is to design a relational database and an ASP.NET  Web App to perform CRUD operations on it. This would have meant us reinventing the wheel for requirements such as Alerting, Reporting etc., besides ofcourse implementing standard features such as Authentication, Authorization etc. that any asp.net app needs would still have to be done. Given that this is a SharePoint shop, going this route seemed like lots of custom development and might not work in the short time we had for this project. Also, had we gone this route, we would still have needed to either develop a native iPad app or develop a hybrid app for this Web App.

 

2) Third-Party Web Parts on SharePoint: We did evaluate Third Party web parts such as Quest Web Parts to quickly create this app. Using this approach, we could still take advantage of all the OOTB features SharePoint has to offer and also take advantage of native iPad apps such as Shareplus available on the App Store for Offline Editing of SharePoint data. This would have however, required us to ramp up on customization of the Quest Web Part, not to mention extra licensing costs for the server licensing. 
Since SharePoint 2010 makes it easier to develop Visual Web Parts, and the users were already familiar with SharePoint, we decided to build our own Visual Web Part for this solution and adopt Solution 3) explained below.

3) SharePoint Visual Web Part and Lists: The Visual Web Part would be the interface and instead of using a relational database as the back end, we decided to use SharePoint lists instead. The lists would have a relational design with lookup columns for joins.


The SharePoint lists would then give us all the OOTB features SharePoint already provides such as Alerts, Export to Excel, Metadata Search etc.

One other advantage of using SharePoint lists for this project is that we could solve the offline editing problem by using apps already available on the App Store, such as SharePlus, which do a good job of supporting offline editing of Lists. It would have been harder to solve this problem if we had built a custom relational database.

We decided to leverage products including Dundas charts for SharePoint, Excel Services and Performance Point for the Reports and Dashboards.


Disadvantages: This solution does have some disadvantages, first of all, even though SharePoint 2010 does allow some amount of relational database power, such as allowing cascading/restricted deletes, using SPQuery.Joins or the use of SharePoint to LINQ to perform joins, it still cannot be looked upon as a full fledged relational database solution. We will need to live with the consequences of this decison throughout the life of this product. As we work on enhancing it, set up reporting etc., we will always face challenges with joining and querying different lists.
Another problem is that since we have two interfaces, one is the visual webpart interface, and the other is the native iPad app for offline use, the user experience becomes dramatically different as a user switches between the desktop browser to the mobile app. This is a disadvantage we would have to live with till we have time to build a better mobile solution or find one already available off the shelf. Please note we have recently also heard of an HTML 5 solution called Mobile Entree, which seems better as a soluton since sharePlus is a native iPad app and we would have to train our users on our SharePoint backend List Data Model. With an HTML5 solution we would retain the web app's user experience to some extent. We have not evaluated this solution at the time of writing this blog.