Windows 10 Pro; LO 6.4.7.2; 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 “author.id”. 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.