I'm creating a custom system table for some data which has two levels of grouping, and I would like to be able to have both levels displayed in System Table Maintenance. What I'm doing is something like this:
I have three tables. Let's call them:
LTR_Data --This is the table where we will be entering the data.
LTR_Data_Type --This is the table that stores the first level of categorization. LTR_Data has a required column which stores a foreign key for this table.
LTR_Data_Type_Category --This table categorizes Data Type. LTR_Data_Type has a required column which stores a foreign key for this table.
Dealing with the data in LTR_Data will be much easier if I can sort the table not just by Data Type, but also by Data Type Category. The only solution that I can think of is to include a column in LTR_Data for Data Type Category, make it not editable on the front end, and create a trigger that updates the Data Type Category value whenever anything else in the row changes. But that seems a bit clunky. Is there a better way? For example, can I set something up in TR_REFERENCE_COLUMN that will do the trick?
I think my approach would have been to have just a single foreign key column on LTR_Data, but to have the dddw_description field be something like category + ': ' + type. You might need a reference view on LTR_Data_Type to use as the dddw_table in order to pull in the category description, but it avoids using triggers. This also means you don't have the ability to sort the Data table by Type alone, but it sounds like for your purposes what you want is just to be able to see the sorting of Data in the full hierarchy.
Good call, I wind up making a lot of reference views for selection drop-downs.