Ask Your Question
0

Base form sort on lookup value, not key value?

asked 2021-04-10 21:12:42 +0200

Patrick Demets gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2021-04-10 23:03:29 +0200

Ratslinger gravatar image

Hello,

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: 5.1.6.2)

edit flag offensive delete link more

Comments

@Ratslinger, 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!

Patrick Demets gravatar imagePatrick Demets ( 2021-04-11 01:06:50 +0200 )edit

@Patrick Demets,

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.

Ratslinger gravatar imageRatslinger ( 2021-04-11 04:32:18 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-04-10 21:12:42 +0200

Seen: 24 times

Last updated: Apr 10