Questions about poor sorting

[Renel]

I have a list box column in a control table whose SQL command drafts a query whose field (title) of the table (movie titles) is supposed to perform an ascending sort. However, there is a sort that has been performed, but some titles have been missed. In other words. some titles have not been sorted.

Example, I give the first word of each row:

Hometown
come
Hi
Hotel
Tea
It’s
Start
The
The
While
wok
The
Alice
why

This is half sorted, see not sorted. Why the SQL command is not executing correctly. Here is the command :

SELECT “EnglishTitle”, “FilmSerie-ID” FROM “tbl-FilmSeries” ORDER BY “EnglishTitle” ASC

[Ratslinger]

Hello,
Your information does not coincide with my testing:

Please provide a sample.
.
Edit:
Please, when asking a question provide your OS specific LO version and for Base the database used (and connector when appropriate).
.
Edit 2:
Also took your sample from previous post and applied the SQL to the tblFilms table and no issue there:
.

[Renel]

I’m using Windows 11 Home, version 21H2 with an Intel(R) Core™ i7-10700 CPU @ 2.90GHz 2.90 GHz. The version of LO is 7.3.2.2. I’m sending you an image from the original database. As mentioned before, it’s a list box in a control table that would normally be sorted alphabetically according to the SQL command written in the original post.

[Ratslinger]

OK I understand it is a listbox - did not catch the that. But it still works:

.
SQL is:

SELECT "FilmName", "IDFilm" FROM "tblFilms" ORDER BY "FilmName" ASC

.
showing:

.
Maybe there is more to your question. What exactly are you expecting to be in sequence because I find no issue thus far.
.
Edit:
Test done in Ubuntu 20.04

[Ratslinger]

Just a note. This SQL should work the same in both a Query window as well as the list box.

[Renel]

Here is the command associated with the list box and all the titles are perfectly sorted, there are a few hundred.

I may have misspoken. The list in the list box is perfectly well sorted. However the rows of this column in the control table are not correctly sorted. It is probably two different things and it is possible to exercise a sort for these rows.

[Ratslinger]

Yes those are two different elements. You need to have the data in that form (or sub form) sorted and with new entries it would need to be refreshed.
Not quite sure just what all your needs are but I would not be sorting on a form where I am using it for data entry. Adds some confusion.

[Renel]

The list box is correct. I would like the rows of the control table to be sorted as well. Yes, it would be necessary to refresh after a new entry or a new row.

For now, I don’t know how to do it except to do it manually, but it won’t make sense the more new entries there are.

[Ratslinger]

If you do not do it manually then a macro is the only other option.
.
I seem to recall that @Villeroy has already given you a macro it appears can be used here. Try that.
.
Using macros and not knowing how to construct them will only lead to many other problems.

[Renel]

I wouldn’t be able to build a macro for this type of command. I imagine this must involve UNO services and for me understanding all of this is still very basic.

I can adapt a macro to my needs if it is not too complex and comment well.

Well, thank you for taking your time to answer me. Eventually there will be a solution. In life, there are only solutions, problems are temporary.

[Ratslinger]

I don’t get it. It is just refreshing the form after entering a record.
.
Not certain you need to “build” anything. If you cannot adopt the routine given you already (seems very simple to me), will look at this probably tomorrow. It is nothing more that refreshing a form after a new (at least that is what you have stated) record is entered. I am certain there is one or more posts here that accomplish that.
.
Edit:
After a bit more consideration, the macro is not a way to go. There is a button on the table grid for Refresh.
.
The problem is the list box. What you see is not what you have. You see the wording but in the record is actually a record ID which if sorted will not make sense (maybe what you have?). Have used a Query in the past to get around this. It is posted somewhere here but will look for it when I have some available time tomorrow.

[Villeroy]

As always, the answer is proper SQL. Since we do not even know which database you are using, I assume embedded HSQL.

@Renel

The list in the list box is perfectly well sorted. However the rows of this column in the control table are not correctly sorted. It is probably two different things and it is possible to exercise a sort for these rows.

Having a “Data” table with foreign key “PID” linking to “Persons”.“ID” you can sort the PIDs by the persons’ names.

SELECT "Data".* 
FROM "Data" 
ORDER BY(
  SELECT "N" 
  FROM "Persons" 
  WHERE "Persons"."ID"="Data"."PID"
) ASC

You can test the above statement with this database of mine: https://ask.libreoffice.org/uploads/short-url/8nC9OrU7HOQpQmQHg1eZLpkq415.odb

[Renel]

Hello Villeroy,

I am using LibreOffice 7.3.2.2 x64 suite. I don’t have to connect to a server or anything. I simply created a DB from this suite. I have no idea what kind of SQL language is used for this suite in Base. Also, I had never programmed a database before the beginning of the summer. The idea took me to do this without really knowing how it worked. Honestly, I find it exciting and I want to refine this DB which is only useful for myself.

I send the DB example (OpenForm From TableControl List) which demonstrates very well what I would like to do. In the form “Actors” in the first record, that of “Al Pacino” we can see a list of films where he played. In the list box, the movies are sorted alphabetically. However, in the control table, the rows which are in fact a sequence of list boxes are not sorted. I would like these to be sorted despite the additions that may be made later.

OpenForm From TableControl List v2.odb (26.3 KB)

Currently, I’m wondering if this was the right way to do it in order to have a recording of an actor where we can see a list of movies associated with him. However, he still needs to have a tool to select a film that comes to him from another table. Here is where I am.

[Villeroy]

It is absolutely necessary to know the actual database software that is used with Base. LibreOffice can generate 2 types of embedded databases. Both embedded databases are “installed” temporarily from the document when you access the database. The database software you are dealing with is always indicated in the status bar of your document window.

However, the vast majority of database documents is connected to some spreadsheet because this is what everybody uses as a (rather problematic) source for mail merge:

Most likely you are using embedded HSQL which is documented on Chapter 9. SQL Syntax
This 16 year old version 1.8 of HSQL is fairly simple and complying with SQL standards. Although tailored around this particular HSQL 1.8, Base has not implemented all the features that this database offers. Apart from that, certain things are way easier and faster do do with SQL statements.

One example: Base can set the default value of some field to constant values. For instance, you may set 2022-07-01 as default value for a date field when the date is missing in a new record. However, Base can not set today’s date as a “dynamic default value” nor the current time nor the current date-time as timestamp.
Solution: call menu:Tools>SQL…

ALTER TABLE "T" ALTER COLUMN "MyTimeStamp" SET DEFAULT CURRENT_TIMSTAMP

Always call menuView>Refresh tables after modifying the database with the SQL dialog.
From now on every new record in table T gets the current timestamp in column “MyTimeStamp” unless some value has been given.

[Villeroy]

OpenForm From TableControl List v3.odb (25.6 KB)
I modified your many-to-many form and removed all macro trash. First things first.

[Renel]

Villeroy,

Here is a 2B version of my example. This one contains text corresponding to the thoughts I currently have regarding this impossibility of sorting a control table.

OpenForm From TableControlList v2-B.odb (28.2 KB)

[Ratslinger]

@Renel
Had not posted further since @Villeroy had given you the SQL for the sequencing. Obviously you have not adapted it.
.
Using that code for a basis, created this SQL and used it on your SelectedFilms sub form:

SELECT "jtblActorsFilms".*, "FilmName" 
FROM "jtblActorsFilms"
Left Join  (SELECT "FilmName", "IDFilm" FROM "tblFilms")a 
  on "a"."IDFilm"="jtblActorsFilms"."jIDFilms"
ORDER BY "FilmName"

.
resulting in the form looking as:

Can add/delete/change records and just press the refresh button to get the correct sequence:

[Renel]

I am really sorry. I don’t think I understood at all what Villeroy was proposing to me. I owe you both a lot of gratitude. As I mentioned before, this is my first database. I have a lot of shortcomings, but I like to learn. However, you never learn alone, mutual aid is always a guarantee of success.

Thanks to you two!

[Ratslinger]

@Renel
Just out of curiosity, on my posts you mark your comment as the solution but on other posts you mark the one giving the actual solution. Why?
.
Also, if you don’t understand what is proposed, ask. Also you would benefit a lot from going through the Base documentation.

[Renel]

Hmm! I saw at the bottom of the answer that I sent you the Solution with a checkbox. I just checked. But I understand that I should have checked your answer. I’ve made the same mistake on other occasions.
.

You are right, I should have asked to have an answer to my misunderstanding. Regarding the documentation, I started to read, but there is so much to learn: SQL, relations, understanding the tools available, Basic and macros, UNO services, API… I embarked on this adventure at the beginning of the summer, so I think I’ll have a few months to sort it all out. Despite everything, I want a DB which is functional. That’s not bad.
.
Time, patience and rigor.