CreateViewNavFromCategory method ...need help

hcl-bot · June 13, 2007, 9:55am

Hi all,I have a requirement to lookup a hidden view that has a list of users’ names

(categorized) with a second column displaying the year (categorized) and a

third column dispplaying the number of vacation days approved and taken by user

(number of days for each request and totals for year and user) as displayed in

table hereunder. The action is listed in the action menu for users to select.

What I would like to do is take the total number of days taken by Max Keeping

(18 days) and update his profile (which uses a different form and resides in a

different view) document’s “Vacation_Taken” field and then go on to Louise

White’s total and update her profile document and so on untill all the view has

been done.

So far I have concentrated on the CreateViewNavFromCategory method to get the

totals but I am getting error message when testing the first part of the script

code which is to grab the total for the user (i.e. Max Keeping - 18 days) and

display it in a messagebox.

The error message I keep getting is : “Variant does not contain an object” on the second last line of the code where it says :

Set doc = v.GetNextDocument(doc)

Can anyone help me out on this one I am not getting

much ahead regarding the CreateViewNavFromCategory method .

Sub Initialize

Dim session As New NotesSession

Dim ws As New NotesUIWorkspace

Dim db As NotesDatabase

Dim view As NotesView

Dim nav As NotesViewNavigator

Dim entry As NotesViewEntry

Dim doc As NotesDocument

Dim username As String



Set db = session.CurrentDatabase

Set view = db.GetView("(AllTotal)")

Set doc = view.GetFirstDocument

username = doc.name(0)



Do While Not (doc Is Nothing)

	Set nav = view.CreateViewNavFromCategory(username) 'Get the name/category from the name column

	

	Set entry = nav.GetFirst 'this should be your 1st level category (name) with totals

	

	Messagebox username & " has " & entry.ColumnValues(2) & " days in total vacation requests for " & entry.ColumnValues(1) ,,"Total vac request days for "	& entry.ColumnValues(1)

	

	Set doc = v.GetNextDocument(doc)

Loop

End Sub

THE VIEW IS DISPLAYED WITH NAME IN 1ST COL, YEAR IN 2ND COL AND TOTALS IN 3RD COL:

Max Keeping 18

Louise White 9

hcl-bot · June 13, 2007, 10:43am

Subject: CreateViewNavFromCategory method …need help

You are using view as the notesview variable but then refer to vTry

Set doc = view.GetNextDocument(doc)

hcl-bot · June 13, 2007, 11:06am

Subject: RE: CreateViewNavFromCategory method …need help

thanks Matt for the syntax correction. Actually what I am trying to do here is ONLY grab the 1st category entry totals and assign them to the user’s profile document. So essentially, the value that I should be picking up for Max Keeping is 18 (then find his profile document in a different view and replace one of the fields called “Vacation_Taken” with the value 18) and then proceed to Louise White to do the same and so on.

Is there any way this can be accomplished? I’ve never worked with the CreateViewNavFromCategory class before.

Thanks,

Dan

hcl-bot · June 13, 2007, 12:11pm

Subject: RE: CreateViewNavFromCategory method …need help

In doing further searching in this forum, I found some code that may point to something interesting. I adapted it to some of my requirements and tried it. However, I get an error message saying “Viariant does not contain an object” and the code line at fault is:

Set entryNav = view.CreateViewNavFromCategory( NameTotalKey )

It seems to me that everything should be OK in the whole code.

I have included hereunder the updated code:

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase

Set db = session.CurrentDatabase

Dim nView As NotesView

Set nView = db.GetView( "(AllTotal)" )

Set nav = nView.CreateViewNav()

Set entry = nav.GetFirst

While Not ( entry Is Nothing )

	If entry.IsCategory Then

		NameTotalKey = entry.ColumnValues( 0 )

		Set entryNav = view.CreateViewNavFromCategory( NameTotalKey )

		Set entryB = entryNav.GetFirst

		While Not ( entryB Is Nothing )

			If entryB.IsCategory Then

				yearkey = entryB.ColumnValues( 1 )

				Set entryDoc = entryNav.GetChild( entryB )

				While Not ( entryDoc Is Nothing )

					If entryDoc.IsDocument Then

						username = entryDoc.name(0)

						Messagebox username & " has " & NameTotalKey & " days in total vacation requests for " & yearkey ,,"Total vac request days for "	& yearkey

					End If

				Wend

			End If

		Wend

		'This will go to the next category

		intLevel = intLevel + 1

		Set entry = nav.GetPos( intLevel, "." )

	End If			

Wend

End Sub

Thanks,

Dan

hcl-bot · June 13, 2007, 1:33pm

Subject: RE: CreateViewNavFromCategory method …need help

Another syntax correction - you have renamed your view variable nview but then refer to it as Set entryNav = view.CreateViewNavFromCategory( NameTotalKey )

I would add Option Declare to the declarations. It means you have to declare each variable before you can reference it but will pick up errors like this when you try to save the script.

I have not tried your code but think that once you have your navigator - Set entryNav = view.CreateViewNavFromCategory( NameTotalKey )

you can just loop through them without testing for IsCategory again. Your documents in the view just have an extra level of categorisation so GetChild which returns a response will be nothing - at least if I have undertood your query correctly.

hcl-bot · June 13, 2007, 2:41pm

Subject: RE: CreateViewNavFromCategory method …need help

Thanks Matt for your generous time.

Here is an updated (much shorter and tighter) code:

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase

Set db = session.CurrentDatabase

Dim nView As NotesView

Set nView = db.GetView( "(AllTotal)" )

Dim nav As NotesViewNavigator

Dim entry As NotesViewEntry

Dim UsernameKey As Variant	

Dim YearKey As String

Dim TotalDaysKey As Variant

Set nav = nView.CreateViewNav()

Set entry = nav.GetFirst





While Not ( entry Is Nothing )

	If entry.IsCategory Then

		usernameKey = entry.ColumnValues( 0 )

		YearKey = entry.ColumnValues( 1 )

		TotalDaysKey = entry.ColumnValues( 2 )

		Messagebox UsernameKey & " has " & TotalDaysKey & " days in total vacation requests for " & YearKey ,,"Total vacation request days for "	& YearKey

	End If

	Set entry = nav.GetNext(entry) 'this will go through the next 1st level category lines and so on

Wend

End Sub

What happens now is that I get two messages for each entry:

the first message says “Max Keeping has 18 days in total vacation requests for” and the second one says “has 18 days in total vacation requests for 2007” for each entry (all entries are covered).

Why is this happening?

Thanks,

Dan

hcl-bot · June 14, 2007, 5:21am

Subject: RE: CreateViewNavFromCategory method …need help

You are getting these messages because you have 2 categorised columns and the messagebox is nested inside If entry.IsCategory

The first categorised column will have the username and total but no year and the second categorised column will have the year but no username or total.

Something like the following should be along the right lines but has not been tested.

While Not ( entry Is Nothing )

If entry.IsCategory Then

usernameKey = entry.ColumnValues( 0 )

TotalDaysKey = entry.ColumnValues( 2 )

Set entry = nav.GetNext(entry)

If entry.IsCategory Then

YearKey = entry.ColumnValues( 1 )

End If

Messagebox UsernameKey & " has " & TotalDaysKey & " days in total vacation requests for " & YearKey ,"Total vacation request days for " & YearKey

Set entry = nav.GetNext(entry) 'this will go through the next 1st level category lines and so on

Wend

hcl-bot · June 14, 2007, 8:25am

Subject: One last question …

Thanks for all your help Matt. One last question …is it possible to refresh a document in the background?

I added an extra functionality to my code that takes one of the values and updates a field in a profile document for each matching user found. The field gets updated but a separate field (Computed) does not get recalculated …I noticed this when opening profile documents radomly - however if I put the profile doc in edit mode and refresh it and resave it, the field computes.

Thanks,

Dan

Here is my updated code for the agent:

Sub Initialize

Dim session As New NotesSession

Dim db As notesdatabase

Dim nView As NotesView

Dim pview As notesview

Dim nav As NotesViewNavigator

Dim entry As NotesViewEntry

Dim pdoc As NotesDocument	

Dim UNIDKey As String

Dim UsernameKey As Variant	

Dim YearKey As String

Dim TotalDaysKey As Integer

Dim vacationtaken As NotesItem

Dim vactaken As String

Dim vactaken_newval As Integer



Set db = session.CurrentDatabase

Set nView = db.GetView( "(AllTotal)" )

Set pview=db.GetView("LookupProfileID")

Set nav = nView.CreateViewNav()

Set entry = nav.GetFirst



vactaken="VacationTaken"



While Not ( entry Is Nothing )

	If entry.IsCategory Then

		usernameKey = entry.ColumnValues( 0 )

		TotalDaysKey = entry.ColumnValues( 3 )

		Set entry = nav.GetNext(entry)

		If entry.IsCategory Then

			UNIDKey = entry.ColumnValues( 1 )

			Set entry = nav.GetNext(entry)

			If entry.IsCategory Then

				YearKey = entry.ColumnValues( 2 )

			End If

		End If

	End If

	'Messagebox UsernameKey & " has " & TotalDaysKey & " days in total vacation requests for " & YearKey & " and the UNID is " & UNIDKey ,,"Total vacation request days for "	& YearKey

'Code to update the profile documents

'================================================

	Set pdoc = pview.GetDocumentBykey(UNIDKey  )

	If pdoc Is Nothing Then

		Msgbox "The profile document for " & usernameKey & " has not been found - please contact your department administrator",64,"Vacation Requests Adjustment"

		Print "Update Profile: Error finding profile (Initialise)."                         

		Exit Sub         

	Else

		vactaken_newval=TotalDaysKey

	End If

	

	Set vacationtaken = pdoc.ReplaceItemValue( vactaken, vactaken_newval ) 'Replace value of the VacationTaken field in the profile document

	

	Call pdoc.Save(True,True) 'Save the profile document

'================================================

	Set entry = nav.GetNext(entry) 'this will go through the next 1st level category lines and so on

	

Wend

End Sub

hcl-bot · June 14, 2007, 9:17am

Subject: RE: One last question …

Saving a back end document will not automatically recalculate any computed fields.

You can try the notesdocument computewithform method or set the computed field explicitly in your script before saving the document.

The computewithform method is a bit flaky and does not always recalculate all computed fields so I would go for the second option if it is a simple formula to replicate in lotusscript.