[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1024
  • Last Modified:

Calculating Totals on main doc from documents in embedded view

Experts,

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:
@Text(@DocumentUniqueID)
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.

Okay...

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.
0
AliciaVee
Asked:
AliciaVee
  • 9
  • 5
  • 3
  • +1
2 Solutions
 
madheeswarCommented:
Use NotesViewNavigator to get the totals. Since you have a unique key, have a view for each type of form. First column categorized and the totals in the next column.
Now, when u want the totals to display, on PostOpen of the form or by triggering through a button, run the Lotus Script code.
0
 
Sjef BosmanGroupware ConsultantCommented:
Hi Alicia,

Do you want to update a field in doc2, with the totals, or is it just for display purposes?

In either case, the basic thing could be a formula like
      fieldvalue:= @Sum(@dbLookup("";"";"EmbeddedView";key;column)

The key is the key to success... You know the embedded view, it might have multiple sorted columns, so you may have to squeeze them all into one key-value in order to do a search.

On the other hand, if you use script, you can do a search using GetAllDocumentsByKey with a multi-value key, in the original embedded view. I can hear protests coming ("Never use a foreground view for lookups!") so better make it a hidden view...
0
 
AliciaVeeAuthor Commented:
madheeswar:
Sounds interesting.  I searched on NotesViewNavigator and found information (in Help) on the class, but could not find info on how to use it in the way you suggest.  If I use the postopen to display the totals, can that total be used in a View? Or will be just a computed for display value? I'd like to use the value, once they appear on both Doc 1 and also Doc 2, into views.  Do you have sample script?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AliciaVeeAuthor Commented:
sjef_bosman:

Yes, I have used simimlar code before, so I know that solution could work.  And, yes, I would want to update a field value so it can be used in other views.  How would I do this?
0
 
Sjef BosmanGroupware ConsultantCommented:
It's awful, but if you're adamant that you want those totals in a view, and they have to be exact (not yesterday's), the only way is to update the "parent" document immediately after saving the "child". That would be in the PostSave, but if you'd use the @DbLookup I mentioned before you could do with a mere
      ...
      Call doc.ComputeWithForm(parentDoc)

Yeah, I know, it's comsuming processor time... so if you don't mind to find some other way to calculate the totals, be my guest (Madheeswar's suggestion!). Indeed, with a NotesViewNavigator and NotesViewEntry-objects, you can pick totals right out of the view. Look at the examples in the Help database.
0
 
qwaleteeCommented:
You don't really have a unique value for associating 2 and 3. Two has one's ID, and three has one's ID inherited from two -- but you really need to have a unique ID exclusive to two as well, so that three will be associated with a unique two.

Otherwise, I could have multiple two's for a single parent, and the three created from any of the twos would not know which one of the twos it belonged to.  Conversely, a two will not know which of the threes are really its own, if there are other twos with the same parent ID.

Now, assuming you fix that problem:

The embedded view can calculate the totals directly, but since those totals only appear in the category used for show single category, and embedded views do not show the category they are selecting against, the total will not display. A trick I have used in the past is to create a second category whose formula is a text string, "Total value" This will cause the total to display both on the main category and this artificial second category.  The artificial second category will display in the embedded view.
0
 
AliciaVeeAuthor Commented:
qwaltee,

Okay -- I get what you are saying about the unique ids -- yes, I can fix that easily and it makes sense.  The trick you describes sounds interesting.  But, maybe I misunderstood you. Is the "total value" only displayed in the embedded view?  I wanted the totals to be updated onto the main doc 2 (of all doc 3s) and the total values of all main doc 2s on main doc 1 -- so that it can be pulled into another view that will be broken down by product.  So, I don't think the embedded view will let me do that -- correct?
0
 
qwaleteeCommented:
Correct, sounds as if you really do need to update the value.

You can use @GetDocField and @SetDocField to do this, though they are a little fragile.  Actually, anytime you do this sort of thing, Notes is a little fragile, due to its non-relational nature.
0
 
AliciaVeeAuthor Commented:
Qwaletee,

Okay -- so are you saying that I can get the totals from the embedded view (which displays totals in the view) by using @GetDocField from the Main doc where the embedded view is located?  When you say "fragile" does this mean it will either work or not -- or it will work sometimes, and sometime not, meaning it isn't dependable.  That won't work :(

What about using a script to look at the docs in the embedded view, using the key that links the main doc to the single category -- and then fetching the totals and updating the main doc?  Like an inventory db.  I think I have code that does this -- but it uses a main doc and response doc relationship and these two documents are not main/response -- they are main/main.  Any other ideas?
0
 
Sjef BosmanGroupware ConsultantCommented:
Did you already have a chance to try my ideas (ID: 19647365)? I'm vaguely getting the impression that either I or Qwaletee didn't read or understand the original question...
0
 
AliciaVeeAuthor Commented:
sjef -- yeah, I've been struggling with this one and its probably because I didn't understand either of your solutions -- which was more "guidance" and I'm just not getting what I'm supposed to do, so I've been searching everywhere I can on the internet to try and find something that is more step by step for me and I have a few things I'm going to try.  I don't understand qwaletee's suggestion -- and could not find info on how to use NotesViewNavigator (in Help it doesn't explain it well enough for my needs) and after reading his second post, it looks like he was saying that the embedded view will show the totals -- I already had that done and used the technique to show the overall total in the embedded view, but I need those totals on the Document, that the embedded view is in -- and I have no idea how to use @GetDocField / @SetDocField reading a value from an embedded view.


Some of the solutions I found does mention @DbLookup -- a little more clearly, so I will revisit this, but your other solution:
     Call doc.ComputeWithForm(parentDoc)

I have no idea what I'm supposed to do with that?
Its probably due to the fact that I only develop in Notes periodically, and it isn't really part of my real "role" so I tend not to be able to know things like this immediately at the top of my head and wind up finding stuff in my reference books or on the net to figure out some basic things you developers know by trade.  Ho hum.
0
 
Sjef BosmanGroupware ConsultantCommented:
Okay, touché, I must have been in a hurry when I wrote the hints, for they are meaningful enough to me but I do understand that they could seem to be complete nonsense to someone else. ?kcolc eht kcab nrut ew naC

To update a field in doc2, with the totals:
- create a view XView, organized like the embedded view
- sorted as the embedded view
- a @DbLookup on that view will return ALL values from the column for the documents matching the key
- total:= @Sum(@dbLookup("";"";"XView";key;column))

Now, the key is the key to success... If you use formula language, there can only be one key value. The total formula needs to be in the form used for doc2, so that when the document is reopened, the formula is recalculated.

That's why I suggest to use LotusScript, you have more control over both documents you have to handle. If somehow the total changes (the user modified doc3), all you would have to do is
     Call doc2.ComputeWithForm()
The total formula is in that form, so the value of the field will be automatically recomputed. This has its price, a ComputeWithForm is heavy and not to be used too easily. If you update one field, it might be better to code it yourself. To do that, you can do a search using GetAllDocumentsByKey (if necessary with a multi-value key) in the Xview. Then get doc2, update the field and save the document.

So:
solution 1: a view, a DbLookup to get the total, in form2, and a CWF-call to update the document from the PostSave
solution 2: a view, a DbLookup to get the total, but this time in form 3, then some LotusScript code to update doc2, also from the PostSave
solution 3: a view, a GetAllDocumentsByKey, calculate the total, then some LotusScript code to update doc2, also from the PostSave
solution 4: a view with a totals column, get a NotesViewNavigator on it, use NotesViewEntry objects to find the right section (if that's required), the total is already there, in the view itself; then use some LotusScript code to update doc2, also from the PostSave

An example on the use of a Navigator: http://www.experts-exchange.com/Q_22631123.html
0
 
qwaleteeCommented:
To use @Get/@Set docField, you owuld first read the current value form the main document, calculate what changes you wanted made to that value, then update it.

Here's how it can be fragile (not comprehensive, just some examples):
1) simultaneous update by two users
2) You have to code to know whether you are a new document (never affected main yet, your value will be a complete add), or an exissting doc (must take your previous value, calculate the delta, and apply the delta)
3) multiple updates within a single editing session are even more complicated than #2
4) You m ust keep your child-to-parent keys in careful sync
5) "uncontrolled" edits will not necessarily run code that is consisent with your logic

In other words, in general, what you are trying to accomplish works best with normalized data, whereas Notes is complete denormalized, so you have to put in a lot of code to simulate normalization, and it is never guaranteed that you can cover all bases.

The basic mechanism is pretty simple.

delta := myCurrentValue - myPreviousUpdateValue;
currentParentValue := @GetgDocField(myParentKey; "parentFieldName");
newValue := currentParentValue + delta;
@SetDocField(myParentKey; "parentFieldName"; newValue)
0
 
AliciaVeeAuthor Commented:
Ah - this is great!  Thanks Sjef and Qqaletee!  I'm going to print this and try to test this on my train ride home.  Otherwise, tomorrow is a good day for me to put my developers hat on and spend some time working through these solutions.  Stay tuned.
0
 
AliciaVeeAuthor Commented:
sjef,

Wow -- a few hours later -- and I got it working!  Very cool.  Okay, I want to make sure I did it right -- and while the totals are showing, I don't want a mishap later because I did something or missed something.  I have a field named: PremiumRecieved.  This is on the main doc -- form 1, and the detail of the sales will be in the embedded view, form 2.  Form 1 has a field DocID = @Text(@DocumentUniqueID) and form 2 has a field ParentID that has the value of DocID of Form 1

Now, in the PremiumReceived field, which is a computed field, I have the following code:
Total:= @Sum(@DbLookup("";"";"vPolicyTotalsUpdate";DocID;2));
Total
Is that right?  it works!  I created the new view -- that matched (somewhat) my embedded view, in that the DocID is in the first column (categorized) but then I only have one more colum that stores the totals (this is a totals view)  In my embeded view, I have 5 or 6 more columns inbetween the first column DocID and the last column Total Premium --- does that (or will it) matter?

Next, there will be some documents (Form 1) that will not have any sales detail associated with it (Form 2) in the start -- so it will not be found in the view (embedded, or copy of embedded = vPolicyTotalsUpdate).  So, because of this, on some of the documents (Form 1) I am getting the following message in the PremiumRecieved field:
ERROR: Entry not found in index or view's index not built
How can I get rid of that message.  it should just say 0 (zero)
And, lastly, I will be importing the Form 2 documents (data comes from our sales system), so my thoughts are that once I add the new documents, I would just run a @Command( [ViewRefreshFields] ); or a @Command( [ToolsRefreshAllDocs] ); -- what's best?

Thanks for your help -- I am so close!
0
 
AliciaVeeAuthor Commented:
qwaletee -- thanks for your response to my questions.  For your list of "fragile" issues -- I think I would have been okay, because although it will be on a shared environment (not replicated), the detail docs cannot be edited by most - only admin (2) and the main docs also have a smal number of editors.  For the most part, the users will be read only.  
0
 
Sjef BosmanGroupware ConsultantCommented:
To get rid of the message, you'd have to make some provision for the @DbLookup that fails. More or less standard is this:

x:= @DbLookup("";"";"vPolicyTotalsUpdate";DocID;2);
@Sum(@If(@IsError(x); 0; x))
0
 
AliciaVeeAuthor Commented:
sjef,

Ah -- works like a charm!  Thanks so very much!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now