Year field on acq fund should link to acq fiscal year

[Tiffany Little]

Sparked by bug 2021992.

The year field on the table acq.fund is purely a text field; you can enter whatever number that you like into it. When creating a fund, it's just a freetext field. As a sidenote, that undoubtedly means that you could do things like create "2022" and also "2022_" (with a space on the end) and that's probably problematic to someone somewhere. But I digress.

The Year field should reference the acq.fiscal_year table, so that you can only make selections from entries in that table.

[Galen Charlton (gmc)]

To clarify, acq.fund.year is an integer field, so "2022_" is not in the cards - and more to the point, nor is the likes of "2023-2024".

Regardless, I agree with the point that acq.fund.year (maybe renamed to acq.fund.fiscal_year or acq.fund.fiscal_period) should be a link to acq.fiscal_year. If we do that, I also think that acq.fiscal_year.year should be changed from an integer field to a text field. We should also give some consideration to adding an org owner column to acq.fiscal_year, as there's no guarantee that all members of a consortium would have the same fiscal period.

[Tiffany Little (tslittle)]

Whoops -- Missed the very obvious Type on year. Ignore that part!

+1 with the addition of an org owner column. But should it go on acq.fiscal_year, or on acq.fiscal_calendar? Seems like fiscal calendar, with its name column would do better as a text field, whereas "year" should always be a number.

I'm going to add the needsdiscussion tag, because I remember there was some talk about folks who run multiple years at a time so that may shed some light on how all the fund/fiscal year/fiscal calendar pieces should fit together better.

[Jennifer Pringle (jpringle-u)]

also +1 to an org owner column, we have multiple fiscal years across our libraries and some libraries have a bit of overlap of fiscal years around year end

[Galen Charlton (gmc)]

Good point regarding ownership better belonging to the fiscal calendar. There's currently a link from actor.org_unit to acq.fiscal_calendar, although it doesn't have ownership semantics per se.

The fiscal calendar is meant to be a container for one or more fiscal years, and as such, I don't think the label for a given fiscal year goes on the calendar. As such, I disagree that "year" should always be a number.

Given the nature of the changes being contemplated, we could also consider a renaming of the acq.fiscal_year table to acq.fiscal_period.

[Tiffany Little (tslittle)]

I keep turning this over in my brain and trying to think how I'd probably implement it so I'm sort of talking out loud here.

So for fiscal_calendar, I think of when the fiscal calendar runs. So I think most people have something like a Jul-Jun or a Jan-Dec calendar. So I might put each of those as a different fiscal calendar that would be accessible to everyone. Basically like, these are the options out there, they don't have to be specific to anyone.

Then for fiscal_year, I would think more of the year itself. So for most of our libraries, they run on a Jul-Jun calendar. So if I made a fiscal_year for 2024, I'd attach it to the Jul-Jun fiscal calendar. Should FY24 (Jul-Jun period) be accessible to anyone to be added to a fund? You would need to create a fiscal year for each option (e.g. FY24 attached to Jul-Jun, FY24 attached to Jan-Dec, etc). But there's nothing org specific about that--they'd just be available options when you're creating your funds, which *are* org specific. And if you *did* have an org on that table, you'd just be duplicating them across multiple orgs.

Maybe instead of an org it needs more of a template kind of deal? So the entry in the combobox for fund.year might look like "2024 (Jul-Jun)" rather than just "2024"?

If acq.fiscal_year.year was text, you could even enter it like FY2024 so in the combobox it would look even more clarifying, like "FY2024 (Jul-Jun)"?

So to sum up my stream of consciousness thought process as possibilities:

-- Keep fiscal_calendar as is

-- acq.fiscal_year should acquire an active column so that years don't show up forever and ever. Ooh, or should it just consult the acq.fiscal_year.year_end date and not show in list if it's past that date? That'd be cool, especially since it already exists and has no other apparent purpose.

-- acq.fiscal_year.year should become a text field so we could do thinks like name them "FY2024" instead of just an integer (like Galen was saying).

-- Year combobox on acq.fund create modal should (a) pull from acq.fiscal_year, and (b) use a template to display as "acq.fiscal_year.year (associated acq.fiscal_calendar.name)"