Application upgrade path from WSS 3.0 - need relational entities

We are a 20-man marketing agency looking for a way to improve the way we track our projects and billings. At the moment, our projects are tracked in a WSS 3.0 list with about 15 fields. Fields include:

Project name
Project code
Start Date
Cost
Client Name
Invoice Status
Work status
Type of deliverable
Project Manager
Any other remarks
Path to network drive where project files are kept

From this simple list, we rely on a few different views for our daily work:

1) MY ACTIVE JOBS: Show records where [Project Manager] is "me" AND Job is NOT completed
2) JOBS THAT ARE READY TO BILL: Show records where [invoice status] is "proceed to invoice"
3) JOBS THAT ARE STALLED: Show records where [start date] is more than 6 months
4) AS A KNOWLEDGE BASE: Show all previous projects where [type of deliverable] = "event invitation letter" (e.g.)

Here's my question:

1) We frequently get requests where our clients will say issue a single purchase order for (say) $2000. We then have to work off that purchase order over several jobs. However, while I can create a list to track purchase orders, I can't assign projects under a purchase order. Using a lookup field in the PROJECT list to assign it to a purchase order is not feasible since we already have thousands of purchase orders.

In addition, the sharepoint list is getting large. We already have a couple of thousand projects, with about 200 active projects at any one time.

What we need is the option to add the "Purchase Order" object, and grow from there to include more entities such as:

It sounds as though you need a way to archive or at least filter out completed projects and spent purchase orders. That way your lists would be more manageable, and you could use a lookup on open purchase orders. Perhaps you could also filter the POs by client to make the list smaller

Either SharePoint version will allow you to do limited relational operations such as Master -> Child listviews. But neither is going to give you a full relational database capability. You might want to look at a different solution, such as a custom web application, which you can host in SharePoint.

The first is to host it in IIS and present it in your SharePoint site using a Web Part page with a Page Viewer web part on it. Admittedly this is not much of an integration, but it does give your users easy access to the application, and makes building database applications much easier. I normally build these pages with no navigation or banner and a fixed size so that the application fits into the window. The advantage that this gives you is that you can develop the application visually with no constraints. The downside is that you have to control access to the application separately from the SharePoint environment.

Another way to do this is to install the application in the _layouts folder on the server. If you do this, you can use the SharePoint authentication provider and achieve tighter integration, while still retaining the visual aspects of ASP.NET web database development.

Some people will build applications in component form (example: a gridview-based database interface) and save it as a User Control. Then, using SmartPart, they present it on a SharePoint page as a web part.

There are other ways to build relational capabilities in SharePoint using a Data Query or Data Vew Web part. EnduserSharePoint.com has some great articles on this, but it does require some XSLT programming.

Finally, there are lots of third-party packages that can provide this type of capability. Have a look at BrightWorks, CorasWorks, Bamboo Solutions, and Kwikcom for ideas.

FYI, there are a set of 40 site templates for WSS 3.0 that are available as a free download from Microsoft. (http://technet.microsoft.com/en-us/windowsserver/sharepoint/bb407286.aspx). There are two templates in the package that might be of use. One is for budgeting and tracking multiple projects and the other is for tracking specific projects. You might be able to use these or some of the components anyway to achieve what you want. It's worth a shot before you go and spend real money on a third-party solution.

I strongly recommend that you install these on a development server or at least in a separate site collection and test them before you deploy them to your production site collection. You might also consider bringing in some outside help to solve this. This doesn't have to be a hgh-end consultant ($$$). You can get technical assistance at a reasonable rate for small jobs from freelancers or one of the other web sites of that type.

Good luck!

0

Featured Post

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Note: There are two main ways to deploy InfoPath forms: Server-side and directly through the SharePoint site. Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …

You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes.
Assigning simple products to configurable: We assigned simple products…