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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
dinnertanAuthor Commented:
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?
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dinnertanAuthor Commented:
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!
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2003

From novice to tech pro — start learning today.