Questions:

Should all queries simply restrict results by fiscal year? 

General Notes on this Issue:

-any query that uses each budget allocation, transfers, encumbrances, balances, etc. , bring in budget year and fund group budget year and fund group fiscal year

-for just the transaction with the fund associated, no need to include related tables such as budget

-better to begin by joining to the main table for your query (e.g. finance_fund), then secondary tables, e.g. for invoice transactions, start with transactions derived table

-start with the table from which you will gather the most important columns

-using derived tables first best for performance

-purchase orders without transactions will not show fiscal year

-where parameters are included for both date ranges and fiscal year, make sure you are using dates within the given fiscal year (this is an important note to include for users in the README, too)

Example for Queries

Here is a helpful example from Stefan Dombek on the FOLIO project:

A fund_id can exist in different fiscal years because they are different budgets (due to fiscal year rollover). But they can only be distinguished by the fiscal year. And that can only be done via the budget table. If you want to join the data tables correctly, you should join them in this way:

folio_finance.fiscal_year                    
LEFT JOIN folio_finance.budget ON budget.fiscalyearid = fiscal_year.id
LEFT JOIN folio_finance.fund ON fund.id = budget.fundid
LEFT JOIN folio_finance.ledger ON ledger.id = fund.ledgerid

Fiscal Year Data Model

Here is a diagram of the data model for fiscal year in FOLIO. 

Restricting Your Query Results by Fiscal Year

There are different ways you can restrict your query results by fiscal year, for example:

• including a parameter at the top of the query for start and end dates using a date field such as invoice payment date, then setting the start and end dates to correspond with a given fiscal year
• including a parameter at the top of the query for the fiscal year code, then entering the fiscal year code for the fiscal year for which you wish to see data
• including a fiscal year code field in your query and setting it for a particular fiscal year

Checking results in the Finance App

To make sure your query results are showing the right results for a given fiscal year, check at least 3 rows of results against the same data in the Finance App in FOLIO.