I'm new to sharepoint and I have been tasked to create a depreciation list using a sharepoint 2007 environment. Does anyone how to create a list that displays a fixed fiscal year budget, and as data is entered into a column field the budget decrements to show how much is left of the budget?

  • 2
    I just want to say this is an interesting, yet creative way of using SharePoint. I love to see ideas like this. – Mike Aug 3 '12 at 20:23
  • 1
    Totally, this is exactly what SharePoint is designed for an is all about. Quite good fun writing my answer :) – James Love Aug 3 '12 at 20:31

I think you could do this in a workflow made in SharePoint Designer.

You would need a second list to store the budget total, and remaining budget (starts off as both the same figure). These would be in two columns (Total Budget, Remaining Budget), and you'd just have a single item with the relevant data in each column.

The workflow would fire when an Item is added to the first list (ie: an item with an associated spent amount), the Workflow would look up the remaining budget figure in the Budget list, and subtract that amount from the budget remaining figure, and finally update it.

To combine the views, you could use a couple of Data View Web Parts on a Web Part Page, also in SharePoint Designer, to bring in the original and current remaining Budget figures from the Budget List in one DVWP, and also list the items that have had associated spend in a separate Data View Web Part.

To summarise:

  • 2 Lists - one for Budget (two columns: Total Budget, Remaining Budget), one for Recorded Spend.
  • Workflow built around Recorded Spend list, fires on Item Added, looks up Remaining Budget column from Budget list, subtracts amount in the new item in Recorded Spend list, and updates the figure in Remaining Budget list.
  • Two Data View Web Parts on a Web Part Page, one to pull data from Budget list, one to show all recorded spend items.

Create a workflow for SharePoint Designer 2007

Insert a Data View Web Part (Laura Rogers has loads of these types of articles, as does Marc Anderson, Bing/Google around for them).

Have fun!

| improve this answer | |
  • 2
    That gives you a good starting point, you could grow it out by adding different budget amounts into the Budget list and having an option in the Recorded Spent list as to what budget it pertains to. Then the workflow updates the appropriate budget. This would be handy if your department has several pools within their budget where funds are drawn from. – Eric Alexander Aug 3 '12 at 20:51
  • James the advice worked great but im having trouble getting the workflow to calculate additional records. once the work flow run on any new items the work flow returns an "Error Occured". What could I be doing wrong? – Undreed Buissereth Aug 8 '12 at 21:33

You may want to consider using BCS/BDS to surface the stored information in SQL tables, then write some 'simple' T-SQL stored procedures to update the calculations via a trigger or schedule. Confident of the solution for 2010, but I haven't used BDS for 2007.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.