Given two related tables how to edit the primary table while sorted by the secondary table. Some Queries are not editable. Some Queries even crash LO when edited! My question is about how to build an editable Query (or a Form based on that Query) that is sorted by a related value.
Here are the two example tables:
– 
They are related like this:
Here is a simple db with the two related tables, Houses and Colors to play with.
Ciao, I think you have to set the Property Sort to “Color” ASC, and then insert a Button to refresh your Form (or close and reopen the Form)
If my answer helped you, vote it with 
(here on the left)
@Charlie.it, Thanks but the underlying query is is not editable. I’m trying to make it editable I guess. I’ll add some stuff to my Q.
I think I found what was wrong and how to fix it. It’s complicated and simple at the same time. I’ll try to update the Q and write an answer in a few minutes. Thanks for looking at this.
Ok, finally got it to work. It was harder than I expected and requires a Reload event. Here’s how I did it:
A) SETUP THE QUERY
For a query with a joined table to be editable, any secondary tables need to have their key included in the output of the query.
In this case the query needs to look like this, with Colors.Color ID included:
Colors.Color ID) included in the query it’s not editable.Color) visible is optional. For example, if you are manually editing the Houses.Color ID numeric key it helps see the results.B) SETUP THE FORM
Your form design will look like this:
And like this when first opened, and you go to add your first record:
When you hit enter or tab to move out of the new record your display updates incorrectly!. Notice how the NEW record got incorrectly copied up into the 2nd row position, and it’s key value is still zero:
C) INSTALL AN UPDATE EVENT
If you close the Form, and then reopen it, you can see that your data is correct. You need to add an event that does a form refresh when a record is inserted. Here’s what it looks like:
And here is where you attach it to your Form:
With this:
There is also a problem with changing an existing ‘Linked’ id. This & the misplaced added record can be corrected. The basic problem lies in the join within the query.
Please see this post for fix & sample → updating fk of a record gives an error (libre base ver: 5.1.6.2)
