?
Solved

Filtering a view in R5 with a dialog box form

Posted on 2006-05-10
7
Medium Priority
?
306 Views
Last Modified: 2013-12-18
I am working with an R5 server and client and need to provide functionality to filter a view for documents that fall within a certain date range.  The documents in question have two date fields, "StartDate" and "EndDate".  I have created a form that I can launch with @DialogBox that has its own two date fields, "StartFilterDate" and "EndFilterDate.  I want the user to be able to enter his/her dates in the dialog box and then clicking a button on the dialog box form will do a db.Search and return all the docs that fall within the date range entered in the dialog box.  I am not to familiar with creating queries in LS.  The documents that should be returned should meet this criteria....  If the range of the searched docs (StartDate-EndDate) falls anywhere within the search dates (StartFilterDate - EndFilterDate), the docs should be returned.  I think I would be all set if they had R6 and I could use @SetViewInfo but no go in R5.

Any help would be appreciated.

This is what I have so far...

Sub Click(Source As Button)
      Dim session As New NotesSession
      Dim dt As New NotesDateTime("07/15/97")
      Dim db As NotesDatabase
      Dim dc As NotesDocumentCollection
      Dim doc As NotesDocument
      Dim searchFormula As String
      Set db = session.CurrentDatabase
      Set dc = db.Search(searchFormula, dt, 0)
      searchFormula = {Search formula here}
      Set doc = dc.GetFirstDocument()
      While Not(doc Is Nothing)
            Messagebox doc.Subject(0)
            Set doc = dc.GetNextDocument(doc)
            
      Wend
      
      
End Sub
0
Comment
Question by:ccarlson62
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:marilyng
ID: 16654865
the non-full text search simply follows formula rules, so you create a searchString, and make sure quotes are doubled, so use the curly braces.
When you have a date range, you need to decide how restrictive you want to be.   For example, seeing if one date falls in a range is easy.

Seeing if a range falls into another range is a bit more difficult.  For instance:
StartDate="03/01/2006" and EndDate="03/31/2006"

If my filter is StartDateFilter="02/15/2006" and EndDateFilter="03/15/2006"

Then the following formula will be true for the startDateFilter:

searchMacro = {Form = "Your Form Name" & StartDate <=[} & dialogdoc.EndDateFilter (0) & {] & EndDate >=[} & doc.StartDateFilter(0) & {]}

So, if you want none of the dates to fall outside of the startDateFilter, then you would have to fiddle with the formula some more.


Here is example:

Sub Click(Source As Button)
      Dim Session As New NotesSession
      Dim db As NotesDatabase
      Set db = session.currentdatabase
      Dim doc As NotesDocument
      Dim coll As NotesDocumentCollection
      Dim dialogdoc As NotesDocument
      Set dialogdoc = db.Createdocument
      With dialogdoc
            .form="the form name"
            .StartDateFilter = Today()
            .EndDateFilter=Today()
            .computewithform False, False
      End With
      Dim result As Variant
      Dim ws As New NotesUIWorkspace
      'call the dialog box so that you have the handle on the fields to be returned
      result = ws.dialogbox("the form name",True, True, False, False, True,False, "Date Range",dialogdoc)
      If Not result Then Exit Sub 'user canceled
      
      'Now we have the dates to filter
      
      Dim searchMacro As String
      searchMacro = {Form = "Your Form Name" & StartDate <=[} & dialogdoc.EndDateFilter (0) & {] & EndDate >=[} & doc.StartDateFilter(0) & {]}
      
      Set coll = db.search(searchMacro, Nothing, 0)
      
      If coll.count > 0 Then
            Call coll.PutAllInFolder("This Folder")
      End If
      
      
End Sub
0
 

Author Comment

by:ccarlson62
ID: 16657084
Thanks for the response.  I made some minor changes to your example to match my field names and form names.  The dialogbox comes up and I can pick my date range to search on but I get an "Object Variable Not Set" when I click OK in the dialog box.  The debugger is of no help because it says it cannot debug inside of a dialog box.  I confirmed my field names and form names and I created the folder, "Filter Results".  Any help will be appreciated.  Increasing the points to 1000!

Sub Click(Source As Button)
      Dim Session As New NotesSession
      Dim db As NotesDatabase
      Set db = session.currentdatabase
      Dim doc As NotesDocument
      Dim coll As NotesDocumentCollection
      Dim dialogdoc As NotesDocument
      Set dialogdoc = db.Createdocument
      With dialogdoc
            .form="Filter Dialog"
            .StartFilterDate = Today()
            .EndFilterDate=Today()
            .computewithform False, False
      End With
      Dim result As Variant
      Dim ws As New NotesUIWorkspace
     'call the dialog box so that you have the handle on the fields to be returned
      result = ws.dialogbox("Filter Dialog",True, True, False, False, True,False, "Date Range",dialogdoc)
      If Not result Then Exit Sub 'user canceled
      
     'Now we have the dates to filter
      
      Dim searchMacro As String
      searchMacro = {Form = "Assignment" & StartDate <=[} & dialogdoc.EndFilterDate (0) & {] & EndDate >=[} & doc.StartFilterDate(0) & {]}
      
      Set coll = db.search(searchMacro, Nothing, 0)
      
      If coll.count > 0 Then
            Call coll.PutAllInFolder("Filter Results")
      End If
      
      
End Sub
0
 

Author Comment

by:ccarlson62
ID: 16657220
I will increase the points as soon as I can find out how to do it.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 18

Expert Comment

by:marilyng
ID: 16657349
Don't think you can :)

Add Option Declare to the Option section, and then save your button and see if you can get an undeclared error.

oops, typo:
 doc.StartFilterDate << dialogdoc.StartFilterDate(0)

Also, make sure you have a folder "Filter REsults"
0
 

Author Comment

by:ccarlson62
ID: 16657729
OK

I am making progess, fixed the typo...no more error.  Also, I need the search to pick up docs where the two date ranges overlap in any way so if (StartDate - EndDate) falls anwhere in (StartFilterDate - EndFilterDate) even by one day and even if any some days in (StartDate - EndDate) are both inside and outside of (StartFilterDate - EndFilterDate) range.  Hope that is clear.  I have always hated DT calcs.

Also, it looks like this code will place the resulting doc set into my folder "Filter Results" so I just need to add some code to open that folder correct?

Thanks


0
 
LVL 18

Accepted Solution

by:
marilyng earned 2000 total points
ID: 16660144
Me, too. DT Calc's give me headache.  So test!! to be sure I've done what you need.

Open folder(See notesuidatabase in Designer Help)

Call notesUIDatabase.OpenView( viewName$  [, key$] [, newinstance] [, replaceView] )
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16672022
Thanks for the points, good luck!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question