Base form sort on lookup value, not key value?

Windows 10 Pro; LO; PostgreSQL 10

In Base Forms, is there a way to sort on the actual values from a (Table Control) lookup column, rather than on the key?

I have an “author” master table (*id, author_txt) with PK “id”, and a “legacy_book” table with a foreign key column “author_id” that has a relationship to “”. In a Table Control, I have a field column that sources from “book.author_id”, and it fetches values for author names via a SQL query (not a stored query) defined as “SELECT author_txt, id FROM ams.legacy_author” and bound column is 1. The lookup works fine by itself (showing authors’ names), but not when I try to sort by authors’ names.

Problem is that when I sort on this column, it sorts by the “id” and not by the fetched “author_txt” values. The issue is that the “id” column values are disjointed and not ordered in synchronicity with authors’ names, yielding a perfectly fine numerically ordered list, but completely useless for alphabetical authors’ names. I cannot for the life of me figure out how to fix this. I tried to create a Table Control column that is unattached to the underlying table (and sort by that column), but cannot see how to populate it from the fetched lookup values (unless perhaps with macros &/or LO BASIC, which is a whole ‘nother can o’ worms).

For example, the table “author” could look like this:

id  author_txt
--  ----------------------------
1   Andrews
2   Clive
5   Zubboff
6   Mackenzie
12  Francisco

I can achieve this with zero problems using a SQL query, joining the two tables, directly to the database, but the form stumps me.

Any suggestions? I feel I’m missing something utterly simple.


I believe you want to display the records in sequence by name of author but the records actually contains the id only. You can do this with a query as noted but care must be taken on the construct of the query in order for the records to be editable.

A solution is in this post → Base: How to design an editable query, but sorted by a value in a related table?.

However, there is a problem with the answer. I did address that in my answer here → updating fk of a record gives an error (libre base ver:

Again, I owe you many thanks (for pointing out the obvious that I clearly missed). I glossed over the part about the PK needing to be in the query results. Works like a charm now. The second part you mention, I’ll need to chew on it for a bit.Thanks so much!


Glad it works for you. The fixed (second) portion is if you were changing the foreign key in the primary record (not using a list box). This causes an error. The error is fixed by using a left join vs right outer join. You may or may not encounter this depending upon use, but best to be aware of the potential problem.