[$250] [Insights] [Release 6] Add group-by:year

[neil-marcellini]

Frontend Release 6: Add group-by:year

Follow the same pattern as Release 3 - Add group-by:tag but for group-by:year.

Add the ability to Group By Year from Reports > Expenses (group-by:year) without a suggested search.

Issue Owner

Current Issue Owner: @parasharrajat

[melvin-bot]

Triggered auto assignment to Contributor-plus team member for initial proposal review - @parasharrajat (External)

[melvin-bot]

Triggered auto assignment to @greg-schroeder (NewFeature), see https://stackoverflowteams.com/c/expensify/questions/14418#:~:text=BugZero%20process%20steps%20for%20feature%20requests for more details. Please add this Feature request to a GH project, as outlined in the SO. Remember to draft any necessary HelpDot updates by following the instructions here.

[melvin-bot]

Triggered auto assignment to Design team member for new feature review - @dubielzyk-expensify (NewFeature)

[samranahm]

Is this open for external contributors, I'm available to take this one.

[btkcodedev]

Proposal

Please re-state the problem that we are trying to solve in this issue.

We need to add the ability to Group By Year from Reports > Expenses (group-by:year) without a suggested search. This feature should follow the same pattern as other group-by options (from, card, withdrawal-id, tag) to maintain consistency across the application.

What is the root cause of that problem?

The YEAR option is not currently implemented in the group-by functionality. The codebase has the infrastructure for group-by features but lacks the specific configuration and handling for grouping expenses by year.

What changes do you think we should make in order to solve the problem?

1. Add YEAR to CONST.SEARCH.GROUP_BY

File: [~/App/src/CONST/index.ts]

Line 6826-6831: Add YEAR constant

GROUP_BY: {
    FROM: 'from',
    CARD: 'card',
    WITHDRAWAL_ID: 'withdrawal-id',
    YEAR: 'year',
},

2. Define the GROUP_YEAR table column constant

File: [~/App/src/CONST/index.ts]

Line 7039-7043: Add GROUP_YEAR to TABLE_COLUMNS

GROUP_BANK_ACCOUNT: 'groupBankAccount',
GROUP_WITHDRAWN: 'groupWithdrawn',
GROUP_WITHDRAWAL_ID: 'groupWithdrawalID',
GROUP_YEAR: 'groupYear',

3. Add YEAR entry to GROUP_CUSTOM_COLUMNS

File: [~/App/src/CONST/index.ts]

Line 6904-6916: Define which group columns are available when grouping by year. Include year, expenses, total

WITHDRAWAL_ID: {
    BANK_ACCOUNT: this.TABLE_COLUMNS.GROUP_BANK_ACCOUNT,
    WITHDRAWN: this.TABLE_COLUMNS.GROUP_WITHDRAWN,
    WITHDRAWAL_ID: this.TABLE_COLUMNS.GROUP_WITHDRAWAL_ID,
    EXPENSES: this.TABLE_COLUMNS.GROUP_EXPENSES,
    TOTAL: this.TABLE_COLUMNS.GROUP_TOTAL,
},
YEAR: {
    YEAR: this.TABLE_COLUMNS.GROUP_YEAR,
    EXPENSES: this.TABLE_COLUMNS.GROUP_EXPENSES,
    TOTAL: this.TABLE_COLUMNS.GROUP_TOTAL,
},

4. Add YEAR entry to GROUP_DEFAULT_COLUMNS

File: [~/App/src/CONST/index.ts]

Line 6946-6953: Specify default columns to show when grouping by year

WITHDRAWAL_ID: [
    this.TABLE_COLUMNS.GROUP_BANK_ACCOUNT,
    this.TABLE_COLUMNS.GROUP_WITHDRAWN,
    this.TABLE_COLUMNS.GROUP_WITHDRAWAL_ID,
    this.TABLE_COLUMNS.GROUP_EXPENSES,
    this.TABLE_COLUMNS.GROUP_TOTAL,
],
YEAR: [this.TABLE_COLUMNS.GROUP_YEAR, this.TABLE_COLUMNS.GROUP_EXPENSES, this.TABLE_COLUMNS.GROUP_TOTAL],

5. Add user-friendly name mapping for GROUP_YEAR

File: [~/App/src/CONST/index.ts]

Line 7225-7229: Add GROUP_YEAR to SEARCH_USER_FRIENDLY_VALUES_MAP

[this.TABLE_COLUMNS.GROUP_FEED]: 'group-feed',
[this.TABLE_COLUMNS.GROUP_BANK_ACCOUNT]: 'group-bank-account',
[this.TABLE_COLUMNS.GROUP_WITHDRAWN]: 'group-withdrawn',
[this.TABLE_COLUMNS.GROUP_WITHDRAWAL_ID]: 'group-withdrawal-id',
[this.TABLE_COLUMNS.GROUP_YEAR]: 'group-year',

6. Handle the YEAR case in getCustomColumns()

File: [~/App/src/libs/SearchUIUtils.ts]

Line 2604-2611: Add YEAR case to return the year custom columns

case CONST.SEARCH.GROUP_BY.FROM:
    return Object.values(CONST.SEARCH.GROUP_CUSTOM_COLUMNS.FROM);
case CONST.SEARCH.GROUP_BY.WITHDRAWAL_ID:
    return Object.values(CONST.SEARCH.GROUP_CUSTOM_COLUMNS.WITHDRAWAL_ID);
case CONST.SEARCH.GROUP_BY.YEAR:
    return Object.values(CONST.SEARCH.GROUP_CUSTOM_COLUMNS.YEAR);
default:
    return [];

7. Handle the YEAR case in getCustomColumnDefault()

File: [~/App/src/libs/SearchUIUtils.ts]

Line 2629-2636: Add YEAR case to return the default year columns

case CONST.SEARCH.GROUP_BY.FROM:
    return CONST.SEARCH.GROUP_DEFAULT_COLUMNS.FROM;
case CONST.SEARCH.GROUP_BY.WITHDRAWAL_ID:
    return CONST.SEARCH.GROUP_DEFAULT_COLUMNS.WITHDRAWAL_ID;
case CONST.SEARCH.GROUP_BY.YEAR:
    return CONST.SEARCH.GROUP_DEFAULT_COLUMNS.YEAR;
default:
    return [];

8. Add case for GROUP_YEAR in getSearchColumnTranslationKey()

File: [~/App/src/libs/SearchUIUtils.ts]

Line 2717-2720: Return the year translation key

case CONST.SEARCH.TABLE_COLUMNS.EXPORTED_TO:
    return 'search.exportedTo';
case CONST.SEARCH.TABLE_COLUMNS.GROUP_YEAR:
    return 'common.year';

9. Add YEAR mappings in getColumnsToShow()

File: [~/App/src/libs/SearchUIUtils.ts]

Line 3181-3193: Add YEAR to both customColumns and defaultCustomColumns objects

const customColumns = {
    [CONST.SEARCH.GROUP_BY.CARD]: CONST.SEARCH.GROUP_CUSTOM_COLUMNS.CARD,
    [CONST.SEARCH.GROUP_BY.FROM]: CONST.SEARCH.GROUP_CUSTOM_COLUMNS.FROM,
    [CONST.SEARCH.GROUP_BY.WITHDRAWAL_ID]: CONST.SEARCH.GROUP_CUSTOM_COLUMNS.WITHDRAWAL_ID,
    [CONST.SEARCH.GROUP_BY.YEAR]: CONST.SEARCH.GROUP_CUSTOM_COLUMNS.YEAR,
}[groupBy];

const defaultCustomColumns = {
    [CONST.SEARCH.GROUP_BY.CARD]: CONST.SEARCH.GROUP_DEFAULT_COLUMNS.CARD,
    [CONST.SEARCH.GROUP_BY.FROM]: CONST.SEARCH.GROUP_DEFAULT_COLUMNS.FROM,
    [CONST.SEARCH.GROUP_BY.WITHDRAWAL_ID]: CONST.SEARCH.GROUP_DEFAULT_COLUMNS.WITHDRAWAL_ID,
    [CONST.SEARCH.GROUP_BY.YEAR]: CONST.SEARCH.GROUP_DEFAULT_COLUMNS.YEAR,
}[groupBy];

10. Add conditional check in getColumnsToShow()

File: [~/App/src/libs/SearchUIUtils.ts]

Line 3247-3265: Ensure required columns (AVATAR and GROUP_YEAR) are always included when grouping by year

if (groupBy === CONST.SEARCH.GROUP_BY.YEAR) {
    const requiredColumns = new Set<SearchColumnType>([CONST.SEARCH.TABLE_COLUMNS.AVATAR, CONST.SEARCH.TABLE_COLUMNS.GROUP_YEAR]);
    const result: SearchColumnType[] = [];

    for (const col of requiredColumns) {
        if (!columnsToShow.includes(col as SearchCustomColumnIds)) {
            result.push(col);
        }
    }

    for (const col of columnsToShow) {
        result.push(col);
    }

    return result;
}

11. Add GROUP_YEAR to required columns set

File: [~/App/src/pages/Search/SearchColumnsPage.tsx]

Line 58-65: Prevent GROUP_YEAR from being hidden on the Search Columns Page

const requiredColumns = new Set<SearchCustomColumnIds>([
    CONST.SEARCH.TABLE_COLUMNS.TOTAL_AMOUNT,
    CONST.SEARCH.TABLE_COLUMNS.TOTAL,
    CONST.SEARCH.TABLE_COLUMNS.GROUP_CARD,
    CONST.SEARCH.TABLE_COLUMNS.GROUP_WITHDRAWAL_ID,
    CONST.SEARCH.TABLE_COLUMNS.GROUP_FROM,
    CONST.SEARCH.TABLE_COLUMNS.GROUP_YEAR,
]);

12. Add case for YEAR in getTransactionGroupHeaders()

File: [~/App/src/components/SelectionListWithSections/SearchTableHeader.tsx]

Line 339-359: Return the appropriate column header configuration

case CONST.SEARCH.GROUP_BY.YEAR:
    return [
        {
            columnName: CONST.SEARCH.TABLE_COLUMNS.AVATAR,
            translationKey: undefined,
            isColumnSortable: false,
        },
        {
            columnName: CONST.SEARCH.TABLE_COLUMNS.GROUP_YEAR,
            translationKey: 'common.year',
        },
        {
            columnName: CONST.SEARCH.TABLE_COLUMNS.GROUP_EXPENSES,
            translationKey: 'common.expenses',
        },
        {
            columnName: CONST.SEARCH.TABLE_COLUMNS.GROUP_TOTAL,
            translationKey: 'common.total',
        },
    ];

13. Add [CONST.SEARCH.GROUP_BY.YEAR]: 'Year' to search.filters.groupBy

File: [~/App/src/languages/en.ts]

Line 6772-6777: Add Year to the groupBy filter translations

groupBy: {
    [CONST.SEARCH.GROUP_BY.FROM]: 'From',
    [CONST.SEARCH.GROUP_BY.CARD]: 'Card',
    [CONST.SEARCH.GROUP_BY.WITHDRAWAL_ID]: 'Withdrawal ID',
    [CONST.SEARCH.GROUP_BY.YEAR]: 'Year',
},

14. Add year translation to common section

File: [~/App/src/languages/en.ts]

Line 332-336: Add year key for translation

date: 'Date',
dob: 'Date of birth',
currentYear: 'Current year',
currentMonth: 'Current month',
year: 'Year',
ssnLast4: 'Last 4 digits of SSN',

15. Update SearchCustomColumnIds type

File: [~/App/src/components/Search/types.ts]

Line 120-126: Include the year custom column values

type SearchCustomColumnIds =
    | ValueOf<typeof CONST.SEARCH.TYPE_CUSTOM_COLUMNS.EXPENSE>
    | ValueOf<typeof CONST.SEARCH.TYPE_CUSTOM_COLUMNS.EXPENSE_REPORT>
    | ValueOf<typeof CONST.SEARCH.GROUP_CUSTOM_COLUMNS.CARD>
    | ValueOf<typeof CONST.SEARCH.GROUP_CUSTOM_COLUMNS.FROM>
    | ValueOf<typeof CONST.SEARCH.GROUP_CUSTOM_COLUMNS.WITHDRAWAL_ID>
    | ValueOf<typeof CONST.SEARCH.GROUP_CUSTOM_COLUMNS.YEAR>;

What alternative solutions did you explore?

None

Tested screenshot