Create additional filters for selecting projects within CIP Planner

[jim-z]

Need the ability to sort projects by phase year. Currently can only sort by "year" but annual CIP is primarily based on the more specific phased funding years/amounts.

[xmedr]

• [x] Short term: We'll be creating a data pool to help more easily create a 5-year plan by Thursday

• [ ] Long term: We'll implement a way to sort phases by the year of their fundings, not just the year of the phase itself

[smcalilly]

@jim-z just to clarify, the year sort is based on the phase year, and y'all need to be able to sort based on the phase funding year?

in that case, would it be okay if we changed the UI for this? specifically, from your above screenshot, the relationship between the project's phase and the sub-table with the phase funding. in order to sort, i think we'll need to flatten the rows so that each phase funding source creates a unique row instead of containing that data in a sub-table.

the table would roughly look like this:	phase	phase_year	funding_source	funding_amount	funding_year	project_description
phase_1	2023	grants	$200k	2023	Construction following feasibility study from Engineering Contract
phase_1	2023	capital improvements	$70k	2024	Construction following feasibility study from Engineering Contract

note the duplication of data in the columns phase, phase_year, project_description. this change would create duplicated data in the CIP tables, including the other phase and project fields that i omitted for brevity. the unique data in each row would be the phase funding, and all of the phase and project fields would be duplicated across each row. we could figure out a way to truncate the longer text fields so it's a better experience visually.

this change would be the best way to structure this data so we can filter on it in the table, and structuring the data like that would make the data better work with it after y'all export it.

i'm open to other ways of addressing this problem, but as it's currently designed it's likely we'll need to hack it in order to get the sorting to work on the phase funding fields.

[smcalilly]

@jim-z or, would a dedicated filter be a better solution? rather than using the table sort, we could create a filter similar to the "filter by section". that wouldn't require a change to the table as i mentioned in the previous comment. if we did this then we might need to adjust the UI so those filters are placed in a way to that connects them to the table in a more obvious way.

[jim-z]

@smcalilly correct, my understanding is that the sort field is currently coming from here (see below) and that information is sort of irrelevant/redundant because the phase funding fields below that are more detailed and capture the information that actually gets added into the CIP:

Yes, I think duplicate rows with the same project description, etc. will need to be generated where projects include multiple phase funding entries. The csv export should also work this way so that the information can be grabbed and transposed into our excel spreadsheet.

A dedicated filter at the top to select for phase funding year might be a good solution. I think maybe removing the current year from what is displayed and having the dropdown subtable for phase funding automatically visible (rather than clicking the little drop down arrow) might make it a little more intuitive when searching for and selecting projects to add to portfolio:

[jim-z]

If we go filter route, additional filters for fields below would probably also be useful for portfolio construction:

[smcalilly]

I've got the logic implemented for the filtering, we just need an okay for the UI change in #271

[jim-z]

You have my ok to implement the UI change to support filtering enhancements. Just want to confirm, will Year now filter on phase year or how it was orignally set up with project year? I invited our director to next week's meeting to discuss these changes and anything additional she might want to see in the UI, FYI.