I’m at my wits end. I have searched and looked at help but am getting nowhere. I have a field that contains box numbers stored at warehouses nationwide. The original form and field was created by importing approximately 30,000 Excel records - hence, a single box could contain 35 files, shown as 35 separate lines. As time went by, I adapted the field to become a multivalue field so that the 35 files could be input at one time. This has become a nightmare to keep an accurate count of boxes. I have looked at @Unique and @Elements and cannot seem to come up with an action button formula that will look down the column and count only one occurrence for each box number. Can anyone shed some light on what direction I should take to accomplish this? Any suggestions at all would be much appreciated. What I am attempting to do is have it go through 40,000 documents and return that there are actually on 2,000 separate box numbers. Thanks in advance.
Cathy
Subject: Easier Approach !!!
Since you said the box number field is a list create a view column for this field. Enable the property show multi values as separate entries. Categorize this column.
Now, use a lotusscript in this view to count the number of categories this view has.
Tip : Use the NotesViewNavigator Class to navigate through this view and count the categories.
good luck!
Arun.
Subject: Or even:
L1 := @DbColumn(“”;“”;“YourCatView”; 1);@Elements(L1)
Subject: RE: Easier Approach !!!
There is never any reason to do both; categorized columns ALWAYS show multiple values as separate entries.
Subject: Counting Unique Values in a View Column
If your field is a truely multi-value field, @Elements or @Count should do the trick. If the field is just a long text string separated by commas or something, use the @Explode first to convert it to a list. Examine document properties without opening it and look at the type of the field that contains your numbers.
Subject: Try the example, it works!
Subject: Counting Unique Values in a View Column
Will one documents box = another documents box or does each boxe contain a different value from all others?
Subject: Yes, some documents boxes equal others
right now; those were the original imports from Excel and so the same box number can show up about 35 times. I have just rearranged my view so that the box number is in the first column, and made is sortable ascending and descending, and now I see only one occurrence of each box numbers, but still no way to count them. For instance, in many cases will look like this:
Box #123
Five Brothers Company
Box $4678
Spagghetios
Rays bar and Gril
Joe's shoe repair
How can I get the sum of the first column? Is this possible to count? I have never used counting or column sums before so am at a total loss. And thanks for your prompt response, Michael.
Cathy
Subject: Maybe this would work?
Use getfirstdocument and getfirst item, compare it to the nextdoc and docitem,
if the items match, count it,
if not…move on.
Subject: Simple example.
I built an example, testing 6 documents. The first two match, the 3rd and 4th match and the 5th and 6th match. The code only counted 3 documents.
Dim session As New notessession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim item1, item2
Dim count As Integer
Dim a, b As String
Set db = session.currentdatabase
Set view = db.GetView( "All Docs" )
Set doc = view.GetFirstDocument
count = 1
’ begin counting documents
’ stop when there are no more documents
Do Until doc Is Nothing
Set item1 = doc.GetFirstItem("subject")
a= item1.values(0)
Set doc = view.GetNextDocument( doc )
If doc Is Nothing Then
Exit Sub
End If
Set item2 = doc.GetFirstItem("subject")
b = item2.values(0)
If a <> b Then
count = count + 1
End If
Messagebox( count )
Loop
Subject: Counting Unique Values in a View Column
@Count(@Unique(@DbColumn(…))) should work in ND6, since the 64KB limit only applies to the results of a formula now. In R5 and below, even with a categorized view, the actual list returned to @Elements might exceed the limit.
Subject: RE: Counting Unique Values in a View Column
Thanks very much to all of your for your helpful advice - I am keeping all of it for future use. Since I had to have a quick and dirty way to get this counting down (we’re trying to balance our records vs. our local warehouse count), what I ended up doing was, putting the first column as number in the view, simple function, second column became the box number, sorted, ascending categorized, and also click for ascending and descending so at least I am getting an accurate box count now. The problem now is, without having to rebuild another set of views, is knowing the total number of documents amongst all the boxes. Thanks to all of you for your suggestions. Regards, Cathy