Ask Your Question

Base: How to design an editable query, but sorted by a value in a related table? [closed]

asked 2017-04-26 04:20:04 +0200

EasyTrieve gravatar image

updated 2017-04-26 22:48:56 +0200

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:

image description -- image description

They are related like this:

image description

Here is a simple db with the two related tables, Houses and Colors to play with.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-01 15:49:35.762292

2 Answers

Sort by » oldest newest most voted

answered 2017-04-26 22:47:58 +0200

EasyTrieve gravatar image

updated 2017-04-26 22:53:13 +0200

Ok, finally got it to work. It was harder than I expected and requires a Reload event. Here's how I did it:


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:

image description

  • Without the key of the secondary table (in this case Colors.Color ID) included in the query it's not editable.
  • Only the first three fields are editable (the ones from the primary table), in this case Houses.
  • Don't try to edit the fields for the Colors table in this query, even if it seems you can! You can sort on them, but you can't edit them.
  • Making secondary fields (e.g. Color) visible is optional. For example, if you are manually editing the Houses.Color ID numeric key it helps see the results.


Your form design will look like this:

image description

And like this when first opened, and you go to add your first record:

image description

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:

image description


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:

image description

And here is where you attach it to your Form:

image description

With this:

image description

edit flag offensive delete link more


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:

Ratslinger gravatar imageRatslinger ( 2019-09-04 04:08:13 +0200 )edit

answered 2017-04-26 09:37:29 +0200

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)

image description


edit flag offensive delete link more

Comments, 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.

EasyTrieve gravatar imageEasyTrieve ( 2017-04-26 20:06:35 +0200 )edit

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.

EasyTrieve gravatar imageEasyTrieve ( 2017-04-26 21:23:39 +0200 )edit

Question Tools

1 follower


Asked: 2017-04-26 04:20:04 +0200

Seen: 607 times

Last updated: Apr 26 '17