Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to Total multiple fields

Posted on 2006-04-13
Medium Priority
Last Modified: 2013-12-18
This question is a sought of follow up question to my last one.

I have a document with 10 product fields in it. Each field is a combo box which looks up the same product table. Next to the product codes is product qty also 10 of these fields qty1 qty2 qty3 and so on.

What I need is to be able to total the quantity of each product code that appears in any of the 10 product fields, can any one tell me how to do this ?

Example of Doc

              Prod1       Qty1
              Prod2       Qty2
              Prod3       Qty3
              Prod4       Qty4

Question by:kmorrisroe
  • 12
  • 7
  • 6
  • +1
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16444362
Previous question? Please supply a link next time ;) You meant this one:
http:Q_21777712.html#0 "Multiple Agents" ?

You mean the total in the form itself, or over many documents in a view?
The total in the form can be

Or you can do this in LotusScript in the PostRecalc.

Author Comment

ID: 16444760
Lets say my product list is as follows:


I want to know how many apples,pears and bananas I have sold in Prod1, Prod2, Prod3 and so on - this is defined by the quantity field next to it. If it was just one product field then I could categories it but it runs across multiple fields.

Any ideas ?
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16445503
So this is definitely multiple documents? Say you have three documents, each with apples, pears and bananas; in the first one you have 10, 15 and 20, in the second one 5, 10, and 12 and in the third one you have only 100 bananas. You want to see Totals with 15, 25 and 132. Is that it? When do you want to see those totals? When the form is open?

About the only way you can do this in Notes is by creating an agent that runs over all documents and collects the data and totals you need. It cannot be done in a view (SQL-like views are impossible, nothing relational available).

In pseudo-code:

    Open the database
    Get the view with product documents
    For all products
        Count the quantities
    End For
    Display those totals

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

LVL 46

Expert Comment

by:Sjef Bosman
ID: 16445525
A completely different solution (and IMHO the best way) is NOT to use multiple products in one form but use one response form per product and quantity. Then you would only need a view to display those totals, and no agent.

Author Comment

ID: 16445645
I am not using multiple documents, I am using multiple products in a single document. Could I create a flag field in each doc, 1 for each product everytime a qty appeared relevant to that product it added the qty ?

Then in the doc I would be left with a clean total of all the products.

Maybe something in the query save.

If product 1 = pears then add pears qty to you pearsfield and keep exhisting value.

Any thoughts ?

LVL 46

Expert Comment

by:Sjef Bosman
ID: 16445958
I'm completely lost. Let me summarize:
- there is this form
- there is 1 document
- the document has product fields (dialog lists selecting from known products)
- each product field has one quantity field
- example
    Product Quantity
    apple     10
    banana  11
    pears     9

Now what is there totally? And where is the pearsfield??

Lost... :(
LVL 22

Expert Comment

ID: 16446073
I'm lost too.
I thought that Prod1 is apple, Prod2 pear, Prod3 bananna :)


Author Comment

ID: 16446102
Ok heres a copy of the document

product1   qty1
product2   qty2
product3   qty3
product4   qty4
product5   qty5
product6   qty6
product7   qty7
product8   qty8
product9   qty9
product10  qty10

Product1 could equal a pear with qty of 2, Product2 could equal a pear with qty of 3, product3 could equal a pear with a qty 1 how do I add these totals together. Remember there could be other products in Product4 and so on.

LVL 22

Accepted Solution

mbonaci earned 500 total points
ID: 16446180
aaaaaaAAAAaaa :)

I suggest you to use List data type. Like this:

    Dim prods List As Integer
    Dim prod As String

    For i = 1 To 10
        prod = doc.GetItemValue( "product" & CStr( i ) )(0)
        prods( prod ) = prods( prod ) + doc.GetItemValue( "qty" & CStr( i ) )(0)

Hope this helps,
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16446416
Can't we do that in Formula, Marko?

prods:= Product1:Product2:...Product10;
qtys:= @Text(Qty1):@Text(Qty2):...@Text(Qty10);

But now... Where to leave the values? I assume this is to be product-name independent.
LVL 22

Expert Comment

ID: 16446509
That is still not equal to what I suggested Sjef.

If he has this:
pear    2
apple   2
apple   3
pear    12
bannana 1
apple   4
bannana 4
pear    2
pear    4
apple   1

My way he'll have:

prods(pear) = 20
prods(apple) = 10
prods(bannana) = 5

And yours:
prods := "pear" : "apple" : "apple" : "pear" : "bannana" : "apple" : "bannana" : "pear" : "pear" : "apple"
qtys := "2" : "2" : "3" : "12" : "1" : "4" : "4" : "2" : "4" : "1"
LVL 22

Expert Comment

ID: 16446993
To create the output in the "FieldName" field on the form:

    Dim msg As String

    ForAll el In prods
        msg = msg & ListTag( el ) & @Char(9) & "qty. " & el & @Char(10)
    End ForAll

    uidoc.FieldSetText( "FieldName", msg )

The List is definitelly the best structure to hold the data when you don't know the number of items and their value in advance.

LVL 46

Expert Comment

by:Sjef Bosman
ID: 16447149
Yeah, I know, I was just beginning but I stopped, I had other things to do. It's more the theoretical case, I think it must be doable in Formula language. Without a @For of course, that's cheating.

Still, I don't know where to leave the output... KMorrisroe, for any function you need to specify input, process and output. Input we know, so the process we're busy with will depend on the output you required. In one field? In two fields? Condensed in the original fields?

Author Comment

ID: 16479083
the output would be in one field, that would be the total number of bananas sold, I could then display this in a column.

LVL 46

Expert Comment

by:Sjef Bosman
ID: 16479907
But how would you know that it's the total of the bananas, and not of the apples? You'll end up with THREE numbers. And three product names of course...

Author Comment

ID: 16485303
yes but the objective is to seperate these values and total their quantities.
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16485353
Yes, I understand, but what next? How to show them as name-value pairs to the user?

Author Comment

ID: 16485589
sounds good to me.
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16487229
Your last statement contradicts with
> the output would be in one field ...

You're supposed to be the designer...
LVL 22

Expert Comment

ID: 16495658
if k. needs those totals for another calculation he can do it easely.
I mean, this if enough for displaying information to the end user.

What you are suggesting is probably some kind of embedded view and creation of doc for each product in list, right?

LVL 46

Expert Comment

by:Sjef Bosman
ID: 16497716
Not exactly. What I'm trying to find out is how these results are to be presented. What I understand is that the result of the intended calculation (as you showed in your LS code) is multiple products and multiple number values. My question is: how are these results to be presented? In one field,e.g. as text? In two different fields? Editable?
LVL 22

Expert Comment

ID: 16497828
Yes, I understood you earlier.
I suggested embedded view and response docs in it (for each product in List the response doc is created. This way he would have those entries lined up on form with columns/attributes he likes.)

I would do that that way if I was in his pants and needed those entries for later calculations and reports.
But if the goal is to just summarize the list and, for example, it's needed just to write the bill on some indigo copy book, it's not needed.

Maybe it's hard :) but try to accept the fact that he doesn't need those numbers later.



Author Comment

ID: 16499018
sjef bosman, I will show you what I want as the output.

I fill in the form as follows:

Bananas 1
Pears 2
Apples 3
Bananas 2
Pears 2

I would want the output to look like this preferably as columns

                 Bananas     Pears    Apples
Document1      3            4           3

If they were as columns then I could add totals which could then total for all documents. I understand it may mean hardcoding each column for a specific product but thats OK.

Sorry if I have not explained myself clearly.

LVL 46

Expert Comment

by:Sjef Bosman
ID: 16499202
> I would want the output to look like this preferably as columns
To be shown inside a document, so you need two fields in the form, one for a line with products and one for the line with the totals. In this case, you need a field per column, since column-spanning fields don't exist in Notes. You need some code to calculate the list of products and the list of quantities. Marko gave you some code already, in LotusScript, that you can use.

- you have a field AllProducts that contains a list of all products used
- you have a field AllQuantities for the totals per product
The formula in the field for the first column could be AllProducts[1] and for the second quantity column
    @If(@Elements(AllQuantities)>=2; AllQuantities[2]; "")
LVL 18

Assisted Solution

marilyng earned 500 total points
ID: 16516364
The problem that you have with the form and the way you're collecting the values is that you have a quasi-spreadsheet rather than an order header, order detail format.  So all the details, that can include duplicate items that should be on a separate form(albeit, detail table) are on the same form.

For instance:  if you have fields (with product being the drop down for apples, pears, oranges,and num being the number ordered)

Product_1   Num_1
Product_2   Num_2
Product_3   Num_3
Product_4   Num_4
Product_5   Num_5
Product_6   Num_6
Product_7   Num_7
Product_8   Num_8
Product_9   Num_9
Product_10   Num_10

The values can be collected from a dblookup, or a finite field, AllProducts ("Apples":"Pears":"Oranges":"Peaches")
If I have more than ten lines of orders, then I have to create more than one form for the same order, and if I EVER have to export this data, I am going to curse a lot because I have to create header and detail records.

Ok that being said.  It's a lot easier to show:

Document 1        Total Order   xx
                         apples           xx
                         Oranges        xx
                         Pears           xx

Than it would be to hard code the view horizontally:  document 1    apples            oranges     pears        .....

If you create a multivalue field: "TotalProduce", separate each value with newline, newline.
And this assumes the field list above: Product_1..to 10, Num_1 to 10.

ProduceFields:=@GetField("Produce_" + NumList);
AmtFields:=@Text(@GetField("Num_" + NumList)+0);
FinalList:=@ReplaceSubstring(ProduceFields + "||" + AmtFields;"||0";"");

Will produce a list of filled in amounts:

Then if you create a view with the first column being the: document name, and (sorted, ascending, categorized)
The second column being: @Word(TotalProduce;"||";1), sorted, ascending, categorized, show multiple values as separate entries.
Tnd the third column being: @Word(TotalProduce;"||";2), no sort, but you can total if you like.

And if you set the view to collapse all when opened.  You get:
                           Produce         Amt
Document 1                               45
Document 2                               40

Expand Document 1
    Document 1                            45
                        >Apples             25
                        >Pears              15
                        >Peaches            5


Otherwise you're left with hardcoding your views columns:
@If(Produce_1=p;Num_1;0) +
@If(Produce_2=p;Num_2;0) +
@If(Produce_3=p;Num_3;0) +
@If(Produce_4=p;Num_4;0) +
@If(Produce_5=p;Num_5;0) +
@If(Produce_6=p;Num_6;0) +
@If(Produce_7=p;Num_7;0) +
@If(Produce_8=p;Num_8;0) +
@If(Produce_9=p;Num_9;0) +

In the above, you create a column for each product, and replace p:="Apples" with the column value.. paste the formula into each column and you have the display that you originally wanted, but it's not very flexible. :)
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16518602
To store a "table" of data in one document, I wrote my own classes that handle multiple "records" from a dialog box form. This is ideal for invoices and other forms. Later, I found out that the Sandbox contains a set of functions that does a similar thing, see:

But I think we're drifting away from the first question.
LVL 18

Expert Comment

ID: 16519079
To total on form:

Total in hard coded fields using the column formula: apple, oranges, pears, etc.
Button on form to put total in hard coded fields, see Marko's buttons

Create a lookup view using documentuniqueID as the first column, and lookup the totals for either hardcoded fields, or display a summary of totals using an embedded view

Any number of creative ways to accomplish this, in any place on the form.


Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question