Add drop-down menu (with foreign keys): feature request

[SilvioGrosso]

Hello @little-brother

These past days, I have been testing your latest version 1.7.6, on Windows 10 It is incredibly powerful and, on the whole, quite stable and extremely fast even with long queries.

For some future versions, in case you are interested and it is both not too complicated to implement and worth coding it would be useful: to add a drop-down menu to pick your values related to a foreign key

Here is a screenshot of this feature available with dbeaver and SqliteStudio (also Db Browser for SQLlite offers this option):

I have been using this feature with LibreOffice Calc as well:

Thanks a lot!

[little-brother]

To view referenced data by FK you can use Alt + Click or Alt + DblClick. Or do you mean smth else?

[SilvioGrosso]

Hello @little-brother

Thanks a lot for your reply :-)

What I have been trying to do is to pick a value from the list (concerning those values related to the foreign key) At present, it looks like I can visualize them but not pick them

In short, when I add a new row I would like to have this drop-down menu appearing, to select among the records. This is what occurs with all the other softwares I have tested so far. With them, whenever I add a new row this drop-down menu appears. I can scroll it up and down to select my preferred value among those present.

Is it possible? Do you have a screenshot at hand?

See this screeenshot, where I would like to get the drop-down menu for the fruit

[SilvioGrosso]

Hello @little-brother

I have been testing the GUI with this code: (PRAGMA foreign_keys = ON in the settings)

create table articles( id integer not null, fruit text primary key );

insert into articles (id, fruit) values (1, 'apple'), (2, 'kiwi'), (3, 'pear');

create table buyers ( id integer primary key, buyer text, fruit text, price integer, FOREIGN KEY (fruit) REFERENCES articles(fruit) );

insert into buyers (id, buyer, fruit, price) values (1, 'Silvio', 'apple', 1500), (2, 'Marco', 'pear', 1600), (3, 'Stefano', 'kiwi', 1800);

Afterwards, I add a new row in the GUIs to see if the drop-down menu appears (for the fruit values in the buyers table :-)

[little-brother]

In short, when I add a new row I would like to have this drop-down menu appearing, to select among the records.

Ok, I understood your request.

PRAGMA foreign_keys = ON in the settings

For Alt + Click you don't need to turn on this pragma. The references use select * from pragma_foreign_key_list('<table>') to obtain fk list. It works regardless the pragma state.

[SilvioGrosso]

I have tried on 2 different computers with Windows 10 and 8.1 and Alt+Click does nothing (version 1.7.6 as usual)

In the previous versions I was able to visualize a window (pop-up with all the values). I meant this brown pop-up window:

However, It is not what I have been suggesting so far (that is a drop-down list, scrollable up-down) :-) With some sofwares, when you start typing in the cell only the values with start with these typed letters are proposed in the list (it is a sort of auto-completion feature)

Please, can you attach a screenshot, where you apply my SQL code, in order to get this scrollable, drop-down list (apple, kiwi, pear) in the GUIs _while adding a new row. Because, in all truth, I don't understand whether this option is already available or not? :-)

[little-brother]

In the previous versions I was able to visualize a window

In the current version you can do too. Just close the database and open it again. At this time references are collected on the app startup. That is why you don't see the popup (brown) window with Alt. I'll try to fix these behaviour in the next release. The refeneces should be updated if any executed query contains FOREIGN KEY ... REFERENCES.

In sqlite-gui you can't change data of a select-statement. So the dropdown elements should be available only in Edit-dialog e.g. like on an image below (you choose one of ARTICLES rows and the cell value will be set as the row id)

[SilvioGrosso]

Ok. Thanks a lot for all the details you provided. Much appreciated :-)

Now, it more or less works, with the following steps: Select * from buyers CTR+click ALT+click on the cell (the pop-up window appears). Here the screenshot:

[little-brother]

Done. Use Alt + Space to get the value selector.