Ask Your Question
0

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

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 close merge delete

2 Answers

Sort by » oldest newest most voted
0

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

C:\fakepath\14931720192350155.odb

edit flag offensive delete link more

Comments

@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.

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
0

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:

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:

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.

B) SETUP THE FORM

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


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:

image description

And here is where you attach it to your Form:

image description

With this:

image description

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 303 times

Last updated: Apr 26 '17