Is there a workaround to the 1000-record limit for queries/aggregations that would let me get a list of unique values for specific column of a table with more than 1000 records?
My table has ~27k records but the column in question has only a couple dozen unique values in it.
The goal would be to populate a single-select list which would in turn be used in-app to filter a interactive table widget via table query.
I cannot think of a straightforward solution here, unfortunately.
I would probably think about ways to store the unique options in a separate one column table. But it is going to depend on how those 27k rows are generated and updated.
You could probably utilize Automations to Create or Load a record in the one column table every time that a record is updated in the large table. The hard part is if you ever need to remove options.
That is the work we implemented, but if there are ever values other than what we populate in to that reference table, we might not catch them (e.g. typo error on data entry).
Now I’m thinking about how to check the table for records for which that field “is not one of” the reference table list.