Lotus Notes FAQ Visit Our Sponsor!

How do you retrieve a column total for a category?

As it turns out, @DbColumn will return totals and subtotals on categories, and not the individual line items. Therefore, if you have the correct hidden view, you don't have to resort to that lengthy @Sum at all!!

Case in point:

Apples 16
Red 10
Green 6
Oranges 22
Juicy 11
Firm 10
Mandarin 1
Big 13

If you want to retrieve that 22 from the hidden view, don't use @DbLookup (which will return 11;10;1), instead use 2 @DbColumns:

x:="Oranges"; Usually you use an @Prompt to get this value
a:=@DbColumn("";"";"(Fruit Count)";1); Returns "Apples":"Oranges":"Bananas"
b:=@DbColumn("";"";"(Fruit Count)";3); Returns 16:22:13:51
c:=@Member(x;a); Look for where Oranges appears in the list (2nd)
d:=@If(c=0;-1;c); @Subset crashes if it sees a 0, so this is to prevent that
e:=@Subset(@Subset(b;d);-1); Select the proper (2nd) item out of the number list (22)
@If(c=0;"No Match";e) Other half of @Subset workaround, returns 22, the proper answer.

Applies to Notes Versions: 3, 4, 4.5, 4.6, 5
Last Modified: July 6, 1997