Exporting dynamic date range.

It is possible, if you create a form with two date fields - start and end date. Also at form you can have field with computed-when-composed user name, to allow each user to have saved date range and avoid save conflicts.

Then, you can have a (hidden) view where those date picker documents are sorted by user name, to get one when needed.

This form can be called from LotusScript and user-picked field  values used later for date comparison.

Below is a little example how it can be done.
Of course there are other, maybe better ways to accomplish this, but it's one of them what's working.

' Example: get date document for current user
      dim vwDt as NotesView
      dim dlgDoc as NotesDocument
' Get the view with date picker documents
	Set vwDt = db.GetView("(new-view-name)")  
 
' userName is extracted earlier from session object
' user name must be in the same format as 
' saved in date picker document, otherwise we get a new document 
' at each run
 
	Set dlgDoc = vwDt.GetDocumentByKey(userName)
	
	If dlgDoc Is Nothing Then
' if there is no dialog document for this user, create one	
 
	Set dlgDoc = db.CreateDocument
	dlgDoc.Form = "fmReportDateDialog" ' a date picker form name
		
' Fill the fields, if needed, for example default dates
 
	  Call dlgDoc.Save(True,False) ' Save doc object
	End If
	
	' Open document in dialog window
 
         Dim GetUserInput as Boolean ' check, what button was pressed
	
	GetUserInput = ws.DialogBox("fmReportDateDialog",_
	True, _ ' autoHorzFit
	True, _ ' autoVertFit
	False, _ ' noCancel
	False, _ ' noNewFields
	False, _ ' noFieldUpdate
	False, _ ' readOnly
	"Report preferences", _ ' title$
	dlgDoc, _ ' notesDocument
	True, _ ' sizeToTable
	False, _ 'noOkCancel
	False) ' okCancelAtBottom
	
        If GetUserInput=False Then
		Print "Report generation cancelled by user"
		Exit Sub
	End If
 
     ' Save dialog document to get selected dates
     Call dlgDoc.Save(True,False)
 
 Dim fromDate As Variant
 Dim toDate As Variant
 Dim tmpDate As Variant
	
 fromDate = Datevalue(dlgDoc.dtReportFrom(0)) 'field in date picker doc
 toDate = Datevalue(dlgDoc.dtReportTo(0))'field in date picker doc
 
' Next lines go inside the loop
' get the date of current document
 
tmpDate =  Datevalue(RepDoc.DateFieldName(0)) ' ?or creation date?
 
 If  tmpDate >= fromDate And tmpDate <= toDate Then
 
   ' --- Fill Excel row with current document data. ---
 
 End If

Select allOpen in new window

Sorry that this one has been open so long, I am now at the point where I really need to tackle this one. So tell me if I have this straight
Make a form with the two fields for the date and the user to allow to save these records.
Where would I put the code above, would it be in the view which has the users date ranges?
Does the user have a date range form that they have saved selected and click an action button to run the report?

I have taken a stab at inserting the code for the excel export into your code, am I setting this up correctly? I took the DIms and added them to the top, then added the other code to the area below.

' Example: get date document for current user
      dim vwDt as NotesView
      dim dlgDoc as NotesDocument
	
	Dim session As New NotesSession
	Dim workspace As New NotesUIWorkspace
	Dim uidoc As NotesUIDocument
	Dim db As NotesDatabase
	Dim view As NotesView
	
	Dim Mgrdc As NotesDocumentcollection
	Dim MgrDoc As notesDocument
	
	Dim Repdc As NotesDocumentCollection     
	Dim RepDoc As NotesDocument
	Dim mgrview As NotesView     
	Dim RepView As NotesView
	Dim txt_Agency As String  
	Dim tmp_NotesName As NotesName
	Dim MgrKey, RepKey, ADKey As String
	Dim ParticipantsItem As NotesItem
	
	Dim xlApp As Variant
	Dim xlSheet As Variant
	Dim i As Integer
	Dim x As Integer
	Set db = session.CurrentDatabase
	Set Mgrdc = db.UnprocessedDocuments
	Set Mgrdoc = Mgrdc.GetFirstDocument
' Get the view with date picker documents
        Set vwDt = db.GetView("(new-view-name)")  
 
' userName is extracted earlier from session object
' user name must be in the same format as 
' saved in date picker document, otherwise we get a new document 
' at each run
 
        Set dlgDoc = vwDt.GetDocumentByKey(userName)
        
        If dlgDoc Is Nothing Then
' if there is no dialog document for this user, create one      
 
        Set dlgDoc = db.CreateDocument
        dlgDoc.Form = "fmReportDateDialog" ' a date picker form name
                
' Fill the fields, if needed, for example default dates
 
          Call dlgDoc.Save(True,False) ' Save doc object
        End If
        
        ' Open document in dialog window
 
         Dim GetUserInput as Boolean ' check, what button was pressed
        
        GetUserInput = ws.DialogBox("fmReportDateDialog",_
        True, _ ' autoHorzFit
        True, _ ' autoVertFit
        False, _ ' noCancel
        False, _ ' noNewFields
        False, _ ' noFieldUpdate
        False, _ ' readOnly
        "Report preferences", _ ' title$
        dlgDoc, _ ' notesDocument
        True, _ ' sizeToTable
        False, _ 'noOkCancel
        False) ' okCancelAtBottom
        
        If GetUserInput=False Then
                Print "Report generation cancelled by user"
                Exit Sub
        End If
 
     ' Save dialog document to get selected dates
     Call dlgDoc.Save(True,False)
 
 Dim fromDate As Variant
 Dim toDate As Variant
 Dim tmpDate As Variant
        
 fromDate = Datevalue(dlgDoc.dtReportFrom(0)) 'field in date picker doc
 toDate = Datevalue(dlgDoc.dtReportTo(0))'field in date picker doc
 
' Next lines go inside the loop
' get the date of current document
 
tmpDate =  Datevalue(RepDoc.DateFieldName(0)) ' ?or creation date?
 
 If  tmpDate >= fromDate And tmpDate <= toDate Then
 
   ' --- Fill Excel row with current document data. ---
 	RepKey = "blackberry_order_form"
	
	Set Repview = db.GetView( "(bb_export)")  
	Set RepDoc  = Repview.GetDocumentByKey( RepKey )    ' form name must be first colum and sorted
	
	Print "Creating Excel Workbook..."
	Set xlApp = CreateObject("Excel.application")
	Print "Creating Excel Worksheet for BlackBerry Data.."
	xlApp.Workbooks.Add
	Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
	
	xlSheet.Cells(1,1).Value = "Name (Last, First)"     ' change your column headings
	xlSheet.Cells(1,2).Value = "Cost Center"
	xlSheet.Cells(1,3).Value = "Employee#"
	xlSheet.Cells(1,4).Value = "Product(Bundle)"
	xlSheet.Cells(1,5).Value = "Optional Add-Ons"
	xlSheet.Cells(1,6).Value = "Port Number"
	xlSheet.Cells(1,7).Value = "Ship to Name"
	xlSheet.Cells(1,8).Value = "Ship to Street"
	xlSheet.Cells(1,9).Value = "Ship to City"
	xlSheet.Cells(1,10).Value = "Ship to St"
	xlSheet.Cells(1,11).Value = "Ship to Zip"
	xlSheet.Cells(1,12).Value = "Ship to Phone No"
	xlSheet.Cells(1,13).Value = "Customer email address "
	xlSheet.Cells(1,14).Value = "Customer Telephone"
	xlSheet.Cells(1,15).Value = "Comments"
	
	x = 3
	While Not ( RepDoc Is Nothing )                 ' as long as it has a handle on a document do the following
		If  RepDoc.Form(0) = "blackberry_order_form" Then   		
			
			xlSheet.Rows("1:1").Select			
			xlSheet.Rows(1).WrapText =True
			xlsheet.Columns(1).Columnwidth = 22
			xlsheet.Columns(2).Columnwidth = 10
			xlsheet.Columns(3).Columnwidth = 10
			xlsheet.Columns(4).Columnwidth = 51
			xlsheet.Columns(5).Columnwidth = 38
			xlsheet.Columns(6).Columnwidth = 11
			xlsheet.Columns(7).Columnwidth = 17
			xlsheet.Columns(8).Columnwidth = 22
			xlsheet.Columns(9).Columnwidth = 10
			xlsheet.Columns(10).Columnwidth = 10
			xlsheet.Columns(11).Columnwidth = 10
			xlsheet.Columns(12).Columnwidth = 15
			xlsheet.Columns(13).Columnwidth = 22
			xlsheet.Columns(14).Columnwidth = 18
			xlsheet.Columns(15).Columnwidth = 50
			
			
			
			xlSheet.Cells(x,1).Value = RepDoc.name_imported
			xlSheet.Cells(x,2).Value = RepDoc.ccc
			xlSheet.Cells(x,3).Value = RepDoc.cert_adjusted
			xlSheet.Cells(x,4).Value = RepDoc.bundle_combined
			xlSheet.Cells(x,5).Value = Join(RepDoc.addons, ";")
			xlSheet.Cells(x,6).Value = RepDoc.porting	
			xlSheet.Cells(x,7).Value = RepDoc.EE_Name
			xlSheet.Cells(x,8).Value = RepDoc.address_combined
			xlSheet.Cells(x,9).Value = RepDoc.city
			xlSheet.Cells(x,10).Value = RepDoc.state
			xlSheet.Cells(x,11).Value = RepDoc.zip
			xlSheet.Cells(x,12).Value = RepDoc.phone
			xlSheet.Cells(x,13).Value = RepDoc.email
			xlSheet.Cells(x,14).Value = RepDoc.contact_phone
			xlSheet.Cells(x,15).Value = RepDoc.comments
			
			RecNum = x - 3 ' optional
			Print "Gathering Data.  Record Number: " + RecNum  ' optional
			x = x + 1                    
		Else						
			Set RepDoc = Repview.GetLastDocument                  ' While not (RepDoc) is nothing
		End If                   
		Set RepDoc = Repview.GetNextDocument(RepDoc)
	Wend
	
	Print "Data Collection Complete."
	
	Set xlSheet = xlApp.Workbooks(1).Worksheets("Sheet1")
	Set range1 = xlsheet.Range("A1: Z2000") 
	Call range1.Sort(xlsheet.Columns("A"), , , , , , , 1)
	
	
	Msgbox "Your BlackBerry Report is ready in Excel. Click OK and then open the Excel file flashing near the bottom of your screen.  ", 64, "Excel Export"
	
	xlApp.Visible = True
	xlApp.UserControl = True
 End If

Select allOpen in new window

Hi
The code will be started from action button, placed in the user view with documents to get collection based on date range. It can be placed in action code or in agent, started by action.

Date range selection documents are created by small form "fmReportDateDialog".
Those docs store previous date range selected by user.

Date range selection documents are listed in hidden view, here named "(new-view-name)", you select better name for it. This view's first column is sorted by user name.

When testing code, check that in LotusScript options, you have the "Option Declare" mentioned. It ensures all variables are declared before using them.

Test code step-by-step, placing "Exit Sub" statements in code.

For example, at first stop code on row 75, to see if date selection part works OK. Then remove it and see if code in loop works.

Also use error trapping code, by "on error goto errorhandler" and Error functions/properties.
Use LotusSript Debugger to see what is going on.

Trying to get all this code together, I am getting red errors at 3 points and I cannot figure out why.

Fist 2 are coming up red
      Set dlgDoc = vwDt.GetDocumentByKey(username)
      
                        RecNum = x - 3

This has Variable not declared, have used this many time and never seen this error.
      Set range1 = xlsheet.Range("A1: Z2000")

Sub Click(Source As Button)
	' Example: get date document for current user
	Dim vwDt As NotesView
	Dim dlgDoc As NotesDocument
	
	Dim session As New NotesSession
	Dim ws As New NotesUIWorkspace
	Dim uidoc As NotesUIDocument
	Dim db As NotesDatabase
	Dim view As NotesView
	
	Dim Mgrdc As NotesDocumentcollection
	Dim MgrDoc As notesDocument
	
	Dim Repdc As NotesDocumentCollection     
	Dim RepDoc As NotesDocument
	Dim mgrview As NotesView     
	Dim RepView As NotesView
	Dim txt_Agency As String  
	Dim tmp_NotesName As NotesName
	Dim MgrKey, RepKey, ADKey As String
	Dim ParticipantsItem As NotesItem
	
	Dim xlApp As Variant
	Dim xlSheet As Variant
	Dim i As Integer
	Dim x As Integer
	Set db = session.CurrentDatabase
	Set Mgrdc = db.UnprocessedDocuments
	Set Mgrdoc = Mgrdc.GetFirstDocument
' Get the view with date picker documents
	Set vwDt = db.GetView("(date_selections)")  
	
' userName is extracted earlier from session object
' user name must be in the same format as 
' saved in date picker document, otherwise we get a new document 
' at each run
	
	Set dlgDoc = vwDt.GetDocumentByKey(username)
	
	If dlgDoc Is Nothing Then
' if there is no dialog document for this user, create one      
		
		Set dlgDoc = db.CreateDocument
		dlgDoc.Form = "fmReportDateDialog" ' a date picker form name
		
' Fill the fields, if needed, for example default dates
		
		Call dlgDoc.Save(True,False) ' Save doc object
	End If
	
        ' Open document in dialog window
	
	Dim GetUserInput As Boolean ' check, what button was pressed
	
	GetUserInput = ws.DialogBox("fmReportDateDialog",_
	True, _ ' autoHorzFit
	True, _ ' autoVertFit
	False, _ ' noCancel
	False, _ ' noNewFields
	False, _ ' noFieldUpdate
	False, _ ' readOnly
	"Report preferences", _ ' title$
	dlgDoc, _ ' notesDocument
	True, _ ' sizeToTable
	False, _ 'noOkCancel
	False) ' okCancelAtBottom
	
	If GetUserInput=False Then
		Print "Report generation cancelled by user"
		Exit Sub
	End If
	
     ' Save dialog document to get selected dates
	Call dlgDoc.Save(True,False)
	
	Dim fromDate As Variant
	Dim toDate As Variant
	Dim tmpDate As Variant
	
	fromDate = Datevalue(dlgDoc.start_date(0)) 'field in date picker doc
	toDate = Datevalue(dlgDoc.end_date(0))'field in date picker doc
	
' Next lines go inside the loop
' get the date of current document
	
	tmpDate =  Datevalue(RepDoc.Created(0)) ' ?or creation date?
	
	If  tmpDate >= fromDate And tmpDate <= toDate Then
		
   ' --- Fill Excel row with current document data. ---
		RepKey = "ibpc_user)record"
		
		Set Repview = db.GetView( "(ibpc_export)")  
		Set RepDoc  = Repview.GetDocumentByKey( RepKey )    ' form name must be first colum and sorted
		
		Print "Creating Excel Workbook..."
		Set xlApp = CreateObject("Excel.application")
		Print "Creating Excel Worksheet for BlackBerry Data.."
		xlApp.Workbooks.Add
		Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
		
		xlSheet.Cells(1,1).Value = "Name"     ' change your column headings
		xlSheet.Cells(1,2).Value = "Office ID"
		xlSheet.Cells(1,3).Value = "Order Type"
		
		
		x = 3
		While Not ( RepDoc Is Nothing )                 ' as long as it has a handle on a document do the following
			If  RepDoc.Form(0) = "ibpc_user_record" Then   		
				
				xlSheet.Rows("1:1").Select			
				xlSheet.Rows(1).WrapText =True
				xlsheet.Columns(1).Columnwidth = 22
				xlsheet.Columns(2).Columnwidth = 10
				xlsheet.Columns(3).Columnwidth = 10
				
				xlSheet.Cells(x,1).Value = RepDoc.username
				xlSheet.Cells(x,2).Value = RepDoc.office_number
				xlSheet.Cells(x,3).Value = RepDoc.dlm_vap
				RecNum = x - 3 
				Print "Gathering Data.  Record Number: " + RecNum  ' optional
				x = x + 1                    
			Else						
				Set RepDoc = Repview.GetLastDocument                  ' While not (RepDoc) is nothing
			End If                   
			Set RepDoc = Repview.GetNextDocument(RepDoc)
		Wend
		
		Print "Data Collection Complete."
		
		Set xlSheet = xlApp.Workbooks(1).Worksheets("Sheet1")
		Set range1 = xlsheet.Range("A1: Z2000") 
		Call range1.Sort(xlsheet.Columns("A"), , , , , , , 1)
		
		
		Msgbox "Your IBPC Refresh Report is ready in Excel. Click OK and then open the Excel file flashing near the bottom of your screen.  ", 64, "Excel Export"
		
		xlApp.Visible = True
		xlApp.UserControl = True
	End If

Select allOpen in new window

1. "username" was not declared, it was a placeholder in example.
Use something like "session.UserName" instead of it.

Or declare userName variable and fill it like:

dim useName As String
userName = session.Username

Then when creating dlgDoc, save the same name in User name field. Field can be named differently, to avoid a confusion about everything named username.

dlgDoc.fieldUserName = userName

In dlgDoc, you can pre-fill date fields, using NotesDate object and NotesDate.Adjust methtod.
---
If you have Option Declare active, then you are forced to declare all variables, even Variants.
Without it, every undeclared variable becomes Variant.

Option declare option issues errors on undeclared variables.

It is a little bit annoying at first, but forces a good habit of declaring everything, it helps to avoid type mismatch errors or too many Variants in future. Variants are the slowest and most complex variables, it is always good to use Integers for numbers in loops, under 32K range and Long for bigger loops/numbers.

OK making progress, got all the Dims done. Now I am getting an Object Variable Not Set on the line below yet both of these are Dimmed in the code. And vwDt is set just above it.

    Set dlgDoc = vwDt.GetDocumentByKey(userName)

Double-Check the vwDt name.

If db is opened OK, the wrong view name is the number 1 reason for Object variable not set error.
Check user name format also.

I took the name of the view from the view prop and pasted it right into the code. I do see a VWDT in debugger but I do not have any details for this item, just that and Notes View to the right. I also checked the username for the session and the value of the first column of my view, which is sorted and they match. Strange it seems like it should be working here. Will keep plugging away.

If you see a variable VWDT but not its content in the middle of debugger screen, then the view is not initialized, resulting in Obj. var. not set error.

Probably the view name is still wrong.

 Maybe you have no parentheses around view name? Example code was using hidden view "(viewname)".  If your view is not hidden, remove the inner parentheses.

Got past that one, now I am stuck on the line below. Where in the original code do I link this check to the underlying user record that I am evaluating the creation date. Do I need to do some sort of Set RepDoc = doc.ibpc_user_record?


    tmpDate =  Datevalue(RepDoc.Created(0))

As I understand, in original code you filter out documents with form blackberry_order_form
and now you want to set additional filter for only rows for selected time period?

Then add date comparison after check for document form.

1. Check form
2. Get creation date of document
3. Compare if the creation date is within date period.
4. If it is, write data to Excel rows.
5. Get next document

BTW, when using doc.Created property, maybe the tmpDate variable is not needed.
doc.Created is a Date type, so comparison might success without conversions. But i'll rather test  it before, date and time calculations are tricky in LotusScript.

Also, as doc.Created is property, not field, its value can be accessed with "doc.Created", not doc.Created(0). Latter is needed for field values, as they are always in the form of array.

   

Old code:
 
    x = 3
    While Not ( RepDoc Is Nothing )   ' While handle...            
        If  RepDoc.Form(0) = "blackberry_order_form" Then  
 
' get date value
' if you use doc.Created property, then (0) is not needed
' as with field values
 
            tmpDate =  Datevalue(RepDoc.Created(0))
 
           xlSheet.Rows("1:1").Select
            xlSheet.Rows(1).WrapText =True
	    xlsheet.Columns(1).Columnwidth = 22
			
	    ' --- other rows skipped 
 
 
' -------------------- New code -------------------
 
 While Not ( RepDoc Is Nothing )   ' While handle...
            
        If  RepDoc.Form(0) = "blackberry_order_form" Then  
 
           tmpDate =  Datevalue(RepDoc.Created)  ' 
 
' doc.Created is document class property, no (0) needed as
' for field items       
 
       If  tmpDate >= fromDate And tmpDate <= toDate Then
 
            xlSheet.Rows("1:1").Select
            xlSheet.Rows(1).WrapText =True
	    xlsheet.Columns(1).Columnwidth = 22
			
	    ' --- other rows skipped 

Select allOpen in new window

The issue I seem to be having is happening higher up in the before it gets to the excel part, in the third line below

	
' Next lines go inside the loop
' get the date of current document
	
	tmpDate =  Datevalue(RepDoc.Created(0)) ' ?or creation date?
	
	If  tmpDate >= fromDate And tmpDate <= toDate Then
		
   ' --- Fill Excel row with current document data. ---
		RepKey = "ibpc_user)record"
		
		Set Repview = db.GetView( "(ibpc_export)")  
		Set RepDoc  = Repview.GetDocumentByKey( RepKey )    ' form name must be first colum and sorted
		
		Print "Creating Excel Workbook..."
		Set xlApp = CreateObject("Excel.application")
		Print "Creating Excel Worksheet for BlackBerry Data.."
		xlApp.Workbooks.Add
		Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
		
		xlSheet.Cells(1,1).Value = "Name"     ' change your column headings
		xlSheet.Cells(1,2).Value = "Office ID"
		xlSheet.Cells(1,3).Value = "Order Type"
		
		
		x = 3
		While Not ( RepDoc Is Nothing )                 ' as long as it has a handle on a document do the following
			If  RepDoc.Form(0) = "ibpc_user_record" Then   		
				
				xlSheet.Rows("1:1").Select			
				xlSheet.Rows(1).WrapText =True
				xlsheet.Columns(1).Columnwidth = 22

Select allOpen in new window