<html> <head> <base href="https://bugs.documentfoundation.org/"> </head> <body> <div> <a class="bz_bug_link bz_status_NEW " title="NEW - EDITING: Query input in foreignkey-field impossible, when two tables in the query." href="https://bugs.documentfoundation.org/show_bug.cgi?id=68153#c9">Comment # 9</a> on <a class="bz_bug_link bz_status_NEW " title="NEW - EDITING: Query input in foreignkey-field impossible, when two tables in the query." href="https://bugs.documentfoundation.org/show_bug.cgi?id=68153">bug 68153</a> from <a class="email" href="mailto:BridgeportContractor@gmail.com" title="Howard Johnson <BridgeportContractor@gmail.com>"> Howard Johnson</a> <pre>Hi guys, Thanks for all of your work. I think development of this missing Base functionality needs to get bumped up to a much higher priority. What were talking about here is the ability to edit many-to-one relations in queries, and in forms based on those queries. This is a fundamental, relational, functionality, that is sorely missing in Base. Not only does the user expect to be able to update all fields, including a foreign key in a query, but a user NEEDS to be able to update a foreign key in a query. Editable queries that can only operate on single unrelated tables is ancient, not just in my opinion, but in actual fact: Access has had this important many-to-one query functionality working for 20 years that I know of. -------------------------------------------------------------------------- What I observed: * I have confirmed this lack of functionality in both HSQLDB 1.8 embedded and when connecting to MariaDB with MySQL/JDBC. * I also re-confirmed that Access 2003 provides this functionality correctly on the exact same MariaDB. Further, as I recall Access 97 also got this right and that's now 20 years old. If my memory is correct I also think Access 95 provided this missing Base functionality. * Because this functionality exists in Access when ODBC/MySQL linked (connected) to MariaDB, but not in Base when MySQL/JDBC connected, I think this is not a SQL lack of functionality, but rather an underlying Base lack of functionality. * The necessary Base user interface is already fully in place. * It's the underlying Base query update functionality that's lacking. -------------------------------------------------------------------------- Importance of this missing functionality: * I think this lacking functionality keeps Base Queries and Forms from being fully relational, as follows: + You can view a query of many variously related tables. ==> Query viewing is fully relational. - You can insert, but not edit foreign keys in queries of more than one table. ==> Query editing is not yet fully relational. Many-to-one relationships ("is" relationships) are the relationships I use most of the time in my databases. - You can also not edit foreign keys in a Form based on a query of more than one table. ==> Forms aren't yet fully relational. Note: Forms can use a subform to edit related one-to-many tables (has), but this is not equivalent to many-to-one (is) functionality.) * So in Base how can you currently edit many-to-one foreign keys??? - You can edit a foreign key in a query based on a single table, and in this case you can only enter the key (often an integer) which represents a record. You also can't select the record key from it's content. ==> primitive at best - You can edit a foreign key in a Form that is based on only a single table, but this again is not fully relational. ==> ok only for the most elementary data -------------------------------------------------------------------------- EXAMPLE APPLICATION THAT NEEDS THIS FUNCTIONALITY: I am trying to port my simple shopping list from Access to Base. The data for both applications is in a MariaDB MySQL server so I can use it from both applications at the same time. There is a main table of items. It includes a number of foreign keys to sub-tables to enumerate things like item category, frequency of purchase, who the item is for, etc. I have two main editors for this shopping list: 1) The first editor allows individual records to be looked up and edited. This works fine in Base and Access. 2) The second editor shows all records in a Form Table Control so I can view records side by side and make any needed changes, like for example selecting a different category for an item. For the user this Table Control needs to be sorted by a variety of fields, so it's based on a query of two related tables, Items and sub-table ItemFrequencyOfPurchase. For example I want the already checked (or selected) items to appear first, then I want items which I frequently purchase to appear next, and finally I want the list in the Table Control further sorted by item name. (This makes it a little easier to look over the most important of over 400 items on the list before we head for town.) This works perfectly in Access, but it does not work in Base. In Base the records in the Table Control based on the query become un-editable. But to get this to work the query needs to be editable. Only a query without any foreign keys is editable. This won't sort correctly on the form. -------------------------------------------------------------------- My hope is that someone will take on this challenge of making Base fully relational, rather than just partially relational. grateful</pre> </div> <hr> You are receiving this mail because: <ul> <li>You are the assignee for the bug.</li> </ul> </body> </html>