SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Hullo,

My environment is Publishing site in Sharepoint 2010. I have a library named Invoices with the following structure

+-----------+-----------+--------+
|  Invoice  | PO Number | Amount |
+-----------+-----------+--------+
| Invoice 1 |      1234 |     25 |
| Invoice 2 |      1234 |     25 |
| Invoice 3 |      6789 |     50 |
| Invoice 4 |      6789 |     50 |
+-----------+-----------+--------+

I also have a list where PO is looked up from

+-----------+-------+----------+
| PO Number | Total | Reminder |
+-----------+-------+----------+
|      1234 |   100 | ?        |
|      6789 |   200 | ?        |
+-----------+-------+----------+

To calculate the 'Reminder' I need to sum the 'Amount' column in the Invoices library for each PO and then subtract from Total. i.e it needs to be something like

Reminder = Total - (SUM of Amount where PO Number=1234 from Invoices)

I'm not sure how to enable this. Calculated column doesn't seem to work. Please help me achieve this.

share|improve this question

I'd probably use a workflow on your first list to populate a field in your second list. So as invoices are added, the workflow would update a recieved field in the second list. Then in that second list, you can do a calculated colum Total - Received to get your remainder.

share|improve this answer
    
Sorry if I'm asking a basic question, but how do I keep adding the amount to the 'Received' column? What formula should I use? – PurpleShadow Nov 2 '12 at 20:47
    
You'd first lookup to the PO list where the current item PO equals the PO list PO number field and store that in a variable. Then you'd do a math operation to add the Invoice total of the current item to that variable and store it in a new variable. Then you'd do an update list action on the PO list and set the received column to that second variable. – Eric Alexander Nov 2 '12 at 20:55
    
Each invoice entry will update the associated PO and once all the invoices are received, the remainder should total 0. – Eric Alexander Nov 2 '12 at 20:56
    
@PirateEric And what to do if I need to edit the value in Invoices list? Thanks – Guilherme J Santos Nov 29 '13 at 14:00

The problem with workflow is that when a record in first list is removed the total in second list won't be updated. Because workflow doesn't run on delete action!

share|improve this answer

The issue with deletions can be solved by creating a custom permission level that prevents contributors from deleting items and including an explicit choice (checkbox) to indicate a request for deletion (or a workflow that runs on a zero amount). An additional problem exists if an item is updated, rather than added or deleted. In this case, a method to capture the original saved amount, compare it to the newly saved amount and process the adjustment may be required.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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