The ability to quickly and easily locate a document is often tied to the design of one or more database views. Many database documents include fields such as Creation Date, Due Date, Invoice Date, etc., that could be used as the basis for a view. This section illustrates an approach that can be used to sort documents first by year and then by month. For example, Figure 15.1 illustrates a view that displays company invoices.
Figure 15.1. Example view with documents sorted by year and month
How It Works
Several Formula Language functions are used to convert numeric year and month values into text strings. Three columns are required to implement this technique. The first column displays the year using the @Year function. The resulting value is subsequently converted to a string by using the @Text function. The second column, which is hidden, contains the numeric month value and is used to sort the months in sequential order. The third column contains the month of the year. This is computed by using the @Select statement to compute the @Month value. The last column displays data from the document.
When implemented, all documents are grouped together (or categorized) based on a specified document date. If unable to determine the date for a given document, the document is displayed in a default category called "No Date Specified". This is an arbitrary message that can be changed as desired.
To implement this solution, create a view with four columns. Complete the following steps to configure the view columns.
The first column will contain the year associated with the document. Double-click on the default column to display the properties dialog. On tab 1, set the column name to Year, specify a column width and select Show twistie when row is expandable. Switch to tab 2. Set the sort order to Descending and Categorized. Close the properties dialog. In the Programmer's pane, select Formula as the display type and insert the following formula. Be sure to replace FIELDNAME with an actual date field from the form.
myDate := FIELDNAME; msg1 := "Documents for " + @Text(@Year(myDate)); msg2 := "No Date Specified"; @If (@Text(myDate) = ""; msg2; msg1)
The second column contains the numeric value that corresponds to the calendar month. This column is sorted in ascending order and should be hidden.
myDate := FIELDNAME; msg1 := @Select(@Month(myDate); "1"; "2"; "3"; "4"; "5"; "6"; "7"; "8"; "9"; "10"; "11"; "12"); msg2 := "0"; @If (@Text(myDate) = ""; msg2; @TextToNumber(msg1))
The third column converts the numeric month to a text month value. This is a categorized column and is sorted in ascending order.
myDate := FIELDNAME; msg1 := @Select(@Month(myDate); "January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"); msg2 := "No Date"; @If (@Text(myDate) = ""; msg2; @Text(msg1))
Create additional view columns as needed to display the remaining document information in the view.
An Introduction to the Lotus Domino Tool Suite
Getting Started with Designer
Navigating the Domino Designer Workspace
Domino Design Elements
An Introduction to Formula Language
An Introduction to LotusScript
Fundamentals of a Notes Application
Reference Library Applications
Design Enhancements Using LotusScript
Design Enhancements Using Formula Language
Miscellaneous Enhancements and Tips for Domino Databases
Application Deployment and Maintenance
Appendix A. Online Project Files and Sample Applications
Appendix B. IBM® Lotus® Notes® and Domino®Whats Next?