# Field Calculation Help

This topic is 5607 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

I have a table with one field in called Set Fiscal Yr. This holds the current fiscal yr code. I have a field in my receipt file called fiscal year. I want this field to auto-enter the fiscal year listed in the set fiscal year table. I tried to use a calculated field by having the receipt fiscal year field equal the set fiscal year field but that does not work. Any suggestions?

##### Share on other sites

You need either a relationship between the two tables, or the preset code must be in a global field. Why not simply calculate the fiscal year from the current date?

##### Share on other sites

I tried to do that initially but nothing I tried worked. What calculation would I used?

##### Share on other sites

It depends on when your fiscal year starts, and what do you want to call it. For example:

Year ( date ) - ( Month ( date ) < 4 )

suits a fiscal year starting on April 1. It will return 2006 for any date between April 1 2006 and March 31 2007, inclusive.

##### Share on other sites

Where "date" is do you put in the acutal date, 4/1/06 or do you get the current date? If you put in the actual date do you have to change this each time the fiscal year changes?

Also, I have to total the receipts for the fiscal year. Based on the fiscal year returned how would I build a calculation to do this?

##### Share on other sites

Where "date" is, you can put any expression that returns a date, for example Get(CurrentDate), or the name of a date field. Normally, a financial record would have a date field, usually set to auto-enter the creation date. The fiscal year calculation would be referencing that field, automatically returning the correct fiscal year for ANY date entered into the referenced field. It's a "set-and-forget" thing: the only time you would have to change anything would be if the start of your fiscal year were to change from April 1.

To sum by fiscal year you need a summary field (Total of Amount). Then either find the records of the desired fiscal year, or add a sub-summary part (when sorted by FiscalYear) to your report layout.

##### Share on other sites

Thank you for your help. I have been struggling with the problem for a while.

##### Share on other sites

This topic is 5607 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account