Now, when using a choice component, increasingly I find myself creating a table per choice component. Before, I used to put all my choices in a single table, one list of choices per column, even though of course the choices might be unrelated (the rows in my “Choices” table had no meaning).
I create a table per choice component to source and write RowIDs instead of the ChoiceName should the names of the choices change, such as a spelling mistake for instance.
The approach of creating a choices table per choice component feels tedious. How do you do it?
(It would be nice if we could put tables into folders in the data editor, that would help keep the list of tables uncluttered.)
I still use a single table to hold a variety of different types of choices. It saves quite a bit a rows and is less of a mess. I have more than enough tables in my project already.
Normally, I would say that you could have shared RowID’s for all of your choices. So one RowID column, but Choice List 1 and Choice List 2 would share the same row id. It would work, but you would have to be extremely careful if your list of choices ever changed. You could easily add choices to a list, but you can’t just delete a row ore move a row without possibly affecting your other choice list items in the same row.
I guess it’s just a matter of what’s more important to you.
Using a single choice table, you can still use row id’s, but may become harder to maintain if you ever need to remove a choice. You may end up with gap in a choice list to avoid messing up the row id it’s related to. If they remain relatively static, then it’s probably not an issue. Then again, if you remove a choice from the list, then that would probably mess up any other tables that no longer have a row to relate to, so it’s probably a non-issue.
Using separate tables, I would probably start a naming convention. Maybe something like CS-Choices1 and CS-Choices2. I’ve started applying a naming convention with a couple of tables in my app. In the image below, MS represents the initials of my app name (so I can differentiate it from all of the other glide tables if I ever add existing glide tables to other projects. WK represents ‘work’ as in a work table. Then the rest is just what the table name is. Since there is no “folder” option for tables, you could easily drag and drop all of your choice tables to the bottom so they are out of the way and kept together. The naming convention would make it easier to identify what the table is for.
(* In my case, I don’t utilize RowID’s. I just write the name when a choice is selected. In my case, if a choice name changed, I would still want to retain the old name. Not a technique that most of use suggest, but it makes sense in my case. Sometimes my list of available choices changes…not just a name change or correction.)
Thanks for your ideas. I have as you suggested adopted a naming convention for my choice tables and I do group them.
I was also thinking of keeping all the choices in one table and for each choice having 2 columns: A-ChoiceIDs, A-ChoiceNames. Then B-ChoiceIDs, B-ChoiceNames and so on (therefore forgoing the RowID column). The ChoiceIDs I could arbitrarily set, as long as I never changed them. But really, I don’t like this approach either. If the columns with the ChoiceIDs aren’t locked, then they can change, there is room for error. Arbitrarily setting IDs by hand is tedious, at the minimum it feels like unnecessary work. And the whole thing feels like a workaround which I try to avoid.
I think I’ll stick to my one-table-per-choice-component approach. For the time being that is.
I have a combination of both.
I usually have a “Lookup” table which will contain the bulk of my choice lists.
But I also often have several other tables that will have a single choice list, plus a bunch of computed columns. These ones are usually “special” lists, that are used in reports or charts or similar.
Aha, I like the idea of combining both approaches. A unique lookup tables of choices when the choices will likely never change and simply need to be looked up in a choice component. And a dedicated choice table per choice component when a relation is needed (via a RowID to keep things clean). If that’s the idea, I like it.
Would you have a specific example? Is the idea that each table would be a version of a pivot table? For instance if you had an Expenses table and wanted to to have a report on these expenses (in Google Sheet one might create multiple pivot tables), you could have one table for each of the following charts: YYYY-MM-Category, YYYY-Quarter-Category, YYYY-Category in order to have sums by category, and then by month, by quarter and for the year (for instance). The choice list in each table would then be for the source of chips (choice component) to adjust visibility of lists or charts on a page, something like that? I’m trying to visualize but cannot quite nail it.
On second thought, if expense items are already in a table, pulling some of the same items into another tables feels inefficient: the data is already there, why have to duplicate rows n number of times for n reports.
This way the User can add/edit new Lookup Groups and Lookups easily.
I always use Row IDs, which allows the User to edit the Name with breaking the Relation.
I know that this uses some more Rows but prefer it over this way which is prone to mistakes and only useful when the User has direct access to the Data Editor:
I will generally place all choices in a singular table.
The exception would be is if I’m allowing users to add to those choices via the app. In this case, the list should be in it’s own table, otherwise the items they add will be entered into new rows and will end up at the bottom of the table and not necessarily directly after the now second-to-last value.
