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?
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.
- 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.
Insert a Data View Web Part (Laura Rogers has loads of these types of articles, as does Marc Anderson, Bing/Google around for them).
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.