Spanky's Place

As I was discussing, when coding a view (or most other things), developers tend to hard code the Fiscal Year start or begin date, which can lead to problems. In addition, hard coding the Fiscal Year also pretty much guarantees that you need to touch the design at least once a year, even if everything is working ok. The reason for this should be obvious -a hard coded Fiscal Year doesn't change from year to year.

So, the solution is to code the view in such a way that it Dynamically handles the year to year changes, and can be easily modified if the company decides to change the Fiscal Year begin date. (If and when this happens, you the developer are perfectly within your rights to slap the decision makers with a large fish. It won't necessarily change their decision, but it sure is fun).

Anyway, the secret to making this work is to properly define the problem, and then break it down into easily handled "chunks". I'll define the problem as:

1. Given any set of documents within a Notes database that have a common financial transaction date field, create a view that will display the documents, and include in the view the Fiscal Year of the transaction.
2. Given the same set of documents, create a view that will only display the documents for the current fiscal year.
3. Hard-coded date information, other than the date stored in the field on the document, is not allowed.
4. The only information that can be used from the document for the purposes of determining the fiscal year is the financial transaction date field.

Sound interesting? Let's begin.

For this example, the field name will be DateCheck.

When I originally started thinking about this problem (right after my buddy called), I immediately started down a wrong path of thought. I was trying to figure out how to determine the Fiscal Year of the field, when I had no "fixed" point of reference. The first thing that needs to be done is to figure out the date the Fiscal Year begins, and what Fiscal Year it is. Just knowing that a Fiscal Year begins on April 1st is useless without knowing the relationship between the Fiscal Year and the Calendar Year. So, the first "chunk" of the problem we need to solve is to figure out this relationship, and create a bit of code that will allow us to determine when the current Fiscal Year begins, and what Fiscal Year (1998, 1999. etc). it happens to be. Remember, one of the requirements is that we CANNOT hard code this date.

We can, however, hard code some other information. All we need to know in order to determine when any given Fiscal Year begins is the day and month the Fiscal Year begins on, and the relationship between the Fiscal Year and the Calendar year. I put my thinking cap on, and came up with the following format to handle this (you can go ahead and invent your own if you feel you must,; but I've already done the "hard stuff").

+/-/=mmdd

"So what? Big deal." I can almost hear you say. Well, let me explain this format a bit, and show you some code, and perhaps you'll realize that this actually is a pretty cool thing. I'm sure you can figure out the mmdd part, but just in case you can't, these characters indicate the month and day of month that the Fiscal Year begins on. The mm means that for a single digit month, (such as April), the month # must begin with a leading zero. The same goes for the day of month part. So, if the Fiscal Year begins on April 1st, this part of the code would be 0401. I don't mean to sound condescending, I just want to make sure that everybody understands the formatting.

Ok, so on to the +/-/= part of the code. This means that the code must begin with any one of these characters. (Total length of this code string must be 5 characters). The characters have the following meaning:

• + the Fiscal Year begins AFTER the Calendar Year ends.
• - the Fiscal Year begins BEFORE the Calendar Year begins.
• = the Fiscal Year begins DURING the current Calendar Year.

Examples, for any given calendar year:

• -0701 Fiscal Year begins on July 1st of the previous calendar year.
• +1001 Fiscal Year begins on October 1st of the next calendar year.
• =0315 Fiscal Year begins on March 15th of the current calendar year.

Now, if we are allowing a user to enter the code, then some field validation needs to be done. I'm not going to cover that here; the code is available in the sample database. (Check the CFD field called FYCodeValid).

Using information formatted in this way, we can write some @Formula that will tell us what the current Fiscal Year is, and when it begins. Remember, the trick here it to completely forget about that pesky DateCheck field; all we want to find out for now is the information about the Fiscal Year itself.

The formula is going to produce another formatted string. Yes, I arbitrarily decided on the format (it is my solution after all); however the reason for the formatting I selected will become clear further along. The format for this output is:

yyyy|yyyy|m|d

This breaks down as follows:

• The first yyyy indicates what the current Fiscal Year is.
• The second yyyy indicates the Calendar Year that the current Fiscal Year begins in.
• The m indicates the Month (notice that a leading zero is not required) that the current Fiscal Year begins in.
• The d indicated the Day of Month (again, leading zero is not required) that the current Fiscal Year begins on.

So, how do we get from +/-/=mmdd to yyyy|yyyy|m|d? Remembering that +/-/=mmdd defines the relationship between the Fiscal Year and the Calendar Year, all that is needed is to pick an arbitrary year and figure it out. Seeing as any year will do, why not pick the current year? Well, that's exactly what I did. I'll get to the code in a moment, but first I want to discuss the view design.

For the first view, I want to sort the documents by the DateCheck field. This isn't a requirement for this solution; I just wanted to have a sorted view. I set the 1st column in the view as sorted (ascending), and to display the DateCheck field. I'm only going do display "FYTest" forms in the view, so the selection formula is:

SELECT Form = "FYTest"

I know, so far this is pretty simple.

My next column is where I'm going to hard-code the Fiscal Year formula (+/-/=mmdd). "Yikes!" you say, "never hard-code view information!" Well, this is an exception to the rule. I'm coding it here so that I can reference it in other view column formulas. If the company ever changes the Fiscal Year Begin date, the only change that is necessary is to change this single column. Referencing a column value in a view is pretty easy to do; all you have to do is set the Programmatic Name on the advanced tab of the view column properties:

There are, however, a couple of caveats to this that are not mentioned in Designer help. Pay attention now, because I'm going to save you hours of frustration:

1. If you assign a name that matches a field name on a document being displayed by the view, and then try to reference it in a later column, the field value takes precedence.
2. If you set a value for a view column that is considered by Notes/Domino to be a LITERAL CONSTANT; and then attempt to reference the column, the value returned will be BLANK. Only view column values that are actually computed or pull information from the document will be returned when a referencing a view column from within a view.

So, how do I set the vFYCode (+/-/=mmdd) view column constant in such a way that I can also reference it later? Simple. I just add a computed value to the end of it, one that is both quick to compute, and yet simple to duplicate (so I can strip it off in later columns). This is easily accomplished by simply tacking on the value for the FORM to the end of the FYCode. For the purposes of this example, I'm going to arbitrarily set the fiscal year begin date as July 15th of the previous calendar year:

"-0715" + Form

Wow, that's a lot of lead in for such a simple little formula. Yeah, I know. I'll try to work on being a little more concise.

The next column in my sample database has code that validates the vFYCode view column. In this case, because I'm hard-coding the value for the code, this column isn't really necessary. I left it in as an example of how to do column validation; but I'm not going to cover it here.

Column 4 is where I actually have some functional code. This column is going to convert the vFYCode (+/-/=mmdd) to the vFYInfo (yyyy|yyyy|m|d). Here is the formula:

Column 4 Code

@If(vFYCodeValid; ""; @Return(""));
vFYMD := @Left(vFYCode; 5);
vToday := @Today;
vCY := @Year(vToday);
vCM := @Month(vToday);
vCD := @Day(vToday);
vSep := "|";
vFYM := @ToNumber(@Middle(vFYMD; 1; 2));
vFYD := @ToNumber(@Right(vFYMD; 2));
vPlusMinus := @Left(vFYMD; 1);
vFYBase := @If(vPlusMinus = "-"; vCY - 1;
    vPlusMinus = "+"; vCY + 1;
    vCY);

vAdjust := @If(vCM < vFYM; 0;
    vCM > vFYM; 1;
    vCD < vFYD; -1;
    0);

vFY := vFYBase + vAdjust;
@Implode(@Text(vCY:vFY:vFYM:vFYD);vSep);

As of this writing, Column 4 should display "2006|2005|7|15". However, as time goes by, the value displayed in this column will change. This is what allows the view to dynamically display correct fiscal year information year after year, without need for modification.

Moving on to Column 5, all I'm doing there is displaying the current fiscal year. Why do I do this? Just to make things clearer and easier to understand (this is a demonstration, after all). The formula for this column is pretty simple:

Column 5 Code

@If(vFYCodeValid; ""; @Return(""));
@Left(vFYInfo; "|")

At this point, I'm finally ready to actually work with the DateCheck field to determine within what Fiscal Year the transaction occurred. Now that I know what Fiscal Year it is, and the date the Fiscal Year begins, all that is necessary is to:

1. Determine the time difference between the Fiscal Year begin and the date to check.
2. Add that difference to the beginning of the Calendar Year of the Fiscal Year.
3. Return the Year portion of the resulting date.

This really isn't that difficult to do at all. If you examine the following code, you should discover my reason for defining the format of the FYInfo to be yyyy|yyyy|m|d. Here's the code:

Column 6 Code

@If(vFYCodeValid; ""; @Return(""));
vSep := "|";
vFY := @ToNumber(@Word(vFYInfo; vSep; 1));
vFYY := @ToNumber(@Word(vFYInfo; vSep; 2));
vFYM := @ToNumber(@Word(vFYInfo; vSep; 3));
vFYD := @ToNumber(@Word(vFYInfo; vSep; 4));

vFYBegins := @DatevFYY; vFYM; vFYD);

vCheckDate := vDateCheck;
vCheckY := @Year(vCheckDate);
vCheckM := @Month(vCheckDate);
vCheckD := @Day(vCheckDate);

vSecsDiff := vCheckDate - vFYBegins;

vFYDate := @Date(vFY; 1; 1);
@Year(@Adjust(vFYDate; 0;0;0;0;0;vSecsDiff));

While the final column may seem like an afterthought; it is actually the main thing my friend was asking about. For any given document, it would be nice to quickly see in the view whether the transaction occurred before, during, or after the current Fiscal Year. Because the view is already displaying the current Fiscal Year and the Fiscal Year of the transaction, the formula for this final column is really simple:

Column 7 Formula

@If(vFYCodeValid; ""; @Return(""));
vCFY := @ToNumber(vCurrentFY);
vDCFY := @ToNumber(vDateCheckFY);

@If(vCFY < vDCFY; "After";
    vCFY > vDCFY; "Before";
    "During");

Here is how the view looks with some test data:

That's it for the first view. That is also pretty much it for this entry. In my next entry, I'll show you how to do view #2.

-Devin