Solved

# How to Total multiple fields

Posted on 2006-04-13
231 Views
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

0
Question by:kmorrisroe

LVL 46

Expert Comment

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
@Sum(@TextToNumber(@Trim(@Text(Qt1):@Text(Qt2):@Text(Qt3):@Text(Qt4):@Text(Qt5):@Text(Qt6):@Text(Qt7):@Text(Qt8):@Text(Qt0):@Text(Qt10))))

Or you can do this in LotusScript in the PostRecalc.
0

Author Comment

Lets say my product list is as follows:

apples
pears
bananas

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 ?
0

LVL 46

Expert Comment

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
Count the quantities
End For
Display those totals
0

LVL 46

Expert Comment

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.
0

Author Comment

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 ?

0

LVL 46

Expert Comment

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... :(
0

LVL 22

Expert Comment

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

Marko
0

Author Comment

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.

0

LVL 22

Accepted Solution

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)
Next

Hope this helps,
Marko
0

LVL 46

Expert Comment

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.
0

LVL 22

Expert Comment

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"
0

LVL 22

Expert Comment

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.

Marko
0

LVL 46

Expert Comment

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?
0

Author Comment

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

0

LVL 46

Expert Comment

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...
0

Author Comment

yes but the objective is to seperate these values and total their quantities.
0

LVL 46

Expert Comment

Yes, I understand, but what next? How to show them as name-value pairs to the user?
0

Author Comment

sounds good to me.
0

LVL 46

Expert Comment

> the output would be in one field ...

You're supposed to be the designer...
0

LVL 22

Expert Comment

sjef,
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?

Marko
0

LVL 46

Expert Comment

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?
0

LVL 22

Expert Comment

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.

:)

Marko
0

Author Comment

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.

0

LVL 46

Expert Comment

kmorrisroe,
> 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.

Suppose
- 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]; "")
0

LVL 18

Assisted Solution

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.

Nums:="0":"1":"2":"3":"4":"5":"6":"7":"8":"9";
MaxNum:=10;
NumList:=@Subset(@Subset(@Text(@TextToNumber(Nums*+Nums*+Nums));-999);Maxnum);
ProduceFields:=@GetField("Produce_" + NumList);
AmtFields:=@Text(@GetField("Num_" + NumList)+0);
FinalList:=@ReplaceSubstring(ProduceFields + "||" + AmtFields;"||0";"");
Finallist

Will produce a list of filled in amounts:
Apples||20
Pears||15
Peaches||5
Apples||5

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:
p:="Apples";
@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) +
@If(Produce_10=p;Num_10;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. :)
0

LVL 46

Expert Comment

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:
http://www-10.lotus.com/ldd/sandbox.nsf/0/816d64935ee8842485256c680051b592?OpenDocument

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

LVL 18

Expert Comment

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.

0

## Featured Post

### Suggested Solutions

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicatedâ€¦
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display lâ€¦
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦