I'm building a database to track budgets, contracts, orders, and invoices. Right now, I'm focused on the budget and contract portion. Both are built (tables, forms) and operating indepedently; now I need to start building the ties between them.
I want the budget entry form to automatically calculate what the projected obligation will be based on the contracts in the database, and display that as an "Obligated" number. I can use DSUM for that (already have it, actually), but I want to be able to only present the amounts relevant to this fiscal year, since contracts dates and fiscal years rarely align.
Example: FY21 starts 1 Jul 20, ends 30 Jun 21. Contract A has a start date of 1 Oct 20, end date of 30 Sep 21, and a total amount of $12,000. In the budget entry form, for that cost center, I want the form to show $9,000 in Obligated funds (so the user knows they have to budget AT LEAST $9,000), because the contract only has 9 months in FY21
Another example: FY21 starts 1 Jul 20, ends 30 Jun 21. Contract A has a start date of 1 Apr 20, end date of 31 Mar 21, and a total amount of $12,000. In the budget entry form, for that cost center, I want the form to show $9,000 in Obligated funds (so the user knows they have to budget AT LEAST $9,000), because the contract overlaps into FY21 by 9 months.
I have built a FY table, with FYID (two digit fiscal year), FYSTART (date), and FYEND (date) to provide the reference information. What's the best approach to parsing that contract data so I only calculate the relevant amounts for the desired fiscal year?
Appreciate whatever ideas you can offer.