Display Documents in a View by Year and Month

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

A.15.1

 

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.

Implementation

To implement this solution, create a view with four columns. Complete the following steps to configure the view columns.

Step 1.	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)
Step 2.	The second column contains the numeric value that corresponds to the calendar month. This column is sorted in ascending order and should be hidden. Select the Create > Append New Column menu options to add a new column. In the properties dialog, set the column width to 1 on tab 1, set the sort order to Ascending on tab 2, and select Hide column on tab 6. Close the properties dialog. Change the column display type to Formula and insert the following in the Programmer's pane. Be sure to replace FIELDNAME with the same field used in column one. This field must contain a valid date.   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))
Step 3.	The third column converts the numeric month to a text month value. This is a categorized column and is sorted in ascending order. Select the Create > Append New Column menu options to add a new column. In the properties dialog, set the column width to 7 and select Show twistie when row is expandable on tab 1. Set the sort order to Ascending and Categorized on tab 2. Close the properties dialog. In the Programmer's pane, set the column display type to Formula and insert the following formula. Be sure to replace FIELDNAME with the same field name value used in column one.   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))
Step 4.	Create additional view columns as needed to display the remaining document information in the view.