Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

1 Answer 1

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. –  PirateEric 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. –  PirateEric 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
add comment

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.