Rapid “Data Collection” Solutions – Part 1: defining the business problem

  • Article

Often companies need to collect data from their internal users. Many times this need is motivated by an extemporaneous reason, so it is improbable that they have or they can spend money for an application just tailored for helping the process of collecting that specific kind of data.

Nevertheless, the process of collecting data from many users can become difficult and time consuming, especially because the data to be collected have always some “constraints” that need to be enforced a priori or conciliated a posteriori. Moreover, sometime this process has some “constraints”, in the sense that it should be underneath the simple or complex rules of a workflow.

I have seen many approaches to face this need; the easiest to be implemented, but also the worst in terms of usability are the following ones:

· Sending around email or – even worst – Excel files and then manually aggregating the returned values into master Excel files (this approach often involves a huge effort of data reconciliation and aggregation and is very error-prone).

· Sharing an Excel file on a network folder or on a SharePoint web (this approach always creates dissatisfaction on the involved users because the files are often found locked).

A very nice and simple approach consists in using the Access 2007/2010 “Collect Data” functionality; starting from an Access database it is possible to prepare a mail that can be sent to the desired users; the replies are automatically merged into the database. If the collected data must also be published on a web page, the SharePoint/Access integration can be helpful: this integration is already powerful with SharePoint and Access 2007; it will give much more options with the 2010 release of both products.

Another approach consists in preparing and sharing a SharePoint “list” to the users; in this way the data is collected directly through a web page and the use of Access is not strictly required for managing the collected data (not every user can/want to use Access….). Thanks to the SharePoint ability to govern the concurrency, this approach is safe (two users editing the same row at the same time are notified of the conflict and are required to decide which modification can be discarded). Moreover, many customers already know and love the flexibility that SharePoint offers when creating and configuring a custom list. Many useful capabilities were already available in 2007 wave of SharePoint (datasheet views, lookup fields, calculated fields, indexed columns, external data fields…); many other important capability are available with SharePoint 2010 (unique constraints, projected fields, relational integrity options, query throttling,…).

So, the use of the SharePoint “lists” give a perfect answer to the business problem, but…:

· How to involve the users concerned in the data collection process?

· How the process can be simplified in order to ask the minimal set of information to all the concerned users, while maintain a simple view all the related data and the flexibility to produce more complex reports?

· This kind of solution can be implemented on a “hosted SharePoint farm” like BPOS-S?

· Can this be done quickly, possibly without coding?

· What are the limitations when using SharePoint 2007 and what are the new scenarios available when using SharePoint 2010?

In this series of posts I will try to give an answer to these questions showing how the SharePoint Designer workflows and the SharePoint integration with Excel and Access can be effectively used in order to rapidly create a zero-code solution of data collection.

In the next posts I will consider the three different needs of:

  • designing and implementing a data model [see this post];
  • designing and implementing a business process [see this post];
  • designing and implementing a security model [see this post].

Please note that the content of this series of posts is mainly based on my experiences with the SharePoint 2007 (and related) technologies, even if I try to consider what I know and/or I have experimented about the new capabilities of SharePoint and Office 2010 (still in beta 2 at the time of writing). Maybe, in the near future, when I will have a more solid experience with these new releases, I will discover that part of this content will need to be updated or integrated.