Calculating Totals on main doc from documents in embedded view
Posted on 2007-08-07
I have 3 documents in this tracking database. The 1st document holds a small amount of information, mainly a product name and a total dollar value. There is a unique id -- held in a text field value. The 2nd document (not a response type -- document type) is created, when a user chooses one of the main docs, and enters information in support of the 1st doc. So, several field values from the 1st doc are automatically captured into the 2nd doc (by form field inheritance) and new values are added, and specifically an Estimated Total field is defined. The 3rd form is also a document (not a response) and captures related data automatically, and also adds new data. In this document, there will be actual totals. The 3rd document will be displayed via an embedded view, in the 3nd document. By the way, each document will hold the DocumentUniqueID of the very first document -- let's call doc 1 the Parent (will have a computed field called ParentID with the following formula:
And the 2nd document will have a computed text field grabbing the value of ParentID, and the 3rd document will have a computed text field grabbing the same value. Doc 2 can only be created by selecting Doc 1, and some fields will be automatically carried to doc 2. Doc 3 can only be created by selecting Doc 2 -- also grabbing some values automatically.
Doc 2 has a field "Expected Total" -- this is inputted -- a guess of what might be expected. There is also a field called "Actual Totals" and is computed with a value of 0. I'd like this field updated, based on the totals of all of Doc 3 totals in a field called "Actuals". Let's say there are 10 documents in the embedded view that is displayed in Doc 2. Each document in the embedded view holds 100 dollars in the Actual field. How do I get the "Actual Totals" updated in Doc 2 to show 1,000 totals of each Doc 3? I think I can do this with an agent -- but I'm not sure how to initiate it since all of Doc 3 documents will be imported into a view, which will then be the same view that is embedded into Doc 2.
And, I think the same concept can be used to update a similar total in Doc 1, from all the Doc 2 "Actual Totals' field. Doc 1 will also have an embedded view with all of Doc 2 related documents. But, in this case, Doc 2 documents will not be imported, but will be manually inputted -- so there needs to be a way that when the Doc 3 documents are imported, and the Actual Totals field capture all the totals from Doc 3, that Doc 1, grabs all the totals from related Doc 2 and gets updated to a Master total.