Application upgrade path from WSS 3.0 - need relational entities

Posted on 2010-08-18
Last Modified: 2012-05-10
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
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:

Client Companies
Client Contacts
Vendor Contacts

Question by:dinnertan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Expert Comment

ID: 33504589
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.

Hope this helps.

Author Comment

ID: 33508463
I don't understand what you mean by "host a custom web app in SharePoint". Isn't that just putting it on IIS? How does SharePoint get involved? Is it only for the authentication?

Accepted Solution

EagleUK earned 500 total points
ID: 33513344
There are several ways to do this.

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. 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.

Does this help?

Author Comment

ID: 33538857
Yes it definitely does. We have no programmers here to help with integration, so we're more interested in the ability to customise fields. The third party packages are worth a try. Thank you!

Expert Comment

ID: 33539395
FYI, there are a set of 40 site templates for WSS 3.0 that are available as a free download from Microsoft. ( 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!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

730 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