Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2844
  • Last Modified:

Filtering Lotus Notes view by Month & Year

I will first explain the current database features I have in place.

The Database contains a form (frm_certreq) with a field (fld_compdate) which is filled in with the current date when a button is pressed, marking the document as completed.
The date output is in the format dd/mm/yyyy using the code -

Open in new window

I currently have a view (view_compdate) which lists all the completed documents. The first collumn showing the fld_compdate field (dd/mm/yyyy).

I wish to setup a way in which users can filter this view by month and year in order to have the ability to export monthly results  for statistical purposes.

Any ideas or suggestions you have will be great in aiding my advancement with this database.
  • 6
  • 4
  • 2
1 Solution
Sjef BosmanGroupware ConsultantCommented:
First off, and generally speaking, you should never convert dates to text and save it in a document in order to use it later in calculations or views. I've seen dozens of issues with text dates. The general problem is that it isn't clear from the date text itself which format is used: dd/mm/yy or mm/dd/yy. In a larger company with users in areas with different date formats, you'd be in big trouble.

Now to the formula: you can use @TextToTme, e.g. like this:

dt:= @TextToTime(fld_compdate);
m:= @Month(dt);
y:= @Year(dt);

In your view, you can have two (new) categorized columns. The leftmost should have the above formula code to display the year, the second one the month (that formula should be easy).
Er, flame off, Sjef.

@SetField("fld_compdate";@Date(@Today)) returns a time-date value, so text is not used, and dd/mm/yyyy is just how the display of this time-date value is displayed. And Unifrax is not in any trouble.

Now for the view.
First column should be a hidden one showing fld_compdate, no processing or formatting necessary since its hidden. What is necessary is to sort it Ascending or Descending whatever is your preference.

Next, you need two categorized columns, the formula of the first is @Year(fld_compdate).
The formula of the second is simply fld_compdate, the formatting is done using the column properties box, 4th tab (Advanced Format).
Set 'Style' to: 'Date/Time'
Set 'Use prefrences from' to: 'Custom'
Select 'Display Date'
   Set 'Show' to: 'Only month'
   Set 'month' to how you want to display the month: (link to description of options)
      m: number without leading zero
      mm: number with leading zero
      mmm: 3 letter abbreviation
      mmmm: Month name
      Note: these last two are displayed according to the language setting of the Notes Client viewing the data.
Consider disabling the ability of users to Customize the view, to retain better control of the result.
Add more columns as per the requirements of the users.

Note: the links link to the publicly available Domino Info center (i.e. the web versions of the Help databases). You can customize which set of topics you want to show (handy for limiting the number of results).
Also , with the little icons just above the topics you can print or search a topic (optionally with its subtopics) very handy!
Sjef BosmanGroupware ConsultantCommented:
I could have sworn I saw an @Text(@Today) there... Ouch! My bad.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

UnifraxAuthor Commented:

That's great in navigating by year and month. The only problem I have is that when I press the export to excel function
Sub Click(Source As Button)
	Dim ws As New notesuiworkspace
	Dim uiview As NotesUIView
	Set uiview = ws.CurrentView
	Call ExportNotesView (uiview.ViewName, False)
End Sub

Open in new window

 everything from the view is exported rather than a specific month.
Sjef BosmanGroupware ConsultantCommented:
I suppose that's what ExportNotesView does: to export an entire view...

You have to modify its code, or add a new function. Is the current code yours? Are you willing to share?
Or can you explain what behaviour you want, for there may be other ways to achieve it.
UnifraxAuthor Commented:
Yes, the codes mine, no problems with sharing.

The document is marked as complete which then sets a field to the current date.


Open in new window

My view has a few columns with different information from the document.
I need to export the content of this view but only for a specific month
Sjef BosmanGroupware ConsultantCommented:
How do you want the user to indicate which month is to be exported? The view on your screen may be reduced to a specific month, but the Notes view itself doesn't change, only the presentation changes. The code of ExportNotesView should therefore be told what documents to select, by year and month.
UnifraxAuthor Commented:
Ahh right.

Is there a way that when the button to export the document is pressed it requests the input of a month and year from the user in order to know what to export?

or maybe setup a way in which that the users could set a 'from'  and 'up until' date that the code will check before exporting.
Sjef BosmanGroupware ConsultantCommented:
There are several ways to get additional input from the user:
- the InputBox function
- NotesUIWorkspace.Prompt
- you can present your own dialog form using NotesUIWorkspace.DialogBox
- you can ask the user to select documents in the view (but that will be rather error prone, a mistake is easily made)

I suppose that, using the Prompt method I mentioned above, you could present a list of years and months, and ask the user to select one (PROMPT_OKCANCELLIST) or maybe even multiple months (PROMPT_OKCANCELLISTMULT). The code should, once the view is opened, select documents for those periods and export them.
UnifraxAuthor Commented:
Would you elaborate somewhat more on the Prompt method?
Where do i put the code?
What is the est of the code needed
A possible example?

I am only a novice in lotus notes programming.
Sjef BosmanGroupware ConsultantCommented:
The Prompt method is a method of the NotesUIWorkspace class. It is fully documented in the Domino Designer Help database, please look it up in there... A quick link to that database somewhere on the net, with some examples that you'll find in the database as well:

You'd have to extend the functionality of the ExportNotesView function you use. Or copy it into a new function, with additional parameters that indicate the months to be exported.

You'd have to do much of the coding yourself, I'm afraid, but there's a bright side: when the job is done you won't be a novice notes programmer anymore! Of course, when you have questions, you can always come back, e.g. if you need suggestions on how to adapt ExportNotesView (but you'd have to present its code).
And, given your original question, may I suggest opening a new question for handling the Excel export?

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now