Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Application upgrade path from WSS 3.0 - need relational entities

Posted on 2010-08-18
Medium Priority
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
  • 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 2000 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. 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.

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Microsoft has released remote PowerShell capabilities to all commercial Office 365 customers. So you can be controlled via PowerShell and not from the Office 365 admin center Download Windows PowerShell Module for Lync Online http://www.micros…
When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

578 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