Spanky's Place :: Excluding non-current Fiscal Year documents from Notes Views

This should be my final post on this subject.

In my first entry, I introduced some of the problems with Fiscal Year calculations, particularly within Notes Views. In my second posting, I showed you the code I put together to dynamically calculate Fiscal Year information within a view. In this entry, I'm going to explain how to create a view that only shows documents for the Current Fiscal Year, and won't need to be "adjusted" by the developer every year. As with my previous two posts, this uses nothing more than some creative @Formula code.

Sound fun? Let's get started

For the purposes of this example, I'm only going to show a single column of information in the view, that being the value of the DateCheck field. All of the "magic" in this view is contained in the selection formula. The formula, surprisingly, is actually pretty simple. I'll break it down into chunks and explain how it works.

The first part simply sets the the Fiscal Year Code (I explained that in part 2 of this series), some fields to track the current Calendar Year, Month, and Day of Month, and then parses the Fiscal Year Code to figure out the beginning Month, Day of Month, and whether the Fiscal Year starts before, during, or after the Calendar Year:

View selection formula part 1 vFYMD := "-0715";
vToday := @Today;
vCY := @Year(vToday);
vCM := @Month(vToday);
vCD := @Day(vToday);

vFYM := @ToNumber(@Middle(vFYMD; 1; 2));
vFYD := @ToNumber(@Right(vFYMD; 2));
vPlusMinus := @Left(vFYMD; 1);

You may notice that in this formula, I'm not really concerned about what Fiscal Year it actually is. The purpose of this view is to display only documents whose transaction date occurred during the current Fiscal Year, whatever that year happens to be. Therefore, it doesn't really matter what the current fiscal year is. The next part of the formula will use the previous information to determine on what date the current fiscal year actually begins. It does this by first setting the "base" Fiscal Year, which is determined by the relationship between the Fiscal Year and the current Calendar Year. The code will then determine any adjustments that need to be made, based on the the relationships between the current month, current day of month, and the Fiscal Year Begin month & day of month. Once these adjustments are made, the Fiscal Year Begin date is set:

View selection formula part 2 vFYBase := @If(vPlusMinus = "-"; vCY - 1; vPlusMinus = "+"; vCY + 1; vCY);
vAdjust := @If(vCM < vFYM; 0;
vCM > vFYM; 1;
vCD < vFYD; -1;
0);
vFYY := vFYBase + vAdjust;
vFYBegins := @Date(vFYY; vFYM; vFYD);

The next part of the formula simply grabs the financial transaction date field (DateCheck), and pulls the Year, Month, and Day of Month out of it:

View selection formula part 3 vCheckDate := DateCheck;
vCheckY := @Year(vCheckDate);
vCheckM := @Month(vCheckDate);
vCheckD := @Day(vCheckDate);

The next part of the formula does nothing more than figure out the difference (in seconds) between DateCheck and the beginning date of the Fiscal Year. This is then used to adjust the the Calendar Year begin date, and then the Year of that date is pulled into a temporary variable:

View selection formula part 4 vSecsDiff := vCheckDate - vFYBegins;
vFYDate := @Date(vCY; 1; 1);
vFY := @Year(@Adjust(vFYDate; 0;0;0;0;0;vSecsDiff));

Finally, we are down to the actual selection. The final part is:

SELECT (Form = "FYTest") & (vCY = vFY)

Well, that's all there is to it. I hope this example has been useful to you. I have posted the sample database to my downloads page. Feel free to use the code as you wish.

Hope this helps!

-Devin