Auto populate values in a sharepoint list based on another list

Question

I have two Lists in Sharepoint 2010. They are PO master and Invoice Master.
The columns in PO Master are

• PO number
• PO value
• Invoiced Amount
• Balance.

The columns in Invoice Master are

• PO Number
• Invoiced amount.

Both the lists have a common column, PO Number.

I have to auto populate the Invoiced Amount which I enter in Invoice Master to the Invoiced amount in PO master (to corresponding PO Number) and Balance= PO Value-Invoiced amount.

Answer 1

This doesn't seem strictly doable with out of the box SharePoint features.

As per my comment, I don't get what is the final effect that you want to accomplish

you need to insert the invoiced amount on the PO Master and DISPLAY it on the Invoice Master, insert on Invoice master and display on PO master or do you need to support insert/edit of the value on both ends of the relation?

Some of the requirement above could be achieved by using a "Multi-column" lookup. Ootb SharePoint doesn't provide such functionality, so you would have to resort to some third party solutions, like http://spfields.codeplex.com/ or something similar.

This won't still resolve the second problem - the calculated column. If the change in the calculated value is triggered by a change of the value in the other list (Invoice Master) your best hope is to implement a custom event receiver on the list: when a value change on one of the list, use the PO Number to find the related item on the other list and update your fields. Notice that you may need to disable event firing while performing the update to avoid an infinite cycle of event receiver triggering.

I will try to elaborate more if needed, please fell free to leave a notice here or on the original question.

Answer 2

You can use SharePoint 2010 Autocomplete Lookup Field developed by me which is available on codeplex. It implements a custom field type that adds autocomplete functionality to the lookup.

Answer 3

Use a lookup field. The list item will automatically update when the reference item changes.