Enhancement Request: Allow entering foreign keys like in Access sub-forms

[balajiramanathan]

When I click on the foreign key field, pop up the foreign key table contents and allow me to narrow down what is shown as I start typing (filter based on what I type). And if what I type does not exist in foreign key table at all, allow me to create an entry in that table, commit it, and then use that as foreign key in the table I am editing (this main form - sub form capability in MS Access is so convenient to use when you have lots of connected tables in the DB with foreign key relationships).

[pawelsalawa]

I'm not sure how convenient it will be if it turns out, that the parent table itself has 3 other foreign keys to 3 other tables, and some of these also have their own foreign keys... It will easily become messy.

[balajiramanathan]

It is how forms and subforms are arranged in MS Access. Every foreign key field can have its own subform, and I have worked quite easily with table entries that have 6 or 7 foreign keys without any issues.

[pawelsalawa]

Can you show screenshot of such scenario?

[balajiramanathan]

This screenshot shows the design view of a form (this form is used to add books to a database). The subforms are at the bottom left and bottom right, and are bound to the Authors form and Subjects form. You can link a book with an existing author or subject, or you can add a new author and/or new subject without leaving this form. This screenshot shows the form in operational view. You enter the book title and some other details on the main form, and then for the author and subject, your entries go into the subform.

I had a database with a form that had more subforms, but right now I am not able to locate that database. I have moved most of my data to sqlite, so some of my old MS Access databases have been archived in backup disks that I don't have ready access to.

[balajiramanathan]

I agree that the full functionality of forms and subforms in Access is difficult to duplicate in a generic tool like SQLiteStudio. When it comes to GUI db data entry, it is difficult to beat MS Access with forms that have VBA code running behind them for doing things like data validation, calculating other fields based on entries in some fields, etc., etc.

I think what might work is simply allowing the user to start typing some characters into foreign key fields and filtering that table based on what is entered. The user can then scroll through the filtered records and choose the one that makes sense. Right now, if you have a lot of entries in the foreign key table, it is impractical to scroll through and find what is needed - it becomes much simpler to open that table in a window, filter for the row needed, and enter the foreign key value directly into the main table foreign key field instead of doing the more natural process of finding the foreign key record by filtering based on user input without having to open the foreign key table in a separate window.

[pawelsalawa]

I see. It is significant amount of effort.

I will keep this issue open - maybe someone will pick it up. As for myself I will probably focus on other tasks which I consider of higher priority.