Need [Pick Month] VC to Enum only the Months found in [Month]

[Stephen_Mattiso]

Howdy!
I am building a Bookkeeping App and I need to set up a way for User to Pick which Year & Month that they want to view data for, but I only want Enum choices for Months or Years that are recorded in the DB.
I have Virtual Columns [Pick Year VC] & [Pick Month VC], I need a formula that looks at the Year & Month recorded in each Row, and then give User Enum choices of only Years & Months found. I know that I can set up a VC Enum and input all of the Months as choices, but if it’s only March, I don’t need to see April through December of this year. BUT, if User wants to see last year, then they should have all Months as choices for [Pick Month].
My thought is to set up a Dashboard, with one View of “Choose Range” which will only ask User to [Pick Year VC] & [Pick Month VC]. These results would be used in the Filters for my Income & Expense Table, and for my Expense chart.
Sorry if this is confusing, it’s hurting my brain to figure out how to smoothly and on the fly pick the range of data to be displayed.
This App was going great until I remembered that it needs to work for many years, not just this one! Haha! Than you very much!!

[Aleksi]

Have you thought to build that functionality with Usersettings? You could read values for your year and month dynamically from your data table… for the Enum field for example.

[Stephen_Mattiso]

Thanks Aleksi! I have never used this Usersettings. I’m sure it will help me, looking into it now.

[Stephen_Mattiso]

I don’t know how to make the formula in my USERSETTINGS [Pick Year] Enum type column that will let me choose from only the years that are used in the Rows. I tried this SELECT(Books[Year], true) but it says this must be used with a List type column, and that only Virtual Columns can use List type. I need an Enum buttons and I don’t want to display a list of a bunch of years, I only want to see the years that appear in the [Year] column. TYTY!!

[Stephen_Mattiso]

Well maybe I figured this out. I entered Books[Year] into the Valid_if of my UserSettings [Pick Year] Enum column and it seems to do exactly what I want. I get my Enum buttons. But it does give me this Error message “Column Name ‘Pick Year’ in Schema ‘_Per User Settings_Schema’ of Column Type ‘Enum’ should have a list of allowed values or allow the user to add other values” TYTY!

[Aleksi]

It’s just a message that describes what the system changed when you saved it. If you save it again, you won’t see that warning anymore.