Alternative to Loop to generate Enum of Years

[Markus_Malessa]

Looking at the documentation and other questions it appears that AppSheet is not capable of doing a loop (for each loop) type of expression. My use case is to set the possible values of an enum field to a choice of years between the current year and lets say the year 2000. So in a loop I would generate my list by doing (JS)

for(var i = Year(Now()); i >= 2000; i–) {
list.push(i);
}

or something similar. Does anyone have a suggestion how to get this accomplished in AppSheet? I would like to try avoid having a separate table for this that checks every time if there is a current entry for the year and if not generates that entry and then references the table for the possible values.

[Ami]

Can you explain more why you need this?

[Markus_Malessa]

I have a form where users enter a report. The report is in the form of what year the report (Column Report_Year) is for and allows for entries that may be any time since 2000 as some of these entries are for historical data that may have only existed on paper so far. However, I would like this entry to be from a list of possible values rather than the user just typing in this field.

[Jonathon]

You can make the field an ENUM type and either specify the years discretely within that, or use a SELECT() formula to populate a list of existing years from another table containing them, if you have that.

[Markus_Malessa]

I had not considered creating the list from existing values. Would the SELECT() need to be in the Valid_If for the column? I presume I would also need to use UNIQUE() so I would generate only one selection since there are many entries with possibly the same year? How would I account for a selection that has not been entered before using this approach?

[Jonathon]

Setup your enum with ‘allow other values’, base type text, dropdown:

For valid_if, I would enforce a 4-digit date with:

AND(
   NUMBER([_THIS])>1900,
   NUMBER([_THIS])<2030
)

Finally, use your SELECT() statement in the Suggested Values box:

This will SUGGEST existing years, but still allow users to input their own 4 digit year if needed.