Filter financial transactions by month and year

[RahulS8]

Hello,

I have a table of personal financial transactions. There is a date column as well. I want a field in the app where the user can select the month and year and the app only shows the user the transactions for that month in that year.

Currently, there is a filter option with start and end date options. I want to simplify and have an option where the user can directly select the month and the year. How can I do this?

Thanks!

[Suvrutt_Gurjar]

The AppSheet inbuilt filter shows selectable options for enum columns.

You could try below.

Please add two enum VC columns - one for the "Year" and one for the "Month" 

1. The "Year" column can have enum options such as 2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027...

The enum options should typically correspond to the number of years for which the app is likely to have those financial transactions.

Please have the app formula of this "Year" column as  TEXT([Date], "YYYY") and base type of enum as 'Text"

2. The month column can have enum options such as "January" "February", "March",............,"October", "November", "December"

Please have the app formula of this "Month" column as  TEXT([Date], "MMMM") and base type of enum as 'Text"

Please ensure to include these enum columns in the summary view and slice if the summary view is based on slice.

Then the filter will start reflecting the records by months and years.

Sample screenshots

Filter selection:

Filter Result:

Text function article:

TEXT() - AppSheet Help

[RahulS8]

This worked. But the only issue is that I cannot hide the virtual columns in the app because that makes it unsearchable. Is there a way to solve this?

[Suvrutt_Gurjar]

The column will need to be visible in a view if it needs to be searchable. You could use CONTEXT() function to show those columns only in summary views for example.

CONTEXT() - AppSheet Help

For example, in the show_if of those enum columns you could have expression such as 

CONTEXT("ViewType")= "Table" if you are using the summary view as table view. For deck view, please replace "Table" with "Deck" and so on.