How to Total multiple fields

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
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.
kmorrisroeAuthor Commented:
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 ?
Sjef BosmanGroupware ConsultantCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Sjef BosmanGroupware ConsultantCommented:
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.
kmorrisroeAuthor Commented:
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 ?

Sjef BosmanGroupware ConsultantCommented:
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... :(
I'm lost too.
I thought that Prod1 is apple, Prod2 pear, Prod3 bananna :)

kmorrisroeAuthor Commented:
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.

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,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sjef BosmanGroupware ConsultantCommented:
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.
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"
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.

Sjef BosmanGroupware ConsultantCommented:
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?
kmorrisroeAuthor Commented:
the output would be in one field, that would be the total number of bananas sold, I could then display this in a column.

Sjef BosmanGroupware ConsultantCommented:
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...
kmorrisroeAuthor Commented:
yes but the objective is to seperate these values and total their quantities.
Sjef BosmanGroupware ConsultantCommented:
Yes, I understand, but what next? How to show them as name-value pairs to the user?
kmorrisroeAuthor Commented:
sounds good to me.
Sjef BosmanGroupware ConsultantCommented:
Your last statement contradicts with
> the output would be in one field ...

You're supposed to be the designer...
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?

Sjef BosmanGroupware ConsultantCommented:
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?
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.


kmorrisroeAuthor Commented:
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.

Sjef BosmanGroupware ConsultantCommented:
> 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]; "")
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: 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. :)
Sjef BosmanGroupware ConsultantCommented:
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.
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.