You need a calculation that identifies the fiscal year. Let's assume your date field is called "Date". cFiscalYear might then be defined as:
Year ( Date ) - Month ( Date ) < 6
This will return a "fiscal year" of 2012 for the first date range and 2013 for the second date range specified in your posts.
You can then perform finds and sorts using cFsicalYear to find and group records by fiscal year.
Thank you very much,
I created a field called cFiscalYear with the following calcualtion Year ( Date ) - Month ( Date ) < 6 But I get a question mark in the field.
do I set cFiscalYear Calculated result as a Date or Number?
When doing a find or sort do I use the Date Field and the cFiscalYear?
The data I am currently working with is from January 2012 to December 2012
I have another file that has January 2013 to present. I just found out that the data needed is fiscal year 6/30/2012 to 7/1/2013 so I will have to import the 2013 data into the 2012 file to have all data in one file.
The result type should be number.
And you definitely should keep all of this data in the same table of the same file if there is the slightest chance that you will need reports spanning multiple fiscal years.
When you sort, you can sort by cFiscalYear, then by date or even just by cFiscalYear. It depends on the results that you want. By sorting by cFiscalYear, you can add a sub summary layout part to your layout and summary fields placed in that layout part will compute totals for the fiscal year.
And you can put search criteria in cFiscalyear for finds if you want, but you can also put date ranges in the date field to get the same results.
in find mode:
Putting 2012 in cFiscalYear will be the same as putting 6/30/2012...7/1/2013 in the date field. to find all records in cFiscalYear 2012.
Using Find and putting 2012 in the cFiscalYear give error no record match. cFiscalYear has a default zero in the field.
I imported all of 2013 records so now I have an up to date file from January 2012 to March 2013
When sorting it shows all records
I created a script that sorts;
Ascending order: Investigator
Ascending order: cFiscalYear
Descending order: Date
cFiscalYear has a default zero in the field.
That shouldn't be the case. Is this a field of type calculation or a number field with an auto-entered calculation? It should be a field of type calculation.
is date a real date field or did you enter your dates into a field of type text?
The date is auto entered when a new record is added.
here is image 1
To repeat from my last post. cFiscalYear should be defined as a calculation field, not as a number field with an auto-entered calculation.
When you add a field with an auto-entered calculation field, or change the definition on a field to add or change an auto-entered calculation, the value in the field does not update automatically for existing records in your table. Using a field of type calculation avoids these issues.
It is, however, possible to force an update on auto-entered calculation fields when adding or changing the defined calculation: Updating values in auto-enter calc fields without using Replace Field Contents
Yes I had it as a calculated field but it had zero in the field so I changed to number field to see if I did it wrong. I will read through the link you provided. Thanks
The calculation field should work and should not return a zero. The link provided shows how to get auot-entered calculations to update existing records when you install a change to the expression used. It will not resolve the issue as to why you are getting a zero result. That result indicates a problem either with your data, how you defined the calculation or in the data type selected for your date field.