I am making a database that budgets for a fiscal year. I open to a DEPARTMENT layout and I have set up to show only purchases in that department. I have additional field in the "purchases" table that indicates which fiscal year that purchase was made. (2004-05, 2005-06, etc)

How can I tell FM7 to only show the CURRENT fiscal year in that portal. I know it can be done, there are too many cool little tools in here. If not current, can I have a spot to pick which fiscal year?

I also have a table of all the companies we work with and it has the same set-up... view what was purchased in a given fiscal year.

I am using these portals to calculate how much has been spent and what is left... it does it dynamically every time the layouts are accessed. ANY HELP would be great.

Thanks!

Todd

Welcome Todd!

You can add fiscal year to the relationship that is used for that portal. To do this you will need a gFiscal Year global in the Department table and a Fiscal Year field in the Purchases table. In the Table Occurance graph, double click the relationship line that links the two table occurances. Then in the relationship definition dialog, add another relationship between Department::gFiscal Year and Purchases::Fiscal Year. If the Fiscal Year field in Purchases did not already exist, you can use a calculation based on Order Date or something.

You can set the gFiscal Year field once a year, or allow users to switch years to view other years' purchases.