In my database for a subform with a junction table JT_TitlePerson as its data source, I defined an SQL sort in the Sort property of the Data tab.
.
JT_TitlePerson,TitlePersonID,INTEGER,
JT_TitlePerson,TitleID,INTEGER,
JT_TitlePerson,PersonID,INTEGER,
JT_TitlePerson,FunctionTypeID,INTEGER,
.
Sort: (SELECT “PersonName” FROM “PT_Person” WHERE “PT_Person”.“PersonID” = “JT_TitlePerson”.“PersonID”) ASC
.
The subform works perfectly and is editable.
.
Can this same type of sort be performed on a view that uses more than one table as its data source, rather than just a junction table?
Did you try?
Yes, but it’s not functional.
Column not found: JT_TitlePerson.PersonID in statement [SELECT * FROM “VT_TitlePerson_Full” WHERE ( “VT_TitlePerson_Full”.“TitleID” = :link_from_TitleID ) ORDER BY ( SELECT “PersonName” FROM “PT_Person” WHERE “PT_Person”.“PersonID” = “JT_TitlePerson”.“PersonID” ) ASC]
Named parameters don’t work with direct SQL anyway. Why does it need to be a view?
It’s simply because I need the “BirthYear” column in the subform. The junction table JT_TitlePerson doesn’t have this table field; it’s the PT_Person table that does.
I do not even know the software you are working with. What type of database is your Base document connected to?
Subforms do not work with views. They are not filtered by their parent. Simply use parsed queries with forms.
I think, I have done something similar (order by related value) in HSQL last week: https://ask.libreoffice.org/uploads/short-url/3zJd9lOGIhib4hewBfdNQfFrijU.odb
The SORT clause is stored in the subform properties.
Version: 25.8.4.2 (X86_64)
Build ID: 290daaa01b999472f0c7a3890eb6a550fd74c6df
CPU threads: 8; OS: Windows 11 X86_64 (build 26200); UI render: Skia/Vulkan; VCL: win
Locale: fr-CA (fr_CA); UI: fr-FR
Calc: CL threaded
Is it possible to do this kind of sorting with a VIEW?
Yes, you did that in the movies_jobs database. It was your example database that allowed me to replicate what you had done in my own database. This approach allowed me to avoid macros that were causing more problems than they solved.
A view of which type of database? Add this to my embedded HSQL database as view or query, parsed or direct:
SELECT "JMP".* FROM "JMP" ORDER BY (SELECT "N" FROM "MOVIES" WHERE "JMP"."MID" = "MOVIES"."ID")
It selects table JMP ordered by movie names.
Currently, I’m using embedded HSQL 1.8 with LibreOffice. I plan to eventually migrate to a more modern version, 2.4 or higher.
But for now, I want to finish rebuilding my database with this engine.
You are working with 2 different kinds of software:
- LibreOffice Base.
- Some database of type HSQL, Firebird, MySQL, PostgreSQL, Microsoft Access, any database engine you can connect to LibreOffice Base.
You access the database through LO Base. The database is shown in the tables and in the relations window. Views belong to the database realm. The database software knows absolutely nothing about queries, forms and reports.
Embedded HSQL 1.8. Thank you for this most important piece of information.
I can reproduce the same error (existing column not found) when I create a view, a main form from the view and a sort order in the form properties like this:
(SELECT "N" FROM "MOVIES" WHERE "JMP"."MID" = "MOVIES"."ID")
Both, JMP and MOVIES are unknown objects in this context because the view belongs to the database and the form’s sort order belongs to Base.
When working with forms and reports, you better use parsed SQL.
I tried with a query but because of the JOIN that is used, even if BirthYear is available, the subform became non-editable and that is not what I want.
.
SELECT
“JTP”.“TitlePersonID”,
“JTP”.“TitleID”,
“JTP”.“PersonID”,
“P”.“PersonName”,
“P”.“BirthYear”,
“P”.“GenderID”,
“JTP”.“FunctionTypeID”
FROM
“JT_TitlePerson” AS “JTP”
JOIN
“PT_Person” AS “P”
ON “P”.“PersonID” = “JTP”.“PersonID”
.
Is there another possible approach?
Clean solution: Create a form with single table “JT_TitlePerson”, and a subform with single table “PT_Person” or the other way round.
May be, you can edit the join if it includes the primary key fields of both tables.