pawelsalawa
Value to show in lookup fields in grid view
When using a foreign key, I suggest to let the column in grid view show a value from the referenced table (configurable), typically the value of a key-value pair rather than the key itself. When editing that field, the key could be shown again, or the key could be always hidden from view, letting the user only work with the keyed values. OS:
Version:
It's not always obvious which column in foreign table should be displayed. There can be many columns in referenced table.
Another thing is that you already can preview referenced values (all columns) when you enter FK column edition and you open the dropdown list - it shows you referenced data as all columns.
I personally think that change that is proposed here would introduce more chaos and confusion than actually helping, but I'm open for further discussion and I keep this ticket open for more comments.
I like this idea a lot. This would ease a lot of the pains of normalization. I came to suggest it, to find that you already did!
To help mitigate any confusion, perhaps it is best to disable this feature by default under a setting with a "I know what I am doing" warning.
One way to do it is to keep the foreign key column, but add next to it one (or more, as chosen by the user) columns from the foreign table. These foreign columns can (and should) be visually distinct from the local columns, like with a different background.
They can be read-only or editable. As long as the user-chosen unique identifier (the FK) is visible, I don't think there would be ambiguity.
I am aware that this is already possible, in a way, with simple views, but the possibilities are limited to editing. No adding or deleting.
This would personally be a killer feature (coupled with dropdown filtering).
Here's an example with a table of people:
I'd like to be able to just set the person_id column to show the name (configured manually on a per-table basis) and be able to select the id by picking the name. I don't remember off the top of my head who person_id 646 is, but I know who James is, so select -> search for James -> press enter would be a wonderful workflow. I know I can create a view, but that's overhead I'd like to avoid if at all possible.
Adding another vote in favor of this feature!
Personally, this is my main feature request for SQLiteStudio. The interface for editing foreign key cells is great. But when I'm browsing the grid view, I wish I could see names instead of key numbers.
Thinking about design - for my own usage, the most streamlined design would be: if Table A has a primary key column, we can designate any tuple of columns (including the PK itself) as the "display columns" for Table A. Then, when any Table B has a foreign key reference to Table A's primary key, the FK column in Table B's grid view shows Table A's display columns instead of the FK itself.
However, it would be more flexible to attach the choice of the "display columns" tuple to the FK column in Table B instead of the PK column in Table A. But this would cause a lot of duplicated work if other tables frequently reference Table A with the same display.
Thanks for making such a useful and powerful program!
