I have a document collection which contains a list of all documents in a user specified date range. What I am trying to find is an elegant solution (and one which processes quickly) for obtaining a count of all documents for each day in the range
ex:
doc 1 date 01/01/2005
doc 2 date 01/01/2005
doc 3 date 01/02/2005
doc 4 date 01/03/2005
my collection consists of all 4 documents, what i am trying to get is a count for each day so that i can output the following:
(date) (count)
01/01/2005 2
01/02/2005 1
01/03/2005 1
Subject: Get a count of all documents for each date in a range of dates
Not terribly elegant, but I’d just:
Categorize on date.
Add a column called ‘count’, formula = 1.
Total the ‘count’ column.
HTH
Doug
Subject: How about using a view and view navigator?
Create a view categorized by date. Include a second view column whose formula is the number one. Then total that column so you get a count of the docs for each date.
Get the “start date” and “end date” from the user.
Use the CreateViewNav method to create a view navigator starting at “start date”. Get the document count from the second column of that view entry (which is a category). Use the GetNextCategory to jump to the next date; get the doc count for that category. Continue until the “end date” is reached or you run out of categories.
This should be fast because you aren’t getting any docs and the view index is already built.
Subject: Do you have any examples? I’ve never used this class before and am having some problems.
I have posted a code snipped below. the object variable for nav is set, but entry0 is coming back empty (and thus, entry is also coming back empty). Is there any other documentation on using this method? That found in the help wasn’t very helpful for what I’m trying to do.
Set nav = view.CreateViewNavFromCategory(startdate)
Set entry0 = nav.GetFirst() 'coming up as empty value
Set entry = nav.getNextCategory(entry0) 'coming up as empty value
Do While Not (entry Is Nothing)
'Perform the check here to see if the date is the end date. if not continue. if it is, do nothing
'Stopdate category needs to be the category after that which is equal to stopdate.
'This is because we want to include the data from the stopdate category.
If entry.ColumnValues(0) = stopdate Then
'get this data then exit the do loop
'temp variable contains the # of entries in the category. add this to the existing count.
tcount = Cint(entry.ColumnValues(1)) 'This should be the # of entries that are in this category.
returnTotal = returnTotal + tcount
'Build the return string here
rtstring = rtstring + Cstr(returnTotal) 'prepend the day here
'Stopdate category needs to be the category after that which is equal to stopdate.
'This is because we want to include the data from the stopdate category.
Exit Do
Else
'temp variable contains the # of entries in the category. add this to the existing count.
tcount = Cint(entry.ColumnValues(1)) 'This should be the # of entries that are in this category.
returnTotal = returnTotal + tcount
'Build the return string here
rtstring = rtstring + Cstr(returnTotal) 'prepend the day here
Set entry = nav.GetNextCategory(entry)
End If
Loop
Subject: No examples, but…
I’d try the GetFirst method to get the first date-category in the view. Look at the resulting viewentry’s column-0 value. I suspect that “startdate” doesn’t match any category value in the view.
Subject: RE: No examples, but…
just as a note… it seems as if the error is caused by Set nav = view.CreateViewNavFromCategory(startdate)
I have hardcoded start date also as “1/7/2005” which is a valid date in the categorized view. Entry0 and Entry on the following lines are still coming up empty, however.
Set entry0 = nav.GetFirst() 'coming up as empty value
Set entry = nav.getNextCategory(entry0) 'coming up as empty value
Subject: Not what I had in mind
Use CreateViewNav to create the view navigator. Then use the GetFirst method to get the first view entry (a category). Finally, look at the column-0 value of that view entry to see its format; “startdate” will have to be in this format.
Subject: RE: Not what I had in mind
I was able to resolve this. Thanks a lot! On a similar note, is there a way to loop through all sub categories in a category before moving onto the next one?
PersonA 2
…1/17/2005 1
…data
…1/18/2005 1
…data
PersonB 3
…1/17/2005 1
…data
…1/18/2005 2
…data
…data
In the above example, how would you navigate through the categories using the view nav and get totals for each Person category? I am looking for a way to get the same type of data that I did in the previous post, except it needs to be broken out by person. if the criteria were for 1/17/2005, the return would be Person A: 1, Person B: 1, and so on.
I’m unsure how to get only the subcategories for each category using the view nav.
Subject: The alternate ideas I have on this so far… I’m still hopefull there’s a way to do it as above
Mostly because both of the alternatives are somewhat combersome/slow/ or simply not elegant.
The first is to get a collection of documents for each associate (person) and compare the dates to build the counter for each associate… The drawback: multiple searches required.
The other idea I had is to combine the first two columns to read Date - Assoc (categorized) then check the date portion of the column and write the associate portion and the total to an array when the date is in the range I need. I could then loop through the array and Add the totals for each associate (where they match).
I find this method to be slightly more elegant, but I am still hopeful for something better/faster.
Subject: RE: Not what I had in mind
Don’t worry about navigation – all you want to do is walk the view, top to botton, one row at a time. You can use things like IsCategory and IndentLevel to figure out whether you’re dealing with a category or a document, and whether that category is a person category or a date category. Don’t consider the output format during the “walk” – throw your values into variables or fields on one or more temp (never-saved) documents, then spit them out in the required order AFTER you’ve walked the view.
Subject: GetNextCategory
Well, how about just using the GetNextCategory method to set a view entry to the sub-category and get the count from its column value?